MySQL backups on your Mac
December 17, 2009 | Comments: 0Time Machine is truly a slick and useful tool for maintaining regular backups on your Mac. However if you ever need to restore a MySQL database, trying to do it with Time Machine alone involves a fair bit of ugly hackery, like overriding default MySQL permissions as root on your backup drive, and attempting to restore with raw files rather than cleanly with a mysql load. To make this better, simply create a dbdumps directory which will be backed up by Time Machine, and set up the following script to run daily MySQL dumps on your databases. Then if you need to restore, use Time Machine to restore the dumpfile you want, and execute the dumpfile with mysql. Of course you can do this from the shell with something like:
mysql -u <user> --password=<pass> -h localhost -D <database> < <path_to_dbdumps>/dumpfile.sql
But if you prefer GUI-ness, Sequel Pro has a very nice import command.
Here is the bash script:
#!/bin/bash
#--------------------------------------------------------------------------
# Daily MySQL dump on OSX to use in conjuction with Time Machine
#
# 1) Save this file in some appropriate location like /usr/sbin/dbbackup.sh
# 2) Make sure it is executable: sudo chmod +x /usr/sbin/dbbackup.sh
# 3) Create the backup directory: mkdir ~/dbdumps
# 4) Create the cron entry to run it daily by executing the following:
# sudo echo "47 5 * * * /usr/sbin/dbbackup.sh" >> /usr/lib/cron/tabs/root
# (This will run the script at 05:47 am each day)
#
# December 2009, Andrew vonderLuft. http://avlux.net
#--------------------------------------------------------------------------
# Edit these values for your local configuration
CMD_DUMP=/usr/local/mysql/bin/mysqldump
MYSQLDIR=/usr/local/mysql/data
BACKUPDIR=/Users/avonderluft/dbdumps
MYSQLUSER="admin"
MYSQLPASS="********"
ls -1 $MYSQLDIR | while read db;
do
if [ -d $MYSQLDIR/$db ]; then
echo -n dumping MySQL database $db ...
$CMD_DUMP -u $MYSQLUSER --password=$MYSQLPASS $db > $BACKUPDIR/$db.sql;
echo done.
fi
done