Docker配置SpringBoot+ShardingJDBC+MybatisPlus项目实现分库分表与读写分离

深碍√TFBOYSˉ_ 2023-06-30 01:41 22阅读 0赞

> 本文ShardingJDBC相关知识主要参考自[ShardingJDBC官方文档][ShardingJDBC]，更多的用法建议到官网文档查看。

### 文章目录 ###

*  前言
 *  项目环境
 *  搭建流程
 *   *   *  数据表order\_info\_x
         *  项目搭建
         *  Docker容器配置
         *  运行测试
 *  附

# 前言 #

传统的业务系统都是将数据集中存储至单一数据节点的解决方案，如今随着互联网数据的海量增长，单点存储早已无法满足性能、可用性和运维成本上的要求了，分库分表变成了目前数据库存储问题的主流解决方案。  
目前最常见的MySQL分库分表选择主要为ShardingJDBC(基于JDBC驱动的轻量级Java框架)与MyCat(基于Proxy)。在这两个分库分表方案中个人更倾向于ShardingJDBC，主要原因如下：

*  官方文档详细优雅，入门容易
 *  轻量级服务，搭建简便
 *  相关产品与架构孵化演进都让人感到该开源项目未来可期

下图为ShardingSphere官网中的Sharding-JDBC+Sharding-Proxy架构图，通过混合使用Sharding-JDBC和Sharding-Proxy，并采用同一注册中心统一配置分片策略，灵活的搭建适用于各种场景的应用系统，更加自由的调整适合于当前业务的最佳系统架构。  
![sharding-mix-architecture][]

# 项目环境 #

本文例子主要基于Spring Boot+Mybatis plus+ShardingJDBC框架集成搭建项目，通过Docker配置运行Mysql主从容器，Spring Boot项目运行连接Docker Mysql容器进行实现完整的分库分表与读写分离，主要的搭建流程如下：

*  [Docker与Docker Compose安装][Docker_Docker Compose]
 *  Docker Compose启动容器
 *  Mysql从机容器同步主机配置
 *  启动Spring Boot项目

由于该项目主要是为了记录与展现分库分表与读写分离的实现搭建细节，所以Mysql的主从配置也是以双Master同时各配单Slave最低需求搭建的，具体架构图如(project-architecture)下：  
![project-architecture][]

# 搭建流程 #

### 数据表order\_info\_x ###

create table order_info
    (
        id          bigint                              not null
            primary key,
        user_id     bigint                              not null comment '用户id',
        username    varchar(50)                         not null comment '用户名',
        note        varchar(300)                        not null default '' comment '备注',
        create_time timestamp default CURRENT_TIMESTAMP null,
        update_time timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP
    )
        comment '订单表';

该项目中实际的操作数据表为order\_info\_x，order\_info表仅用于代码文件逆向生成。当生成的order.id%2==0时，则存到Master1数据库上，若order.id%==1时，则存到Master2数据库上；当确认存到哪台机器上时，再根据user\_id%2判断是存到order\_info\_0还是order\_info\_1。  
每当Master有数据库创建或数据更新时，Slave会通过日志将主机数据库信息同步。

### 项目搭建 ###

