I've got a bunch of MyISAM files (MYD, MYI, FRM) in the mysql directory and newer versions of them in another directory. How can I replace "old" ones with "newer" without stopping the mysql server?
Replacing MyISAM Files Without Stopping MySQL
MySQL
Best Answer
For my answer, let's assume
Here is a bait-and-switch approach
Step 01)
mysql> CREATE DATABASE baitandswitch;
This should create the folder
/var/lib/mysql/baitandswitch
Step 02) Place the new version of the proddata table into the
baitandswitch
database as follows:CAVEAT : Don't worry about mysql. It will detect the presence of the new table in the
baitandswitch
database and update the information_schema on-the-fly.Step 03) Perform a swap of the proddata table between
proddb
database andbaitandswitch
databaseI recommend that you run these two commands on the same line:
That's it
Have fun with it.
Give it a Try !!!
CAVEAT
Step 03 uses SQL to perform the swap because it will negotiate when it is safe to move the table. Hence, if there are any queries running against it, the execution of the SQL will wait for all queries to cease before performing the move of the table.
An added benefit of this approach is that the old table is still availble in the
baitandswitch
database. You can either make copies of the tables in the OS, run SQL against it, or simply drop thebaitandswitch
database to delete all tables that needed to be switched.