I depend on MySQL replication to provide off-site backup and data distribution to "satellite" databases. Unlike two-phase commit, the replicating databases can be off-line without affecting the primary database. This loose coupling is often preferable.

The work flow with replication is for the primary (MASTER in mysql terms) database to write all of its transactions into a log file. This log file is copied tot he replicating (SLAVE) databases as "relay-logs". Then the SQL commands are read from the relay-logs and applied to the database. The file 'relay-log.info' records the current position within the relay-logs and the original logs on the MASTER server for the next SQL command to execute..

An improper shutdown on the SLAVE server can result in corrupted relay data preventing normal replication. The usual fix is to simply get the MASTER log file name and position from the relay-log.info file and issue a "CHANGE MASTER TO" SQL command. You can get this information from the SQL command "SHOW SLAVE STATUS" or by reading the relay-log.info file directly.

=CHANGE MASTER TO MASTER_LOG_FILE='(use the relay_master_log_file)', MASTER_LOG_POS=(use the exec_master_log_pos);=

references the labels from SHOW SLAVE STATUS

The CHANGE MASTER TO command can fail (Operational Error) if the relay-log.info references a missing or broken relay log. The error message will say: could not initialize master info structure You will need to move relay-log.info to a different name/directory so that MySQL no longer tries to process that relay log. Then simply repeat the CHANGE MASTER TO command as above.

The critical point to remember is to use the MASTER_LOG_FILE and MASTER_LOG_POS from relay-log.info. The file master.info also contains MASTER log information, but that's used for building the relay logs. You need to restart at the first unprocessed SQL command.

-- LloydKvam - 22 Jul 2011