Mastering MySQL Replication: Types, Setup, and Troubleshooting Guide

Overview of MySQL Replication

MySQL’s built‑in replication is the foundation for building large, high‑performance applications by distributing data from a master server to one or more slave servers. The master writes changes to a binary log, and each slave reads those events, replays them, and keeps its data in sync.

Supported Replication Types

Statement‑based replication (SBR): the SQL statements executed on the master are sent to the slave and re‑executed. This is the default and is efficient when statements can be reproduced exactly.

Row‑based replication (RBR): the actual row changes are logged, ensuring correct replication for all statements but producing larger binary logs.

Mixed replication: MySQL automatically switches from SBR to RBR when a statement cannot be safely replicated.

What Replication Solves

Data distribution

Load balancing

Backups

High availability and failover

How Replication Works (Three Steps)

The master records changes to the binary log (binary log events).

The slave copies those events into its relay log.

The slave replays the relay log, applying the changes to its own data.

Master‑Slave Configuration

Environment

MySQL version 5.0.18 on both master and slave

IP addresses: master 10.100.0.100, slave 10.100.0.200

Create a replication account on the master

mysql> GRANT REPLICATION SLAVE, RELOAD, SUPER ON *.* TO backup@'10.100.0.200' IDENTIFIED BY '1234';

If the password algorithm differs between versions, set the old password format:

set password for 'backup'@'10.100.0.200' = old_password('1234');

Copy data (initial sync)

Stop the master, copy its data directory to the slave, and keep both servers read‑only until the copy finishes.

Configure the master

server-id=1
log-bin=mysql-bin

Restart the master and verify with SHOW MASTER STATUS.

Configure the slave

log_bin               = mysql-bin
server_id             = 2
relay_log             = mysql-relay-bin
log_slave_updates     = 1
read_only             = 1

Start the slave by issuing a CHANGE MASTER TO statement instead of editing the config file:

CHANGE MASTER TO MASTER_HOST='server1', MASTER_USER='repl', MASTER_PASSWORD='p4ssword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0;

Then start the replication: START SLAVE; Check status with SHOW SLAVE STATUS\\G. The key fields are Slave_IO_Running=Yes and Slave_SQL_Running=Yes.

Adding a New Slave

When the master has been running for a while, a new slave can be added by:

Taking a snapshot of the master’s data.

Recording the master’s binary‑log file name and position (log file coordinate) via SHOW MASTER STATUS.

Copying the binary log files.

Cloning a Slave

Cold copy : stop the master, copy its files to the new slave, then restart.

Warm copy : for MyISAM tables, use mysqlhotcopy while the server runs.

mysqldump method:

Lock tables: FLUSH TABLES WITH READ LOCK; Dump all databases: mysqldump --all-databases --lock-all-tables > dbdump.sql Unlock tables:

UNLOCK TABLES;

Deep Dive: Statement vs. Row Replication

Statement‑based replication is simple and produces small logs, but it can fail for non‑deterministic statements, stored procedures, or when time‑dependent functions are used. Row‑based replication logs the exact row changes, guaranteeing correctness for any statement, but the logs can be large and are not human‑readable.

The binlog_format session variable can be set to STATEMENT, ROW, or MIXED to control the format.

Replication‑Related Files

mysql-bin.index

: tracks all binary‑log files. mysql-relay-bin.index: tracks relay‑log files on the slave. master.info: stores the master’s connection info for the slave. relay-log.info: stores the slave’s current binary‑log and relay‑log positions.

Sending Replication Events to Other Slaves

When log_slave_updates is enabled, a slave can act as a master for downstream slaves, propagating its own binary log events.

Replication Filters

Filters can be applied on the master (binary‑log events) or on the slave (relay‑log events) to replicate only a subset of databases or tables.

Common Topologies

Single master with multiple slaves (most common).

Master‑master active‑active: both servers are masters and slaves of each other; requires unique server-id values to avoid loops.

Master‑master active‑passive: one node is read‑only, providing high availability.

Cascade replication (master → slave‑1 → slave‑2 …) to reduce load on the primary master.

Dual‑master with slaves: a standby master replicates from the primary and serves its own slaves.

Common Problems and Solutions

Change master errors : ensure the slave is stopped before issuing CHANGE MASTER TO.

Locked tables : stop the slave before unlocking tables.

Identical server‑id : assign different server_id values on each server.

Slave I/O thread not running : verify network connectivity and correct master host/port.

Binlog position mismatch after master restart : set sync_binlog=1 on the master or adjust MASTER_LOG_POS on the slave.

Missing rows on slave : use SET GLOBAL sql_slave_skip_counter=1; after fixing the data.

Duplicate primary key errors : delete the duplicate row on the slave before restarting replication.

Images