Flush, Lock MySQL tables and Copy Data Folder

I'm thin开发者_运维问答king about building a php script that flushes, locks and copys a mysql data folder. Because I need to lock the tables and typical dump takes 5 minutes plus, I was thinking if I do a flush, lock and file copy of the data folder, it should be quicker. Anyone any experience of this and know if this is a viable solution?

Look to a XtraBackup also if you are planning to do non-stop backups of your data.

The MySQL devs are way ahead of you. The rolled your exact method with some syntactic sugar and proper error checking in a command called mysqlhotcopy.

Might be too late now but what phase does all this break down into? If your server is spending most of the five minutes copying the files instead of the actual flushing then your problem is simply a slow disk.

I think the best answer to the question is the following Windows command:

set bin=C:\Program Files\MySQL\MySQL Server 5.6\bin
"%bin%/mysql" -e "FLUSH TABLES WITH READ LOCK; UNLOCK TABLES;" --user=UserName --password=Password DatabaseName

This quickly forces all MySQL data for a database to its MySQL three or four files in the MySQL data folder, from which they can be copied to some other folder. You'll have to customize this command for your particular version of MySQL and for your database and admin user.

I couldn't get the other answers to work, but this BAT/CMD command works fast and well in my experience.

The only other suggestion I can make is to use the MySQL Workbench (which comes with MySQL) to Stop the MySQL server. When it is stopped it is flushed to disk. Don't forget to Start your MySQL server when you are finished using the files directly (at which time MySQL reads the database files from disk).

Note: if you simply copy the data files for a database to the data folder of another instance of MySQL that is already open, you won't see the data in MySQL applications! You would think that MySQL would check the date/time modified of the disk files to detect updates, but it doesn't. And it doesn't keep its files locked, showing Windows that they are in use, which they are. So strange.