Chapter 19 Replication

Table of Contents

19.1 Chapter Organization

19.2 Source Code Files

19.3 Principles

19.3.1 Binlog Formats

19.3.2 Differences Between Master and Slave

19.3.3 Crashes

19.3.4 Binlog Files and Indexes

19.4 Rules

19.4.1 Determining the Logging Format

19.4.2 Unsafe Statements

19.4.3 Logging Transactions

19.4.4 Logging Updates to auto_increment Columns

19.4.5 Logging Access to Variables and Functions

19.4.6 Other Unsafe Statements

19.4.7 binlog_row_image

19.4.8 Replication Locks

Status of this section: up to date 2009-10-21

This chapter describes MySQL replication principles, rules, and code, as it is supposed to work in version 5.1.

The MySQL replication feature allows a server - the master - to send all changes to another server - the slave - and the slave tries to apply all changes to keep up-to-date with the master. Replication works as follows:

• Whenever the master's database is modified, the change is written to a file, the so-called binary log, or binlog. This is done by the client thread that executed the query that modified the database.

• The master has a thread, called the dump thread, that continuously reads the master's binlog and sends it to the slave.

• The slave has a thread, called the IO thread, that receives the binlog that the master's dump thread sent, and writes it to a file: the relay log.

• The slave has another thread, called the SQL thread, that continuously reads the relay log and applies the changes to the slave server.