At what point do you stop using mysqldump and start doing LVM snapshot?

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!

Solution 1:

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.

Solution 2:

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.