This paper uses Sharding-JDBC to realize read-write separation based on entOS 7 + MySQL 5.7

I. MySQL Installation and Configuration

1.1 installation

Execute orders in turn:

sudo wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

sudo yum -y install mysql57-community-release-el7-10.noarch.rpm

sudo yum -y install mysql-community-server

sudo yum -y remove mysql57-community-release-el7-10.noarch

Start up:

sudo systemctl start  mysqld

1.2 Modifying Password

View the default password:

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

Enter the database:

mysql -uroot -p

Change Password:

alter user 'root'@'localhost' identified by 'NEW PASSWORD';

Remote access:

use mysql;
grant all privileges on *.* TO 'root'@'%' identified by 'PASSWORD';
flush privileges;

1.3 master-slave configuration

This paper consists of one main subject (192.168.30.101) and two subordinates (192.168.30.102, 192.168.30.103).

1.3.1 main library

sudo vim /etc/my.cnf

# Unique Identity of server-id for Database Services
server-id=101
# log-bin Sets this parameter to enable bin log functionality and specify the path name
log-bin=/var/lib/mysql/mysql-bin
sync_binlog=0
# Set log expiration days
# binlog-ignore-db represents a database that is ignored in synchronization
# binlog-do-db represents a database that needs to be synchronized
expire_logs_days=7
binlog-do-db=test
binlog-ignore-db=information_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema

Restart the database and execute SQL:

grant replication slave on *.* to 'root'@'192.168.30.102' identified by 'YOUR PASSWORD';
flush privileges;

grant replication slave on *.* to 'root'@'192.168.30.103' identified by 'YOUR PASSWORD';
flush privileges;

Restart the database and execute SQL:

show master status;

Record File and Postion

1.3.2 Slave Library Configuration

Take 192.168.30.102 as an example:

log-bin=mysql-bin
server-id=102
binlog-ignore-db=information_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60

Restart the database and execute SQL:

stop slave;
change master to master_host='192.168.30.101',master_user='root',master_password='YOUR PASSWORD',master_log_file='mysql-bin.000002', master_log_pos=154;
start slave;

Where master_log_file and master_log_pos are File and Postion of the previous record master library respectively

Two, use

2.1 pom.xml

<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.1.0</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.18</version>
</dependency>
<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>3.1.0</version>
</dependency>

2.2. application.yml

spring:
  main:
    allow-bean-definition-overriding: true

mybatis:
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath:mapping/*.xml

sharding:
  jdbc:
    datasource:
      names: db-master-1,db-slave-1,db-slave-2

      db-master-1:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.30.101:3306/test
        username: root
        password: root
        maxPoolSize: 20

      db-slave-1:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.30.102:3306/test
        username: root
        password: root
        maxPoolSize: 20

      db-slave-2:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.30.103:3306/test
        username: root
        password: root
        maxPoolSize: 20

    config:
      masterslave:
        load-balance-algorithm-type: round_robin # random, round_robin polling
        name: db1s2
        master-data-source-name: db-master-1
        slave-data-source-names: db-slave-1,db-slave-2

    props:
      sql:
        show: true

Complete code: GitHub

Reference resources:

1. CentOS 7 Yum installation of MySQL 5.7
2. Sharing-JDBC tutorial: Mysql database master-slave building
3. Sharing-JDBC Tutorial: Spring Boot Integrates Sharing-JDBC to Realize Read-Write Separation