Skip to content
Home » News » Automate WordPress Database and Files Backup

Automate WordPress Database and Files Backup

Regularly backing up your database and files is one of the most important things to do when running a website. One never knows what could happen and the words have become infamous:

I was sure I had a backup.

Most database plugins for WordPress offer the option to weekly backup your database and even email it to you but if you have a popular site, you might want to prefer a more frequent backup routine. Imagine how many posts and comments Gawker would lose if they only kept weekly backups and suffered problems the 6th day after their last backup.

Another problem with all database plugins is that they will not backup your files. With server storage space being really cheap nowadays, you can easily have several backup procedures in place, even if you host many pictures on your blog.

Personally I have three different, totally automated backup routines, using cron jobs:

  1. Daily backup
  2. Weekly backup
  3. Monthly backup

The reason why I also use weekly and monthly backups is because if you have a corrupted database, probably your daily backups will be corrupted and unusable. This can happen on sites you do not use on a daily basis.
Daily backups are overwritten every 7 days, weekly and monthly backups are stored with attached timestamp.

Because most web hosting companies offer cPanel in their package, this tutorial is based on cPanel but the syntax is the same for Plesk and other backends.

What Are Cron Jobs

From Wikipedia:

Cron is driven by a crontab, a configuration file that specifies shell commands to run periodically on a given schedule.

Backing up your database.

In your cPanel (http://yoururl/cpanel) under the header Advanced Tools, you will find a link Cron Jobs. Click this link or click the icon on the right if your cPanel is setup with icons.
On the next page choose Advanced (Unix Style). There is no real difference between both options, the needed attention level is the same and in both simple and advanced you have to fill in the correct command.

The screenshot shows the daily cron jobs I have setup for my own blog (minus sensitive information of course).

The Command Line

The structure of the command line for the cronjob is following

[source]mysqldump -udbusername -pdbpassword db_name | gzip > /home/cpanelusername/yourfolder/mysql_backup.gz[/source]

The different switches explained
  • mysqldump
    This is the command to backup the database.
  • -udbusername
    This is a double switch: the first part -u identifies the user to the database. Replace dbusername with your database username (if you have used Fantastico to setup your blog and don’t know those data, retrieve them from your wp.config file)
  • -pdbpassword
    Again a double switch, with -p being the trigger to call the database password. Replace dbpassword with your password (if you have used Fantastico to setup your blog and don’t know those data, retrieve them from your wp.config file).
  • db_name
    Here we add the name of the database we want to backup (If you have used Fantastico to setup your blog and don’t know those data, retrieve them from your wp.config file)
  • | gzip
    Triggers the compression for the backup. This is not needed. If you don’t want to compress the backup, just skip this switch.
  • /home/cpanelusername/yourfolder/mysql_backup.gz
    This is where we will store the backup and how it will be named. If you have opted not to compress the mysql backup, change the extension gz into sql. Of course you can name your backup and folder(s) differently.
    Feel free to be creative. I am boring conservative and save my backups in the folder /home/mycpanelusername/etc/dbase (Do not save your backups in a non-public folder, that is one level higher than your public_html/www/ folder. Use the cPanel File Manager to create this folder, or finally get a decent and free FTP-client). cpanelusername is the nick you use to login into your cPanel.

When do we run the script

Last thing to define is when to run the script. Cron Jobs follow an easy structure for this.

  • Minute
    What minute of the time period. Values from 0 to 59
  • Hour
    What hour. Values from 0 to 23
  • Day
    What day. Values from 0 to 31 (remember the shorter months)
  • Month
    Which month, 0 to 12, Gregorian Calendar.
  • Weekday
    Guess… 0 to 7, 0 and 7 being Sunday.

Some examples for dates.

  • 1 * * * * : Every minute after the hour, daily. * works as wild card, EVERY unit.
  • 12 4 * * 3 : 04.12AM every Wednesday.
  • 6 22 15 2 * : February, 15th at 10.06PM.
  • 1 0 * * 1 : Every Monday at 0.01AM.

As can be seen in last example (every Monday), it is easy to create another job for every day (remember to give your file another name for every day as well: fe. mysql_backup-mon.gz and do this for every day of the week).

There also is an option to date stamp the backups but this might not work on every cPanel install and also is the best way to clutter your web space because probably you’ll forget to delete the old files anyway. This will overwrite your backup every time.

Adding a timestamp to the file name

Now we know how to create a daily backup cron job, it is easy to make a weekly backup and add the time and date to the file name.
In this example we are going to let the procedure run every Sunday at 19.45 server time and the backup will be named: weekly.

[source]mysqldump -udbusername -pdbpassword db_name | gzip > /home/cpanelusername/yourfolder/mysql-$(date +%Y-%m-%d-%H.%M.%S).gz[/source]

The needed time values are: 45 19 * * 7. My weekly backup which ran yesterday is named: mysql-2009-12-20-19.45.28.tgz

To run a monthly cron job on the 15th of every month at 06.22, use the same command as for the weekly backup but for time you have to use 22 6 15 * *.

Emailing the backup

It is good procedure to store the backups somewhere else as well. You can easily email backup files. Be aware that daily or weekly backups can quickly take up lots of space in an email account and that there is a limit to file size for most email providers. To email a backup simply precede the command with MAILTO=yourbackupemailaddress (There is a space after your email address).

Backing up your files

Now we know how to use cron to backup your database it actually is very simple to backup files. Probably you do not need a frequent routine for these files and if you do, the only important folder is probably the wp-content folder as this is where your uploads, plugins and themes are stored.

First we are going to create another folder files for this backup. Navigate in your cPanel File Manager or FTP client to your root folder and chose again the folder etc. This is where we also created a folder for our databases. Now create a new folder named files.

To backup our wp-content folder weekly we are going to create a file called file-backup.sh and store this file in our home folder (your root folder).

[source]#/bin/bash
/bin/tar czPf /home/cpanelusername/etc/files/wp-content-weekly.tar.gz /home/cpanelusername/public_html/wp-content/[/source]

This file will now create a backup named wp-content-weekly.tar.gz of your wp-content folder in the directory etc/files.

Go back to your Cron Jobs in cPanel now and now we need to call this file weekly. Use the following command to schedule your new cron job: /home/cpanelusername/file-backup.sh. To run this command every Sunday again, at 13.45 this time use 45 13 * * 7 for the time. This will weekly overwrite the backup, to save every week another backup with timestamp add -`date "+%Y%m%d%H%M"` to the filename in the new file we created, eg.

[source]#/bin/bash
/bin/tar czPf /home/cpanelusername/etc/files/wp-content-weekly-`date "+%Y%m%d%H%M"`.tar.gz /home/cpanelusername/public_html/wp-content/[/source]

Remember that if you have many large files you could run out of web space after some weeks if you do not regularly back them up on your computer and delete old files.

Photo credit: Daryl Mitchell.

Note: I originally wrote the first part of this tutorial more than 2 years ago on iFranky.