Daily MySQL Database Backup

I host and manage about 50 different websites.   The vast majority are static sites for friends and family that don’t require any backup processes above the daily backup provided by my webhost.   In the 8 years that I’ve been running websites I’ve been with about 12 hosting companies and even though my current server has had amazing uptime and backup availability I don’t like to have all my golden eggs in one basket.   This is especially true with the 5-8 sites that I manage that have very dynamic content populated into MySQL Databases including forums, blogs, etc. 

I needed a process that would help me sleep peacefully at night.  As mentioned twice before on this site I’m pretty diligent about computer backups.  In my opinion it is exponentially important to backup the constantly changing information on my sites.  Just a single day’s loss of data could be a huge problem.  For example, on one of my sites, http://www.backyardchickens.com/ there are about 100 new members signing up per day and members are posting about 7,000 new posts per day.   Imagine loosing more than a day or even a week’s worth of members and posts!!!

My Database Backup Process & Software:

1. After trying a few different MySQL backup scripts I found a gem hidden within a blog.  Ameir’s MySQL Backup to FTP and Email script works flawlessly and does everything I need.
2. I have the script setup to run every morning around 5:00 AM EST to do a full backup of about 10 MySQL Databases.
3. The last 3 days worth of backups are kept on one of my secure folders on the server.
4. Each day’s backup is also sent to a completely different web host and server in a different state.
5. Each day I synchronize (using SyncBackSE) my desktop computer with this second web host server and download each day’s backup files.
6. Every month I burn all the backups to a DVD and put them into a fire safe.

If you’ve been counting that is 3-5 copies of my databases that are backed up in different locations throughout the US.  If you count the daily backups my hosting company provides that bumps the number up to about 7 copies of each day’s DB backups!

Database Backup – Only Half The Battle:

Well, the process above sounds very impressive, but about a year ago I made an almost fatal mistake.  Learn from my experience and know that your backups are only as reliable as the last time you attempted to restore one of the backups.

Prior to using Ameir’s MySQL Backup to FTP and Email script I was using an application that had been working fine for me for the previous 2 years.  I hadn’t needed a DB restore for about 10 months… well, that is until I had someone come in and hack one of my forums! All of a sudden, the backup process I hadn’t been thinking about at all become my biggest priority!

I attempted to restore my most recent DB backup file and got errors.   I tried to restore one that was 2 days old… same errors.   After trying 2 more backup files I gave up and called my server admin to have them restore their backup. I lost a day’s worth of new members and posts, but thank goodness my host’s backup system was working AND they happened to have a snapshot from the day before… phew!   After this fire was put out I discovered that my backup script has stopped working about 4 months prior!!!   I don’t know if it was a change in my hosting environment, the size of the DB’s or some other odd problem, but that’s when I abandoned that application for something new & promised myself to be more vigilant about testing my backups more regularly.   Since then I’ve been using Ameir’s script without fail.   BUT, the moral of this story:

1. Backup soon and backup often
2. Frequently try to restore your DB backups to ensure all your hard work and preparation is paying off.

Remember, your backup process is only as good as your ability to restore those backups!!!

UPDATE 7/27/08: The other day a member of my community deleted a bunch of their content and said they would try to re-submit it.  They said it would take them a while to get all the pics and text back up.   I said, “Don’t worry about it.”

In about 15 minutes I had restored a backup from 2 days earlier into a copy of my community system, exported the data the member had deleted, and then imported it into the production site.  Thanks goodness for daily backups!

UPDATE 8/23/08: Regarding backups: For the “static” content of my sites I will occasionally (read very rarely) do a “Full Backup” from within each cPanel hosting account (at least for the top 10 accounts). For the most part I rely on my hosting company for the more frequent backups of my static content…. so a I had a question to ensure I’d sleep a little bit more soundly at night:

What is the most simple way to do occasional (i.e., quarterly) do full backups of all my sites? I’ve read people mentioning using the backup systems in WHM and some talk about using rsync, etc.  These sound good for frequent backups, but I feel I only need to do this relatively infrequently (again, I rely on my daily database backups and the backups my hosting company performs).

After some discussion with one of the NOC engineers, here is what worked REALLY simply for me (using cPanel):

1)  I have a secondary hosting plan at another hosting company (i.e., good backups = all eggs NOT= one basket).

2)  I logged into root cPanel / WHM and edited the backup settings under “Configure Backup”.  I selected all the options to only backup accounts and their respective databases.

3)  I loaded the FTP info for the other host

4)  I then manually selected my most important accounts to be backed up

5)  I logged into ssh as root and ran the command:  /scripts/cpbackup

6)  For the system to completely tar, backup, and FTP 26 accounts and a total of 3.1 gb of compressed data took about 1 hour in the background.   I didn’t see any obvious changes to server load (I did this at about 10:00 pm PST).

7)  I downloaded all the files to my desktop via my DSL line:  Transferred 26 files totaling 3,146,615 KB in 6 hours 30 minutes 55 seconds (134.2 KB/s).

I don’t expect to do this very often… quarterly at most.  I have a lot of faith in my hosting company and their backups but I still like to cover my bases.

UPDATE 2/2/2009
It has been over a year since I posted this thread so I thought I’d do an update, especially as my BYC forum reaches 22,222 members on 2/2/09.   From the time of the original post my DB for this forum alone has doubled and then doubled again!   Two of the search tables were taking up at least half of that space so I asked Ameir to write a unique version of his backup script that would backup all the tables except the search tables (which can be recreated if they are dumped).    This cut my backup sizes in half, which has made a huge difference in my ability to do frequent backups and store copies in multiple locations.  This process of daily backup stored in multiple locations really helps me sleep better at night.

UPDATE 3/06/11: BYC now has over 80,000 members and over 5 MILLION posts!   The forum database is a HUGE BEAST!    Today our backup system was really put to the test when I discovered one of our tables had become corrupt and I couldn’t repair it.     This caused the entire community forum to be down in the middle of one of our most busy days!    I was able to take a backup of the entire DB from early this morning, restore it, export the working table, and import the table to the production database.   I think the entire process took about 45 minutes, and most of that was because I had to restore the entire database backup from the night before just to get at the one table.   I LOVE knowing that my backup process is solid and can really save me in the event of an emergency like this!