0

MySQL comes with "dump" commands and utilities that convert a database to text form and store that text somewhere on disk. For a large database, converting to text can be a slow operation and generate large SQL files that contain text.

MySQL maintains three or four internal and compact files in a Windows folder named for the database and located in its data folder. Copying these files is much faster than copying SQL text files.

But these files are not updated while the MySQL server is running, so copying from these files may yield old data and copying to these files may not show the new data in MySQL applications.

Is there a BAT/CMD command that I can use to make the data files consistent (correct) so they can be copied out of or into MySQL?

David Spector
  • 113
  • 8
  • Key phrase that should appear in this question: "physical backup". Here's [a question on DBA](https://dba.stackexchange.com/questions/78573/how-to-take-physical-backup-of-mysql-database) –  May 23 '18 at 14:07
  • Wumbley: I don't have enough "reputation" to create any keyword containing "physical" because it would be a new keyword. – David Spector May 24 '18 at 00:51
  • I just meant it's a good search term. I didn't even think of the possibility of it being a tag. –  May 24 '18 at 01:09

1 Answers1

0

My own suggestion is to use 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 the internal MySQL data for a database to its files into the database folder in the MySQL data folder, from which they can be copied to some other folder (for backup or replication). You'll have to customize this command for your particular version of MySQL and for your database and admin user.

This BAT/CMD command works fast and well in my experience. I use it frequently for backup and for updating MySQL applications between two computers.

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 actually are.

David Spector
  • 113
  • 8