Backup WordPress to Version Control Automatically

I migrated my WordPress blog to a VPS hosting recently, and after that, the first thing that came to my mind was: Backup.

There were a lot of similar posts on the Internet, but what I found was not good enough for me, so I wrote what I did in this article to help people who want to do the similar things. It would be great if you have better ideas and please feel free to let me know.

The following scripts had been tested on: Ubuntu 13.04 and MySQL 5.5. The directory and scripts may need to be changed for different Linux distributions.

The Problem

First, we need to answer the questions: what do we want to backup, what do not, and where to save the backup.

What do we want to backup? everything not from the setup, which includes:

• Posts, Pages and Comments. They are the most important things that we want to backup.
• Uploaded Media Files. They are the same important with the posts/pages.
• The Installed Themes and Plugins. I don’t want to search, install, and customize them again, especially the colors.

Ok, that’s sounds reasonable, but is there anything you don’t want to backup? Yes, the wp-config.php file, that includes everything about the database. It’s also pretty easy to create a new config file from scratch. So, do not backup the wp-config.php file.

Where to save the backup? It could be copied as a compressed file, but everything is under version control today, and by using version control, we could:

• Track The Changes Easily. We could see the change history, and compare the differences between any two changes.
• Use Less Bandwidth And Disk. Only the different contents are saved
• Choose Free Service Providers. There are a lot of free version control service providers, like BitBucket, GitHub, and more…

So why do I chose git? because It’s powerful, command line friendly and I like it. But it doesn’t really matter, you could use Hg, svn or any others if you like.

Backup to Version Control

Based on the previous part, there are two kinds of things need to be backup in WordPress: Files and Database.

• Files. All the installed WordPress files are in one folder, while the installed plugins and themes are in the wp-content sub folder. To ensure the plugins work with the WordPress , I decided to backup all the files except the wp-config.php file(e.g. It could happen that one old version plugin doesn’t work with the latest WordPress).

  echo 'wp-config.php' > .gitignore

• Database. All the posts, pages, comments and plugin settings are in the database. To have the WordPress files and db files under same repository, we need to put them in same folder, e.g. while the folder of my web root was /usr/share/nginx/html, I created /usr/share/nginx/db folder to save the db scripts. We could backup the database to an sql file by using mysqldump.

  mysqldump --add-drop-database --skip-dump-date --skip-extended-insert \
            --database wordpress > db/wordpress.db.sql

  To keep security, we could put the db username/password to ~/.my.cnf file with mode 0600, and the file looks like:

  [client]
  user=db_username
  password=db_password

OK, everything is ready, we can start to commit it to the git repository. Here’s an example to push it to BitBucket:

git init
git add .
git commit -m "initial backup of the wordpress blog"
git remote add origin ssh://[email protected]/YOUR-ACCOUNT/REPOSITORY.git
git push -u origin --all # pushes up the repo and its refs for the first time

After the push, everything has been saved in the repository.
We still need to make the backup automatically happen, just like the editors which save recovery info for every n minutes.

Run Backup Automatically

We could create a script file /usr/share/nginx/db/backup.sh to run the mysqldump and git commit/push commands.
I also created two log files in case of the script didn’t work.

cd /usr/share/nginx
mysqldump --add-drop-database --skip-dump-date --skip-extended-insert \
          --database wordpress > db/wordpress.db.sql

if [ -n "$(git status --porcelain)" ]; then
  echo "`date`: Changes detected, committing..." >> dbbackup.log
  git add -A
  git commit -m "Backup wordpress database"
  git push
else
  echo "`date`: No changes, stopped." > dbbackup_check.log
fi

Add the log files to .gitignore:

echo 'dbbackup.log' >> .gitignore
echo 'dbbackup_check.log' >> .gitignore

We could use cron job to run the script for backup, so create a new crontab file /etc/cron.d/wordpress_backup, which contains:

*/30 9-23 * * * root /usr/share/nginx/db/backup.sh
0 0-8 * * * root /usr/share/nginx/db/backup.sh

If you are not familiar with the crontab syntax, please reference http://en.wikipedia.org/wiki/Cron for more information. The syntax above (under Ubuntu) could be different with the other Linux distributions, and they means:

1. Run backup for every 30 minutes from 9 A.M. to 11 P.M.
2. Run backup for every hour from 0 A.M. to 8 A.M.

Reduce The Number Of Commits By Ignoring the Minor Changes

Everything should work now, let’s check the repository to verify the commits had been pushed. Cool, It really works, but wait, why there are so many commits even when I did nothing? I found that:

• wp_options table was keep changing.
• wp_postmeta table was changing because I was evaluating a “Post Views Count” plugin

What I do CARE was the posts/pages changes especially after I finished the setup. So I need to change the script to commit only when posts/pages or files changed. Here’s the updated backup.sh file:

cd /usr/share/nginx
mysqldump --add-drop-database --skip-dump-date --skip-extended-insert \
          --database wordpress > db/wordpress.db.sql

if [ -n "$(git status --porcelain | grep -v wordpress.db.sql)" ] ||
   [ "$(git diff db/wordpress.db.sql | grep wp_posts |wc -l)" -ne "0" ]; then

    echo "`date`: Changes detected, committing..." >> dbbackup.log
    git add -A
    git commit -m "Backup wordpress database"
    git push
    exit 0
fi

echo "`date`: No changes, stopped." > dbbackup_check.log