Backing Up Your Website and Database

If you’re like me and most anyone else, you’re probably not that great at backing up your PC. Which means you’re probably even worse at backing up your data on your website. For once, I’m attempting to be proactive and get the stuff backed up on my hosting provider before something bad happens.

Although you may have a reliable webhost, I think it may sometimes be too easy to be lulled into thinking that all your data is safe and secure. However, there are many things that can happen that even your web host cannot prevent. There may be vunerabilites in software that is installed (sometimes by you) that can allow hackers to modify or erase your data. Even if your webhost does do backups, it can take some time (and possibly money) to have them recover your data.

I’ve been using a Linux host at for my hosting (yes, that’s a plug) and I have not had any problems. I would highly recommend them. Their prices are very reasonable as well. Depending on your provider the information below may require some modification, but these are all general Linux commands.

I’m also using a Linux box at home as the backup machine. It’s always on 24/7, but there are some equivalent Windows tools that get the job done on demand. (I’ll list some further reading for Windows users at the end.) Finally, I also assume SSH access to your hosting account.

Setting up the Host

Many web based applications (such as WordPress) use a database to store persistent data. It’s important to dump out that data and keep a backup. Since I’m using MySQL, they provide a handy tool called mysqldump for doing just that.

I’ve created the following script called bkupdb.cron on my web host and stored it in my ~/cronjobs folder. The output is placed in the ~/backups folder (you will need to create both of these.)

td=`eval date +%Y%m%d-%H%M%S`
/usr/bin/mysqldump --host=<db-host> --all-databases --add-drop-table -u <username> --password=<password> > ~/backups/$td-database.sql
/bin/gzip ~/backups/$td-database.sql

Replace <db-host> with the name of the machine that hosts your database. In some cases it’s the same machine as the web server. In that case you can omit that parameter.

<username> and <password> correspond to your database account, which may not necessarily be the same as your SSH account.

The idea is to run this script every day and generate a backup of the entire database. However, I may only want to keep the last 14 days of files hanging around. So, here’s another script called cleanup-files.cron also stored in the ~/cronjobs folder.

/usr/bin/find ~/backups/*sql.gz -type f -mtime +14 -exec rm -f {} \; 2> /dev/null

Make both scripts executable by running:

chmod +x ~/cronjobs/*.cron

The next step is to get the host’s server to run these scripts every day. This is where crontab comes in. Execute ‘crontab -e’ to edit your contab entry. This will probably bring up the default vi editor. If you’re not familiar with vi, just hit ‘a’ then paste this into your SSH terminal:

20 1 * * * ~/cronjobs/bkupdb.cron
25 1 * * * ~/cronjobs/cleanup-files.cron

Then press ESC, :wq, ENTER.

This will run the bkupdb.cron file at 1:20 AM and cleanup-files.cron at 1:25 AM

Next Page: Getting the Files

This entry was posted in Website. Bookmark the permalink.

3 Responses to Backing Up Your Website and Database

  1. Mauriat says:

    Nice. You almost have verbatim how I have my site-backup setup. 🙂 Although for the db, an alternative some users may have is if their provider gives a link to a gzipped dump of their databases. Which is then easy to grab using ‘wget’.

  2. The Ty says:

    That is a pretty good idea.

    I have been using a gzip dump of the database and storing it onsite and offsite from oscommerce, but an always on solution may be a more comprehensive answer.. especially when I am days away from going online with 3 more websites.

    May have to get the York fix! Later bro

    – T

  3. For those web hosts that don’t allow you to access their DB servers via mysqldump, I created a PHP file that does the same thing and returns the SQL output. Unfortunately the version that I posted on my blog is slightly out of date…

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s