MySQL分库分表

Posted on 2021-09-21 Edited on 2024-07-22 In 数据库 , MySQL Views: Word count in article: 40k Reading time ≈ 36 mins.

1、分库分表概述

什么是分库分表？

分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

为什么需要分库分表？
随着公司业务快速发展，数据库数据量不可控的，随着时间和业务发展，造成表里面数据越来越多，如果再去对数据库表CURD操作时候，造成性能问题。
归根结底分库要解决的是硬件资源的问题，不管是拆分字段，还是拆分数据，都是要拆到不同的数据库不同的服务器上，从硬件资源上解决性能瓶颈。而分表是解决单表数据量过大的问题，拆分完后还是放在同一数据库中不同表里面，只是减少了单表的读写锁资源消耗，如果性能瓶颈在硬件资源，只是简单的分表并不能从根本上解决问题，所有具体分库分表亦或者是结合使用都要结合具体的业务场景。

分库分表的方式？

①垂直切分：垂直分表和垂直分库。
- 垂直分表：将一个表按照字段分成多表，每个表存储其中一部分字段。
  - 优势：
    - 一般来说，某业务实体中的各个数据项的访问频次是不一样的，部分数据项可能是占用存储空间比较大的BLOB或是TEXT。例如上例中的商品描述。所以，当表数据量很大时，可以将表按字段切开，将热门字段、冷门字段分开放置在不同库中，这些库可以放在不同的存储设备上，避免IO争抢。垂直切分带来的性能提升主要集中在热门数据的操作效率上，而且磁盘争用情况减少。
    - 能够避免IO争抢并减少锁表的几率，查看详情的用户与商品信息浏览互不影响。
    - 充分发挥热门数据的操作效率，商品信息的操作的高效率不会被商品描述的低效率所拖累。
  - 通常按照以下原则进行垂直拆分：
    - 把不常用的字段单独放在一张表。
    - 把text、blob等大字段拆分出来放在附表中。
    - 经常组合查询的列放在一张表中。
- 垂直分库：垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用。
  - 优势：
    - 解决业务层面的耦合，业务清晰能对不同业务的数据进行分级管理、维护、监控、扩展等高并发场景下，垂直分库一定程度的提升IO、数据库连接数、降低单机硬件资源的瓶颈垂直分库通过将表按业务分类，然后分布在不同数据库，并且可以将这些数据库部署在不同服务器上，从而达到多个服务器共同分摊压力的效果，但是依然没有解决单表数据量过大的问题。
②水平切分：水平分表和水平分库。
- 水平分库：是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。
  - 优势：
    - 解决了单库大数据，高并发的性能瓶颈。提高了系统的稳定性及可用性。当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平分库了，经过水平切分的优化，往往能解决单库存储量及性能瓶颈。但由于同一个表被分配在不同的数据库，需要额外进行数据操作的路由工作，因此大大提升了系统复杂度。
- 水平分表：是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。
  - 优势：
    - 优化单一表数据量过大而产生的性能问题避免IO争抢并减少锁表的几率库内的水平分表，解决了单一表数据量过大的问题，分出来的小表中只包含一部分数据，从而使得单个表的数据量变小，提高检索性能。

分库分表问题分析

①事务一致性问题：由于分库分表把数据分布在不同库甚至不同服务器，不可避免会带来分布式事务问题。
②跨节点关联查询：由于原来一张表的数据现在分布在不同数据库，不同表中，在涉及到多表关联，一定要设计好分片策略以及查询条件，否则很可能出现笛卡尔积现象，导致性能更低。
③跨节点分页、排序函数：跨节点多库进行查询时，limit分页、order by排序等问题，就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序。
④主键避重：不能在采用数据库自增主键，应采用分布式id，保证全局唯一。
⑤公共表： 实际的应用场景中，参数表、数据字典表等都是数据量较小，变动少，而且属于高频联合查询的依赖表。例子中地理区域表也属于此类型。可以将这类表在每个数据库都保存一份，所有对公共表的更新操作都同时发送到所有分库执行。

2、Sharding Sphere

