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

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 2

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 3

On FreeBSD you can add the following:

security.bsd.see_other_uids=0

To /etc/sysctl.conf and reboot, or use

sysctl security.bsd.see_other_uids=0

To set the sysctl value.

Now users other than root are only able to view their own processes. So putting the password in the command line is less risky.

Also, how are running crontab? Did you add it to the root users crontab by using crontab -e -u root, or did you add it to /etc/crontab?

Verify that the right permissions are set on the .my.cnf as well as what environment variables are set by crontab as that may cause it to look in a different location than your home directory (which for root on FreeBSD is /root).

Answer 4

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).