I needed a restore solution for my database before launch. I found automysqlbackup which does everything I need. Installation took a bit of trial and error, so here's what works:
curl http://tiny.cc/ey3ap -L > automysqlbackup sudo mv automysqlbackup /opt sudo ln /opt/automysqlbackup /usr/bin/automysqlbackup -s sudo chmod +x /usr/bin/automysqlbackup
Now the script is installed, but is not configured. The settings are read from /etc/automysqlbackup/automysqlbackup.conf, and a sample is inside the script itself, so:
sudo vim automysqlbackup /START CFG ma /END CFG :'a,. y :e /etc/automysqlbackup/automysqlbackup.conf p ggVG:norm 0dw :bn :bd :x
All that's left to do here is fill out the information requested by the script and save the file. I wanted the backups written to /var/backups/mysql, so I took a moment to set up the permissions for that.
sudo mkdir /var/backups/mysql sudo chgrp webdev mysql sudo chmod 770 mysql
I also noticed that automysqlbackup takes a password variable, but uses mysqldump internally. The man page of mysqldump recommends against passing passwords through the shell. Since this is due to a security issue with ps, I wanted to use the recommended method, a configuration file. I found the dbdump() function in the source of automysqlbackup (line 506 for me), and added a --defaults-file option to the mysqldump call. Due to a bug? this must be the first option given.
# Database dump function
dbdump () {
${MYSQLDUMP} --defaults-file=/home/dylan/.autobackup.cnf --user=${USERNAME} --host=${DBHOST} ${OPT} ${1} > ${2}
return $?
}
Then finish up by creating your .cnf file wherever you chose.
[client] password=your_password
At this point, the automysqlbackup script is all set to go, but will still get rejected by MySQL. There is no reason to that automysqlbackup needs to do anything but read the database to back it up, so create a new user constrained to the loopback address with these rights alone. Make sure this user is set in automysqlbackup.cnf.
CREATE USER 'autobackup'@'localhost' IDENTIFIED BY 'password'; GRANT select, lock tables ON *.* TO 'autobackup'@'localhost';
All that's left is to schedule it, so toss it into cron.
crontab -e Go@daily automysqlbackup<Esc>