3

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?

1

2 Answers 2

4

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

1
  • One can also set "HOME=/root" within the script /home/username/mysqldump-daily. Jun 15, 2015 at 20:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .