Mastering MySQL Master‑Slave Replication: A Step‑by‑Step Guide

What Is MySQL Master‑Slave Replication

When data changes on the master server, those changes are synchronized in real time to the slave server.

Benefits of Master‑Slave Replication

Horizontal scaling of database load.

Fault tolerance and high availability (Failover/High Availability).

Data backup.

How Master‑Slave Replication Works

First, understand the master‑slave architecture, as shown in the diagram below.

All operations—INSERT, UPDATE, DELETE, as well as creating functions or stored procedures—are performed on the master. When the master executes an operation, the slave receives the event quickly and applies the same change.

The replication mechanism works as follows:

On the master, replication events are written to a special binary log (binary‑log).

On the slave, the slave reads these events and applies the corresponding changes to its own data.

Types of Replication Events

There are three formats for replication events: statement, row, and mixed.

Statement : SQL statements are written to the binlog.

Row : Each row change is written to the binlog.

Mixed : A combination of statement and row; MySQL decides which format to use for each event.

Operations on the Master

When data changes on the master, the events are recorded in order in the binlog. The master starts a

binlog dump

thread for each connected slave, which notifies the slave of binlog updates and streams the log contents.

Operations on the Slave

When replication is enabled, the slave creates two threads:

I/O thread : Connects to the master, receives binlog data, and writes it to a local relay log.

SQL thread : Reads the relay log and executes the recorded changes on the slave database.

Viewing Replication Threads

Use the

SHOW PROCESSLIST

command to view the binlog dump thread on the master and the I/O/SQL threads on the slave.

One‑Picture Summary

Practical Step‑by‑Step Setup

Environment

<code>os: ubuntu16.04
mysql: 5.7.17</code>

Machines

<code>master IP: 192.168.33.22
slave IP: 192.168.33.33</code>

Configure the Master

1. Edit

/etc/mysql/mysql.conf.d/mysqld.cnf

:

<code># your master IP
bind-address = 192.168.33.22
# each server needs a unique server-id
server-id = 1
# enable binary logging
log_bin = /var/log/mysql/mysql-bin.log</code>

2. Restart MySQL:

<code>sudo systemctl restart mysql</code>

3. Create a replication user for the slave:

<code>mysql -u root -p
CREATE USER 'slave1'@'192.168.33.33' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'192.168.33.33';</code>

4. Lock the master tables for a consistent dump:

<code>FLUSH TABLES WITH READ LOCK;</code>

5. Record the master’s binary‑log file and position:

<code>SHOW MASTER STATUS;</code>

6. Export the master data:

<code>mysqldump -u root -p --all-databases --master-data > dbdump.sql</code>

7. Unlock the tables:

<code>UNLOCK TABLES;</code>

8. Transfer the dump to the slave:

<code>scp dbdump.sql [email protected]:/home/ubuntu</code>

Configure the Slave

1. Edit

/etc/mysql/mysql.conf.d/mysqld.cnf

:

<code># your slave IP
bind-address = 192.168.33.33
# unique server-id for the slave
server-id = 2
# enable binary logging
log_bin = /var/log/mysql/mysql-bin.log</code>

2. Restart MySQL:

<code>sudo systemctl restart mysql</code>

3. Import the dump file:

<code>mysql -u root -p < /home/ubuntu/dbdump.sql</code>

4. Set up replication and start it:

<code>mysql -u root -p
STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='192.168.33.22',
    MASTER_USER='slave1',
    MASTER_PASSWORD='slavepass',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=613;
START SLAVE;</code>

After these steps, master‑slave synchronization is operational.