了解MySQL的一些常识（二）

vlambda
2020-09-16

了解MySQL的一些常识（二）

【MySQL的binlog日志】

MySQL的binlog日志是记录MySQL数据库DML操作和DDL操作记录到Binary Log中，其中DDL（Data Definition Language）的语句有create alter table……；DML(Data manipulation Language)的主要语句有insert、delete和update，注意select操作不会记录到binlog日志中，MySQL的Binlog记录格式有三种：

基于SQL语句的复制（Statement-based replication，SBR）
基于行的复制（Row-based replication，RBR）
混合模式复制（Mixed-based replication，MBR）

【MySQL 主从复制原理】

简单描述：mysql master节点把修改数据的操作（inster,update,delete,alter,table,grant,etc.）写入到binlog日志中，slave节点接收master节点的bin log记录然后写入到本地的relay log中，通过sql thread (MySQL5.6版本以后的sql thread 变为了coordinator thread)执行，以保证slave节点的数据和master节点的数据保持一致，具体执行步骤如下：

mysql> change master to ……配置完成后，开启slave服务

slave节点连接master节点,并发送replication信息给master；
master的dump thread持续不断的把master binlog发送给slave节点；
slave节点的IO thread接收master发来的数据，写入到自己的relay log中；
slave节点的SQL thread不断的读取relay日志，并将修改应用到slave节点的数据中；

MySQL主从复制binlog拷贝方式：

MySQL的复制默认是采用异步方式进行的，除了这种方式以外还有多线程复制、增强半同步复制。

异步复制：最常见的复制场景，master将操作事件写入到binlog后，成功返回给客户端并不关心slave节点何时获取和处理日志。数据库的完整性完全靠binlog的日志记录不丢失，通过binlog可以手动的将数据还原到从库中（使用mysqlbinlog命令、高可用的MHA架构自动抽取确实的数据不全从库或者启用Global Transaction Identifiers（GTID）来自动抽取缺失的binlog到从库）
多线程复制：MySQL5.6版本的多线程复制是基于schma的（基于实例的，库级别的并行复制，参数设置slave_parallel_workers=N）。
MySQL5.7版本的多线程复制是基于组提交的并行复制（enhanced multi-thread slave,MTS），在MySQL5.7版本中提出了一个组的概念，即组内基于mysql操作中所有处于prepare阶段的事务，都是可以并行提交的，这些事务在slave节点同样也可以并行repaly，有效的避免了slave节点防止lock或者wait的分发算法，提高了slave的执行效率（通过设置slave_parallel_workers=N且global.slave_parallet_type='LOGICAL_CLOCK'）。
MySQL8.0版本多线程技术引入了writeset概念，理解下图：

Trx1 ------------P----------C-------------------------------->Trx2 ----------------P------+---C---------------------------->Trx3 -------------------P---+---+-----C---------------------->Trx4 -----------------------+-P-+-----+----C----------------->Trx5 -----------------------+---+-P---+----+---C------------->Trx6 -----------------------+---+---P-+----+---+---C---------->Trx7 -----------------------+---+-----+----+---+-P-+--C------->

分组如下：

<Trx1、Trx2、Trx3>，<Trx4、Trx5、Trx6>，<Trx7>三组

半同步复制（5.7版本后演变为增强半同步复制）：为了避免mysql数据主从节点在发生故障的那一刻数据的一致性，提出的一个解决方案，也成为无损复制，即在mysql事务dump到binlog以后，同时把二级制文件发送到slave节点并写入到relaylog中，slave节点会返回给master节点一个ACK标志，表明已接收操作完成，master节点收到ACK标志后，进行commit操作，然后返回给客户端。增强半同步复制与半同步复制的改进在于事务写入binlog之后待slave节点返回响应ACK之后将事务提交到事务引擎层。

MySQL binlog并行复制的实现：

MySQL并行复制的MySQL版本为5.7，之前的版本设计不涉及到这部分内容，通过分析查看mysql binlog的日志发现，在日志中有last_committed、sequence_number两个字段的记录，其中last_committed代表mysql的事务组（Transaction Group,TRG）,sequence_number代表的是事务的编号且是顺序增加的。

在mysql binlog日志replay过程中，coordinator thread(之前的sql thread)分析后，认为该事务组可以并行执行就分发给workers进行操作；否则就等正在进行的replay操作完成后，coordinator thread 自己执行该TRG。
如何将事务进行分组？

MySQL的提交方式ordered_committed。Transaction的commit操作有分为FLUSH-SYNC-COMMIT三步。

