Is Percona xtrabackup the right choice for my 500GB MySQL backup?

Question

I have a MySQL db with 500GB, consisting mainly of Innodb tables, and one big (200GB) MyISAM table. My current backup strategy is copying the db files to an external harddrive. This causes a downtime of ~4hrs. My Needs:

1. Low cost.
2. Recovery time can be long. No need of real-time replication.
3. Can handle loss of data in ranges of 1week-2weeks.
4. Would prefer a short as possible down-time for backup, but can live with anything up to 2hrs.

Currently considering trying to move to Percona xtrabackup. Is it the right solution for my use case? I'm specifically concerned that it locks the MyISAM table and learning curve.

Answer 1

From the sound of this, you could use a different DB infrastructure.

Based on your aforementioned needs, I have a suggestion but would require a little compromise.

SUGGESTION #1 : Use MySQL Replication on the Same Server but Different Disk

Your second need (No need of real-time replication) would have to take a slight backseat in this suggestion. Since you have an external harddrive big enough for XtraBackup, why not use as a datadir for a second instance of MySQL on the same box?

I scripted my own service to provision dedicated mysql instance for ports 3307 - 3399. Here are my past posts on this:

• Sep 17, 2012 : mysqlservice command syntax
• Sep 30, 2011 : Running multiple instances on the same host

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

SUGGESTION #3 : Parallel mysqldumps

You can setup parallel mysqldumps of databases or tables. This may result in a shorter window of time for backups. XtraBackup essentially does a checkpointed transactional backup with point-in-time sensitive operations so that the point-in-time of the backup is when the backup finished. With parallel mysqldumps on a slave which is not replicating gives you backups whose point-in-time is the beginning of the backup. Doing mysqldump on a stopped slave would be storage engine agnostic. In other words, it would not matter whether tables are InnoDB or MyISAM.

If you apply this to SUGGESTION #1, this will incur server load and disk I/O since both Master and Slave reside on the same machine.

If you apply this to SUGGESTION #2, there is no penalty on the Master. You can run backups at will on the Slave. You also will not have to concern yourself with locking MyISAM tables.

Please see my past posts on doing parallel mysqldumps

• Jul 24, 2012 : How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server?
• May 22, 2012 : MySQL Server Backup
• Oct 01, 2011 : Quick MySQL Backup
• Apr 17, 2011 : How can I optimize a mysqldump of a large database?