Apache ShardingSphere是一套开源的分布式数据库解决方案组成的生态圈，它由 JDBC、Proxy 和 Sidecar（规划中）这 3 款既能够独立部署，又支持混合部署配合使用的产品组成。它们均提供标准化的数据水平扩展、分布式事务和分布式治理等功能，可适用于如 Java 同构、异构语言、云原生等各种多样化的应用场景。
Apache ShardingSphere旨在充分合理地在分布式的场景下利用关系型数据库的计算和存储能力，而并非实现一个全新的关系型数据库。关系型数据库当今依然占有巨大市场份额，是企业核心系统的基石，未来也难于撼动，我们更加注重在原有基础上提供增量，而非颠覆。

2.1 ShardingSphere-JDBC

其定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。主要目的是简化对分库分表之后数据相关操作。
- 适用于任何基于 JDBC 的 ORM 框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC。
- 支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP 等。
- 支持任意实现 JDBC 规范的数据库，目前支持 MySQL，Oracle，SQLServer，PostgreSQL 以及任何遵循 SQL92 标准的数据库。

2.1.1 水平分表案例

实际案例：人工创建course_db数据库，并创建两张表course_1和course_2，这两张表是课程表经过水平拆分后的表，通过Sharding-Jdbc向课程表插入数据，按照一定的分片规则，主键为偶数的进入course_1，另一部分数据进入course_2，通过Sharding-Jdbc查询数据，根据SQL语句的内容从course_1或course_2查询数据。

①创建SpringBoot项目并引入如下依赖：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.demo</groupId>
    <artifactId>ShardingSphereJDBCdemo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>ShardingSphereJDBCdemo</name>
    <description>ShardingSphereJDBCdemo</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.20</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.13</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.1</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

②按照水平分表的方式在mysql创建数据库course_db，以及创建course_1表和course_2表，并约定规则：如果添加课程id是偶数把数据添加course_1表，如果奇数添加到course_2表。

CREATE DATABASE `course_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
DROP TABLE IF EXISTS `course_1`;
CREATE TABLE `course_1` (
    `cid` bigint(20) NOT NULL COMMENT '课程id',
    `cname` varchar(50) NOT NULL COMMENT '课程名称',
    `user_id` bigint(20) NOT NULL COMMENT '用户id',
    `cstatus` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程状态',
    PRIMARY KEY (`cid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `course_2`;
