MySQL Master‑Slave Replication Setup Guide

Introduction

As application data grows, read queries dominate and single‑server performance degrades. Deploying MySQL in a master‑slave replication mode separates read and write traffic, allowing multiple servers to share the load and improve response time.

Replication Principle

Replication uses three threads: the master writes updates to the binary log, the slave I/O thread copies the binary log to a local relay log, and the slave SQL thread replays the relay log to apply changes.

-- Master writes updates to binary log (master thread)
-- Slave I/O thread copies binary log to relay log (slave I/O thread)
-- Slave SQL thread reads relay log and replays events (slave SQL thread)

1. Configure the Master

1.1 Create replication user

# Create user
create user 'repl'@'%' identified by 'repl';
# Grant replication privileges
grant replication slave, replication client on *.* to 'repl'@'%' identified by 'repl';

1.2 Edit /etc/my.cnf (under [mysqld] )

log-bin          = mysql-bin
log-bin-index    = mysql-bin.index
binlog_format    = mixed
server-id        = 21
sync-binlog      = 1
character-set-server = utf8

Restart the master service: service mysqld restart 1.3 Backup master data

# Lock tables for a consistent snapshot
flush tables with read lock;
# Show binary log position
show master status;
# Dump data (InnoDB example)
mysqldump -uroot -ptiger --all-database -e --single-transaction --flush-logs --max_allowed_packet=1048576 --net_buffer_length=16384 > /data/all_db.sql;
# Unlock tables
unlock tables;

2. Configure the Slave

2.1 Edit /etc/my.cnf (under [mysqld] )

log-bin                = mysql-bin
binlog_format          = mixed
log-slave-updates      = 0
server-id              = 22
relay-log              = mysql-relay-bin
relay-log-index        = mysql-relay-bin.index
read-only              = 1
slave_net_timeout      = 10

Restart the slave service: service mysqld restart 2.2 Import the backup (if created) mysql -uroot -p < /data/all_db.sql 2.3 Align binary‑log coordinates

change master to
master_host='192.168.2.21',
master_user='repl',
master_password='repl',
master_port=3306,
master_log_file='mysql-bin.000001',
master_log_pos=120;

2.4 Start replication start slave; Check replication status: show slave status\G; Key fields to verify: Slave_IO_Running and Slave_SQL_Running must be Yes, and Relay_Master_Log_File should match Master_Log_File with identical positions, indicating full synchronization.

3. Verification

Create a test database on the master (e.g., mysql_test) and confirm it appears on the slave, proving successful replication.

References

MySQL Official Documentation – Replication How‑To: https://dev.mysql.com/doc/refman/5.7/en/replication-howto.html

Article on promoting a slave to master: https://blog.csdn.net/alen_liu_sz/article/details/79451581