CS // 20 Mar 2010 // Comments Off on Behind the scenes – Backups at Computer Solutions

I was reading a post up at Carsonified (http://carsonified.com/blog/dev/bulletproof-backups-for-mysql/), which talked about MySQL backups.

While he slightly re-invents the wheel, its fairly similar to what we do over at Computer Solutions as a solution for Backup.

How do we do it?

Step 1 – Database backups (snapshot dump of the database)

First is to backup the database(s) to the filesystem in a common folder.
We run MySQL daily backups to a folder under /home/dbbackup on all our servers using an easy to install backup shell script:

http://sourceforge.net/projects/automysqlbackup/

Debian users can easily install via:

apt-get install automysqlbackup

On our servers we run automysqlbackup at midnight; and it automatically handles daily, weekly, monthly folders + rotation (no need to reinvent the wheel).
Thanks to the wonders of logging, we can spot a visual effect of whats happening on our daily logs.

The astute will notice that mysql usage goes a little ballistic as the script kicks in.
A weekly view shows this in a clear manner also.

The backup script runs on all our servers daily. This essentially provides a daily snapshot of all the databases on that server. If any issues occur during the backup, I get emailed a copy of the problems.
This is useful for letting me know when a database table is corrupt, or something else went wonky.

Sample errors below:

mysqldump: Got error: 1044: Access denied for user ‘debian-sys-maint’@’localhost’ to database ‘information_schema’ when using LOCK TABLES
mysqldump: Got error: 1146: Table ‘consumer_aware.mw_category’ doesn’t exist when using LOCK TABLES
mysqldump: Got error: 1146: Table ‘counselasia.wp_email’ doesn’t exist when using LOCK TABLES
mysqldump: Got error: 1146: Table ‘joomla_versatility.jos_bannertrack’ doesn’t exist when using LOCK TABLES
mysqldump: Got error: 1146: Table ‘wp_counselasia.wp_counselterm_taxonomy’ doesn’t exist when using LOCK TABLES

(Errors above were corrupt tables, and a MySQL 5.1 issue that AutoMySQLBackup needs a small change for)

(We use the debian-sys-maint as backup user, so it needs lock tables privileges for information_schema)

mysql -u root -p
use mysql;
update user set lock_tables_priv='Y' where host='localhost' and user='debian-sys-maint';
exit;

That works for me, but others have also had to do this small addition to their automysqlbackup config file:

# OPT string for use with mysqldump ( see man mysqldump )
OPT="--quote-names --skip-opt --add-drop-table --create-options --disable-keys --extended-insert --quick --set-charset"

It doesn’t cover all issues that might occur though (see my notes at the bottom for things that have/can happen)

Step 2 – RSync

We then rsync the entire home folder system (which is where I store all the user specific data) to another pseudo-dedicated backup server.
The rsync is setup to run an hour or two after the mysql backup script. From experience, the MySQL backup usually takes about 15 minutes max even on the largest MySQL database server we have (mostly as clients DB’s compress nicely, and the total data size is usually a few hundred megabytes at max per user).

Rsync runs daily (usually in our early 2am – 4am period).
Its setup as a service on each of our servers, and I have 3 separate folders we backup.

/etc
[I’ve found it useful to backup conf specific stuff, and it typically runs in less than a second]

/var/qmail
[We use qmail, so I backup the entire qmail folder structure. The queue will need to be rebuilt in case of a hardware failure, but configuration, and other settings are nice to have. Our user mail is actually stored in /home/vpopmail (as are user accounts), so that goes in a separate backup. This usually completes in a few seconds]

/home
[As we also run backups across multiple servers I specifically exclude any /home/backup folder, as this is usually my dumping ground for the backups. This backup typically takes anywhere from 5 minutes to a few days depending on the amount of changes to be synced over.]

rsync is set to run via cron in /etc/cron.daily or /etc/cron.weekly or /etc/cron.monthly as applicable.

In our case we have a number of servers to backup over a few continents, and web + mysql takes roughly 600GB in total (still fairly easy to stick onto a single drive though).
As machines are scattered around different / regions it can take a few days to complete an initial backup if I deploy a new backup server.
To avoid issues, I do a simple file test in my rsync cron script which gets deleted at the finish of the script. If the script gets run a second time it checks for the file, and aborts if found. This prevents multiple instances being run each day, which can cause issues for the initial long running backup.

Step 3 – Backing up the backups!

I also do a weekly backup of the complete backup folder to a different server so I have some “history”.
This is fairly easy to setup – I just backup the backup folder from our “central” backup to a different server by sticking an rsync script in an appropriate folder – /etc/cron.weekly/monthly…,
To facilitate, I setup, stick a spare 1TB or larger drive in mounted at /home/backup, and let it run.

As I’m still a little paranoid, to make it _even_ safer, I also do a monthly off-site of the backups. This sits in a live-spare server in our office.

I also maintain multiple backup copies of certain data in different locations, just in case(tm).
These are all kept in non user accessible drives on specific servers.

As mentioned in the blog post I linked to, no backup regimen is actually useful until you’ve actually had to use it!
We get at least 1 request a month to restore data or database(s), so I get to use this quite often.

The only time I’ve really needed multiple levels of backup was during our week of repeated Seagate drives failures (multiple hardware failures due to a faulty batch of bad drives); we got to test the backup infrastructure repeatedly, so I know that it all works well!

Notes

If your database is in iffy condition – eg latin1 encoding, with UTF-8 data, or similar, the backup will be what MySQL thinks the encoding is.
This may not mesh with what it actually is.
Corrupt tables or filesystem issues can also cause errors.

AutoMySQLBackup can be configured to email you on issues, as can Rsync.
Encoding related ones will not be seen until a restore is attempted, but other issues will be.
I have successfully restored databases even with “corrupt” encoding data, whether it was latin1 -> utf8 or utf8 double encoding.
MySQL does need some handholding though if that needs to happen though.

Having different snapshots of your data from different time periods (eg, daily, weekly, monthly) is important (and thats why I do it!).
The number of times clients have come back with “oh I deleted something 2 weeks ago”, and I’ve been able to restore it has meant that they’re happy.

That said, it does mean you need to use a whack of drives for backup purposes.
Luckily drive sizes get larger each year, and prices drop, so its an acceptable expense!

Some of you may question why we don’t use RAID for the above.
We do use RAID hardware in some of our servers (usually 3ware 9000 series controllers), however RAID only protects against hardware failure, not against user mishap, or malicious intent. RAID is not a replacement for backup!

Lawrence.