Secure MySQL backup cron job – my.cnf is not being read

Question

I have tried to make backup cron job on my webserver running FreeBSD. Currently it looks something like this:

/usr/local/bin/mysqldump --opt --single-transaction --comments --dump-date --no-autocommit --all-databases --result-file=/var/backups/mysql/all.sql

It works fine when I run it as root (since root has a .my.cnf with the username and password used to connect, but when the job is run by cron, the my.cnf file is not read.

Is there any way around that without having to put username and password into the command itself (since that's kinda insecure)?

Strangely, I have the same setup with PostgreSQL and a .pgpass file, and that works like a charm.

Answer 1

Use the --defaults-extra-file option to tell it where to find the .my.cnf file (assuming it's readable by whichever user is running mysqldump.

Answer 2

Thats weird actually cron should read the .my.cnf. How do you run the cronjob in /etc/crontab or crontab -e? You could try using AutoMySQLBackup which is a small shell script using mysqldump.

Answer 3

I just ran into this as well.

It appears that MySQL is hardcoded to look for '~/.my.cnf', instead of something like '$HOME/.my.cnf'.

On FreeBSD, cronjobs called from /etc/crontab will ignore the tilde '~' character, and therefore will ignore a value like ~/.my.cnf

In fact, the following doesn't work for me at all:

mysql --defaults-extra-file=~/.my.cnf

However, using a $HOME variable does work:

HOME=/home/admin mysql --defaults-extra-file=$HOME/.my.cnf

As an alternative, my cronjob will work if I move it from /etc/crontab to /var/cron/tabs/root (Using 'crontab -e' as root).