Performancing Metrics

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

mysqldump -udbusername -pdbpassword db_name | gzip > /home/cpanelusername/yourfolder/mysql_backup.gz
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.

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

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).

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

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.

#/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/

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.

Categories: Backups
Tags: , , ,

This post was written by . You can visit the for a short bio, more posts, and other information about the author.

Comment with Your Facebook Account

Comments

  1. hombrelobo ) says: 12/21/2009

    Yesterday I started using http://www.wordpressbackup.org/ , an extension that makes a backup of the database, config and wp-content in Amazon’s S3.

    So far, it seems to do everything that is supposed to do very well …

    Reply

    • Franky Branckaute ) says: 12/21/2009

      Seems like a cool plugin. Sadly it is limited to Amazon S3, it would be nice if it also offered the option to save in some (protected folder) with download option. I assume not that many bloggers use Amazon S3.

      Reply

      • Melvin Ram ) says: 12/22/2009

        We’re going to add Rackspace Cloud Files & plain old FTP as storage options in the future. Amazon S3 is so cheap & it gets the job done in a simple way. I mean lets say your website is 50MB x 30 days x $0.15/GB = $0.23/month cost to backup your website.

        Reply

      • Franky Branckaute ) says: 12/22/2009

        Sounds good Melvin. Ping me when you’ve added these to the options of the plugin and I’ll add it to the several tutorials, maybe even give it a complete review.

        Reply

      • Melvin Ram ) says: 12/23/2009

        I’m curious, what is it about S3 that you don’t like?

        Reply

      • Franky Branckaute ) says: 12/23/2009

        Personally I don’t mind S3 but i am pretty sure a majority of our readers here don’t have an S3 account.

        As for reviewing plugins here on the BP, I prefer plugins to be less restrictive. Many readers have a small blog (less than 100MB, pictures inclusive) but several GB of web space for $60/year or less. I do not want to force costs upon our readers, especially not because I just told them how to do it for free.
        Don’t get me wrong, I will gladly update the entry and mention the plugin, evt. even write a full post, but for me to consider doing so it needs to offer more basic features IMO.

        Reply

      • Melvin Ram ) says: 12/23/2009

        Gotcha. I understand. We’re going to add the FTP feature relatively soon. However, in your article it should be highly recommended that they get an S3 account so that there is a dilution of risk… or at least FTP to a server that is different than the one the website is on. If the server they are hosting on crashes, they won’t have any backup while thinking the whole time that they are protected.

        Reply

  2. Todd ) says: 12/21/2009

    Thanks for the tutorial. Could you write another on how to use these backups should something go wrong?

    Reply

    • Franky Branckaute ) says: 12/21/2009

      Todd, for the wp-content backup: easiest is to download the backup, unarchive it (7zip, Jzip come to mind for Windows, on Mac the Mac OSX archiver.app will unarchive .gz files). Once unarchived you just have to upload these files to a the wp-content folder of a fresh install.

      For the database, download the most recent backup, and import via phpMyAdmin: select your database, go to import and upload the file you have just downloaded. If the database backup is larger than the upload limit, get in touch with your hoster and point them to were your backups are stored (etc/dbase).

      Reply

  3. David McDougal ) says: 12/22/2009

    I have been using DBC Backup for all my wordpress sites. It runs on a schedule and compresses the dumped file to a folder that you can specify, and auto adds a .htacess file to prevent viewing via web. I have it dump outside my home directory. I then use the built in cpanel backup tool to send this folder to another server. So far in 3 years no lost data. backups happen every 24 hours for me.

    Reply

  4. Kian Ann says: 1/2/2010

    Thanks for the tip! Awesome. Now I know it is REALLY backed up.

    I used to have to log in to CPanel and download the zip.

    Reply

  5. Adyesha Aditi ) says: 1/8/2010

    Excellent tutorial, But I am getting error as:

    /bin/sh: -c: line 0: unexpected EOF while looking for matching `)’
    /bin/sh: -c: line 1: syntax error: unexpected end of file

    This is in case of weekly backup. Please guide.

    My server OS is Linux Kernel version 2.6.18-164.9.1.el5 and cPanel Version 11.25.0-RELEASE
    MySQL version 4.1.22-standard and Apache is 1.3.41

    Please help.

    Reply

    • Franky Branckaute ) says: 1/8/2010

      Adyesha, do you mean the weekly files backup? The file-backup.sh has to be writeable.

      Reply

      • Adyesha Aditi ) says: 1/8/2010

        Thanks for the reply.
        But the problem is with Weekly Database Backup. Using following command:

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

        But If I am changing it to:
        mysqldump -udbusername -pdbpassword db_name | gzip > /home/cpanelusername/yourfolder/mysql-$(date).gz

        The command executes properly without any error. The file name is becoming some thing like:
        mysql-Fri Jan 8 06:57:01 EST 2010.gz

        But I want it like mysql-YYYY-MM-DD.gz format.

        Reply

      • Franky Branckaute ) says: 1/8/2010

        Hm… it runs for me but I have MySQL 5 running. Try this (date +%d%m%Y) or escaping: (date +%Y\-%m\-%d)

        Reply

  6. Adyesha Aditi ) says: 1/8/2010

    No. Nothing is working in my case. Let me check MySQL online manual. But Franky if you can find any solution please reply this thread.

    Thanks for all the help provided.

    Reply

  7. Robert says: 3/22/2010

    HI,
    Also had the EOF error.
    Try this:
    _mysql-$(date +\%Y-\%m-\%d-\%H.\%M.\%S).gz
    Tip found her: http://dbaspot.com/forums/shell/176999-crontab-command-substitution-bash.html

    Cheers, Robert

    Reply

  8. Rio R. ) says: 5/2/2010

    Hi Franky,
    Thanks fot the awesome tips and tutorial..
    But, could you help me to elaborate more on the emailing the backup files subject…?
    I mean, how do we modify the script so that it would automatically email the file each time they finished performing the backup?

    Thanks in advance..

    Reply

  9. Abdul ) says: 7/10/2010

    Hey

    Thank you for such a great tutorial.

    One question though. Is it possible to get the cron job to email me the backed up file? So basically I will have all the weekly back-up files stored remotely along with the database?

    Thank you

    Abdul

    Reply

  10. Carol says: 7/16/2010

    Thank you very much for yet another first-rate article. I am always searching for original WordPress tips to suggest to my readers. Thanks for creating this article. It’s exactly what I was searching for. Truly great post.

    Reply

  11. Steve says: 10/18/2010

    Isn’t this great!

    Reply

  12. Susan says: 1/23/2011

    Great post and very true. I have had WordPress databases get corrupted and would have lost over a year’s worth of blog posts if I would not have had it backed up. I have it auto backup every week.

    Reply

  13. DedicatedServerXS says: 1/26/2011

    Your tutorial is awesome! Might try this plug in as well… Will let you know if I will encounter errors or problems along the way… Thanks.
    DedicatedServersXS

    Reply

  14. formula 21 says: 1/31/2011

    Personally I don’t mind S3 but i am pretty sure a majority of our readers here don’t have an S3 account.

    As for reviewing plugins here on the BP, I prefer plugins to be less restrictive. Many readers have a small blog (less than 100MB, pictures inclusive) but several GB of web space for $60/year or less. I do not want to force costs upon our readers, especially not because I just told them how to do it for free.
    Don’t get me wrong, I will gladly update the entry and mention the plugin, evt. even write a full post, but for me to consider doing so it needs to offer more basic features IMO.

    Reply

  15. Luther Bastian says: 6/2/2011

    Have you due to any type of considered at all with targeted your web page in to German? I know a bride and groom of of translaters right here that may definitely help you get it done for no price if you wish to make get in touch with with me.

    Reply

  16. Adriana says: 2/25/2012

    thanks for share!

    Reply

  17. sem calcinha says: 2/28/2012

    Adoro me mostrar peladinha na web cam

    Reply

  18. narin says: 4/1/2012

    Should we also backup files in the wordpress root directory such as wp-config.php? Aren’t they important?

    Reply

  19. Elena says: 2/3/2013

    Thanks for tutorial. I managed to get my backup in the folder but received no email. I have done this:

    MAILTO=[email protected] mysqldump -umydb -pdbpassword dbname | gzip > /home/myusername/public_html/myfolder/backup.gz

    I am using Hostgator’s cpanel.

    Any idea what’s wrong?

    Reply