Sharding-Sphere：Sharding-JDBC读写分离，docker搭建mysql主从

港控/mmm° 2022-02-12 11:39 253阅读 0赞

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

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

##### 1.Docker环境搭建Mysql读写分离 #####

*  [linux：mysql读写分离搭建-mysql5.6][linux_mysql_-mysql5.6]
 *  docker： 搭建docker环境下的读写分离时，遇到一个坑，最新的mysql镜像是8.0版本的，无法成功搭建，后面用的5.7.26就好了  
    镜像地址：https://hub.docker.com/\_/mysql?tab=tags，docker pull mysql:5.7.26  
    我是Mac OS 环境，下面简述docker配置mysql主从过程
 *  a.在本机上创建2个 .cnf 的配置文件，2个数据存放目录，用来保存mysql数据  
    ![在这里插入图片描述][20190428200846424.png]
 *  b.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

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

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

*  d.找到主库容器ID，进入主库容器控制台 docker exec -it container id /bin/bash
 *  e.进入mysql命令行，mysql -uroot -p123456 ，输入如下命令：

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70]

*  f.进入从库容器mysql命令行，和上面一样的命令，连接主库

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

看到下图红色线条部分的两个 Yes，则说明主从库搭建成功  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70 1]

使用 mysql 客户端连接主库，创建一个数据库和表，再连接从库查看该数据库是否同步过来了，我这边成功同步了  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70 2]

##### 2.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/manual/sharding-jdbc/usage/read-write-splitting/

先看看Sharding-JDBC的架构图，可以有个直观的了解  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70 3]

下面使用HikariCP与mybatis进行读写分离的验证，创建了3个表，最下面会给出源码地址，里面有建表sql

*  a.新建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>
    	<version>4.0.0-RC1</version>
    </dependency>
    <dependency>
    	<groupId>org.apache.shardingsphere</groupId>
    	<artifactId>sharding-jdbc-spring-namespace</artifactId>
    	<version>4.0.0-RC1</version>
    </dependency>
    <dependency>
    	<groupId>org.mybatis.spring.boot</groupId>
    	<artifactId>mybatis-spring-boot-starter</artifactId>
    	<version>2.0.0</version>
    </dependency>
    <dependency>
    	<groupId>mysql</groupId>
    	<artifactId>mysql-connector-java</artifactId>
    	<version>5.1.47</version>
    </dependency>
    <dependency>
    	<groupId>com.zaxxer</groupId>
    	<artifactId>HikariCP</artifactId>
    	<version>3.3.0</version>
    </dependency>

*  b.配置文件，请不要用 ds\_master，ds\_slave等，有下划线会报错，请参考帮助文档里面的配置

server.port=8070
    
    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/zy_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/zy_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=com.zypcy.sharding.business.entity

*  c.添加控制器、服务、mapper接口等，就不一一列出了，下面有源码地址  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70 4]
 *  d.运行项目，访问 localhost:8070/member/add 接口，添加一条数据，查看数据库，可以看到主从库都已经添加了数据  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70 5]
 *  e.修改从库的数据，把member\_name字段值手动改为 **朱宇12**，然后访问 localhost:8070/member/getMemberById?memberId=201904270001 ，可以看到查询接口返回的是从库修改后的值  
    ![在这里插入图片描述][20190428205032819.png]  
    到此，读写分离的搭建与应用已经完成了，[源码下载][Link 1]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70 6]

但也有它的不足，如：不支持timeout相关操作，不支持存储过程，函数，游标的操作等  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70 7]

[linux_mysql_-mysql5.6]: https://blog.csdn.net/zhuyu19911016520/article/details/73293321
[20190428200846424.png]: /images/20220212/b224a490eeb9476dba6af41504db1cde.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70]: /images/20220212/2b02e12e881c414ead1400d7e104814c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70 1]: /images/20220212/61109a9926f745c496860131a73feced.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70 2]: /images/20220212/5871163e0125407aac2a8efa2e8bd3c7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70 3]: /images/20220212/61d51271f12543778ee6d262b79c83a0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70 4]: /images/20220212/79213758082548f5b41109899b0fe1d3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70 5]: /images/20220212/0e45d3e9b4e24e13b47925968e0bb398.png
[20190428205032819.png]: /images/20220212/7c2310c1708f499c8c958214d745a250.png
[Link 1]: https://gitee.com/zhuyu1991/spring-cloud/tree/master/sharding-business
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70 6]: /images/20220212/541806afdf4f424693ef7721cfac9fe3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70 7]: /images/20220212/8068d597dc004ef4b533c6046c54d0ec.png