mysqldump access denied when being used in bash script

Question

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?

This works but will lead to problems if the password contains special characters like $. Is there a way to use quotes and make the command work within the bash script? — Andrei Herford
– Andrei Herford, Commented Aug 30, 2016 at 8:35
Yes, the link describes perfectly why there need to be quotes around the password. So the question is the same: How to do I get quotes around the password when executing the command in the bash script? — Andrei Herford
– Andrei Herford, Commented Aug 30, 2016 at 9:01

Jiri Valenta · Accepted Answer · 2016-08-31 18:14:08Z

1

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. ;)

answered Aug 31, 2016 at 18:14

Jiri Valenta

5702 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kiddorails · Accepted Answer · 2016-08-31 18:19:18Z

0

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.

answered Aug 31, 2016 at 18:19

kiddorails

13k2 gold badges34 silver badges42 bronze badges

Comments

insign · Accepted Answer · 2017-12-23 17:29:57Z

0

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

answered Dec 23, 2017 at 17:29

insign

5,8631 gold badge43 silver badges35 bronze badges

Collectives™ on Stack Overflow

mysqldump access denied when being used in bash script

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related