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;
- 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?