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</code>
<code>cd /usr/local/mysql</code>
<code>tar -xvf mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz</code>
<code>groupadd mysql</code>
<code>useradd -g mysql mysql</code>
<code>chown -R mysql.mysql /usr/local/mysql

Configure the master server (my.cnf)

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

Create a replication user on the master

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

Configure the slave to connect to the master

STOP SLAVE;</code>
<code>CHANGE MASTER TO</code>
<code>MASTER_HOST='192.168.100.103',</code>
<code>MASTER_PORT=3306,</code>
<code>MASTER_USER='slave',</code>
<code>MASTER_PASSWORD='123456',</code>
<code>MASTER_LOG_FILE='master-bin.000001',</code>
<code>MASTER_LOG_POS=30;</code>
<code>START SLAVE;</code>
<code>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']);</code>
<code>DB::update('update users set name = ? where id = ?', ['lisi', 1]);</code>
<code>DB::delete('delete from users where id = ?', [1]);</code>
<code>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.