Mysqldump without password in crontab

Question

I try to backup my database with mysqldump and cronjobs.

Well, I added the following command to the crontab of user root:

*/30 * * * * mysqldump -u root -pVERYSECUREPASSWORD --all-databases > /var/www/cloud/dump_komplett.sql &> /dev/null

This works fine so far, but the problem is that the password is set in this command.

So I want to include a .database.cnf file that look like this

[mysqldump]
user=root
password=VERYSECUREPASSWORD

and changed the mysqldump command to

mysqldump --defaults-extra-file="/var/crons/mysql/.database.cnf" --all-databases -u root > /var/www/cloud/dump_komplett.sql

to solve this problem.

But this command fails with the error:

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

I don't know what's wrong.

Here are some commands I also tried:

mysqldump --defaults-extra-file="/var/crons/mysql/.database.cnf" --all-databases > /var/www/cloud/dump_komplett.sql
mysqldump --defaults-file="/var/crons/mysql/.database.cnf" --all-databases > /var/www/cloud/dump_komplett.sql
mysqldump --defaults-file="/var/crons/mysql/.database.cnf" --all-databases -u root > /var/www/cloud/dump_komplett.sql

and .database.cnf contents I also tried:

[client]
user=root
password=VERYSECUREPASSWORD

[mysqldump]
host=localhost
user=root
password=VERYSECUREPASSWORD

[client]
host=localhost
user=root
password=VERYSECUREPASSWORD

I tried any of these commands from command line, too. (Not only crontab) — Maarkoize, Nov 12 '13 at 11:36
Thanks for the Link, the user has to be specified in the command and not in the file with the "u" parameter. — Maarkoize, Nov 18 '13 at 15:42

score 9 · Answer 1 · answered Dec 13 '14 at 13:35

9

I found out that password should be between quotes

[client]
user=root
password="VERYSECUREPASSWORD"

Took me a while to figure out why it didn't work with passwords with lots of non alfanumeric symbols

answered Dec 13 '14 at 13:35

NomadHack

91
1
1

Use quotes in long passwords with @#%^! characters in the password – NomadHack Dec 13 '14 at 14:06

score 7 · Accepted Answer · edited May 23 '17 at 11:53

7

The user has to be specified in the command and not in the file with the u parameter.

For more details on scheduling cron jobs using mysqldump, check this answer

edited May 23 '17 at 11:53

Community

1
1

answered Nov 18 '13 at 15:52

Anshul Goyal

73,278
37
149
186

Mysqldump without password in crontab

2 Answers2