Sharding-Sphere：Sharding-JDBC读写分离，Docker搭建MySQL主从

读写分离可以提高系统吞吐量，在网上看了很多SpringBoot实现读写分离的文章，大部分是基于Spring AOP 硬编码Java代码去实现的，代码侵入性较大，原理是：如果调用select、get开头的方法，就调用从库，否则调用主库，本篇将基于当当开源的sharding-jdbc实现读写分离，看名字就知道它是在jdbc上，代码零入侵，配置文件配置主从库地址。

本篇博客将分成两大块：1.Docker环境搭建MySQL读写分离，2.SpringBoot基于Sharding-JDBC应用读写分离

Docker环境搭建Mysql读写分离

搭建Docker环境下MySQL的读写分离时，遇到一个坑，最新的MySQL镜像是8.0版本的，无法成功搭建，后面用的5.7.26就好了

镜像地址：https://hub.docker.com/_/mysql

docker pull mysql:5.7.26

下面简述Docker配置MySQL主从过程

（1）在本机上创建2个.cnf的配置文件，2个数据存放目录，用来保存MySQL数据

master_data/
master.cnf
slave_data/
slave.cnf

（2）mysql.cnf配置文件内容如下，只有server-id不能一样

[mysqld]
pid-file   = /var/run/mysqld/mysqld.pid
socket     = /var/run/mysqld/mysqld.sock
datadir    = /var/lib/mysql
symbolic-links=0
character-set-server = utf8   
#skip-networking  
innodb_print_all_deadlocks = 1
max_connections = 2000  
max_connect_errors = 6000  
open_files_limit = 65535  
table_open_cache = 128    
binlog_cache_size = 1M  
max_heap_table_size = 8M  
query_cache_type = 1  
query_cache_size = 8M  
query_cache_limit = 2M  
ft_min_word_len = 4  

#从库需要修改端口后再用docker启动
port=3306
#从库的server-id不能和主库一致
server-id = 1
log-bin = master-bin.log
sync_binlog=1
#binlog_format = mixed  
binlog_format = row

performance_schema = 0  
explicit_defaults_for_timestamp  
#lower_case_table_names = 1  
interactive_timeout = 2880000
wait_timeout = 2880000 
# Recommended in standard MySQL setup  
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES   
[mysqldump]  
quick  
max_allowed_packet = 64M  
[myisamchk]
tmp_table_size = 16M  
join_buffer_size = 128M  
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
#MySQL读入缓冲区的大小
read_buffer_size = 16M
#MySQL的随机读缓冲区大小
read_rnd_buffer_size = 8M

#线程缓存大小
thread_cache_size=16
thread_concurrency=32

（3）Docker运行2个mysql镜像，使用下面的命令，改一下主从库的配置文件地址与数据存放目录，并设置root的登陆密码123456，从库修改配置文件中的port=3307，对应docker启动命令-p 3307:3307

docker run -itd --name mysql_master -v /data/www/database/mysql/master.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf -v /data/www/database/mysql/master_data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 mysql:5.7.26

（4）找到主库容器ID，进入主库容器控制台docker exec -it container id /bin/bash

（5）进入mysql命令行，mysql -uroot -p123456，输入如下命令：

创建一个用户，让从库通过该用户进行复制日志
创建一个用户：create user 'yezhou'@'%' identified by '123456';
用户赋予权限：grant all privileges on *.* to 'yezhou'@'%' with grant option;
赋予复制权限：grant replication slave on *.* to 'yezhou'@'%' with grant option;
使刚刚的命令生效：flush privileges;
查看主库日志文件：show master status;  //记住File与Position的值，从库要用

（6）进入从库容器mysql命令行，和上面一样的命令，连接主库

连接主库：change master to master_host='192.168.0.115',master_user='root',master_password='123456',master_port=3306,master_log_file='master-bin.000004',master_log_pos=1118;  //主库记录的Position值
开启从库：start slave;
查看从库状态：show slave status\G
如果连接配置错误要重新更改，可以使用 stop slave; reset slave;

看到如下两个 Yes，则说明主从库搭建成功

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

（7）使用 mysql 客户端连接主库，创建一个数据库和表，再连接从库查看该数据库是否成功同步过来

SpringBoot基于Sharding-JDBC应用读写分离

Sharding-JDBC是当当开源的分库分表，读写分离的利器，基于JDBC，已经进入apache孵化器项目
GitHub的地址：https://github.com/apache/incubator-shardingsphere
shardingsphere：https://github.com/apache/incubator-shardingsphere-example
中文文档地址：https://shardingsphere.apache.org/document/current/cn/overview/

先看看Sharding-JDBC的架构图，可以有个直观的了解

（1）使用HikariCP与mybatis进行读写分离的验证，创建了3个表