*  **项目文件树**  
    ![project-file-tree][]
 *  **Maven依赖**
    
        <properties>
            <shardingsphere.version>4.0.0</shardingsphere.version>
        </properties>
        
        <dependencies>
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
            </dependency>
            <dependency>
                <groupId>org.apache.commons</groupId>
                <artifactId>commons-lang3</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            
            <dependency>
                <groupId>io.github.wilson-he</groupId>
                <artifactId>spring-boot-common</artifactId>
                <version>0.0.2</version>
            </dependency>
            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
                <version>${shardingsphere.version}</version>
            </dependency>
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
            <dependency>
                <groupId>com.google.guava</groupId>
                <artifactId>guava</artifactId>
            </dependency>
        </dependencies>
        
        <build>
            <finalName>${project.artifactId}</finalName>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
                
                <plugin>
                    <groupId>io.github.wilson-he</groupId>
                    <artifactId>generator-maven-plugin</artifactId>
                    <version>0.2.4</version>
                    <configuration>
                        <basePackage>io.wilson.sharding</basePackage>
                        <dataSource>
                            <url>jdbc:mysql://localhost/order_1</url>
                            <username>root</username>
                            <password>tiger</password>
                        </dataSource>
                        <templates>
                            <excludeController>false</excludeController>
                        </templates>
                        <crudIdType>Long</crudIdType>
                        
                        <inclusions>order_info</inclusions>
                    </configuration>
                </plugin>
            </plugins>
        </build>
    
     *  **Mybatis Plus语句打印优化配置：ExtMybatisXMLLanguageDriver.java**
    
        /** * 解决Mybatis Plus原生Mapper语句的格式问题 * * @author Wilson * @since 2020/1/13 **/
        public class ExtMybatisXMLLanguageDriver extends MybatisXMLLanguageDriver { 
            /** * 缓存反射字段，减少反射带来的性能损耗 */
            private final Field sqlField;
        
            public ExtMybatisXMLLanguageDriver() throws NoSuchFieldException { 
                Class boundSqlClass = BoundSql.class;
            sqlField = boundSqlClass.getDeclaredField("sql");
            try { 
                sqlField.setAccessible(true);
            } catch (Exception e) { 
                e.printStackTrace();
            }
        }
        
            @Override
            public MybatisDefaultParameterHandler createParameterHandler(MappedStatement mappedStatement,
                                                                         Object parameterObject, BoundSql boundSql) { 
                String sql = boundSql.getSql();
                try { 
                    sqlField.set(boundSql, sql.replaceAll("\r\n|\n", " ")
                            .replaceAll("\\s{2,}", " "));
                } catch (IllegalAccessException e) { 
                    e.printStackTrace();
                }
                return new MybatisDefaultParameterHandler(mappedStatement, parameterObject, boundSql);
            }
        }
    
    该配置类主要用于优化在使用Sharding JDBC时Mybatis Plus的语句格式问题，下图为去除当前类配置后ShardingJDBC打印MybtisPlus原生Mapper语句的格式问题图：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA_size_16_color_FFFFFF_t_70]
    
     *  **OrderController.java**
    
        @RestController
        @RequestMapping("/orderInfo")
        public class OrderInfoController { 
           @Resource
           private OrderInfoService orderInfoService;
        
           @PostMapping("/")
           public ServerResponse add(@Validated @RequestBody OrderVO vo) { 
               return ServerResponse.success(orderInfoService.insert(vo.orderInfo()));
           }
        
           @PutMapping("/")
           public ServerResponse update(@Validated @RequestBody OrderVO vo) { 
               return ServerResponse.success(orderInfoService.updateById(vo.orderInfo()));
           }
        
           @GetMapping("/")
           public ServerResponse get(@RequestParam Long id) { 
               return ServerResponse.success(orderInfoService.findById(id));
           }
        
           @GetMapping("/list")
           public ServerResponse list() { 
               return ServerResponse.success(orderInfoService.list());
           }
        }
    
     *  主程序**ShardingApplication.java**
    
        @SpringBootApplication
        @MapperScan("io.wilson.sharding.mapper")
        public class ShardingApplication { 
            public static void main(String[] args) { 
                SpringApplication.run(ShardingApplication.class, args);
            }
        }
    
     *  配置文件**application-sharding.yml**，分片规则根据以上project-architecture图
    
        spring:
         shardingsphere:
         # 根据个人实际配置，该实例中配置的是个人的ecs ip
           host: localhost
           datasource:
             ds0:
               username: root
               password: tiger
               type: com.alibaba.druid.pool.DruidDataSource
               url: jdbc:mysql://${spring.shardingsphere.host}:3306/order_0
             ds0-slave:
               username: root
               password: tiger
               type: com.alibaba.druid.pool.DruidDataSource
               url: jdbc:mysql://${spring.shardingsphere.host}:3307/order_0
             ds1:
               username: root
               password: tiger
               type: com.alibaba.druid.pool.DruidDataSource
               url: jdbc:mysql://${spring.shardingsphere.host}:4306/order_0
             ds1-slave:
               username: root
               password: tiger
               type: com.alibaba.druid.pool.DruidDataSource
               url: jdbc:mysql://${spring.shardingsphere.host}:4306/order_0
             # 配置数据源名称
             names: ds0,ds0-slave,ds1,ds1-slave
           sharding:
             default-database-strategy:
               inline:
                 # 根据id%2取模判断当前数据存取是在ds0还是ds1对应的数据库
                 algorithm-expression: ds$->{id % 2}
                 # 根据user_id分片存取数据
                 sharding-column: id
             tables:
               order_info:
                 # 根据sharding生成的id%2是0还是1分配到数据库_0还是_1
                 actual-data-nodes: ds$->{0..1}.order_info_$->{0..1}
                 # 雪花算法生成id
                 keyGenerator:
                   type: SNOWFLAKE
                   column: id
                 # 根据user_id%2取模确定分配到数据表_0还是_1
                 table-strategy:
                   inline:
                     # 根据user_id%2取模判断当前数据存取是在表order_info_0还是order_info_1
                     algorithm-expression: order_info_$->{user_id % 2}
                     # 根据user_id分片存取数据
                     sharding-column: user_id
             # 多主从配置
             master-slave-rules:
               ds0:
                 masterDataSourceName: ds0
                 slave-data-source-names: [ds0-slave]
               ds1:
                   masterDataSourceName: ds1
                   slaveDataSourceNames: [ds1-slave]
           # 单主从配置
        #    masterslave:
        #      name: ds0_ms
        #      masterDataSourceName: ds0
        #      slaveDataSourceNames: [ds0-slave]
        props:
         # 打印分片信息与SQL语句
         sql.show: true
    
     *  配置文件**application.yml**
    
        spring:
         common:
           validation:
             msg-locale: zh_CN
           serialization:
             longToString: true
         profiles:
           include: sharding
        swagger:
         docket:
           base-package: io.wilson.sharding.controller
        mybatis-plus:
         configuration:
           default-scripting-language: io.wilson.sharding.mybatis.ExtMybatisXMLLanguageDriver

