How can I reinitialize some MyISAM tables without restarting MySQL?

Question

I'm using rsync to copy some measurement data from one PC to the server.

Is it possible to load the new MyISAM tables without restarting the MySQL server?

Answer 1

Absolutely !!!

Suppose you are moving a MyISAM table with the following characteristics:

• MyISAM table is called mydb.mytable
• datadir is /var/lib/mysql
• Moving From ServerA to ServerB

You can move it to another folder without disturbing any other DB activity

Here is how

Step01) Create the Target Database on ServerB with this command

CREATE DATABASE mydb;

Step02) Copy the table to the mydb database on ServerB

Run this rsync on ServerA

rsync -av --progress /var/lib/mysql/mydb/mytable.* ServerB:/var/lib/mysql/mydb

That's it. The table should be immediate registered in the information_schema without a restart. To verify that the rsync worked, login to mysql on ServerB and run this:

SELECT COUNT(1) TableExists FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';
SELECT table_rows TableCount1 FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';
SELECT COUNT(1) TableCount2 FROM mydb.mytable;

If TableExists is 1 and TableCount1 = TableCount2, CONGRATULATIONS !!!

If you want to double check the rsync, run thi command on both ServerA and ServerB

CHECKSUM TABLE mydb.mytable;

Both checksum values should be identical

Give it a Try !!!