Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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:

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

share|improve this answer
    
+1 for all of this, but my personal preference is taking backups from a slave where possible. And more so from a slave on a different server (to limit hardware failure pain). –  Derek Downey Sep 25 '12 at 15:57
    
@DTest I agree. My preference is always Slave on a Different Server. Some use a Slave on the Same Server but different Port and datadir DiskVolume as a cost-cutting measure. Some of my employer's clients live with it. I live with the consequences. SIGH..... –  RolandoMySQLDBA Sep 25 '12 at 16:01
    
I wasn't always as 'wise' as I am now*! disclaimer: still not wise. –  Derek Downey Sep 25 '12 at 16:05
    
If I will do suggestion #1, how heavy will that be on my master server? In the past I've tried suggestion #2, but the commodity server (cloud based) had very poor I/O and didn't manage the workload. –  Noam Sep 26 '12 at 15:51
    
Suggestion #1 will add some server load and disk I/O since you are running two MySQL instances on the same box. Using datadir for the 3306 instance on one disk and another disk for the datadir of the 3307 instance will lessen disk I/O but the two instances will compete for the same memory. Perhaps (for suggestion #2) you should use a Slave whose hardware is identical to the Master. If you must use the commodity server for suggestion #2, restrict all read and writes for your app to the Master. Use the Slave strictly for Backup. Just STOP SLAVE; when Seconds_Behind_Master is 0. –  RolandoMySQLDBA Sep 26 '12 at 16:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.