MySQL Master‑Slave Replication: Principles, Setup Guide, and Interview Questions

Master‑Slave Replication Overview

In production environments, MySQL master‑slave replication is used to avoid single‑point failures and improve overall service performance by separating write operations on the master from read operations on the slaves.

Typical scenarios involve a long‑running write that could lock tables and make the service unavailable; replication distributes the load and enhances user experience.

Replication Principles

MySQL replication consists of a master server that handles writes and one or more slave servers that handle reads. The process is asynchronous by default.

Three main threads are involved:

master (binlog dump thread) – writes data‑change events to the binary log.

I/O thread – on the slave, requests the binlog from the master.

SQL thread – on the slave, reads the relay log and applies changes to the slave database.

Replication strategies include synchronous, semi‑synchronous, asynchronous, and delayed replication, each balancing consistency and performance.

Step‑by‑Step Setup on CentOS 7 (MySQL 8)

1. Verify existing MySQL packages

rpm -qa | grep mysql

2. Remove old MySQL packages

sudo yum remove mysql*

3. Remove MariaDB if present

// Check for MariaDB
rpm -qa | grep mariadb
// Remove MariaDB
sudo rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64

4. Delete residual MySQL configuration files

sudo find / -name mysql

Delete the listed paths, e.g.:

sudo rm -rf /usr/lib64/mysql

5. Install MySQL 8 repository and the server

wget https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm
sudo yum -y install mysql80-community-release-el7-3.noarch.rpm

After installation, mysql-community.repo and mysql-community-source.repo appear in /etc/yum.repos.d/ .

6. Update YUM cache and install the server

# Update YUM cache
yum clean all
yum makecache
# Install MySQL server
sudo yum -y install mysql-community-server

7. Start MySQL and enable it at boot

# Start MySQL
systemctl start mysqld.service
# Verify status
systemctl status mysqld
# Enable on boot
systemctl enable mysqld

8. Retrieve and change the initial root password

grep "password" /var/log/mysqld.log

Use the timestamp‑matching entry as the initial password, then:

mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewPassword';

9. Create a remote user for replication

// Create user
create user 'test'@'%' identified by 'LDCldc-2020';
// Open firewall or stop it
systemctl stop firewalld.service;
systemctl disable firewalld.service;

10. Verify network connectivity between the two hosts

ping 192.168.163.156

Assume Master: 192.168.163.156 and Slave: 192.168.163.155 .

11. Configure the master

Create a test database and grant privileges:

create database test default character set utf8mb4 collate utf8mb4_general_ci;
grant all privileges on test.* to 'test'@'%';

Edit /etc/my.cnf and add:

# Character set
character_set_server=utf8mb4
init_connect='SET NAMES utf8mb4'
# Replicate only the test database
binlog-do-db=test
# Exclude system databases
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# Log retention
expire_logs_days=30
# Enable binary logging
log-bin=mysql-bin
# Unique server ID
server-id=3

Restart MySQL and verify binary logging:

systemctl restart mysqld
show variables like '%log_bin%';

Record the File and Position from show master status; for later use.

12. Create a replication user on the master

create user 'backup'@'%' identified by 'LDCldc-2020';
grant file on *.* to 'backup'@'%';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'backup'@'%';

13. Configure the slave

Create the same test database and grant privileges.

create database test default character set utf8mb4 collate utf8mb4_general_ci;
grant all privileges on test.* to 'test'@'%';

Edit /etc/my.cnf on the slave:

# Unique slave ID
server-id=4
read_only=1
master_info_repository=TABLE
relay_log_info_repository=TABLE

Restart MySQL on the slave.

systemctl restart mysqld

14. Point the slave to the master

change master to master_host='192.168.163.156', master_port=3306, master_user='backup', master_password='LDCldc-2020', master_log_file='mysql-bin.000001', master_log_pos=1513;

Check the slave status:

show slave status\G

When Slave_IO_Running and Slave_SQL_Running both show Yes , replication is successful.

If Slave_IO_Running is No , restart MySQL and run:

stop slave;
start slave;

Finally, test by creating a table in the master’s test database; the table and its data should appear on the slave.

Interview Questions

What are the advantages of MySQL master‑slave replication?

High performance: read‑write separation distributes load and avoids lock‑table issues.

Reliability: if the master fails, a slave can be promoted; if a slave fails, other slaves continue serving reads.

If replication reaches write‑performance bottlenecks, how would you solve it?

Use sharding (horizontal partitioning) or split databases by business domain to distribute write load.

How to handle replication lag when the slave reads stale data?

Choose a synchronous replication strategy for strong consistency (at the cost of performance) or accept eventual consistency with asynchronous replication based on business needs.