Ubuntu – How to get thesqldump to use the credentials from thesql_config_editor in a crontab-executed script


I am setting up a daily backup of a MySQL 5.6.14 database using mysqldump on an Ubuntu 12.04 Precise server.

I created the credentials file in the root home directory (.mylogin.cnf) according to the instructions for mysql_config_editor.

I then created a shell script to perform the backup:

echo "$(date +'%Y%m%d %k%M%S'): Starting daily backup." >> /path/to/backup/dump.log
/opt/mysql/server-5.6/bin/mysqldump --login-path=localroot --databases mydatabase --single-transaction > /path/to/backup/daily.sql 2>> /path/to/backup/dump.log
echo "$(date +'%Y%m%d %k%M%S'): Finished daily backup." >> /path/to/backup/dump.log

(The script has been chmod 755.)

If I execute this script as root, it works as expected.

I created a crontab entry in the root's crontab using sudo crontab -e:

# m     h       dom     mon     dow     command
15      0       *       *       *       /home/username/mysqldump-daily

The problem is that the output log (dump.log) shows that the script tried to use incorrect credentials:

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

Suffice to say, my root user is not named 'root' so I know that it is not using the credentials stored in the .mylogin.cnf file, nor is it using the actual root user account.

I'm not sure why this is happening, but I suspect it has something to do with cron not being given access to the root user home path, or that it's executing the script as a different user. I thought editing the root's crontab (rather than putting the script into /etc/cron.daily/ for example, which I've tried) would work.

I don't want to include the password in the script for obvious reasons, which is why I am trying to employ the encrypted file.

How can I get the cron-executed script to successfully utilize the credentials I'm specifying?

Best Answer

I had the same problem with mysqldump 5.7.13 in a script launched by crontab. You can set the .mylogin.cnf file location using the MYSQL_TEST_LOGIN_FILE environment variable before calling the mysqldump command :

export MYSQL_TEST_LOGIN_FILE=/root/.mylogin.cnf
mysqldump --login-path=mysqldump [...]
Related Question