首先Transaction加入到一个队列，如果该队列为空，那么这个TR就称为该TRG的队长，等待后续TR的进入（等待时间是该TR处理该队列的时间大小），时间到了以后代理TRG内的事务进行FLUSH操作

# FLUSH 操作1、保证Transaction的顺序为加入到队列的顺序；2、向TRG内的每一个事务分配sequence_number、last_commit,3、把配置好的GTID事件flush到binlog内容中；4、把binlog内容flush到binlog文件中；

待FLUSH操作完成后，通知mysql dump thread以及进行SYNC操作，这些步骤的操作顺序都是为串行的，如果有TRG在进行SYNC操作，必须等它完成后才能进行，最好进行COMMIT操作。

# 在commit操作工序，实际是存储引擎的提交，配置参数inlog_order_commit#　影响提交行为的执行方式：# inlog_order_commit=on 是执行的是串行提交，即按照队列内的顺序依次commit# inlog_order_commit=off是，当作为队长的TR commit操作以后，在Done之前，# 队列内的所有的事务都会各自进行引擎提交。

binlog日志类似下：

last_committed=0 sequence_number=1 last_committed=1 sequence_number=2 last_committed=2 sequence_number=3 last_committed=3 sequence_number=4 last_committed=4 sequence_number=5 last_committed=4 sequence_number=6 last_committed=4 sequence_number=7 last_committed=6 sequence_number=8 last_committed=6 sequence_number=9 last_committed=9 sequence_number=10

多线程复制分发原理是什么？

# 从库在replay时，是以事务为单位的，每一个事务都是一个GTID事件，# 包含last_committed和seqence_number值，1、slave拿到事务时，去除last_committed和sequence_number值；2、判断last_committed值是否大于当前已执行的sequence_number的最小值；3、如果大于，说明上一个组的事务还在执行， 等待low_water_mark变大与last_committed相等；4、如果小于或等于，说明是同一个事务组，不需要等待；5、sql线程通过统计，找到一个空闲的worker线程，执行，如果没有，则等待

M-S模式的目标：

备份
读/写分离
高可用和故障切换
MySQL升级测试

【MySQL 主从复制数据延迟问题的原因分析】

MySQL的主从复制首先必须了解MySQL的主从复制原理，根据MySQL主从复制的原理，可以明白MySQL的数据操作DML、DDL产生的binlog是顺序写入的，效率很高，Slave_IO_Running接收master发送来的bin日志写入到relay log效率也比较高，但下一步slave_SQL_Running在replay SQL时，因为DML、DDL的操作是随机的，slave节点Replay与slave节点有大型查询操作时，会发生Lock，又因为slave_SQL_Running的操作也是单线程的，所以如果有一个Replay卡住以后，后续的操作都必须在等待这个完成以后才能继续执行，这就会产生延时。

原因排查1：master进行大数据量的DML操作（大事务），大量的binlog发送到slave节点，slave节点也需要master节点同样的时间完成操作，进而造成slave数据的延迟。

解决方案：将大事务操作拆分为小事务。

原因排查2：slave节点实例规格比master小

解决方案：m-s架构在做读写分离时，slave的配置至少要大于或等于master的机器配置，提升随机写的性能；调整slave节点的配置，如：

# 表示MySQL不控制binlog的刷新，有文件系统自己控制它的缓存的刷新# 有时候设置成>0的数字，表示多少个事务后刷新binlog刷新到磁盘上sync_binlog=0# 表示事务提交后将redo日志写入到磁盘中# 值为0：提交事务的时候不立即把redo log buffer刷入到磁盘中，而是依靠I弄DB的主线程每秒执行一次刷新到磁盘；# 值为1：事务提交的时候必须把redo log写入到磁盘文件里去，只要事务提交成功，relolog就写入到磁盘；# 值为2：事务提交的时候，把redo日志写入磁盘对应的os cache缓存里，而不是直接写入磁盘文件，通过os cache写入到磁盘；innodb_flush_log_at_trx_commit=2logs-slave-updates=0# 表示Innodb 存储引擎表数据和索引数据的最大缓冲区大小。innodb_buffer_pool_size=32m# 注意：slave机器可以在必要的情况下关闭 bin log的功能。

排查原因3：选择合适的binlog格式及索引需求。

解决方案：主要针对master的binlog_format配置，如果对一张500万记录表更新没有索引的列的50万条记录，如果这个符合row模式，在master节点只需要一次全表扫描，通过binlog传输到slave节点，在日志重放过程中需要进行50万次的全表扫描，这个操作导致的数据延迟时间可能更长，在需要修改的列上加上索引是一个比较明智的选择。