Spring Boot + Sharding-JDBC 读写分离

2019-07-26

本文使用 Sharding-JDBC 实现读写分离，基于 CentOS 7 + MySQL 5.7

一、MySQL 安装及配置

1.1 安装

依次执行命令：

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

启动：

1	sudo systemctl start mysqld

1.2 修改密码

查看默认密码：

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

进入数据库：

1	mysql -uroot -p

修改密码：

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

远程访问：

1
2
3

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

1.3 主从配置

本文一主 (192.168.30.101) 两从 (192.168.30.102, 192.168.30.103)

1.3.1 主库

1	sudo vim /etc/my.cnf

# server-id 给数据库服务的唯一标识
server-id=101
# log-bin 设置此参数表示启用 binlog 功能，并指定路径名称
log-bin=/var/lib/mysql/mysql-bin
sync_binlog=0
# 设置日志过期天数
# binlog-ignore-db 表示同步时忽略的数据库
# binlog-do-db 表示需要同步的数据库
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

重启数据库，执行 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;

重启数据库，执行 SQL:

1	show master status;

记录下 File 和 Position

1.3.2 从库配置

以 192.168.30.102 为例：

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

重启数据库，执行 SQL:

1
2
3

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;

其中 master_log_file 和 master_log_pos 分别为上步记录主库的 File 和 Position

二、使用

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 轮询
        name: db1s2
        master-data-source-name: db-master-1
        slave-data-source-names: db-slave-1,db-slave-2

    props:
      sql:
        show: true

完整代码：GitHub

参考：