Idiots Guide to Backing-up MySQL on OS X | March 3, 2004
At work we use an old G4 to act as our file and development server. At the end of last week, while synchronising one of our local MySQL databases with the live database, I accidentally overwrote a local table with old data. Luckily we backup so this wasn't a problem. However I thought some of you may find it useful to know how we do this, just incase you ever end up in a similar predicament.
Backing up a local MySQL database on OS X is pretty simple. To do a backup, you make use of the mysqldump command. This simply dumps the contents of the specified databases into a text file as INSERT statements. The below example simply dumps the contents of my clowns database to the current directory.
(I'm using the ¬ symbol to indicate where a line wraps)
mysqldump --user=andy --password=secret¬ clowns > clowns.sql
If you want, you can add a number of parameters to change the way mysqldump behaves. In this example, I'm locking the database before running the dump, adding a drop table and then naming the file based on the day of the week. If I run this command each day, I'll end up storing 7 days of backups locally.
mysqldump --user=andy --password=secret clowns¬ --lock-tables --add-drop-table > clowns`date +%u`.sql
If I wanted, I could name the file based on the actual date and store one file for each day. This would probably make more sense, but storage space is pretty tight, so I'm happy with just the last 7 days.
The next thing to do is make a shell script file. Open up a blank document, name it mysqlbackup.sh and then add the following lines.
#!/bin/sh mysqldump --user=andy --password=secret clowns¬ --lock-tables --add-drop-table > clowns`date +%u`.sql;
In my backup script, I've added a bit of logic and error reporting so I know what's going on. If you wanted, you could also turn things like the username and password into variables to make editing the script easier.
Here is an example of the script I'm using.
#!/bin/sh # echo start message echo "Backup Script Processing" # navigate to backup dir if cd /Users/andy/clownbackups/ then echo "...successfully navigated to backup dir" else echo "could not locate backup directory" exit 0 fi # echo message echo "exporting SQL dump" if #dump the db into a .sql file mysqldump --user=andy --password=secret clowns¬ --lock-tables --add-drop-table > clowns`date +%u`.sql; then #echo success message echo "SQL dump successful" else #echo error message echo "mysqldump error" exit 0 fi
You can test your script out by running it from the terminal using the sh command. You'll see the output echo'd to the screen and if it's been successful, your mysql dump will appear in the directory you specified.
Now this is pretty cool, but nobody wants to open up the terminal and run this script every day before you back-up to disk (you do back-up every day, don't you?).
To get round this, you need to set up a cron job. When I first heard about cron jobs, they filled me with fear (as you can tell, I'm not hugely server savvy). However cron jobs are actually very simple. A cron job is simply a job that you want to automate, so that it runs at a set time. OS X does a number of cron jobs by default, mostly involving cleaning up logs and temporary files. The file that manages the system cron jobs is called crontab (short for cron table) and on OS X, is located at /private/etc.
This file is probably owned by root, so you'll need to sudo pico into it to make any changes. Once open, you'll see that it's a simple simple table of jobs (hence the name). Along the top is the frequency, and down the right hand side is the job to perform. Here is an example of my default crontab file.
# /etc/crontab SHELL=/bin/sh PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin HOME=/var/log # #minute hour mday month wday who command # 15 3 * * * root daily 30 4 * * 6 root weekly 30 5 1 * * root monthly
What we need to do is add a new line to this file that tells OS X to run your back-up script at a set period of time. We usually back-up at 5:30pm so I'm going to run my mysql backup script at 5:15pm and then save the file. If you're not comfortable doing this manually, cronnix is a useful OS X GUI for editing your cron jobs.
15 17 * * * root ¬ sh /private/etc/mysqldump.sh¬ >> /private/etc/cron_error.log 2>&1
In the above example I'm also sending the output to an error log so I can make sure the script is executing correctly. When I run the script, the mysqlbackup file appears in my backup folder. However if I open it, it's empty. Looking at the error log tells me that the mysqldump command couldn't be found. Now this may seem strange as you can run the same shell script from the command line and it works fine. The reason lies in environmental variables.
When you log in to the terminal, one of the first things that happens is OS X goes off and looks for a file called .tcshrc (if you're using tsch) which contains customisation information about the user. One of these bits of information is a path which tells OS X where certain programs are stored. If you run a cron job, you need to add this path information to your crontab. If you look at the above example of my crontab file, you'll see the following text.
What you need to do is add to this the path to mysql. When I do this, my path becomes.
Depending on how you installed MySQL, your path information may be different. If you're not exactly sure where myslq is stored, you could either do a quick locate or have a look in your .tcshrc file (it's quite interesting to see what's in there anyway). With this set, your specified databases should get backed up every day and you'll no longer have to worry about overwriting or loosing data. For more fun, you can change your shell script to backup and download from a remote server.
Word of warning though. I'm no *NIX expert so all the usual disclaimers apply. Don't talk to strangers, always wear clean underwear if your going out, and make sure that you create copies of any system files you're editing. There are probably better ways of accomplishing this, but the above method is pretty simple and quite a good example of what can be done with a little command line tinkering on OS X.
Posted at March 3, 2004 9:48 AM