Monthly Archives: April 2009

Using the Shell and Cron to Automate Your MySQL Backups

This little shell script has proven to be a handy way to backup my local databases:

#!/bin/bash
cd '/folder/to/store/your/backups/'
for x in db-name-a db-name-b db-name-c
do
   mysqldump --user=username --password=pass $x --lock-tables --add-drop-table > $x`date +%u`.sql;
done

So what’s up with this little snippet? The first line with the ‘cd’ commad, we’re just changing into the directory where all the sql dumps will be stored. I also store this little script in there (backup.sh).

The next line is a basic loop. we list all of the local database names we want to backup, right after the “for x in” phrase, separated by a space.  The indented line is what actually dumps, names, and saves the file. On my local development computer, I just pass in the root username and pass, so it will work for each database. Each backup file then follows the “db-name” + “day of week” naming convention. For example, db-name-a1.sql would be Monday’s backup, db-name-a2.sql would be Tuesday’s backup, etc. A week’s worth of backups is fine for me, though you can customize this to your liking.

And the command line, you can test if this is working like so (assuming you are in the same directory as the file):

$ sh db-backup.sh

Not Working?

If it’s not working, a couple things may be happening. You can alter the script with more conditionals to output some general errors, but OS X will actually mail you a detailed copy of the error. Getting at it can be a little cryptic though. At the prompt, type mail:

$ mail

If you see a message like “You have mail in /var/mail/your-username” then you should check it out. You can use the terminal to check it (man mail to check the manual entry for mail). Or, what I tend to do is just open the entire mail file in my text editor, which is TextMate – using the command line tool:

$ mate /var/mail/your-username

Some juicy debugging info may be in there. You can delete everything in the file and save it if  you like. This will get rid of the “You have mail in…” message whenever you start up terminal.

Automating It

I have this script run everyday at 3pm – I do that with a cron job, using Cronnix as a graphical interface:

cronnix

One problem you may have is that your crontab’s path is not necessarily the same as your user account’s shell path. For example, if you are seeing an error like “mysqldump – command not found” – simply go back to the terminal, and enter “which mysqldump”:

$ which mysqldump

It will output the full path to this command. Copy that path and paste the full path into your script. So for example, the line following the “do” command may look like:

/Applications/MAMP/Library/bin/mysqldump --user=user --password=pass $x --lock-tables --add-drop-table > $x`date +%u`.sql;

I use the MAMP version of mysql dump, so I need to tell the script exactly what version to use.

From here, I use SVN to keep this folder under version control as well, which makes switching from the desktop to the laptop much easier when developing. Cheers!