Master MySQL Master‑Slave Replication: Setup, Configuration & Common Issues

1. Master‑Slave Replication Basics

Understanding MySQL replication starts with the binary log (binlog), which records completed events and serves as the basis for most replication setups.

1.1 Binary Log Management

Location and naming are set in my.cnf with log-bin = mysql-bin.000000. Example queries:

show variables like '%log_bin%';
show binary logs;

Binlog records events, uses the ROW format by default, and can be inspected with:

show variables like '%format%';

1.2 Traditional Backup Methods and Their Drawbacks

Binary log backup

mysqldump – logical backup, CPU‑intensive and slower to restore

xtrabackup – physical backup, large files and limited cross‑platform support

1.3 Benefits of MySQL Replication

High availability

Backup assistance

Load balancing

2. MySQL Replication Overview

2.1 Replication Technique

Ensures data safety through real‑time asynchronous or semi‑synchronous copying from the master to one or more slaves.

2.2 Replication Architecture

Typical scenarios include real‑time backup, read/write separation, and splitting traffic across multiple slaves.

2.3 Replication Process

Enable binlog on the master.

Master writes changes to binlog.

Slave requests binlog, receives it, and applies events locally.

2.4 First‑Time Master‑Slave Setup

On the slave, execute CHANGE MASTER TO with host, user, password, port, log file and position.

Start the slave with

START SLAVE;

3. Master‑Slave Configuration Steps

3.1 Multi‑Instance Slave Configuration

Example environment (CentOS 6.9, MySQL 5.6):

# cat /etc/redhat-release
CentOS release 6.9 (Final)
# uname -r
2.6.32-696.el6.x86_64
# iptables status
iptables: Firewall is not running.
# getenforce
Disabled
# mysql --version
mysql Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using EditLine wrapper

Start instances:

/data/3306/mysql start
/data/3307/mysql start

Master my.cnf (excerpt):

[mysqld]
port = 3306
log-bin = /data/3306/mysql-bin
server-id = 6
skip_name_resolve = 0

Slave my.cnf (excerpt):

[mysqld]
port = 3307
log-bin = /data/3307/mysql-bin
server-id = 7
read_only = 1
skip_name_resolve = 0

Create replication user on master:

grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

Backup master data:

mysqldump -uroot -p123 -A -B -F --master-data=2 -S /data/3306/mysql.sock > /tmp/full.sql

Restore on slave (disable binlog during restore):

set sql_log_bin=0;
source /tmp/full.sql;

Configure slave to start replication:

CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=120;
START SLAVE;

3.2 Test Replication

Create a database on the master and verify it appears on the slave.

3.3 Common Issues and Solutions

Slave lag: check hardware I/O, network, binlog storage, dump thread load.

Replication stopped: verify Slave_IO_Running and Slave_SQL_Running are Yes .

Delay configuration: CHANGE MASTER TO MASTER_DELAY = 30; Read‑only slave: set read_only = 1 in my.cnf.

Skip errors: SET GLOBAL sql_slave_skip_counter = 1; or slave-skip-errors = 1032,1062,1007 in config.

4. Frequently Asked Questions

4.1 Slave binlog lagging behind master

Causes include disk I/O limits, network latency, heavy dump thread load, or intentional delay nodes.

4.2 Master updates not reflected on slave

Check that the SQL thread is running, no conflicting statements, and that no delay is configured.

4.3 How to configure replication delay

Stop slave, set MASTER_DELAY, then start slave again.

4.4 Securing the slave (read‑only for non‑root users)

Add read_only = 1 (and optionally innodb_read_only = 1 for root) to my.cnf and restart.

4.5 Skipping replication errors

Use STOP SLAVE;, SET GLOBAL sql_slave_skip_counter = 1;, then START SLAVE; or configure slave-skip-errors in the config file.

4.6 Understanding Slave_*_Running status

Slave_IO_Running

indicates the I/O thread state (Yes/No/Connecting); Slave_SQL_Running indicates the SQL thread state (Yes/No).

4.7 Relay log coordinates

Relay_Log_File

and Relay_Log_Pos show the last executed event in the slave's relay log, corresponding to the master’s binlog position.

4.8 Single master‑slave considerations

Use the slave for real‑time backup, offload read traffic, or as part of a multi‑slave topology to improve scalability.