Backup and off-site restore setup

Question

I've been reading a lot of threads about different backup options for MySQL and was hoping for some more insight so I can settle on one.

Our current situation is ~100 GB of InnoDB and MySAM data in 10 DBs (largest single ~60GB) with no replication. Nightly mysqldump backups by AutoMySQLBackup lock the database for 20 minutes.

We removed Percona XtraBackup due to difficulty in restoring the databases to test if the backup was actually viable and if we could recover from a disaster with them. Also we often transfer several of the databases to a different location for development testing. It didn't appear easy (or even possible?) to restore a single database from the XtraBackup dump on our Red Hat server to a Mac OS X machine for local development. Of course it is very easy to do that with a mysqldump.

Our new setup will have a MySQL 5.5 master replicating to a MySQL 5.5 slave on a different server in the same rack. We're looking to have daily backups, no downtime due to locking etc, and be able to move the backups around easily and restore on different servers/platforms. Perhaps this solution suggested by the prolific Rolando is best?

SUGGESTION #2 : Use MySQL Replication on the Different Server

In consideration of your first need (Low Cost), if you have access to a commodity server with adequate diskspace, setup MySQL Replication to that external server. That way you can run backups on the Slave as follows

• STOP SLAVE;
• XtraBackup
• START SLAVE;

You can do this with zero impact (no server load, no disk I/O) on the Master

Naturally we would replace the XtraBackup with a mysqldump. Are there any better ideas for our situation?

Answer 1

Taking a backup from a slave is exactly how I do it, and it works very well. I can't imagine a better way to take a backup without causing downtime of the master and your application.

I am intrigued by your statement about not being able to restore a backup onto a mac osx machine (this could probably be a different question). An alternative is to setup a virtual machine on your local mac machines that run the same OS your production servers run. A tool that makes managing those machines easy is Vagrant.

Mimicking the production environment has the added benefit of allowing you to test any restore procedure with a fairly high degree of certainty that it would work on the production servers should you need to.