5

I know that the cornerstone of mysql backup is mysqldump which works for all engines. I also see that some folks advocate the use of LVM snapshots.

What would be the point at which you stop using mysqldump and start creating LVM snapshots?

Is mysqldump more suitable for smaller databases and LVM snapshots - larger ones?

Thanks!

2 Answers 2

9

I'd suggest LVM, or any other file system level, snapshots should only be used if the restore will only ever go back on the same machine (or a clone of it). For more flexible and reliable backups you should stick to mysqldump, which will provide you with a backup that can be restored to any machine, including ones running a different OS.

If you get to the point where running mysqldump presents any kind of issue then you should be running master/slave replication and perform the backups on the slave.

4

mysqldump does not take consistent snapshots across databases. It also locks the database while dumping. ie: When the dump is running your database is usually read only to clients.

If this is not acceptable LVM snapshots are the next step. The database(s) need to be locked and flushed while the LVM snapshot is being taken. That is generally faster, only a few seconds.

Generally mysqldump is an easy way to do backups, and it works well for smaller databases and less critical systems. Larger or more critical systems use LVM snapshots or commercial backups systems.

1
  • 2
    Note that if you use the --lock-all-tables or --master-data option mysqldump locks all tables on all databases to get a consistent dump.
    – uesp
    Mar 26, 2011 at 14:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .