I'm attempting to setup slave replication of a large MyISAM table. I login to the master server and perform
FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS and that returns right away with the position and file. I then take a LVM snapshot, and then issue
UNLOCK TABLES (note: I do this in the same mysql prompt, I do not exit). As far as I know, this is the correct way to do this.
I then rsync the snapshot to my new system, set the permissions and start up mariadb, almost immediately it says, "Triggering myisam-recover for all MyISAM tables and aria-recover for all Aria tables" and then I look at the processlist for this server and I see that it is doing a 'Repair by sorting' on this table.
Why is this table being repaired, if the tables were originally flushed? Is it possible that the high number of queries and size of the database means that the operating system has not yet flushed the blocks to disk, because it has a lot of dirty buffers, yet still returns me to the command line for the
FLUSH TABLES WITH READ LOCK command?
I would like to be able to avoid the
repair table, because it takes a day and half to complete, but I don't understand why there is a file descriptor difference in the MYD making mariadb decide it needs to repair. The only way I can think of to be sure there is no difference would be to shutdown completely the master's mariadb process, and then do the lvmsnapshot, however then I cannot get the results of a
SHOW MASTER STATUS to setup the replication.
Can someone please tell me how to solve this (also, please don't tell me to move off of MyISAM, believe me I would if I was allowed to).
I decided to try it a second time, and do a
myisamchk --update-state --myisam_sort_buffer_size=8G --key_buffer_size=8G --read_buffer_size=1024M --write_buffer_size=1024M /var/lib/mysql/db/store.MYI to see if I could tell why it thinks it needs to repair, and it says this:
Data records: 365221349 Deleted blocks: 266143
myisamchk: warning: 1 client is using or hasn't closed the table properly
- check file-size
- check record delete-chain
myisamchk: warning: Found 34585232 deleted space in delete link chain. Should be 34630096
myisamchk: error: Found 265707 deleted rows in delete link chain. Should be 266143
myisamchk: error: record delete-link-chain corrupted
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links
and that counter is increasing, I have no idea where it is trying to get to, but I suspect this will take many hours.