CREATE TABLE `course_2` (
    `cid` bigint(20) NOT NULL COMMENT '课程id',
    `cname` varchar(50) NOT NULL COMMENT '课程名称',
    `user_id` bigint(20) NOT NULL COMMENT '用户id',
    `cstatus` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程状态',
    PRIMARY KEY (`cid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

③创建课程表相应实体类，Mapper，在application.properties配置文件中完善配置以及在主启动类中指定MyBatisPlus扫描包路径。

@Data
public class Course {
    /**
    * 课程id
    */
    private Long cid;
    /**
    * 课程名称
    */
    private String cname;
    /**
    * 用户id
    */
    private Long userId;
    /**
    * 课程状态
    */
    private String cstatus;
}

@Repository
public interface CourseMapper extends BaseMapper<Course> {

}

# shardingjdbc分片策略
# 配置数据源，给数据源起名称
spring.shardingsphere.datasource.names=m1

# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true

# 配置数据源具体内容，包含连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/course_db?serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456

# 指定course表分布情况，配置表在哪个数据库里面，表名称都是什么，例如m1.course_1，m1.course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}

# 指定course表里面主键cid的生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

# 指定分片策略约定cid值偶数添加到course_1表，如果cid是奇数添加到course_2表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}

# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

@SpringBootApplication
@MapperScan("com.demo.mapper")
public class ShardingSphereJdbCdemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShardingSphereJdbCdemoApplication.class, args);
    }
}

④在SpringBoot中进行单元测试。

①测试添加课程。

@SpringBootTest
class ShardingSphereJdbCdemoApplicationTests {
    @Autowired
    public CourseMapper courseMapper;

    //添加课程的方法
    @Test
    public void addCourse() {
        for (int i = 1; i <= 10; i++) {
            Course course = new Course();
            course.setCname("java" + i);
            course.setUserId(100L);
            course.setCstatus("Normal" + i);
            courseMapper.insert(course);
        }
    }
}

②测试查找课程。

@SpringBootTest
class ShardingSphereJdbCdemoApplicationTests {
    @Autowired
    public CourseMapper courseMapper;

    //查询课程的方法
    @Test
    public void findCourse() {
        QueryWrapper<Course> wrapper = new QueryWrapper<>();
        wrapper.eq("cid",646099373427523585L);
        Course course = courseMapper.selectOne(wrapper);
        System.out.println(course);
    }
}

2.1.2 水平分库案例

实际案例：人工创建两个数据库分别为edu_db_1和edu_db_2，并分别在两个数据库中创建两张表course_1和course_2，这两张表是课程表经过水平拆分后的表。并设置数据库规则为userid为偶数则添加到edu_db_1数据库，如果为奇数则添加到edu_db_2数据库，设置数据表规则为cid为偶数则添加到course_1表，奇数则添加到course_2表。

①创建SpringBoot项目并引入2.1.1章节的依赖。

②按照要求创建数据库和相应表，sql语句如下：

CREATE DATABASE `edu_db_1` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE DATABASE `edu_db_2` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
# 在edu_db_1和edu_db_2数据库中分别创建course_1、course_2表
DROP TABLE IF EXISTS `course_1`;
CREATE TABLE `course_1` (
    `cid` bigint(20) NOT NULL COMMENT '课程id',
    `cname` varchar(50) NOT NULL COMMENT '课程名称',
    `user_id` bigint(20) NOT NULL COMMENT '用户id',
    `cstatus` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程状态',
    PRIMARY KEY (`cid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `course_2`;
CREATE TABLE `course_2` (
    `cid` bigint(20) NOT NULL COMMENT '课程id',
    `cname` varchar(50) NOT NULL COMMENT '课程名称',
    `user_id` bigint(20) NOT NULL COMMENT '用户id',
    `cstatus` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程状态',
    PRIMARY KEY (`cid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

③SpringBoot配置文件规则如下：

# shardingjdbc分片策略
# 配置数据源，给数据源起名称
spring.shardingsphere.datasource.names=m1,m2

# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true

# 配置数据源具体内容，包含连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/edu_db_1?serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456

spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/edu_db_2?serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=123456

# 指定数据库分布情况，数据库里面表分布情况
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}

# 指定course表里面主键cid的生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

# 指定数据库分片策略，约定user_id是偶数添加m1，是奇数添加m2
# spring.shardingsphere.sharding.default-database-strategy.inline.shardingcolumn=user_id
# spring.shardingsphere.sharding.default-database-strategy.inline.algorithmexpression=m$->{user_id % 2 + 1}
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}

# 指定表分片策略约定cid值偶数添加到course_1表，如果cid是奇数添加到course_2表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}

# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

④编写测试代码。

①测试添加课程。

@SpringBootTest
class ShardingSphereJdbCdemoApplicationTests {
    @Autowired
    public CourseMapper courseMapper;

    //添加操作
    @Test
    public void addCourseDb() {
        Course course = new Course();
        course.setCname("javademo1");
        //分库根据 user_id
        course.setUserId(111L);
        course.setCstatus("Normal1");
        courseMapper.insert(course);
    }
}

②测试查询课程。

@SpringBootTest
class ShardingSphereJdbCdemoApplicationTests {
    @Autowired
    public CourseMapper courseMapper;

    //查询操作
    @Test
    public void findCourseDb() {
        QueryWrapper<Course> wrapper = new QueryWrapper<>();
        //设置 userid 值
        wrapper.eq("user_id",111L);
        //设置 cid 值
        wrapper.eq("cid",646307745049346049L);
        Course course = courseMapper.selectOne(wrapper);
        System.out.println(course);
    }
}

2.1.3 公共表案例

在实际的应用场景中，参数表、数据字典表等都是数据量较小，变动少，而且属于高频联合查询的依赖表。可以将这类表在每个数据库都保存一份，所有对公共表的更新操作都同时发送到所有分库执行。下面分别在user_db、course_db和order_db数据库中创建公共表t_dict作为测试。

①创建表的sql语句如下：

CREATE DATABASE `user_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE DATABASE `course_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE DATABASE `order_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

# 分别在user_db、course_db、order_db数据库中创建t_dict表，此表为公共表
DROP TABLE IF EXISTS `t_dict`;
CREATE TABLE `t_dict` (
    `dict_id` bigint(20) NOT NULL COMMENT '用户id',
    `ustatus` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户状态',
    `uvalue` varchar(50) NOT NULL COMMENT '用户描述',
    PRIMARY KEY (`dict_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

②创建公共表对应实体类，mapper，以及在配置文件中进行相关配置：

@Data
@TableName("t_dict")
public class TDict {
    /**
    * 用户id
    */
    private Long dictId;
    /**
    * 用户状态
    */
    private String ustatus;
    /**
    * 用户描述
    */
    private String uvalue;
}

1
2
3

public interface TDictMapper extends BaseMapper<TDict> {
    
}

# shardingjdbc分片策略
# 配置数据源，给数据源起名称
spring.shardingsphere.datasource.names=m1,m2,m3

# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true

# 配置数据源具体内容，包含连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/user_db?serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456

spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/course_db?serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=123456

spring.shardingsphere.datasource.m3.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m3.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m3.url=jdbc:mysql://localhost:3306/order_db?serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.m3.username=root
spring.shardingsphere.datasource.m3.password=123456

# 配置公共表
spring.shardingsphere.sharding.broadcast-tables=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE

# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

③编写测试类。

①测试插入数据。

@SpringBootTest
class ShardingSphereJdbCdemoApplicationTests {
    @Autowired
    private TDictMapper tDictMapper;

    //添加操作
    @Test
    public void addDict() {
        TDict tDict = new TDict();
        tDict.setUstatus("a");
        tDict.setUvalue("已启用");
        tDictMapper.insert(tDict);
    }
}

②测试删除数据。

@SpringBootTest
class ShardingSphereJdbCdemoApplicationTests {
    @Autowired
    private TDictMapper tDictMapper;

    //删除操作
    @Test
    public void deleteDict() {
        QueryWrapper<TDict> wrapper = new QueryWrapper<>();
        //设置 userid 值
        wrapper.eq("dict_id",646713996895846401L);
        tDictMapper.delete(wrapper);
    }
}

2.1.4 读写分离案例

读写分离是指为了确保数据库产品的稳定性，很多数据库拥有双机热备功能。也就是，第一台数据库服务器，是对外提供增删改业务的生产服务器；第二台数据库服务器，主要进行读的操作。
原理是让主数据库（master）处理事务性增、改、删操作，而从数据库（slave）处理SELECT查询操作。
- master会将变动记录到二进制日志里面。
- master有一个I/O线程将二进制日志发送到slave。
- slave有一个I/O线程把master发送的二进制写入到relay日志里面。
- slave有一个SQL线程，按照relay日志处理slave的数据。

Sharding-JDBC读写分离则是根据SQL语义的分析，将读操作和写操作分别路由至主库与从库。它提供透明化读写分离，让使用方尽量像使用个数据库一样使用主从数据库集群。

①在linux系统中使用docker安装一主一从mysql服务。

①docker拉取mysql镜像并创建容器运行。

[root@iz2zecc4mo4hoc75p0frn8z ~]# docker pull mysql:5.7 # docker从远端拉取mysql5.7镜像
5.7: Pulling from library/mysql
a330b6cecb98: Pull complete 
9c8f656c32b8: Pull complete 
88e473c3f553: Pull complete 
062463ea5d2f: Pull complete 
daf7e3bdf4b6: Pull complete 
1839c0b7aac9: Pull complete 
cf0a0cfee6d0: Pull complete 
fae7a809788c: Pull complete 
dae5a82a61f0: Pull complete 
7063da9569eb: Pull complete 
51a9a9b4ef36: Pull complete 
Digest: sha256:d9b934cdf6826629f8d02ea01f28b2c4ddb1ae27c32664b14867324b3e5e1291
Status: Downloaded newer image for mysql:5.7

[root@iz2zecc4mo4hoc75p0frn8z ~]# docker run -p 3301:3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 # 创建主mysql容器并运行，端口号为3301
8755229738ba84f0516e04782a4a72d1fc0fdc862eeef145460b70139b78053c
[root@iz2zecc4mo4hoc75p0frn8z ~]# docker run -p 3302:3306 --name mysql-slave -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 # 创建从mysql容器并运行，端口号为3302
c80b4fd9f44a60ab7a280cb40810fa9e4953355ee77622d3bea21321ee701191

②进入master容器，安装vim编译器并修改mysql的my.cnf文件。

[root@iz2zecc4mo4hoc75p0frn8z ~]# docker exec -it mysql-master /bin/bash # 进入master容器
root@0de6c3ba2559:/# cd /etc/mysql/ 
root@0de6c3ba2559:/etc/mysql# ls
conf.d  my.cnf  my.cnf.fallback  mysql.cnf  mysql.conf.d
root@0de6c3ba2559:/etc/mysql# apt-get update # 更新软件包
root@0de6c3ba2559:/etc/mysql# apt-get install vim # 安装vim编译器
root@0de6c3ba2559:/etc/mysql# vim my.cnf
[mysqld]
## 同一局域网内注意要唯一
server-id=100  
## 开启二进制日志功能，可以随便取（master-bin是日志文件名称）
log-bin=mysql-bin
root@7dc3e1d13660:/etc/mysql# exit # 退出mysql的master容器
exit
[root@iz2zecc4mo4hoc75p0frn8z ~]# docker restart mysql-master # 重启master容器
mysql-master

# 在Master数据库创建数据同步用户，授予用户slave REPLICATION SLAVE权限和REPLICATION CLIENT权限，用于在主从库之间同步数据。
[root@iz2zecc4mo4hoc75p0frn8z ~]# docker exec -it mysql-master /bin/bash # 重新进入master容器
root@8755229738ba:/# mysql -uroot -p123456 # 登录容器内部mysql服务
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status; # 由于上方配置了log-bin，现在能查看到master状态
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456'; # 创建用户
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%'; # 授权
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES; # 刷新权限信息
Query OK, 0 rows affected (0.00 sec)

②进入slave容器，安装vim编译器并修改mysql的my.cnf文件。

[root@iz2zecc4mo4hoc75p0frn8z ~]# docker exec -it mysql-slave /bin/bash # 进入master容器
root@0de6c3ba2559:/# cd /etc/mysql/ 
root@0de6c3ba2559:/etc/mysql# ls
conf.d  my.cnf  my.cnf.fallback  mysql.cnf  mysql.conf.d
root@0de6c3ba2559:/etc/mysql# apt-get update # 更新软件包
root@0de6c3ba2559:/etc/mysql# apt-get install vim # 安装vim编译器
root@0de6c3ba2559:/etc/mysql# vim my.cnf
[mysqld]
## 设置server_id,注意要唯一
server-id=101  
## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=mysql-slave-bin   
## relay_log配置中继日志
relay_log=mysql-relay-bin
## 设置为只读,该项如果不设置，表示slave可读可写
read_only=1
root@1cb7101e84b1:/etc/mysql# exit
exit
[root@iz2zecc4mo4hoc75p0frn8z ~]# docker restart mysql-slave # 重启slave容器
mysql-slave

③开启Master-Slave主从复制。

[root@iz2zecc4mo4hoc75p0frn8z ~]# docker exec -it mysql-master /bin/bash
root@7dc3e1d13660:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status; # File和Position字段的值后面将会用到，在后面的操作完成之前，需要保证Master库不能做任何操作，否则将会引起状态变化，File和Position字段的值变化
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      769 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 开启一个新的终端并进入mysql-slave容器
[root@iz2zecc4mo4hoc75p0frn8z ~]# docker exec -it mysql-slave /bin/bash
[root@iz2zecc4mo4hoc75p0frn8z ~]# docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-master # 查询master容器的独立ip
172.17.0.2
[root@iz2zecc4mo4hoc75p0frn8z ~]# docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-slave # 查询slave容器的独立ip
172.17.0.3
[root@iz2zecc4mo4hoc75p0frn8z ~]# docker exec -it mysql-slave /bin/bash
root@1cb7101e84b1:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# master_host：Master库的地址
# master_port：Master的端口号，指的是容器的端口号
# master_user：用于数据同步的用户
# master_password：用于同步的用户的密码
# master_log_file：指定Slave从哪个日志文件开始复制数据，即上文中提到的File字段的值
# master_log_pos：从哪个Position开始读，即上文中提到的Position字段的值
# master_connect_retry：如果连接失败，重试的时间间隔，单位是秒，默认是60秒
mysql> change master to master_host='172.17.0.2', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=769, master_connect_retry=30; # 配置主从连接
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave; # 开启主从复制
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G; # 查询主从同步状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.2
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 769
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 769
              Relay_Log_Space: 527
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 100
                  Master_UUID: 9d9a1e86-19ec-11ec-b54e-0242ac110002
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

④测试主从复制。

# 在master中创建test数据库
[root@iz2zecc4mo4hoc75p0frn8z ~]# docker exec -it mysql-master /bin/bash
root@7dc3e1d13660:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

# 此时在slave中能查到此数据库
[root@iz2zecc4mo4hoc75p0frn8z ~]# docker exec -it mysql-slave /bin/bash
root@1cb7101e84b1:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

⑤在master中创建数据库user_db以及数据表t_dict，此时slave会自动同步。

CREATE DATABASE `user_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

DROP TABLE IF EXISTS `t_dict`;
CREATE TABLE `t_dict` (
    `dict_id` bigint(20) NOT NULL COMMENT '用户id',
    `ustatus` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户状态',
    `uvalue` varchar(50) NOT NULL COMMENT '用户描述',
    PRIMARY KEY (`dict_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

⑤在SpringBoot中配置实体类、mapper以及在配置文件中配置读写分离策略。

@Data
@TableName("t_dict")
public class TDict {
    /**
    * 用户id
    */
    private Long dictId;
    /**
    * 用户状态
    */
    private String ustatus;
    /**
    * 用户描述
    */
    private String uvalue;
}

1
2
3

public interface TDictMapper extends BaseMapper<TDict> {
    
}

# shardingjdbc分片策略
# 配置数据源，给数据源起名称
spring.shardingsphere.datasource.names=m1,s1

# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true

# 配置数据源具体内容，包含连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://ip:3301/user_db?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456

spring.shardingsphere.datasource.s1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s1.url=jdbc:mysql://ip:3302/user_db?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.s1.username=root
spring.shardingsphere.datasource.s1.password=123456

# 主库从库逻辑数据源定义ds0为user_db
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m1
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=s1

# 指定t_dict表里面主键cid的生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE

# t_dict分表策略，固定分配至ds0的t_dict真实表
spring.shardingsphere.sharding.tables.t_dict.actual-data-nodes=ds0.t_dict

# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

⑥编写测试类。

①添加操作。

@SpringBootTest
class ShardingSphereJdbCdemoApplicationTests {
    @Autowired
    private TDictMapper tDictMapper;

    //添加操作
    @Test
    public void addDict() {
        TDict tDict = new TDict();
        tDict.setUstatus("a");
        tDict.setUvalue("已启用");
        tDictMapper.insert(tDict);
    }
}

②查询操作。

@SpringBootTest
class ShardingSphereJdbCdemoApplicationTests {
    @Autowired
    private TDictMapper tDictMapper;

    //查询操作
    @Test
    public void findDict() {
        QueryWrapper<TDict> wrapper = new QueryWrapper<>();
        //设置 userid 值
        wrapper.eq("dict_id",646814233673072641L);
        TDict tDict = tDictMapper.selectOne(wrapper);
        System.out.println(tDict);
    }
}

2.2 Sharding-Proxy

定位为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。目前提供 MySQL 和 PostgreSQL 版本，它可以使用任何兼容 MySQL/PostgreSQL 协议的访问客户端（如：MySQL Command Client, MySQL Workbench, Navicat 等）操作数据，对 DBA 更加友好。
- 向应用程序完全透明，可直接当做 MySQL/PostgreSQL 使用。
- 适用于任何兼容 MySQL/PostgreSQL 协议的的客户端。
- 下载链接：https://archive.apache.org/dist/shardingsphere/4.1.1/apache-shardingsphere-4.1.1-sharding-proxy-bin.tar.gz

2.2.1 安装和分表配置

①下载tar包并解压，接着进入conf目录修改文件server.yaml，添加以下内容。

authentication:
  users:
    root:
      password: root
    sharding:
      password: sharding 
      authorizedSchemas: sharding_db

props:
  max.connections.size.per.query: 1
  acceptor.size: 16  # The default value is available processors count * 2.
  executor.size: 16  # Infinite by default.
  proxy.frontend.flush.threshold: 128  # The default value is 128.
    # LOCAL: Proxy will run with LOCAL transaction.
    # XA: Proxy will run with XA transaction.
    # BASE: Proxy will run with B.A.S.E transaction.
  proxy.transaction.type: LOCAL
  proxy.opentracing.enabled: false
  proxy.hint.enabled: false
  query.with.cipher.column: true
  sql.show: false
  allow.range.query.with.inline.sharding: false

②创建test_db数据库。

1	CREATE DATABASE `test_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

②进入conf目录修改文件config-sharding.yaml，添加以下内容。

schemaName: sharding_db

dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:3306/test_db?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

shardingRule:
  tables:
    t_order:
      actualDataNodes: ds_${0}.t_order_${0..1}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_${order_id % 2}
      keyGenerator:
        type: SNOWFLAKE
        column: order_id
  bindingTables:
    - t_order
  defaultDatabaseStrategy:
    inline:
      shardingColumn: user_id
      algorithmExpression: ds_${0}
  defaultTableStrategy:
    none:

③复制mysql的驱动jar包到lib目录下。
④启动Sharding-Proxy服务，即双击bin目录下的start.bat即可，默认端口为3307。
- 如果报com.mysql.cj.jdbc.MysqlXAException: XAER_RMERR: Fatal error occurred in the transaction branch 错误，则需要打开mysql客户端并运行以下命令：
  1
  GRANT XA_RECOVER_ADMIN ON *.* TO root@'%' ;
- 如果报java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed错误则需要在config-sharding.yaml文件的mysql连接处加上属性allowPublicKeyRetrieval=true。
  1
  url: jdbc:mysql://127.0.0.1:3306/test_db?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true

⑤使用Sharding-Proxy启动端口进行连接。

C:\Windows\system32>mysql -P3307 -uroot -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 8.0.20-Sharding-Proxy 4.1.0

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases; # 查看数据库
+-------------+
| Database    |
+-------------+
| sharding_db |
+-------------+
1 row in set (0.01 sec)

mysql> use sharding_db; # 切换到sharding_db数据库
Database changed
mysql> CREATE table if not exists ds0.t_order (
    ->     `order_id` bigint(20) NOT NULL COMMENT '订单id',
    ->     `user_id` int(20) NOT NULL COMMENT '用户id',
    ->     `status` varchar(50) NOT NULL COMMENT '状态',
    ->     PRIMARY KEY (`order_id`) USING BTREE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.16 sec)

mysql> show tables; # 查看数据表
+-------------------+
| Tables_in_test_db |
+-------------------+
| t_order           |
+-------------------+
1 row in set (0.01 sec)

mysql> insert into t_order(order_id,user_id,status) values(11,1,'init');
Query OK, 1 row affected (0.12 sec)

mysql> select * from t_order;
+----------+---------+--------+
| order_id | user_id | status |
+----------+---------+--------+
|       11 |       1 | init   |
+----------+---------+--------+
1 row in set (0.02 sec)

以上步骤完毕后，回到本地3306端口test_db数据库中，看到已经创建好了表，并且由于插入的记录中order_id为奇数，所以插入到了t_order_1表中。

2.2.2 分库配置

①创建两个数据库edu_db_1和edu_db_2。

1 2	CREATE DATABASE `edu_db_1` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; CREATE DATABASE `edu_db_2` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

②修改conf目录下的server.yaml文件和2.2.1节中一致，

③修改conf目录下的config-sharding.yaml文件。

schemaName: sharding_db

dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:3306/edu_db_1?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_1:
    url: jdbc:mysql://127.0.0.1:3306/edu_db_2?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

shardingRule:
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${1..2}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_${order_id % 2 + 1}
      keyGenerator:
        type: SNOWFLAKE
        column: order_id
  bindingTables:
    - t_order
  defaultDatabaseStrategy:
    inline:
      shardingColumn: user_id
      algorithmExpression: ds_${user_id % 2}
  defaultTableStrategy:
    none:

④复制mysql的驱动jar包到lib目录下，也同2.2.1节。
⑤启动Sharding-Proxy服务，即双击bin目录下的start.bat即可，默认端口为3307。

⑥使用Sharding-Proxy启动端口进行连接。

C:\Windows\system32>mysql -P3307 -uroot -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 8.0.20-Sharding-Proxy 4.1.0

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+-------------+
| Database    |
+-------------+
| sharding_db |
+-------------+
1 row in set (0.01 sec)

mysql> use sharding_db;
Database changed
mysql> CREATE table if not exists ds0.t_order (
    ->     `order_id` bigint(20) NOT NULL COMMENT '订单id',
    ->     `user_id` int(20) NOT NULL COMMENT '用户id',
    ->     `status` varchar(50) NOT NULL COMMENT '状态',
    ->     PRIMARY KEY (`order_id`) USING BTREE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.24 sec)

mysql> insert into t_order(order_id,user_id,status) values(11,1,'init');
Query OK, 1 row affected (0.14 sec)

以上步骤完毕后，回到本地3306端口test_db数据库中，看到已经创建好了表，并且由于插入的记录中order_id和user_id都为奇数，所以插入到了edu_db_2数据库中的t_order_2表。

2.2.3 读写分离配置

①创建三个数据库作为一主二从。（此案例只是模拟读写分离的过程，如果要实际做到主从复制，可参考2.1.4节）

1
2
3

CREATE DATABASE `demo_ds_master` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE DATABASE `demo_ds_slave_0` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE DATABASE `demo_ds_slave_1` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

②修改conf目录下的config-master_slave.yaml文件。

schemaName: master_slave_db

dataSources:
  master_ds:
    url: jdbc:mysql://127.0.0.1:3306/demo_ds_master?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  slave_ds_0:
    url: jdbc:mysql://127.0.0.1:3306/demo_ds_slave_0?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  slave_ds_1:
    url: jdbc:mysql://127.0.0.1:3306/demo_ds_slave_1?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

masterSlaveRule:
  name: ms_ds
  masterDataSourceName: master_ds
  slaveDataSourceNames:
    - slave_ds_0
    - slave_ds_1

③修改conf目录下的server.yaml文件和2.2.1节中一致。
④复制mysql的驱动jar包到lib目录下，也同2.2.1节。
⑤启动Sharding-Proxy服务，即双击bin目录下的start.bat即可，默认端口为3307。

⑥使用Sharding-Proxy启动端口进行连接。

C:\Windows\system32>mysql -P3307 -uroot -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 8.0.20-Sharding-Proxy 4.1.0

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+-----------------+
| Database        |
+-----------------+
| master_slave_db |
+-----------------+
1 row in set (0.01 sec)

mysql> use master_slave_db;
Database changed
mysql> CREATE table if not exists demo_ds_master.t_order (
    ->     `order_id` bigint(20) NOT NULL COMMENT '订单id',
    ->     `user_id` int(20) NOT NULL COMMENT '用户id',
    ->     `status` varchar(50) NOT NULL COMMENT '状态',
    ->     PRIMARY KEY (`order_id`) USING BTREE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.13 sec)

mysql> CREATE table if not exists demo_ds_slave_0.t_order (
    ->     `order_id` bigint(20) NOT NULL COMMENT '订单id',
    ->     `user_id` int(20) NOT NULL COMMENT '用户id',
    ->     `status` varchar(50) NOT NULL COMMENT '状态',
    ->     PRIMARY KEY (`order_id`) USING BTREE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE table if not exists demo_ds_slave_1.t_order (
    ->     `order_id` bigint(20) NOT NULL COMMENT '订单id',
    ->     `user_id` int(20) NOT NULL COMMENT '用户id',
    ->     `status` varchar(50) NOT NULL COMMENT '状态',
    ->     PRIMARY KEY (`order_id`) USING BTREE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+--------------------------+
| Tables_in_demo_ds_master |
+--------------------------+
| t_order                  |
+--------------------------+
1 row in set (0.01 sec)

mysql> insert into t_order(order_id,user_id,status) values(11,1,'init'); # insert操作的是主机中的表
Query OK, 1 row affected (0.04 sec)

mysql> select * from t_order; # 虽然上方往表中插入了数据，但由于这里只是模拟了读写分离，没有实际做到主从复制，所以主机没有给从机同步数据，这里证明了查询记录是从从机读取的，所以实现了读写分离。
Empty set (0.02 sec)

mysql> insert into demo_ds_slave_0.t_order(order_id,user_id,status) values(12,1,'init');
Query OK, 1 row affected (0.06 sec)

mysql> insert into demo_ds_slave_1.t_order(order_id,user_id,status) values(13,1,'init');
Query OK, 1 row affected (0.04 sec)

mysql> select * from t_order; # 查询操作只从从机获取，这里从demo_ds_slave_1.t_order获取数据（因为没有实际做到主从复制，导致数据不一致）
+----------+---------+--------+
| order_id | user_id | status |
+----------+---------+--------+
|       13 |       1 | init   |
+----------+---------+--------+
1 row in set (0.01 sec)

mysql> select * from t_order; # 查询操作只从从机获取，这里从demo_ds_slave_0.t_order获取数据（因为没有实际做到主从复制，导致数据不一致）
+----------+---------+--------+
| order_id | user_id | status |
+----------+---------+--------+
|       12 |       1 | init   |
+----------+---------+--------+
1 row in set (0.00 sec)