Backup for WordPress MySQL Database on MediaTemple

In preparation for developing my own custom theme, and for good best practice, I have automated a backup of the MySQL database hosting this blog from a Linux host sitting behind a firewall at home. I am using the Open Source SourceForge project, AutoMySQLBackUp. I have been using this Unix shell script at my employer for a long time, and it has occasionally come in handy. As backups tend to do… when you least expect it. With my local copy of the PHP code (or even a fresh copy of WordPress) and my customizations–stored in Subversion–I can recreate this entire site with minimal work.

I have more often used these backups as I intend to now–to recreate the site on a separate host, using the original site as content for testing a new template design.

Using AutoMySQLBackUp is quite easy if you have a Linux host you can run it on. In my case, I copied the script to my personal ‘bin’ directory under my user account–no need for this script to run by anyone other than a normal user–and set the configuration variables inside the script. The variables you needs to set are fully described in the file below the lines where you enter them. So, if you have a question, be sure to scroll down. The items you need to configure are:

USERNAME=username
PASSWORD=password
DBHOST=hostname
DBNAMES="all"
BACKUPDIR="/path/to/backups"
MAILADDR="you@yourdomain.com"

The ‘username’ is a MySQL user with SELECT access to the databases you wish to be backed up. The ‘password’ is the related password for that MySQL account. Note, this account is not the same as a host user account–it is exclusive to the MySQL database to which you are connecting. The ‘hostname’ is the fully qualified hostname of the MySQL host. If you are accessing a database at MediaTemple, this would be the address they assign that is prefixed ‘external-‘. Since you are accessing the site from outside your web server, this is classed as external.

You can explicitly specify the database names for DBNAMES, instead of using ‘all’, but by specifying ‘all’ you will automatically get any new databases that are added in the future. None get missed that way.

The BACKUPDIR is the base directory where you would like your backups store–make sure it is writable by the user running this script. I use a folder in my home directory. The MAILADDR is where you would like result emails sent. There are several options regarding these emails, which are described in the file, however, my preferred means of running this script is from a user crontab file which schedules it to be run nightly as the user. This line:

32 4 * * * /home/localuser/bin/automysqlbackup

will run the script daily at 4:32 am. Saving this line in a file named ‘crontab’, I can load a copy of it to the crontab daemon of the system simply with:

crontab crontab

Crontab will automatically capture the output and mail to the user. You may need to configure an alias if you would like it sent to an off-host address.

There are a few more options in AutoMySQLBackup, but most can be left at the defaults. Review as needed for your purposes.

One note on accessing a MediaTemple MySQL host remotely, you must authorize the IP of the host you will be connecting from to connect to the server in your Account Center Control Panel under Manage Databases, on the Global Settings tab. There is a convenient button for using your current IP. All my hosts at home are behind a firewall, and it is the external IP of that firewall that is really seen, and this button worked nicely for getting the current value. Note, if your IP is not static, or at least reasonably stable, you will be needing to reset this whenever it changes. Watch for an error email from the nightly run, and go update it when you receive one. Be sure to remove the old IP for security purposes.

If you are having trouble getting this functioning, be sure you have the MySQL client libraries installed, and try a manual connection to the database like this:

 mysql -h hostname -u username -p

You will be prompted for the password, and should get a prompt as follows:

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2827215 to server version: 4.1.11-Debian_4sarge7
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> 

Enter ‘quit’ to disconnect. Any error messages should give you a clue as to what the problem might be. If you can’t connect:

ERROR 2003 (HY000): Can't connect to MySQL server on 'hostname' (111)

Be sure you have access to the server port 3306 (the standard mysql service port) on the host. If you are on MediaTemple, this error can be caused by not having the IP authorized for access as noted above.