### Docker容器配置 ###

该例子中为了简便Docker只用于部署Mysql集群，若想更全面的使用Docker将Mysql与SpringBoot项目整合可参考[ Docker+Jenkins+Nginx+SpringBoot自动化部署项目][Docker_Jenkins_Nginx_SpringBoot]，然后在docker-compose.yml中将Spring Boot容器depends\_on Mysql容器。

*  配置文件`docker-compose.yml`

version: "3.7"
    services:
      db-master:
        image: mysql:5.7.28
        container_name: mysql-master1
        command: --default-authentication-plugin=mysql_native_password
        restart: always
        environment:
          # root用户密码
          MYSQL_ROOT_PASSWORD: tiger
          TZ: Asia/Shanghai
        # 设置容器hostname，使从机可通过hostname连接主机而不必使用ip
        hostname: mysql-master1
        ports:
          - 3306:3306
        volumes:
          - ./mysql/master1/data:/var/lib/mysql
          - ./mysql/master1/log:/var/log/mysql
          - ./mysql/master1/conf:/etc/mysql
          # mysql初始化执行文件，用于创建同步用户
          - ./mysql/init.sql:/docker-entrypoint-initdb.d/init.sql
      db-slave:
        image: mysql:5.7.28
        container_name: mysql-master1-slave1
        command: --default-authentication-plugin=mysql_native_password
        restart: always
        hostname: mysql-master1-slave1
        environment:
          MYSQL_ROOT_PASSWORD: tiger
          TZ: Asia/Shanghai
        ports:
          - 3307:3306
        volumes:
          - ./mysql/slave1/data:/var/lib/mysql
          - ./mysql/slave1/log:/var/log/mysql
          - ./mysql/slave1/conf:/etc/mysql
      db-master2:
        image: mysql:5.7.28
        container_name: mysql-master2
        command: --default-authentication-plugin=mysql_native_password
        restart: always
        environment:
          # root用户密码
          MYSQL_ROOT_PASSWORD: tiger
          TZ: Asia/Shanghai
        # 设置容器hostname，使从机可通过hostname连接主机而不必使用ip
        hostname: mysql-master2
        ports:
          - 4306:3306
        volumes:
          - ./mysql/master2/data:/var/lib/mysql
          - ./mysql/master2/log:/var/log/mysql
          - ./mysql/master2/conf:/etc/mysql
          # mysql初始化执行文件，用于创建同步用户slave
          - ./mysql/init.sql:/docker-entrypoint-initdb.d/init.sql
      db-master2-slave1:
        image: mysql:5.7.28
        container_name: mysql-master2-slave1
        command: --default-authentication-plugin=mysql_native_password
        restart: always
        hostname: mysql-master2-slave1
        environment:
          MYSQL_ROOT_PASSWORD: tiger
          TZ: Asia/Shanghai
        ports:
          - 4307:3306
        volumes:
          - ./mysql/slave2/data:/var/lib/mysql
          - ./mysql/slave2/log:/var/log/mysql
          - ./mysql/slave2/conf:/etc/mysql

