springboot2.2.0+sharding-sphere+MySQL5.7实现数据库读写分离

springboot2.2.0+sharding-sphere+MySQL5.7实现数据库读写分离

最近公司项目遇到MySQL数据库读写性能瓶颈，需要做数据库主从复制来实现数据读写分离。

通过上网查询有三种实现方式：

1. 通过手工编码实现
2. 通过MyCat中间件MySQL代理来实现
3. sharding-sphere来实现

通过比较上面三种实现方式，通过手动编码实现难度高，时间成本很大。通过MyCat中间件实现，需要部署MyCat服务器，成本高。最后选择sharding-sphere来实现相对容易，sharding-sphere提供jar包使用方式实现程序级的读写分离，去中心化。

软件版本说明：

springboot 2.2.0.RELEASE
shareding-sphere 4.0.0-RC2
MySQL5.7.27

服务器规划

这里采用一主一从的架构如下所示：
mysql-master（主）192.168.77.130:3306
mysql-slave （从）192.168.77.131:3306

用户名：root
密码：123456

服务场景

mysql-master 负责写操作
mysql-slave 负责读操作

在192.168.77.130上安装mysql-master主数据库

创建目录:

mkdir -p /etc/mysql/conf.d
mkdir -p /var/lib/mysql

安装mysql:

docker run --name mysql-master --restart always -p 3306:3306 -v /etc/mysql/conf.d:/etc/mysql/conf.d -v /var/lib/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.27

在192.168.77.131上安装mysql-slave从数据库

创建目录:

mkdir -p /etc/mysql/conf.d
mkdir -p /var/lib/mysql

安装mysql:

docker run --name mysql-slave --restart always -p 3306:3306 -v /etc/mysql/conf.d:/etc/mysql/conf.d -v /var/lib/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.27

主从数据库具体配置

分别在mysql-master和mysql-slave配置文件目录/etc/mysql/conf.d创建配置文件my.cnf

配置mysql-master主数据库，修改my.cnf加入如下配置：

[mysqld]
log-bin=mysql-bin
server-id=1

binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

binlog-do-db=demo

重启mysql-master让配置生效：

docker restart mysql-master

配置mysql-slave从数据库，修改my.cnf加入如下配置：

[mysqld]
server-id=2

replicate-do-db=demo
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

重启mysql-slave让配置生效

docker restart mysql-slave

在mysql-master上创建复制用户给mysql-slave使用

CREATE USER 'replication'@'192.168.77.131' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.77.131';
flush privileges;

在mysql-slave开启IO复制线程

CHANGE MASTER TO 
MASTER_HOST='192.168.77.130', 
MASTER_PORT=3306,
MASTER_USER='replication', 
MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=3289;

START SLAVE; //开启IO复制线程

MASTER_LOG_FILE和MASTER_LOG_POS通过在mysql-master上运行如下命令获取：

show master status;

+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000001 |     3289 | demo         | mysql,information_schema,performance_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+

查看mysql-slave复制状态

show slave status\G;

出现如下字段表名IO复制线程已经正常运行：

Slave_IO_State: Waiting for master to send event  
Slave_IO_Running: Yes  
Slave_SQL_Running: Yes  
Seconds_Behind_Master: 0

在mysql-master上查看mysql-slave是否连接到主数据库：

show slave hosts;

在mysql-master创建demo数据库

在mysql-master中创建测试表t_product

建表语句：

CREATE TABLE `t_product`  (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `category_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `product_price` decimal(10, 2) NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`product_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;

创建成功后可以在mysql-slave查看demo数据库中是否存在t_product,如果存在表示主从复制搭建成功。

新建一个springboot项目在pom文件中添加依赖

    <properties>
        <java.version>1.8</java.version>
        <mybatis-plus-boot-starter.version>3.2.0</mybatis-plus-boot-starter.version>
        <sharding-sphere.version>4.0.0-RC2</sharding-sphere.version>
        <guava.version>28.1-jre</guava.version>
        <springfox-swagger2.version>2.9.2</springfox-swagger2.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus-boot-starter.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>${guava.version}</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>${springfox-swagger2.version}</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>${springfox-swagger2.version}</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

配置application.yml：

spring:
  # shardingsphere读写分离配置
  shardingsphere:
    datasource:
      names: master, slave
      master:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://192.168.77.130:3306/demo?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: 123456
      slave:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://192.168.77.131:3306/demo?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: 123456
    masterslave:
      name: ms
      master-data-source-name: master
      slave-data-source-names: slave
    props:
      show.sql: true
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss   # 日期格式化
  server:
    port: 8080
# 打印sql
logging:
  level:
    com.geeker.demo.mapper: debug

源码地址