I’m the first one to admit I’m lazy when it comes to maintaining a local copy of databases I work with. So what I decided to do was make my Mac work for me while I’m sleeping.

I’ve setup a series of daily commands to run as cron jobs on my iMac to do the boring things I spend time on every morning, thus saving me an hour of waiting around for downloads, decompressing tars and imports.

15 1 * * * scp user@hostname:/path/to/backup/dbname_`/bin/date +\%Y\%m\%d`.sql.gz /localpath/to/backups
45 1 * * * gzip -d /localpath/to/backups/dbname_`/bin/date +\%Y\%m\%d`.sql.gz
45 4 * * * mysql -u dbuser -pPASSWORD dbname -e "show tables" | grep -v Tables_in | grep -v "+" | gawk '{print "drop table " $1 ";"}' | mysql -u dbuser -pPASSWORD dbname
50 4 * * * mysql -u dbuser -pPASSWORD dbname < /localpath/to/backups/dbname_`/bin/date +\%Y\%m\%d`.sql

To break each step down..

15 1 * * * scp user@hostname:/path/to/backup/dbname_`/bin/date +\%Y\%m\%d`.sql.gz /localpath/to/backups

I'm downloading a daily mysql dump file (dbname_yyyymmdd.sql.gz) via scp over a trusted connection I've setup between the server and my iMac.

45 1 * * * gzip -d /localpath/to/backups/dbname_`/bin/date +\%Y\%m\%d`.sql.gz

Next, I'm decompressing the mysql dump file so I can use it for importing.

45 4 * * * mysql -u dbuser -pPASSWORD dbname -e "show tables" | grep -v Tables_in | grep -v "+" | gawk '{print "drop table " $1 ";"}' | mysql -u dbuser -pPASSWORD dbname

Now I'm preparing my existing database for the import; the reason I don't drop and recreate the database is that I have stored procedures and other objects I don't want to remove. This step is slightly insecure since I'm storing my mysql password in cron, but since this is just a development server on my local machine, I'm not worried about it.

50 4 * * * mysql -u dbuser -pPASSWORD dbname < /localpath/to/backups/dbname_`/bin/date +\%Y\%m\%d`.sql

Finally, I'm importing the daily mysql dump file into my freshly truncated database, and everything is synced and ready to go for the day.