-- ----------------------------
-- Table structure for t_member
-- ----------------------------
DROP TABLE IF EXISTS `t_member`;
CREATE TABLE `t_member` (
  `member_id` bigint(20) NOT NULL,
  `member_name` varchar(36) DEFAULT NULL,
  `nick_name` varchar(36) DEFAULT NULL,
  `account_no` varchar(20) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birth_date` datetime DEFAULT NULL,
  `ebl_flag` varchar(1) DEFAULT '1',
  `del_flag` varchar(1) DEFAULT '0',
  `description` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='会员表';

-- ----------------------------
-- Table structure for t_order
-- ----------------------------
DROP TABLE IF EXISTS `t_order`;
CREATE TABLE `t_order` (
  `order_id` bigint(20) NOT NULL,
  `member_id` varchar(36) DEFAULT NULL,
  `order_code` varchar(36) DEFAULT NULL,
  `order_amount` varchar(20) DEFAULT NULL,
  `status` varchar(1) DEFAULT '1',
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';

-- ----------------------------
-- Table structure for t_order_item
-- ----------------------------
DROP TABLE IF EXISTS `t_order_item`;
CREATE TABLE `t_order_item` (
  `item_id` bigint(20) NOT NULL,
  `order_id` varchar(36) DEFAULT NULL,
  `product_name` varchar(128) DEFAULT NULL,
  `item_account` double DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单详情表';

（2）新建SpringBoot项目，添加引用：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
</dependency>

（3）配置文件，请不要用ds_master，ds_slave等，有下划线会报错

server.port=8070

# jdbc_config   datasource
#spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#spring.datasource.url=jdbc:mysql://192.168.0.115:3306/appblog_business1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&allowMultiQueries=true&useSSL=false
#spring.datasource.username=root
#spring.datasource.password=123456

# Hikari will use the above plus the following to setup connection pooling
#spring.datasource.type=com.zaxxer.hikari.HikariDataSource
#spring.datasource.hikari.minimum-idle=10
#spring.datasource.hikari.maximum-pool-size=25
#spring.datasource.hikari.auto-commit=true
#spring.datasource.hikari.idle-timeout=30000
#spring.datasource.hikari.pool-name=HikariCP
#spring.datasource.hikari.max-lifetime=1800000
#spring.datasource.hikari.connection-timeout=30000
#spring.datasource.hikari.connection-test-query=SELECT 1

spring.shardingsphere.datasource.names=master,slave0

# 主库
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://192.168.0.115:3306/appblog_business1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

# 从库
spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://192.168.0.115:3307/appblog_business1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=123456

spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0

spring.shardingsphere.props.sql.show=true

# mybatis 配置
mybatis.mapper-locations=classpath:mapping/*.xml
mybatis.type-aliases-package=cn.appblog.sharding.business.entity

（4）MyBatis配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
    <!-- 数据库驱动:选择你的本地硬盘上面的数据库驱动包-->
    <classPathEntry  location="D:\software\database\mybatis-generator-core-1.3.2\lib\mysql-connector-java-5.1.25-bin.jar"/>
    <context id="DB2Tables"  targetRuntime="MyBatis3" defaultModelType="flat">
        <commentGenerator>
            <property name="suppressDate" value="true"/>
            <!-- 是否去除自动生成的注释 true：是 ： false:否 -->
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>
        <!--数据库链接URL，用户名、密码 -->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver" userId="xnxx" password="Xnxx85781245!" connectionURL="jdbc:mysql://47.107.177.150:3306/zy_sharding_business1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&maxReconnects=15000&allowMultiQueries=true">
        </jdbcConnection>
        <javaTypeResolver>
            <property name="forceBigDecimals" value="false"/>
        </javaTypeResolver>
        <!-- 生成模型的包名和位置-->
        <javaModelGenerator targetPackage="com.zypcy.sharding.business.entity" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>
        <!-- 生成映射文件的包名和位置-->
        <sqlMapGenerator targetPackage="mapping" targetProject="src/main/resources">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>
        <!-- 生成DAO的包名和位置-->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.zypcy.sharding.business.repository" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>
        <!-- 要生成的表 tableName是数据库中的表名或视图名 domainObjectName是实体类名-->
        <!-- 注意：已生成的不要再生成，不然会覆盖已生成的文件 -->
        <!--
        <table tableName="t_member" domainObjectName="Member" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
        <table tableName="t_order" domainObjectName="Order" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
        <table tableName="t_order_item" domainObjectName="OrderItem" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
        -->

    </context>
</generatorConfiguration>

（5）添加控制器、服务、mapper接口等

@Service
public class MemberServiceImpl implements IMemberService {

    @Autowired private MemberMapper memberMapper;

    @Override
    public int insert(Member record) {
        return memberMapper.insert(record);
    }

    @Override
    public Member selectByPrimaryKey(String memberId) {
        return memberMapper.selectByPrimaryKey(memberId);
    }

    @Override
    public int deleteByPrimaryKey(String memberId) {
        return memberMapper.deleteByPrimaryKey(memberId);
    }
}

@RequestMapping("/member")
@RestController
public class MemberController {

    @Autowired private IMemberService memberService;

    @RequestMapping("/add")
    public String add() {
        addMember();
        return "success";
    }

    @RequestMapping("/getMemberById")
    public Member getMemberById(String memberId) {
        return memberService.selectByPrimaryKey(memberId);
    }

    @RequestMapping("/delete")
    public String delete(String memberId) {
        memberService.deleteByPrimaryKey(memberId);
        return "success";
    }

    private void addMember() {
        Member member = new Member();
        member.setMemberId("201904280001");
        member.setMemberName("张三");
        member.setNickName("闪耀的瞬间");
        member.setAccountNo("zhangsan");
        member.setPassword("123465");
        member.setAge(27);
        member.setBirthDate(new Date());
        member.setEblFlag("1");
        member.setDelFlag("0");
        member.setDescription("屌丝一个");
        member.setCreateTime(new Date());
        member.setUpdateTime(new Date());
        memberService.insert(member);
    }
}

（6）运行项目，访问localhost:8070/member/add接口，添加一条数据，查看数据库，可以看到主从库都已经添加了数据

（7）修改从库的数据，把member_name字段值手动改为Joe.Ye，然后访问localhost:8070/member/getMemberById?memberId=201908270001，可以看到查询接口返回的是从库修改后的值

到此，读写分离的搭建与应用已经完成