I recently started using the build-in MySQL database server on my Leopard server. I’m collecting user login data in one database and SMB and AFP login information in another. Nothing major but information I wouldn’t really want to lose. I was looking for an easy way to backup these databases when a post on the MacEnterprise mailing list asked the same thing. Several people suggested AutoMySQLBackup, an open source shell script. After checking into it I can say it is an incredibly easy way of backing up all your data. The author has done a great service by posting this script.

The script backups up the databases to the local drive but can also email the backups to you. I wanted them emailed so I could archive them and have them backed up there as well. That way I don’t have to come up with any other scripts to move the backups some where else.

The AutoMySQLBackup script requires that you have Mutt installed if you want to have it email anything. Mutt is an command line email program that does not come pre-installed on OS X. Here are the steps I went through to get everything up and running.

Step 1 – Install Mutt

There are instructions on the Mutt website for installing the program but I wanted something I could easily update without a lot of hassle. I decided to install using MacPorts.

Download and install the latest version of MacPorts. There is excellent documentation on the web page. You’ll need the Apple Developer’s Tools installed before installing MacPorts. If you don’t already have them you can install them from your OS X install disc or download them from the Apple Developer Connection site.

Once MacPorts is installed an running you’re ready to install Mutt. At the time of this writing the default version of Mutt in the MacPorts repository is 1.4.2. I wanted the 1.5.x version as I had read that it was much easier to configure. Check the MacPorts list of available ports before you install. To get the 1.5.x version I had to install the development version.

In Terminal type the following:

sudo port install mutt-devel +smtp +ssl +imap +pop

If you want the standard install of mutt enter this:

sudo port install mutt +smtp +ssl +imap +pop

Then go do something else for a while as it downloads and compiles everything. After a bit you’ll have an install of mutt.

Step 2 – Configure Mutt

With the 1.5.x version of Mutt I only had to make one configuration file. In the home directory root of the account you are running the script from make a “.muttrc” file.

touch .muttrc

Now, use your favorite editor (I prefer pico) and add the address of the SMTP server you plan to use:

set smtp_url="smtp://my.smtp.server.com"

Now try sending an email from Mutt in Terminal and make sure everything is working correctly.

Step 3 – Configuring AutoMySQLBackup

Download AutoMySQLBackup and put it where ever you put your scripts. I changed the permission so that only the account I was running it from had any access.

chmod 700 automysqlbackup.sh

Open the script in your editor of choice. Don’t use Word or other such editors as they will mess up your line returns. Use a command-line editor or a GUI editor like SubEthaEdit that understands UNIX line returns.

The script author has great instructions right in the script so I won’t cover those here. I did, however, have to make two changes to the script to get things to work.

First, I had to add in the path to the MacPorts installation in the path variable for the script. That was on line 338.

The original reads:

PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/mysql/bin

Edit it to look like this:

PATH=/opt/local/bin:/opt/local/sbin:/usr/local/bin:/usr/bin:/bin:/usr/local/mysql/bin

Remember, all the MacPorts installations live in /opt/local. Now the script can find Mutt.

The second change I had to make was to the order of items that the script was sending to Mutt. It just didn’t work in 1.5.x as written. This is on line 644 of the script.

The original reads:

mutt -s "$ERRORNOTE MySQL Backup Log and SQL Files for $HOST - $DATE" $BACKUPFILES $MAILADDR < $LOGFILE

I had to switch the order of $BACKUPFILES and $MAILADDR to get it send the file to me. So my edited version looks like this:

mutt -s "$ERRORNOTE MySQL Backup Log and SQL Files for $HOST - $DATE" $MAILADDR $BACKUPFILES < $LOGFILE

After making that switch and running the script it backed up my databases and emailed me the backup files as well. Fantastic!

Schedule your script to run on a nightly basis so you get regular backups of everything. You can either do that via cron or via a launchd item. If you want to use cron and don’t want to do it at the command line I recommend Cronnix. For launchd I recommend Lingon

One last note. When I was trying to troubleshoot why the attachments weren’t being send I couldn’t find the log files that said what was happening. That’s because the automysqlbackup script puts them in the script and then deletes them along with everything else after it mails them. To disable that function I had to comment out these two lines at the very end of the script:

eval rm -f "$LOGFILE"
eval rm -f "$LOGERR"

Once I had things working I uncommented them so things would continue to be cleaned up.

:: Dec.14.2009 :: Scripts, Server, System, Terminal :: Comments Off on Backing up MySQL databases on an OS X Server