Step-by-Step Guide to Configuring MySQL Master‑Slave Replication and Read/Write Splitting

Objective Build two MySQL servers: a master for write operations and a slave for read operations.

Test Environment Master: CentOS 7, MySQL 15.1, IP 192.168.1.233 Slave: CentOS 7, MySQL 15.1, IP 192.168.1.234

Step 1: Ensure Identical Data Create a test database named test on both servers so that the schemas match.

Step 2: Create Replication Account on Master

Use a dedicated account with REPLICATION SLAVE privilege. Example:

CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'repl123';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';

Open firewall port 3306 if needed:

firewall-cmd --zone=public --add-port=3306/tcp --permanent

firewall-cmd --reload

Step 3: Configure Master Server

Enable binary logging and assign a unique server‑id (e.g., 1) in my.cnf under the [mysqld] section:

[mysqld]

log-bin=mysql-bin

server-id=1

Optional settings for specific databases can be added (e.g., binlog-do-db=mstest ).

Restart MySQL and verify master status with SHOW MASTER STATUS; (record File and Position).

Step 4: Configure Slave Server

Assign a unique server‑id (e.g., 2) and optionally specify databases to replicate:

[mysqld]

server-id=2

replicate-do-db=test

replicate-ignore-db=mysql

Stop any existing slave process, then point the slave to the master:

STOP SLAVE;

CHANGE MASTER TO MASTER_HOST='192.168.1.233', MASTER_USER='repl', MASTER_PASSWORD='repl123', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=313;

START SLAVE;

Check slave status with SHOW SLAVE STATUS \G; .

Step 5: Test Data Synchronization

Create a table (e.g., stu_user ) on the master; it appears automatically on the slave. Insert a row on the master and verify it replicates to the slave.

Step 6: Implement Read/Write Splitting

Use the lightweight SpeedPHP framework. Configure the framework with master and slave connection details, then write simple CRUD methods. Reads are directed to the slave (192.168.1.234) and writes to the master (192.168.1.233), achieving read/write separation.

References

Official MySQL replication documentation, plus several Chinese blog tutorials.