What I Would Do If I Ran Tarsnap

Could anyone explain in more detail this tidbit from the article:

> Did you want to backup your MySQL database? Did you backup the actual data files rather than a mysqldump? Sucks to be you...

What are the dangers of backing up the data files instead of a mysqldump?

Briefly: that's a great way to get a backup which is not actually an accurate and consistent representation of your database, unless everything about both your setup and the exact state of MySQL's memory and your write usage over the interval of your backup operation breaks in your favor. For more detail, see the part about "Copying Table Files" in the backup chapter of the manual.

Makes sense -- thanks.

I had a scary moment when I thought we were backing up our databases as data files... fortunately someone smarter than me set that up and in fact our backups are mysqldump's. Phew!

They're big binary blobs.

If the tables aren't locked while you're backing up, you're going to end up with inconsistent data.

There are tools like xtrabackup that allow you to extract a live copy of the files, but sometimes take some hand loving to get working on another system.

A good solution I found when wanting to copy those particular files instead of a dump/restore was to utilise the snapshotting features of LVM.

e.g. 1. lock tables

2. take snapshot

3. unlock tables

4. mount snapshot and copy data from it

Then the system continues to work and you have a consistent copy to back up.