Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 4 down vote accepted

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.

share|improve this answer
    
This makes sense, thanks! –  georg Dec 15 '11 at 10:09
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)

share|improve this answer
    
lock table, flush table, swap table, flush table. –  Jeff Ferland Dec 14 '11 at 15:49

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

share|improve this answer
    
Thanks for the tip, but this doesn't answer my question. –  georg Dec 14 '11 at 14:38
    
That's true, but as long as mySQL is running, the files will be in use. You will run a risk of corruption if you were to try to replace them while the database is running. –  Rilindo Dec 14 '11 at 15:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.