Sharding-Jdbc 实现读写分离、分库分表，妙

2022 年 5 月 13 日
本文字数：6623 字
阅读完需：约 22 分钟

binlog_cache_size=1M

##?binlog 格式(mixed、statement、row,默认格式是 statement)

binlog_format=mixed

3）创建并启动 mysql 主服务

docker?run?-itd?-p?3306:3306?--name?master?-v?/usr/local/mysqlData/master/cnf:/etc/mysql/conf.d?-v?/usr/local/mysqlData/master/data:/var/lib/mysql?-e?MYSQL_ROOT_PASSWORD=123456?mysql:5.7

4）添加复制 master 数据的用户 reader，供从服务器使用

[root@aliyun?/]#?docker?ps

CONTAINER?ID????????IMAGE???????????????COMMAND??????????????????CREATED?????????????STATUS??????????????PORTS???????????????????????????????NAMES

6af1df686fff????????mysql:5.7???????????"docker-entrypoint..."???5?seconds?ago???????Up?4?seconds????????0.0.0.0:3306->3306/tcp,?33060/tcp???master

[root@aliyun?/]#?docker?exec?-it?master?/bin/bash

root@41d795785db1:/#?mysql?-u?root?-p123456

mysql>?GRANT?REPLICATION?SLAVE?ON?.?to?'reader'@'%'?identified?by?'reader';

Query?OK,?0?rows?affected,?1?warning?(0.00?sec)

mysql>?FLUSH?PRIVILEGES;

Query?OK,?0?rows?affected?(0.00?sec)

5）创建从服务器所需目录，编辑配置文件

mkdir?/usr/local/mysqlData/slave/cnf?-p

vim?/usr/local/mysqlData/slave/cnf/mysql.cnf

[mysqld]

##?设置 server_id,注意要唯一

server-id=2

##?开启 binlog,以备 Slave 作为其它 Slave 的 Master 时使用

log-bin=mysql-slave-bin

##?relay_log 配置中继日志

relay_log=edu-mysql-relay-bin

##?如果需要同步函数或者存储过程

log_bin_trust_function_creators=true

##?binlog 缓存

binlog_cache_size=1M

##?binlog 格式(mixed、statement、row,默认格式是 statement)

binlog_format=mixed

##?跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中断

##?如:1062 错误是指一些主键重复,1032 错误是因为主从数据库数据不一致

slave_skip_errors=1062

6）创建并运行 mysql 从服务器

docker?run?-itd?-p?3307:3306?--name?slaver?-v?/usr/local/mysqlData/slave/cnf:/etc/mysql/conf.d?-v?/usr/local/mysqlData/slave/data:/var/lib/mysql?-e?MYSQL_ROOT_PASSWORD=123456?mysql:5.7

7）在从服务器上配置连接主服务器的信息

首先主服务器上查看master_log_file、master_log_pos两个参数，然后切换到从服务器上进行主服务器的连接信息的设置

主服务上执行：

root@6af1df686fff:/#?mysql?-u?root?-p123456

mysql>?show?master?status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

|?mysql-bin.000003?|??????591?|??????????????|??????????????????|???????????????????|

+------------------+----------+--------------+------------------+-------------------+

1?row?in?set?(0.00?sec)

docker 查看主服务器容器的 ip 地址

[root@aliyun?/]#?docker?inspect?--format='{{.NetworkSettings.IPAddress}}'?master

172.17.0.2

从服务器上执行：

[root@aliyun?/]#?docker?exec?-it?slaver?/bin/bash

root@fe8b6fc2f1ca:/#?mysql?-u?root?-p123456??

mysql>?change?master?to?master_host='172.17.0.2',master_user='reader',master_password='reader',master_log_file='mysql-bin.000003',master_log_pos=591;

8）从服务器启动 I/O 线程和 SQL 线程

mysql>?start?slave;

