了解MySQL的一些常识（二）
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 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并行复制的实现：
如何将事务进行分组？
# FLUSH 操作1、保证Transaction的顺序为加入到队列的顺序；2、向TRG内的每一个事务分配sequence_number、last_commit,3、把配置好的GTID事件flush到binlog内容中；4、把binlog内容flush到binlog文件中；
# 在commit操作工序，实际是存储引擎的提交，配置参数inlog_order_commit# 影响提交行为的执行方式：# inlog_order_commit=on 是执行的是串行提交，即按照队列内的顺序依次commit# inlog_order_commit=off是，当作为队长的TR commit操作以后，在Done之前，# 队列内的所有的事务都会各自进行引擎提交。
binlog日志类似下：
last_committed=0 sequence_number=1last_committed=1 sequence_number=2last_committed=2 sequence_number=3last_committed=3 sequence_number=4last_committed=4 sequence_number=5last_committed=4 sequence_number=6last_committed=4 sequence_number=7last_committed=6 sequence_number=8last_committed=6 sequence_number=9last_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的数据操作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万次的全表扫描，这个操作导致的数据延迟时间可能更长，在需要修改的列上加上索引是一个比较明智的选择。