Replace MyISAM files without stopping Mysql

Question

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?

Answer 1

For my answer, let's assume

• you are replacing a table called proddata in the proddb database
• you have proddata.frm, proddata.MYD, proddata.MYI in /root

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:

$ cp /root/proddata.frm /var/lib/mysql/baitandswitch/.
$ cp /root/proddata.MYD /var/lib/mysql/baitandswitch/.
$ cp /root/proddata.MYI /var/lib/mysql/baitandswitch/.

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 and baitandswitch database

mysql> ALTER TABLE proddb.proddata RENAME baitandswitch.proddata_old;
mysql> ALTER TABLE baitandswitch.proddata RENAME proddb.proddata;

I recommend that you run these two commands on the same line:

mysql> ALTER TABLE proddb.proddata RENAME baitandswitch.proddata_old; ALTER TABLE baitandswitch.proddata RENAME proddb.proddata;

That's it

• No locking
• No flushing
• No data corruption
• No stopping the DB Server

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 the baitandswitch database to delete all tables that needed to be switched.

Answer 2

LOCK TABLES bla;
FLUSH TABLES bla;

Swap files on the Operating System level.

FLUSH TABLES bla;
UNLOCK TABLES bla;

(credit to Jeff for the lock table)

Answer 3

Don't. Instead, load them into another copy of mysql, and get a dump using mysqldump. Then load that into the running mysql.