Ensuring data integrity of mysqldump <-> rsync

Question

Hello,

I use rsync to back up the files on my server, and mysqldump to back up my database. Here's my concern:

A mysqldump on my database takes about 30 seconds. I have a table called photos that stores info about the images a user has uploaded, including the path to the file. I am worried about what will happen when photos are uploaded or deleted during the 30 seconds it takes to complete the mysqldump. If that happened and I were then to restore the rsync'd files and the mysqldump data, I could then be looking at a database that contains rows pointing to deleted photos, or missing rows for photos that were successfully uploaded.

How can I make sure the mysqldump exactly matches the rsync?

Thanks in advance, Brian

Answer 1

Use LOCK TABLES to block any write activity from the tables you're backing up. Then unlock them once your mysqldump is finished.

Answer 2

You could MD5 the resulting mysqldump (on the server) and the transfered (locally) by rsync, then compare the two hashes to ensure they match. Another alternative, is to set mysqldump on a version controlled file (with git or svn or your favorite vcs). The advantage with git, for example, is that you could easily setup some post-commit hooks to push the changes to a remote server, and the upload would be just the differences between versions, not the entire dump. This way you could think in decreasing the backup period.

Answer 3

I think the answer is simple, just run rsync AFTER you complete mysqldump :) This way at worst you will have a couple NEW files that are not in the db dump, but you will not have inconsistent db entries.