在docker-compose.yml目录下执行`docker-compose up -d`指令，查看mysql master容器mysql binary log文件(File)与位置(Position)：  
![在这里插入图片描述][2020011813365865.png]

docker exec -it mysql-master1 /bin/bash
    mysql -uroot -ptiger
    show master status;

slave容器mysql master连接配置并进行同步：

docker exec -it mysql-master1-slave1 /bin/bash
    mysql -uroot -ptiger
    # master_host可设为ip或容器hostname，使用用户为slave连接主机并通过log同步
    change master to master_host='mysql-master1', master_user='slave', master_password='123456', master_port=3306, master_log_file='master1-bin.000005', master_log_pos=154, master_connect_retry=30;
    # 开启同步
    start slave;

Slave1根据Master1配置binlog，Slave2根据Master2配置binlog，更具体的主从同步可参考[Docker搭建基于binlog的Mysql主从同步][Docker_binlog_Mysql]，此处便省略一些配置文件讲解了，更详细的项目信息可到文末查看项目地址，当完成配置后即可启动本地Spring Boot项目，可以看到2主2从都已被初始化。  
![在这里插入图片描述][20200118135959709.png]

### 运行测试 ###

1.  插入数据根据id与user\_id是否成功写入到相应的库与数据表，并查看**从库数据是否同步**  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA_size_16_color_FFFFFF_t_70 1]  
    插入数据的id%2=0,user\_id%2=1，根据前面定义的规则会放入master1的order\_0.order\_info\_0表，查看控制台与master-slave1数据库数据校验数据是否正确、数据是否同步，如下图：![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA_size_16_color_FFFFFF_t_70 2]  
    id与user\_id都为基数时，根据规则数据放入master2的order\_1.order\_info\_1，以下为测试数据与校验图：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA_size_16_color_FFFFFF_t_70 3]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA_size_16_color_FFFFFF_t_70 4]
2.  **读分离测试**  
    1中已经成功验证了只会写Master库并从库通过binlog同步数据，读分离验证则只需获取OrderInfo列表(OrderController.list接口)查看JDBC从哪查即可：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA_size_16_color_FFFFFF_t_70 5]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA_size_16_color_FFFFFF_t_70 6]  
    从上图可以看出ShardingJDBC已成功从所有从库中获取数据

# 附 #

[github项目地址][github]

[ShardingJDBC]: https://shardingsphere.apache.org/document/current/cn/manual/sharding-jdbc/
[sharding-mix-architecture]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9zaGFyZGluZ3NwaGVyZS5hcGFjaGUub3JnL2RvY3VtZW50L2N1cnJlbnQvaW1nL3NoYXJkaW5nc3BoZXJlLWh5YnJpZC5wbmc?x-oss-process=image/format,png
[Docker_Docker Compose]: https://mp.csdn.net/postedit/103989245
[project-architecture]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9rbm93bGVkZ2UtcGljdHVyZXMub3NzLWNuLWJlaWppbmcuYWxpeXVuY3MuY29tL2Jsb2cvZG9ja2VyX3NoYXJkaW5namRiYy5wbmc?x-oss-process=image/format,png
[project-file-tree]: https://img-blog.csdnimg.cn/20200118104303863.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200118112935710.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA==,size_16,color_FFFFFF,t_70
[Docker_Jenkins_Nginx_SpringBoot]: https://blog.csdn.net/z28126308/article/details/103772177
[2020011813365865.png]: https://img-blog.csdnimg.cn/2020011813365865.png
[Docker_binlog_Mysql]: https://blog.csdn.net/z28126308/article/details/103988732
[20200118135959709.png]: https://img-blog.csdnimg.cn/20200118135959709.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200118140929665.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200118140911947.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200119094633688.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200119094758774.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200119095416855.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200119095502872.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3oyODEyNjMwOA==,size_16,color_FFFFFF,t_70
[github]: https://github.com/Wilson-He/spring-boot-series/tree/master/sharding-jdbc