Query?OK,?0?rows?affected,?1?warning?(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:?reader

Master_Port:?3306

Connect_Retry:?60

Master_Log_File:?mysql-bin.000003

Read_Master_Log_Pos:?591

Relay_Log_File:?edu-mysql-relay-bin.000002

Relay_Log_Pos:?320

Relay_Master_Log_File:?mysql-bin.000003

Slave_IO_Running:?Yes

Slave_SQL_Running:?Yes

Slave_IO_Running: Yes，Slave_SQL_Running: Yes 即表示启动成功

2）、binlog 和 redo log 回顾《一线大厂 Java 面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》无偿开源威信搜索公众号【编程进阶路】

1）redo log（重做日志）

InnoDB 首先将 redo log 放入到 redo log buffer，然后按一定频率将其刷新到 redo log file

下列三种情况下会将 redo log buffer 刷新到 redo log file：

Master Thread 每一秒将 redo log buffer 刷新到 redo log file
每个事务提交时会将 redo log buffer 刷新到 redo log file
当 redo log 缓冲池剩余空间小于 1/2 时，会将 redo log buffer 刷新到 redo log file

MySQL 里常说的 WAL 技术，全称是 Write Ahead Log，即当事务提交时，先写 redo log，再修改页。也就是说，当有一条记录需要更新的时候，InnoDB 会先把记录写到 redo log 里面，并更新 Buffer Pool 的 page，这个时候更新操作就算完成了

Buffer Pool 是物理页的缓存，对 InnoDB 的任何修改操作都会首先在 Buffer Pool 的 page 上进行，然后这样的页将被标记为脏页并被放到专门的 Flush List 上，后续将由专门的刷脏线程阶段性的将这些页面写入磁盘

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，循环使用，从头开始写，写到末尾就又回到开头循环写（顺序写，节省了随机写磁盘的 IO 消耗）

Write Pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。Check Point 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件

Write Pos 和 Check Point 之间空着的部分，可以用来记录新的操作。如果 Write Pos 追上 Check Point，这时候不能再执行新的更新，需要停下来擦掉一些记录，把 Check Point 推进一下

当数据库发生宕机时，数据库不需要重做所有的日志，因为 Check Point 之前的页都已经刷新回磁盘，只需对 Check Point 后的 redo log 进行恢复，从而缩短了恢复的时间

当缓冲池不够用时，根据 LRU 算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行 Check Point，将脏页刷新回磁盘

2）binlog（归档日志）

MySQL 整体来看就有两块：一块是 Server 层，主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog

binlog 记录了对 MySQL 数据库执行更改的所有操作，不包括 SELECT 和 SHOW 这类操作，主要作用是用于数据库的主从复制及数据的增量恢复

使用 mysqldump 备份时，只是对一段时间的数据进行全备，但是如果备份后突然发现数据库服务器故障，这个时候就要用到 binlog 的日志了

binlog 格式有三种：STATEMENT，ROW，MIXED

STATEMENT 模式：binlog 里面记录的就是 SQL 语句的原文。优点是并不需要记录每一行的数据变化，减少了 binlog 日志量，节约 IO，提高性能。缺点是在某些情况下会导致 master-slave 中的数据不一致
ROW 模式：不记录每条 SQL 语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了，解决了 STATEMENT 模式下出现 master-slave 中的数据不一致。缺点是会产生大量的日志，尤其是 alter table 的时候会让日志暴涨
MIXED 模式：以上两种模式的混合使用，一般的复制使用 STATEMENT 模式保存 binlog，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog，MySQL 会根据执行的 SQL 语句选择日志保存方式

3）redo log 和 binlog 日志的不同

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用
redo log 是物理日志，记录的是在某个数据也上做了什么修改；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如给 ID=2 这一行的 c 字段加 1
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的，binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

4）两阶段提交

create?table?T(ID?int?primary?key,?c?int);

update?T?set?c=c+1?where?ID=2;

执行器和 InnoDB 引擎在执行这个 update 语句时的内部流程：

执行器先找到引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据也本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回
执行器拿到引擎给的行数据，把这个值加上 1，得到新的一行数据，再调用引擎接口写入这行新数据
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务
执行器生成这个操作的 binlog，并把 binlog 写入磁盘
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交状态，更新完成

update 语句的执行流程图如下，图中浅色框表示在 InnoDB 内部执行的，深色框表示是在执行器中执行的

将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是两阶段提交

3）、MySQL 主从复制原理

从库 B 和主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务从库 B 的这个长连接。一个事务日志同步的完整过程如下：

在从库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量
在从库 B 上执行 start slave 命令，这时从库会启动两个线程，就是图中的 I/O 线程和 SQL 线程。其中 I/O 线程负责与主库建立连接
主库 A 校验完用户名、密码后，开始按照从库 B 传过来的位置，从本地读取 binlog，发给 B
从库 B 拿到 binlog 后，写到本地文件，称为中继日志
SQL 线程读取中继日志，解析出日志里的命令，并执行

由于多线程复制方案的引入，SQL 线程演化成了多个线程

主从复制不是完全实时地进行同步，而是异步实时。这中间存在主从服务之间的执行延时，如果主服务器的压力很大，则可能导致主从服务器延时较大

3、Sharding-Jdbc 实现读写分离

1）、新建 Springboot 工程，引入相关依赖

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-web</artifactId>

</dependency>

<groupId>org.mybatis.spring.boot</groupId>

<artifactId>mybatis-spring-boot-starter</artifactId>

</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

<scope>runtime</scope>

</dependency>

<groupId>com.alibaba</groupId>

<artifactId>druid-spring-boot-starter</artifactId>

</dependency>

<groupId>org.apache.shardingsphere</groupId>

<artifactId>sharding-jdbc-spring-boot-starter</artifactId>

</dependency>

<groupId>org.projectlombok</groupId>

<artifactId>lombok</artifactId>

</dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-test</artifactId>

</dependency>

</dependencies>

2）、application.properties 配置文件

spring.main.allow-bean-definition-overriding=true

#显示 sql

spring.shardingsphere.props.sql.show=true

#配置数据源

spring.shardingsphere.datasource.names=ds1,ds2,ds3

#master-ds1 数据库连接信息

spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource

spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver

spring.shardingsphere.datasource.ds1.url=jdbc:mysql://47.101.58.187:3306/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai

spring.shardingsphere.datasource.ds1.username=root

spring.shardingsphere.datasource.ds1.password=123456

spring.shardingsphere.datasource.ds1.maxPoolSize=100

spring.shardingsphere.datasource.ds1.minPoolSize=5

#slave-ds2 数据库连接信息

spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource

spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver

spring.shardingsphere.datasource.ds2.url=jdbc:mysql://47.101.58.187:3307/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai

spring.shardingsphere.datasource.ds2.username=root

spring.shardingsphere.datasource.ds2.password=123456

spring.shardingsphere.datasource.ds2.maxPoolSize=100

spring.shardingsphere.datasource.ds2.minPoolSize=5

#slave-ds3 数据库连接信息

spring.shardingsphere.datasource.ds3.type=com.alibaba.druid.pool.DruidDataSource

spring.shardingsphere.datasource.ds3.driver-class-name=com.mysql.cj.jdbc.Driver

spring.shardingsphere.datasource.ds3.url=jdbc:mysql://47.101.58.187:3307/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai

spring.shardingsphere.datasource.ds3.username=root

spring.shardingsphere.datasource.ds3.password=123456

spring.shardingsphere.datasource.ds.maxPoolSize=100

spring.shardingsphere.datasource.ds3.minPoolSize=5

#配置默认数据源 ds1?默认数据源,主要用于写

spring.shardingsphere.sharding.default-data-source-name=ds1

#配置主从名称

spring.shardingsphere.masterslave.name=ms

#置主库 master,负责数据的写入

spring.shardingsphere.masterslave.master-data-source-name=ds1

#配置从库 slave 节点

spring.shardingsphere.masterslave.slave-data-source-names=ds2,ds3

#配置 slave 节点的负载均衡均衡策略,采用轮询机制

spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin

#整合 mybatis 的配置

mybatis.type-aliases-package=com.ppdai.shardingjdbc.entity

3）、创建 t_user 表

CREATE?TABLE?t_user?(

id?int(11)?NOT?NULL?AUTO_INCREMENT,

nickname?varchar(100)?DEFAULT?NULL,

password?varchar(100)?DEFAULT?NULL,

sex?int(11)?DEFAULT?NULL,

birthday?varchar(50)?DEFAULT?NULL,

PRIMARY?KEY?(id)

)?ENGINE=InnoDB?AUTO_INCREMENT=4?DEFAULT?CHARSET=utf8mb4;

4）、定义 Controller、Mapper、Entity

@Data

public?class?User?{

private?Integer?id;

private?String?nickname;

private?String?password;

private?Integer?sex;

private?String?birthday;

}

@RestController

@RequestMapping("/api/user")

public?class?UserController?{

@Autowired

private?UserMapper?userMapper;

@PostMapping("/save")

public?String?addUser()?{

User?user?=?new?User();

user.setNickname("zhangsan"?+?new?Random().nextInt());

user.setPassword("123456");

user.setSex(1);

user.setBirthday("1997-12-03");

userMapper.addUser(user);

return?"success";

}

@GetMapping("/findUsers")

public?List<User>?findUsers()?{

return?userMapper.findUsers();

}

public?interface?UserMapper?{

@Insert("insert?into?t_user(nickname,password,sex,birthday)?values(#{nickname},#{password},#{sex},#{birthday})")

void?addUser(User?user);

@Select("select?*?from?t_user")

List<User>?findUsers();

}

5）、验证

启动日志中三个数据源初始化成功：

调用http://localhost:8080/api/user/save一直进入到 ds1 主节点

调用http://localhost:8080/api/user/findUsers一直进入到 ds2、ds3 节点，并且轮询进入

4、MySQL 分库分表原理

1）、分库分表

水平拆分：同一个表的数据拆到不同的库不同的表中。可以根据时间、地区或某个业务键维度，也可以通过 hash 进行拆分，最后通过路由访问到具体的数据。拆分后的每个表结构保持一致

垂直拆分：就是把一个有很多字段的表给拆分成多个表，或者是多个库上去。每个库表的结构都不一样，每个库表都包含部分字段。一般来说，可以根据业务维度进行拆分，如订单表可以拆分为订单、订单支持、订单地址、订单商品、订单扩展等表；也可以，根据数据冷热程度拆分，20%的热点字段拆到一个表，80%的冷字段拆到另外一个表

2）、不停机分库分表数据迁移

一般数据库的拆分也是有一个过程的，一开始是单表，后面慢慢拆成多表。那么我们就看下如何平滑的从 MySQL 单表过度到 MySQL 的分库分表架构

利用 MySQL+Canal 做增量数据同步，利用分库分表中间件，将数据路由到对应的新表中

发布于: 刚刚阅读数: 2

爱好编程进阶

关注

还未添加个人签名 2022.04.13 加入

还未添加个人简介

发布

暂无评论

创作场景

Sharding-Jdbc 实现读写分离、分库分表，妙

2）、binlog 和 redo log 回顾 《一线大厂 Java 面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》无偿开源 威信搜索公众号【编程进阶路】

3）、MySQL 主从复制原理

1）、分库分表

2）、不停机分库分表数据迁移

爱好编程进阶

评论

2）、binlog 和 redo log 回顾《一线大厂 Java 面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》无偿开源威信搜索公众号【编程进阶路】