Master‑Slave MySQL Replication: Full Setup, Types, and Troubleshooting

Overview

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 replayed on the slaves. This is MySQL’s default and is efficient when it can be applied precisely.

Row‑based replication (RBR): the actual row changes are logged and applied on the slaves. Introduced in MySQL 5.0, it works for any statement but can generate larger binary logs.

Mixed replication: MySQL starts with SBR and automatically switches to RBR when a statement cannot be replicated accurately.

Problems Solved by Replication

Data distribution

Load balancing

Backup

High availability and failover

How Replication Works

The master records changes in the binary log (binlog).

The slave copies the master’s binlog events into its relay log.

The slave replays the relay log events to update its own data.

The diagram below illustrates the process:

Master‑Slave Configuration

Assume two MySQL 5.0.18 servers: a master (IP 10.100.0.100) and a slave (IP 10.100.0.200). The initial data sets are identical.

Create Replication User

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

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

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

Configure the Master

Enable binary logging and assign a unique server‑id in my.cnf:

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

Restart MySQL and verify with SHOW MASTER STATUS.

Configure the Slave

Set the following options in my.cnf and restart the slave:

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

Note: log_bin on the slave is optional unless the slave itself will act as a master for other slaves.

Start Replication

Use CHANGE MASTER TO to point the slave at the master without editing configuration files:

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 slave: START SLAVE; Verify with SHOW SLAVE STATUS\\G. The key fields are Slave_IO_Running=Yes and Slave_SQL_Running=Yes. When both are Yes and Seconds_Behind_Master is 0, replication is working.

Adding a New Slave

To clone a fresh slave, you need:

A snapshot of the master’s data at a specific point.

The master’s current binary‑log file name and position (found with SHOW MASTER STATUS).

The master’s binary‑log files.

Cloning methods include:

Cold copy – stop the master, copy its data files, then restart.

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

mysqldump – lock tables, dump all databases, unlock, and import the dump on the new slave.

FLUSH TABLES WITH READ LOCK;
mysqldump --all-databases --lock-all-tables > dbdump.db;
UNLOCK TABLES;

Deep Dive: Statement‑Based vs Row‑Based

Statement‑based replication logs the original SQL statements. It is simple and produces small logs, but it can fail for non‑deterministic statements, functions that depend on the master’s environment, or when using stored procedures and triggers.

Row‑based replication logs the actual row changes. It works for any statement and avoids many nondeterministic issues, but the binary log can become large and is not human‑readable.

MySQL 5.1 allows dynamic switching via the binlog_format session variable.

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 connection information for the slave. relay-log.info – stores the slave’s current binary‑log and relay‑log positions.

Replication Filters

Filters can limit which events are replicated, either on the master (binary‑log filtering) or on the slave (relay‑log filtering). This is useful for replicating only a subset of databases or tables.

Common Topologies

Single master with multiple slaves (most common).

Master‑master active‑active (both servers act as master and slave).

Master‑master active‑passive (one server is read‑only).

Cascading replication (master → first‑level slaves → second‑level slaves).

Dual‑master with slaves (a standby master plus its own slaves).

Each topology has trade‑offs in terms of write load, latency, and fault tolerance.

Common Problems & Troubleshooting

Error 1: Last_IO_Error after CHANGE MASTER – usually a network or authentication issue.

Error 2: Stopping slave while tables are locked – unlock tables or finish the transaction before stopping.

Error 3: Running CHANGE MASTER without stopping the slave – stop the slave first.

Error 4: Duplicate server_id on master and slave – assign unique IDs or use --replicate-same-server-id with caution.

Error 5: Slave_IO_Running=No after CHANGE MASTER – restart MySQL to apply changes.

Error 6: Slave reads a binlog position beyond the master’s file size – caused by sync_binlog settings; set sync_binlog=1 or adjust the slave’s position.

Data‑sync errors: Missing rows, duplicate primary keys, or update failures – resolve by manually fixing data, using sql_slave_skip_counter, or re‑initializing the slave.

Typical commands for fixing errors:

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;