Re: re syncing a broken replicatoin

[Rick James]

Not if you have InnoDB. FLUSH TABLES is not sufficient.

1. Stop MySQL (Master).
2. tar (& zip) data directory
3. Start MySQL.

Now, you can more leisurely deal with the slave:
0. Slave Mysql should be stopped
1. copy tarball to slave; unzip/untar
2. Start mysql with the option to skip-slave-start
3. do CHANGE MASTER... Note: the stop on the master left you conveniently at a binlog boundary. The Pos will be 4 (or 0) of the next binlog written after the dump.
4. Wait for replication to catch up. (Else users will get stale data.)

Please double check this procedure, I haven't done it in a long time. (I usually have multiple slaves, so never need to dump master.)

It's hard to say whether to include zipping -- it adds CPU time, but saves I/O time. Different systems will perform differently. Of course, if you don't have enuf space for a non-zipped copy, you need to do the zip.

Newer Linux OSs have "Logical Volume Manager" This will let you do a snapshot in seconds, regardless of the size. How, you ask? Bovine magic (COW).

Your procedures for finding positions, etc, probably work equally well. I think mine are simpler.