Sharding-Jdbc 实现读写分离、分库分表,妙
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_file
、master_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 做增量数据同步,利用分库分表中间件,将数据路由到对应的新表中
评论