写点什么

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


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_filemaster_log_pos两个参数,然后切换到从服务器上进行主服务器的连接信息的设置


主服务上执行:


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


mysql>?show?master?status;


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


|?File?????????????|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|?Executed_Gtid_Set?|


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


|?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 工程,引入相关依赖


<dependencies>


<dependency>


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


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


</dependency>


<dependency>


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


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


<version>2.1.4</version>


</dependency>


<dependency>


<groupId>mysql</groupId>


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


<scope>runtime</scope>


</dependency>


<dependency>


<groupId>com.alibaba</groupId>


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


<version>1.1.21</version>


</dependency>


<dependency>


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


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


<version>4.0.0-RC1</version>


</dependency>


<dependency>


<groupId>org.projectlombok</groupId>


<artifactId>lombok</artifactId>


<optional>true</optional>


</dependency>


<dependency>


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


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


<scope>test</scope>


</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 做增量数据同步,利用分库分表中间件,将数据路由到对应的新表中

用户头像

还未添加个人签名 2022.04.13 加入

还未添加个人简介

评论

发布
暂无评论
Sharding-Jdbc实现读写分离、分库分表,妙_Java_爱好编程进阶_InfoQ写作社区