Setup a replication of MYISAM database

We have a database running on "192.168.1.106", and a DR site "10.1.0.4". Now we want to setup a slave database on "10.1.0.4". 

Most tables on "192.168.1.106" are MYISAM.

I think the most difficult part is to get a consistent copy of the whole database, because most of the tables on "192.168.1.106" are MYISAM tables. The general approach is to shutdown it or lock the whole database for a long time.

I have monitored the database server for days, try to get a accurate time when the loading is very low, so I can lock the database and minimize the side effect. Unfortunately I didn't get it.

 

The day before tomorrow I found that the "cp" command had a "update" option, which can copy only the updated files.

So I copied the whole database when the database is running, then locked it in read mode, got the bin-log position(for setup slave), and used the "update" option to copy the incremental part. The lock process just lasted for seconds.

 

Then I transferred the copy to "10.1.0.4", checked the configure file to make sure the database can work as a slave.

Because the database version on "10.1.0.4" is 5.1.53, which is higher than "192.168.1.106", so I run the "mysql_upgrade" command after I started the server.

 

Then I setup the slave, using the replication user I setup on "192.168.1.106" and the bin-log position I got when locked the database.

 

I found some tables were corrupt after I started the slave process, and I don't know why.

I can just re-dump then from "192.168.1.106" to import then into "10.1.0.4". It's too dangers to do it like this, because this would lead duplicate rows in those tables after restarted the slave process.

 

As expected, I saw "duplicate rows" error when I used "show slave status".

So I deleted the rows generated after I did the last "update" optioned "cp" command, and restarted the slave. It works!!!