MySQL Replication: Master‑Slave Configuration, Binlog Formats, and Protocol Details

MySQL replication involves three binlog formats—Statement‑Based Replication (SBR), Row‑Based Replication (RBR), and Mixed‑Based Replication (MBR)—each corresponding to the binlog modes STATEMENT, ROW, and MIXED. The article begins with a brief overview of these concepts and the need to understand master‑slave relationships.

1. Configuring the MySQL Master

The master server listens on port 3306. Its my.cnf configuration includes essential settings such as port=3306 , basedir , datadir , log_bin=mysql-bin , and a unique server_id=10 . The master is started with:

# sudo bin/mysqld --defaults-file=/usr/local/mysql8.0.20/etc/my.cnf --user=root

A replication user is created:

GRANT REPLICATION SLAVE ON *.* TO repl@'localhost' IDENTIFIED BY '123456';

Its status can be checked via:

show master status \G;

2. Configuring the MySQL Slave

The slave runs on port 3309 with its own my.cnf (named salve.conf in the article). Important parameters include port=3309 , log_bin=mysql-bin , server_id=2 , and read_only=1 . The slave is started with:

# sudo bin/mysqld --defaults-file=/usr/local/mysql8.0.20/etc/salve.conf --user=root

After connecting, the replication channel is defined:

change master to master_host='localhost', master_user='repl', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=0;

Synchronization status can be inspected using show slave status; and binlog events with SHOW BINLOG EVENTS; . Common error 1236 is resolved by stopping, resetting, and restarting the slave:

stop slave;
reset slave;
start slave;

3. Binlog Format Settings

The binlog_format variable determines which mode MySQL uses. The recommended setting is MIXED, but MySQL 8.0.20 defaults to ROW. Example configuration:

#binlog日志格式，mysql默认采用statement，建议使用mixed
binlog_format = MIXED
log-bin = /data/mysql/mysql-bin.log
expire_logs_days = 7
max_binlog_size = 100m
binlog_cache_size = 4m
max_binlog_cache_size = 512m

When GTID is enabled, the server may send COM_BINLOG_DUMP_GTID instead of COM_BINLOG_DUMP .

4. Replication Protocol Flow

On the slave side, the start slave; command triggers start_slave() , which stops existing threads, loads replication metadata, and launches the I/O and SQL threads. The I/O thread executes handle_slave_io() , performing the following steps:

Initialize the slave thread.

Connect to the master ( safe_connect() ).

Register the slave on the master ( register_slave_on_master() ), sending a COM_REGISTER_SLAVE packet.

Enter a loop that repeatedly requests binlog dumps ( request_dump() ), which may issue COM_BINLOG_DUMP or COM_BINLOG_DUMP_GTID depending on GTID settings.

The master processes these commands in dispatch_command() , handling registration and binlog dump requests, and then streams the binlog to the slave via Binlog_sender::send_binlog() .

5. Packet Sequence During Setup

Typical SQL statements exchanged during the establishment of replication include querying the current timestamp, obtaining the master’s SERVER_ID , setting the heartbeat period ( SET @master_heartbeat_period=30000001024 ), configuring checksum, checking GTID mode, and exchanging UUIDs.

6. Summary

MySQL supports three replication modes: STATEMENT (SBR), ROW (RBR), and MIXED (MBR).

MySQL 8.0.20 defaults to ROW mode.

Binlog transmission uses COM_BINLOG_DUMP or COM_BINLOG_DUMP_GTID .

The default master heartbeat period is 30 seconds (nanoseconds unit).

The I/O and SQL threads correspond to handle_slave_io() and handle_slave_sql() respectively.