Skip to topic | Skip to bottom
Www.MySQLReplicationr1.1 - 22 Jul 2011 - 10:07 - LloydKvamtopic end

Start of topic | Skip to actions
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 '' 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 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 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 references a missing or broken relay log. The error message will say: could not initialize master info structure You will need to move 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 The file 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
to top

Www.MySQLReplication moved from Www.MySQLTAT on 22 Jul 2011 - 14:08 by LloydKvam - put it back
You are here: Www > LinuxResources > TipsAndTechniques > MySQLReplication

to top

All content is Copyright © 1999-2023 by, and the property of, the contributing authors.
Questions, comments, or concerns? Contact GNHLUG
Legal Notice (includes Terms of Service)