1

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?

3

What you need to do is set your schedule line as follows

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

Note the "user" column. Also add the HOME entry before it for whatever is relevant for your login-path

  • One can also set "HOME=/root" within the script /home/username/mysqldump-daily. – Stefan Lasiewski Jun 15 '15 at 20:48
2

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 [...]

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.