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

Solution 1

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

Solution 2

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

Comments

[Anshul Goyal]

Check this out stackoverflow.com/a/6861458/1860929