Backing Up A MySQL Database with AutoMySQLBackup

Are you looking to backup your MySQL database? My site runs with MySQL as its backend database, and as such I wanted a backup script I can just drop in.

I have found a great open source utility called AutoMySQLBackup which can be downloaded from SourceForge.net. There is no need to reinvent the wheel writing your own, this one works extremely well.

Here are the steps I followed to configure this script.

1) Download the script from the AutoMySQLBackup site on sourceforge. My examples are based off of version 2.5 of this script.

2) I renamed the file I downloaded, automysqlbackup.sh.2.5 to automysqlbackup.sh.

mv automysqlbackup.sh.2.5 automysqlbackup.sh

3) Next set execute permissions on this script.

chmod 755 automysqlbackup.sh

4) Now we configure the script. Open the script in your favorite editor. I prefer to use vi. You need to set a few key variables towards the top of the script. The variables I set are:

USERNAME=root
PASSWORD=
DBNAMES="drupal"
BACKUPDIR="/u01/backups/mysql"
MAILCONTENT="log"
MAILADDR="emailaddress@somedomain.com"

Note: You'll have to create the directory specified in BACKUPDIR. Also, I selected to use the root user to login to my MySQL database with. Ensure that you can login to your MySQL database at the command prompt with the userid and password specified in your script. You need to set the MAILCONTENT parameter from "stdout" to "log" to have it actually email you anything. If you leave it stdout, the output when running through cron will just be mailed to the cron user running the script, and the parameter specified for MAILADDR will be ignored.

5) Try to run this script manually. Ensure it runs, and if you get any errors you'll have to correct them. Most errors occur from the user running the script not being able to write to the backup directory you specified, or for the MySQL user specified not being able to log into the database.

./automysqlbackup.sh

If this script runs successfully, your output should look like the following:

======================================================================
AutoMySQLBackup VER 2.5
http://sourceforge.net/projects/automysqlbackup/

Backup of Database Server - as1.filmoregroup.com
======================================================================
Backup Start Time Fri Mar 23 10:05:18 CDT 2007
======================================================================
Daily Backup of Database ( drupal )
Rotating last weeks Backup...


Backup Information for /u01/backups/mysql/daily/drupal/drupal_2007-03-23_10h05m.Friday.sql
         compressed        uncompressed  ratio uncompressed_name
              74134              489282  84.9% /u01/backups/mysql/daily/drupal/drupal_2007-03-23_10h05m.Friday.sql
----------------------------------------------------------------------
Backup End Fri Mar 23 10:05:19 CDT 2007
======================================================================
Total disk space used for backup storage..
Size - Location
108K /u01/backups/mysql

======================================================================
If you find AutoMySQLBackup valuable please make a donation at
http://sourceforge.net/project/project_donations.php?group_id=101066
======================================================================

6) You should now have some new directories created in the directory you specified in the BACKUPDIR parameter in the script.
The contents of your BACKUPDIR directory should look something like mine below:

root@[asdb] > ls -la /u01/backups/mysql

total 20
drwxr-xr-x  5 root root 4096 Mar 23 10:08 .
drwxrwxrwx  4 root root 4096 Mar 23 10:03 ..
drwxr-xr-x  3 root root 4096 Mar 23 10:05 daily
drwxr-xr-x  2 root root 4096 Mar 23 10:05 monthly
drwxr-xr-x  3 root root 4096 Mar 23 10:05 weekly

6) Lastly we setup this script to run from CRON. I am installing this script on a RedHat AS3 system, so the easiest way for me is to just move the script into the /etc/cron.daily directory.