Understanding MySQL Internals

Chapter 12. Replication

MySQL implements asynchronous master-slave replication. The master keeps a log of updates, while the slave reads it and executes it in sequence. This chapter discusses some details of MySQL replication.

Overview

MySQL replication is relatively simple and straightforward. A server may act as a master or a slave. The master maintains a log of updates that is called the binary log for historical reasons. The binary log records events. Each event contains some information that is relevant for the slave to be able to execute the update exactly the same way the master did it. The majority of events are merely SQL queries that update the database in one way or another. However, it is also necessary to store some metadata that the slave must use to recreate the context of the update in order for the update query to yield the same results.

The slave connects to the master and starts executing updates as it reads them from the master’s binary log. There are two threads on the slave to perform this work: the I/O thread and the SQL thread. The I/O thread downloads the contents of the master binary log and stores them locally in temporary files called relay logs. The relay logs are processed by the SQL thread, which re-creates the original execution context and executes the updates.

The slave keeps track of where it is in the replication process via two parameters: current log name and current log position. If the slave ever disconnects from the master and then reconnects, ...