The Problem

I'd like to implement a MySQL backup strategy on a dedicated server.

It has multiple databases on, combined total is about 150GB. Most of this is weighted toward one of the databases.

Database is mixed engine MyISAM and InnoDB (plans to overhall are taking place long term but nothing possible short term).

Looking for suggestions for strategies/software that could be used.

 Key Objectives

The ideal MySQL backup procedure will achieve the following objectives.

  1. Consistent MySQL backups.
  2. Lockless backups (or close to so that database can still serve requests).
  3. Per table restoration.

Suggestions so far

I'm not a sys/db admin by profession, so I would appreciate the learned advice of the community. Some suggestions I've had so far...

mysqllvm Holland Backup

Holland Backup has a mysqllvm option.

It works by taking a complete snapshot of the MySQL data directory. It locks for a very small amount of time whilst a snapshot is taken and is consistent.

It achieves objectives 1 and 2.

Objective 3, not so much... only restoration of the whole data directory is possible. It's not possible to do per database restoration, never mind per table.

mysqldumplvm Holland Backup

Holland Backup has a mysqldumplvm option.

This achieves objectives 1 and 2.

Objective 3, not so much... It only allows per-database restoration from .sql dumps, which due to the size of the database would take hours/day to restore to a dummy database just to extract one table. Disk space may start to become an issue here.


There is a way to achieve all three objectives, but I don't know if there a product that does, so I would script it this way :

  • Use LVM snapshot to get a consistent state of your database (don't forget the FLUSH TABLE WITH READ LOCK)
  • Mount your snapshot on a specific location : /opt/mysql read-write
  • Start mysqld as a second instance with --data-dir=/opt/mysql (and option like --skip-network to avoid conflict and the socket file to be separated elsewhere than /var/lib/mysql/mysql.sock) - Help : Running Multiple Mysql Instance on Unix Server

Then, you may use any script that will generate a dump table per table with the guarantee to be consistent by connecting to your second instance of mysql

Eventually, if snapshot performance or disk size is a problem, you can just move the snapshot on a second server then execute the dump on the second location.

| improve this answer | |
  • 1
    As per answer elsewhere, I'd recommend doing an explicit filesystem sync after FLUSH TABLES. And watch out for deadlocks. – symcbean Mar 3 '14 at 12:58

I presume you are aware that objective 3 contradicts objective 1.

Obvious solutions would be to implement the data storage on a mirrored filesystem. When you want to take a backup, stop the DBMS, flush the write buffers, break the mirror and restart the DBMS, then mount the mirrored filesystem elsewhere and start a second instance of the DBMS using the copy and use that for backup.

(while you could break the mirror without shutting down the DBMS you'll then have to repair the data and won't have a consistent snapshot).

Alternatively, setup a replication cluster to a second machine and carry out the backup from the other node.

(using filesystem snapshots might be another approach - but there is a big performance overhead with LVM or BTRFS).

| improve this answer | |
  • Thanks symcbean. Yeah, I'm aware that restoring a table from a past state into the current state of a database would break consistency. It's useful to have the table restoration option available incase just one table was nuked and the only way to recover it was to restore just that table which would be better than restoring the whole shabang. It may also be that we wanted to see the state of a table from a few days ago because it got corrupted. – Chris Rosillo Mar 3 '14 at 16:53

You can also consider Percona XtraBackup (http://www.percona.com/software/percona-xtrabackup) though AFAIK it is not compatible with MyISAM

Edited: Actually it is compatible with MyISAM

| improve this answer | |

1, 2 and 3 you can achieve if using Xtrabackup and InnoDB. For MyISAM it's not possible to get all three.

| improve this answer | |
  • XtraBackup supports MyISAM tables too, but to get a consistent view it locks the tables to copy them. – akuzminsky Mar 3 '14 at 14:25

mysqldumplvm Holland Backup

To expand on how this works:

-> Issues FLUSH WITH READ LOCK -> Takes an LVM snapshot of the MySQL data dir -> Spins up a separate MySQL instance based off this snapshot -> Performs a mysqldump on each database to database.sql -> Compresses these files -> Kills separate MySQL instance and removes snapshot

I'm thinking the mysqldump step could be amended to loop through each table and mysqldump to database/table.sql instead. This would enable per-table restoration.

| improve this answer | |

Use MySQL's replication functionality to create a second, replica database. Then do your backups on the replica. Locking/slowing the replica database won't affect the performance of your master database at all and will provide consistent backups.

Backing up each table separately will require writing a wrapper script around mysqldump to separate the backup files, OR (even faster but only works on MyISAM tables) you can shut down the replica database and just copy the entire data directory. Each table will have it's own data files. Restart the replica server and it'll catch back up from the binary logs.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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