CODEWITHSUNDEEP
mysqlbashshell
Andrei Herford
Andrei Herford

Reputation: 18729

mysqldump access denied when being used in bash script

I am trying to create a bash script that uses mysqldump to create a backup of the database that is specified as parameter. However mysqldump fails with an access denied error. Using the same command directly (copying it to the shell an executing it) works without any problem.

#!/bin/bash

# ... use parameters to get db name and password
# build the mysqldump command and execute it...

command="mysqldump -alv -h127.0.0.3 --default-character-set=utf8 -u ${database} -p'${pw}' --extended-insert ${database} | gzip > ${path}"
echo "$command"
echo ""
$command

This gives me the following output:

$ ./dbbak DBUSER DBNAME PASSWORD
mysqldump -alv -h127.0.0.3 --default-character-set=utf8 -u DBUSER -p'PASSWORD' --extended-insert DBNAME | gzip > /path/to/backup/backup.sql.gz

Warning: Using a password on the command line interface can be insecure.
-- Connecting to 127.0.0.3...
mysqldump: Got error: 1045: Access denied for user 'DBUSER'@'localhost' (using password: YES) when trying to connect

As said before: When I copy the echoed mysqldump command and execute it directly, the backup works just fine.

What is the problem here? Since the command is executed correctly when being used manually all parameters (password, username, etc.) seem to be correct. Additionally the bash script is executed with the same user account as the manual command.

So why does the manual execution work while the bash script fails?

EDIT:

As Jens pointed out in his comment, removing the quotes from the password will solve the problem. ...-p${pw}... will work, BUT this will also lead to a new problem, if the password contains special characters like $ < > ...

I assume that the problem with the quotes is how bash parses the string. Meanwhile I found some docs that say, that it is a bad habit to store commands in variables and execute them. Instead one should execute commands directly. However the following does not work as well:

result=$(mysqldump -alv -h127.0.0.3 --default-character-set=utf8 -u ${database} -p'${pw}' --extended-insert ${database} | gzip > ${path})

When executing this with bash -x dbbak the output shows the problem:

...
++ mysqldump -alv -h127.0.0.3 --default-character-set=utf8 -u DBUSER '-p'\''DBPASS'\''' --extended-insert DBNAME

While I do understand why the quotes around DBPASS are added ('DBPASS' --> \''DBPASS'\'), I do not understand why there are also quotes around-p`.

How do I get rid of these quotes when executing the command?

Upvotes: 1

Views: 1589

Answers (3)

insign
insign

Reputation: 5773

To remove the single quotes around the password solved for me.

Upvotes: 0

kiddorails
kiddorails

Reputation: 13014

Configure it by .cnf file and provide it in --defaults-file

mysqldump --defaults-file=~/my_mysql.cnf db table > table.sql

In ~/my_msyql.cnf

[mysqldump]
user=user_name
password=my_password
host=my_host

This is also safe if you version this. You can save my_mysql.cnf differently per environment.

Upvotes: 0

Jiri Valenta
Jiri Valenta

Reputation: 540

You can either:

  • store the password in an environment variable MYSQL_PWD
  • store the password in a plain-text file .my.cnf which you need to put into the home directory of the user that executes the script
  • use the mysql_config_editor utility to store the password in an encrypted file

The first one is the easiest to use/implement but obviously the least secure.

I recommend to take a look at the documentation where all the possibilities are described. ;)

Upvotes: 1

Related Questions