2

I'm aware that there are dozens of questions similar to this, but it seems none has a definitive answer to my problem, so that's why I'm posting this... I hope in the right place.

The problem:

I have a script placed in /etc/cron.daily that performs a daily database backup among the other things. It works fine as long as there is a password hardcoded into the script for the mysqldump command.

#!/bin/sh
$ mysqldump -u [uname] -p[pass] db_name > db_backup.sql

However, not wanting to have the password in the script, I've set up ~/.my.cnf file (chmod 600) with my user's password stored there so the mysqldump command in the script would be passwordless.

~/.my.cnf    
[mysqldump]
password="pass"

#!/bin/sh    
$ mysqldump -u [uname] db_name > db_backup.sql

When I run this new script manually from the command line as root it works like a charm.

sudo sh /etc/cron.daily/daily-backup-script

But when cron wants to run it it's unable to dump the database giving the following error:

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

So, I assume cron doesn't have appropriate privilege to perform the passwordless mysqldymp command in the script, with password placed in ~/.my.cnf, however the script and the passwordless mysqldump command IN IT are working flawlessly from the command line with sudo.

Effort so far:

  1. I've tried sudo in front of the mysqldump command in the script.
  2. I've tried sudo -u user in front of the mysqldump command in the script.
  3. I've chown-ed the ~/.my.cnf file as root:root.
4

Solved with the help of this answer.

cron doesn't know the path to ~/.my.cnf, so as per the MYSQL documentation you need to specify the path to the .my.cnf file.

The proper passwordless mysqldump command in the shell script triggered via cron should be:

$ mysqldump --defaults-extra-file=/path/to/.my.cnf -u [uname] db_name > db_backup.sql

Works like a charm.

  • What's quite intriguing is that I just ran into this problem not with cron but with bacula (post backup commands and here a script); my /etc/cron.daily script that backs up all databases had no problem finding the .my.cnf file. This threw me off and so after the bacula failure I made sure. Yes. It's a new install that made me notice it; whereas in the more recent versions of bacula on my Fedora box it would report a failed backup the older version in CentOS did not so I never noticed it ... and since I did full mysql backups nightly it wasn't a real issue I suppose but still. Ta for the fix! – Pryftan May 25 at 15:43

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.