MySQL Master‑Slave Replication: Theory, Installation, Configuration, and Laravel Read‑Write Separation

Database master‑slave replication is a technique where data changes on a primary (master) MySQL server are copied to one or more secondary (slave) servers using the binary log (binlog) to keep the data consistent.

Advantages include higher data availability (slaves can serve reads if the master fails), load balancing of read operations, automatic backup and fast recovery, and horizontal scalability.

Disadvantages are replication latency, increased operational complexity, potential data consistency issues due to asynchronous replication, and the need for manual failover handling.

Key precautions: the MySQL versions on master and slaves must match, the servers must be network‑reachable, and after MySQL 8.0 the default authentication plugin (caching_sha2_password) may need to be changed to mysql_native_password for compatibility.

Step 5 – Download and install MySQL 8.0 on three machines (master 192.168.100.103, slaves 192.168.100.101 and 192.168.100.102). Download URL: https://downloads.mysql.com/archives/community/ .

Installation commands:

mkdir /usr/local/mysql
cd /usr/local/mysql
tar -xvf mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz
groupadd mysql
useradd -g mysql mysql
chown -R mysql.mysql /usr/local/mysql

Configure the master server (my.cnf)

[mysqld]
user=root
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
port=3306
max_connections=200
max_connect_errors=10
character-set-server=utf8mb4
default-storage-engine=INNODB
default_authentication_plugin=mysql_native_password
lower_case_table_names=1
group_concat_max_len=102400
# Core settings
server-id=1
log-bin=master
binlog-do-db=mysql_test
binlog_format=MIXED

Configure the slave servers (my.cnf) – the file is identical except for server-id (2 for 192.168.100.101, 3 for 192.168.100.102) and the same core settings.

[mysqld]
user=root
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
port=3306
max_connections=200
max_connect_errors=10
character-set-server=utf8mb4
default-storage-engine=INNODB
default_authentication_plugin=mysql_native_password
lower_case_table_names=1
group_concat_max_len=102400
# Core settings
server-id=2   # or 3 for the second slave
log-bin=master
binlog-do-db=mysql_test

Create a replication user on the master

# Syntax: CREATE USER 'username'@'IP address' IDENTIFIED BY 'password';
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SELECT host, user, plugin, authentication_string FROM mysql.user;

Configure the slave to connect to the master

STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='192.168.100.103',
MASTER_PORT=3306,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='master-bin.000001',
MASTER_LOG_POS=30;
START SLAVE;
SHOW SLAVE STATUS\G;

If Slave_IO_Running = Yes and Slave_SQL_Running = Yes , replication is successful.

Laravel read‑write separation example

DB::insert('insert into users (id, name) values (?, ?)', [1, 'zhangsan']);
DB::update('update users set name = ? where id = ?', ['lisi', 1]);
DB::delete('delete from users where id = ?', [1]);
DB::select('select * from users where id = :id', ['id' => 1]);

After performing these insert, update, delete, and select operations on the master (192.168.100.101), the changes are automatically synchronized to the slave servers.