Backing up the MacMini MySQL Database Server

Recently I documented the process of setting up a MacMini as a MySQL database server; this is all now running well so I thought I'd add the second part - Making sure it's backed up!

Creating the backup file

The first part is creating a bash script to use the MySQL mysqldump command to dump all the databases on the server in to one file:

Create a new file somewhere convenient (at the root of your user directory is a good place) on the server named backup-script.sh and open it in a text editor.

The contents of the file can be found on GitHub Gist. It's pretty self explanatory but you'll need to change line 11 to be your local backup location and the other area you will want to change is on line 23:

# Move it to a backup location server
cp "/Users/$USER/Backup/MySQL/mysql_dump_$DATE.sql.gz""/Volumes/General/Backups/mysql_dump_$DATE.sql.gz"

This block copies the file from the original location to a new location. My recommendation is that this location should be somewhere on a different computer and one ideally that is backed up offsite (perhaps a dropbox directory)

Running the task regularly

Ok, so now we can create a backup we need to automate it. The easiest way (that I've found) is to use a Cron task. Open up Terminal and type:

$ sudo crontab -e;

This will open the cron task in an editor (VIM in my case). There are plenty of websites describing how Cron timings work so I won't bother. I'll just explain that this is what we use to trigger the bash script at 1am every morning.

0 1 * * * sudo sh /Users/user_name/backup-script.sh

You'll need to change user_name to be the username in question and make sure that this is the full path to the script created. Save the file and exit.