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.– ZoredacheSep 4, 2015 at 21:58
1 Answer
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)
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
Apr 17, 2011: How can I optimize a mysqldump of a large database?Mar 14, 2014: mysqldump with automatically generated export name
-
It looks like my db is using MyISAM exclusively, so I'll probably go with the non-mysqldump option. stackoverflow.com/questions/4515490/…– samwyseSep 8, 2015 at 15:03