←back to thread

801 points tnorthcutt | 1 comments | | HN request time: 0.24s | source
Show context
jordanlev ◴[] No.7526381[source]
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?

replies(2): >>7526642 #>>7527320 #
1. chrismsnz ◴[] No.7527320[source]
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.