2

I've got a MySQL database on a server running Windows 7, and I want to create a quick (but safe!) copy without shutting anything down. I issue FLUSH TABLES WITH READ LOCK, run Robocopy, then issue UNLOCK TABLES. Here's the issue: If I use the /B option to Robocopy, it prints a lot of messages saying "The process cannot access the file because it is being used by another process.", and only copies 209 of the 535 files in MySql data directory. If I leave off the /B option, Robocopy reports that all files are copied. But now I'm unsure. I'm guessing that MySQL leaves the table files open even while things are locked, and Robocopy/B decides that backing them up would be unsafe. Without /B, Robocopy makes its best effort, which should work because MySQL isn't doing any I/O, but I'd like to opinion of someone wiser than I. Thanks!

  • Unless you have a good reason not to, you really should be using mysqldump to properly backup mysql databases. – Zoredache Sep 4 '15 at 21:58
4

MyISAM

If you have an all-MyISAM database, this could work because all of the MyISAM tables will have 0 file handle count in their headers.

InnoDB

If you have any InnoDB tables, FLUSH TABLES WITH READ LOCK; may not provide proper insulation for data integrity. I wrote about this in the DBA StackExchange back in November 2012 (How to run a cold backup with Linux/tar without shutting down MySQL slave?)

Given the InnoDB Architecture (Picture made by Percona CTO Vadim Tkechenko)

InnoDB Plumbing

there are four objects that are still moving after FLUSH TABLES WITH READ LOCK; is issued:

  • Double Write Buffer
  • Insert Buffer
  • Redo Logs
  • Undo Logs

Suppose you launch FLUSH TABLES WITH READ LOCK; and Robocopy and it takes 5 minutes to copy, that means any physical changes that are being done during the 5 minutes will not be there in the backup. Even worse, the file changes will not be from a single point-in-time. This means there may be some .ibd files that may or may not have changed but the transaction holding its changes was not fully committed.

You would have to copy that data to another machine, start MySQL, and see if crash recovery (which occurs during mysqld startup, reading and processing those four objects) makes the data stable and usable.

You are better off using mysqldump with the --single-transaction option.

For some ideas, see my old DBA StackExchange posts

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.