A WordPress installation consists of both files and a MySQL database. To prevent data loss, it is essential that you backup both the files and the database.

There are many free and commercial WordPress plugins that can do this for you. For instance, search for “backup” from the Install Plugins page of your WordPress installation.

However, to have full control over the process, I decided to make my own backup system. This guide describes the main parts:

• How to backup the files
• How to backup the database
• How to delete old files

As you will learn below, I use the following tools to carry out the task:

1. Transmit from panic.com to sync files between my remote WordPress installation and a local folder on my Mac.
2. Hazel from Noodlesoft to fetch and rename database backups and to purge old files from the backup folder.
3. The OS X Automator to control Transmit during the syncing of the WordPress files.
4. A Linux system, with MySQL installed, to do the database dumps from the remote WordPress installation.

With this short introduction, it is time for the detailed description.

How to backup the files

With Hazel a sync process is set up to run once a day. Hazel launches an Automator workflow that only consists of a Transmit sync task. At a later time, also once a day, Hazel checks if any of the locally synced files have changed during the last 24 hours. If so, the changed folder is archived. The use of the Automator and Transmit combination for backing up WordPress files is inspired by the post “Automating WordPress Backups using Automator and Transmit” at Bits of Tech.

Setting up Automator and Transmit

To setup the Automator sequence, follow these steps:

1. Start Transmit and make your blog’s site a Favorite site. You make your connection a Favorite by dragging it and dropping it on the Favorites node in the left sidebar (c.f. the figure below).
   
2. Start Automator and select Workflow as the document type.
3. Add a Transmit Synchronize action, by dragging it from the left Internet Library category to the right Automator window.
4. The sync task is setup by defining  the favorite site that you defined in step 1, setting the Sync Direction to Download and the Local Path to the local folder where you want to store the synchronized copy of your WordPress site. Check the Delete Orphaned Destinations Items box to remove local items that don’t exists in your remote WordPress site. The figure below shows my setup.
   
5. The workflow is then finished by adding a Quit Application task, to end the Transmit application. Below you find the total Automator workflow.
   
6. Save the workflow on your Mac as a Workflow.

Syncing the WordPress files

Hazel is then used to launch the Automator workflow once a day. The Hazel rule is defined by the following steps:

1. Launch the Hazel settings pane, by selecting Open Hazel… from the Hazel icon in the menu bar or by opening the Hazel settings from the System Preferences application.
2. Add the parent folder to the Local Folder (named sync above) that you defined in the Automator Transmit Synchronize task to the left Folders window. The sync folder  is a sub-folder to a folder called backup in my setup. Thus, add backup to the Folders window.
3. For the backup folder, add a rule in the right Rules window, by clicking the plus button. Add the following conditions for the rule:
   1. Set the time when the rule should run each day.
   2. Make sure that there is a folder called sync in the folder by setting checking for the Folder kind and the name sync.
4. Add an Automator workflow action, where you select the previously defined Automator Workflow. Below you find the complete Hazel rule.
   

Creating archives of modified folders

To save some history, the following Hazel rule creates archives if any synced files has been modified during the last day. The rule is defined by the following steps:

1. Add a folder in the left folder window. In this case select the Local Folder, in my case called sync, that was previously defined in the Automator Transmit sync workflow.

2. Add a rule for the sync folder, by clicking the plus sign in the right Rules window. Add the following conditions and actions for the rule:

   1. Set the time when the rule should be run. Allow for sufficient time that the previous synchronization task has completed.

   2. Make sure that only folders are recognized, by setting Kind to Folder.

   3. Add the following Unix command that returns true if any sub-folder has been modified during the last 24 hours:

      find $1 -type f -mtime 1
      

      Run man find from a Mac Terminal to get more information about the find command. ‘$1’ is replaced by the folder satisfying the conditions defined in step A and B above.

   4. Copy the folder to the parent folder called backup.

   5. Archive the copied folder.

   6. Rename the archive by adding a modification time stamp to the archive file name. In my case, the WordPress files are included in a folder called public_html below the sync folder. Thus, the archive will get a name similar to public_html_2012–05–18_040003.zip. Below you find the Hazel rule for the sync folder. The date format is defined by clicking the little date modified selector and then selecting the Edit Date Pattern… from the menu obtained by clicking the small down arrow on the date modified selector.
      

How to back up the database

To backup the MySQL database I use a Linux machine, since I don’t have MySQL installed on my Mac. The command to perform the actual database backup is called mysqldump.

Dump the database to the Linux machine

Follow the steps below to perform the backup:

1. Create a file called .my.cnf in your home directory on the Linux machine, to store your database password. Thus, with your favorite text editor create the file.

2. Insert the following text in the file:

   [mysqldump]
   user = user_name
   password = pass_word
   

   Replace user_name and pass_word with the particular information you have received from your website provider.

3. Save the file.

4. Make sure that no one else can read the file, by issuing the command

   chmod 600 .my.cnf
   

5. Create a new file, in my case called ~/bin/backup_ulfolin_se_db, with the following content:

   cd
   mysqldump --host=host_name --port=3306 --user=user_name ulfolin_se_db > tmp.sql 2>&1
   mv tmp.sql ulfolin_se_db.sql
   

   Again, replace host_name and user_name with information you have received from your website provider. ulfolin_se_db is the name of my database. Replace it with the name of your database. The port number 3306 is used be my website provider. Your website provider might use another port number. To prevent triggering premature Hazel actions on the database dump file, I first dump the database to a temporary file called tmp.sql and then renames that file to a name that will trigger Hazel actions. The first command, cd, just makes sure that you are performing all statements from your home folder on the Linux machine. Note that the mysqldump statement is a long one and might be displayed above on two lines.

6. Save the file and close the editor.

7. Create a crontab entry by issuing the command

   crontab -e
   

   This will bring up your text editor with your crontab file.

8. Add the following line

   0 4 * * * ~/bin/backup_ulfolin_se_db
   

   This will make sure the file you created above is run every day at four o’clock in the morning.

9. Save the file and close the text editor.

Copy the database dump to your Mac

The following setup works if you have your Linux home directory mounted to your Mac with write access. Then Hazel can watch the Linux home directory for the file ulfolin_se_db.sql and move it to the previously defined backup folder, when the file is available. Thus, define the following Hazel rule:

1. Add your mounted Linux home directory as a folder that Hazel should watch (this folder is called uol in my case).
2. Add the condition that the Full Name is ulfolin_se_db.sql (or whatever you chose to call you database dump file).
3. Move the file to the previously defined backup folder .
4. As for the WordPress file archives, add a modification time stamp to the file name.

The complete Hazel rule is found below.

Delete old files

Finally, create a Hazel rule that deletes old files from your backup folder.

1. Select the backup folder in the left Hazel Folders window. You have already created the sync rule for that folder.
2. Add a condition that Date Last Modified is not in the last 2 months (or whatever retention period that you want).
3. Add the action to Move to folder Trash.

The complete Hazel rule is found below.