MySQL in a Nutshell, 2nd Edition

Replication Process

When replication is running, SQL statements that change data are recorded in a binary log (bin.log) on the master server as it executes them. Only SQL statements that change the data or the schema are logged. This includes data-changing statements such as INSERT, UPDATE, and DELETE, and schema-manipulation statements such as CREATE TABLE, ALTER TABLE, and DROP TABLE. This also includes actions that affect data and schema, but that are executed from the command line by utilities such as mysqladmin. This does not include SELECT statements or any statements that only query the server for information (e.g., SHOW VARIABLES).

Along with the SQL statements, the master records a log position identification number. This is used to determine which log entries the master should relay to the slave. This is necessary because the slave may not always be able to consistently receive information from the master. We’ve already discussed one situation where an administrator deliberately introduces a delay: the planned downtime for making a backup of the slave. In addition, there may be times when the slave has difficulty staying connected to the master due to networking problems, or it may simply fall behind because the master has a heavy load of updates in a short period of time. However, if the slave reconnects hours or even days later, with the position identification number of the last log entry received, it can tell the master where it left off in the binary log and the master ...