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:
#!/bin/bash
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?