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 ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.