Setting Up MySQL Master‑Slave Replication and Read/Write Splitting with Spring Boot

Installation and Basic Configuration of MySQL 8.0.16

Download the MySQL 8.0.16 Linux generic package and upload it to /app/mysql on two CentOS7 virtual machines (master: 192.168.249.131, slave: 192.168.249.129). Stop the firewall if it is running:

service firewalld status  ## view status
service firewalld stop    ## stop firewall

Extract the archive:

xz -d mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz
tar -xvf mysql-8.0.16-linux-glibc2.12-x86_64.tar

Create a data directory, a user group and a user for MySQL:

groupadd mysql
useradd -g mysql -d /app/mysql mysql

Initialize the database:

/app/mysql/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld --user=mysql --basedir=/app/mysql --datadir=/app/mysql/data --initialize

Copy the init script and give it execution permission:

cp mysql-8.0.16-linux-glibc2.12-x86_64/support-files/mysql.server /etc/init.d/mysqld

Edit /etc/my.cnf to set the base directory, data directory, socket and other options (see the full snippet in the original guide). Ensure the error log file exists and is owned by the MySQL user:

chown -R mysql:mysql /app/mysql/data/log/error.log

Start MySQL and create a symbolic link for the client:

service mysqld start
ln -s /app/mysql/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysql /usr/local/bin/mysql

Log in with the temporary password printed during initialization, then change the root password:

mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '000000';

Allow remote connections for the root user:

use mysql;
update user set host='%' where user='root' limit 1;
flush privileges;

Configure Master‑Slave Replication (Binlog)

Replication Principles

The master writes data changes to the binary log (binlog).

The slave reads the binlog via an I/O thread and writes it to a relay log.

The slave replays the relay log to apply the changes locally.

Requirements

Same OS version and architecture on both servers.

Identical MySQL versions.

Identical initial data.

Unique server_id for each instance and binary logging enabled on the master.

Master Configuration (192.168.249.131)

Create a replication user:

CREATE USER 'slave'@'192.168.249.129' IDENTIFIED WITH 'mysql_native_password' BY '000000';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'192.168.249.129';
FLUSH PRIVILEGES;

Enable binlog and set a unique server_id in /etc/my.cnf :

[mysqld]
log-bin=mysql-bin
server-id=131
binlog-do-db=mydb
binlog-ignore-db=mysql

Restart MySQL and check the master status:

show master status;

Slave Configuration (192.168.249.129)

Set a unique server_id and enable binlog:

[mysqld]
server-id=129
log-bin=mysql-bin
replicate-do-db=mydb
replicate-ignore-db=mysql

Configure the master connection using the values obtained from the master status (File and Position):

stop slave;
change master to master_host='192.168.249.131', master_user='slave', master_password='000000', master_log_file='mysql-bin.000001', master_log_pos=155;
start slave;

Verify the slave status:

show slave status\G

Spring Boot Integration for Read/Write Splitting

Only INSERT , UPDATE , DELETE should go to the master, while SELECT goes to the slave. The project uses the open‑source dynamic-datasource-spring-boot-starter library.

Dependencies (pom.xml)

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.0.1</version>
    </dependency>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
        <version>2.5.5</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.15</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
</dependencies>

Dynamic DataSource Configuration (application.yml)

spring:
  datasource:
    dynamic:
      primary: master
      strict: false
      datasource:
        master:
          type: com.zaxxer.hikari.HikariDataSource
          url: jdbc:mysql://192.168.249.131:3306/mydb?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
          username: root
          password: '000000'
          driver-class-name: com.mysql.cj.jdbc.Driver
        slave:
          type: com.zaxxer.hikari.HikariDataSource
          url: jdbc:mysql://192.168.249.129:3306/mydb?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
          username: root
          password: '000000'
          driver-class-name: com.mysql.cj.jdbc.Driver

Mapper, Entity and Service

Create a User entity, a MyBatis mapper with addUser and getById methods, and a service implementation that uses @DS("slave") on the read method to route queries to the slave.

Bootstrapping

Annotate the main class with @SpringBootApplication@MapperScan("com.jianzh5.dynamic.mapper") and run the application.

Testing

Write JUnit tests that insert a user (executed on the master) and retrieve a user by ID (executed on the slave) to verify the read/write splitting works as expected.

After observing the logs, the application correctly switches between master and slave based on the @DS annotation, completing the Spring Boot integration of MySQL master‑slave read/write separation.