MySQL45讲实战篇笔记（数据不丢与主备一致）

23.MySQL是怎么保证数据不丢的？

WAL机制中只要redo log和binlog保证持久化到磁盘，就能确保MySQL异常重启后，数据可以恢复。
binlog的写入机制

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。不论这个事务多大，也要确保一次性写入。
系统给binlog cache分配了一片内存，每个线程一个，参数 binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

image.png

图中的write，指的就是指把日志写入到文件系统的page cache，并没有把数据持久化到磁
盘，所以速度比较快。
图中的fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为fsync才占磁盘的
IOPS。
write 和fsync的时机，是由参数sync_binlog控制的：

sync_binlog=0的时候，表示每次提交事务都只write，不fsync；
sync_binlog=1的时候，表示每次提交事务都会执行fsync；
sync_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync。（如果主机发生异常重启，会丢失最近N个事务的binlog日志）

redo log的写入机制
redo log可能存在的三种状态，分别是：

存在redo log buffer中，物理上是在MySQL进程内存中，就是图中的红色部分；
写到磁盘(write)，但是没有持久化（fsync)，物理上是在文件系统的page cache里面，也就是图中的黄色部分；
持久化到磁盘，对应的是hard disk，也就是图中的绿色部分。

image.png

为了控制redo log的写入策略， InnoDB提供了innodb_flush_log_at_trx_commit参数，它有三种
可能取值：

设置为0的时候，表示每次事务提交时都只是把redo log留在redo log buffer中;
设置为1的时候，表示每次事务提交时都将redo log直接持久化到磁盘；
设置为2的时候，表示每次事务提交时都只是把redo log写到page cache。

InnoDB有一个后台线程，每隔1秒，就会把redo log buffer中的日志，调用write写到文件系统的page cache，然后调用fsync持久化到磁盘。
注意，事务执行中间过程的redo log也是直接写在redo log buffer中的，这些redo log也会被后台线程一起持久化到磁盘。也就是说， 一个没有提交的事务的redo log，也是可能已经持久化到磁盘的。

此外，还有两种场景会让一个没有提交的事务的redolog写入到磁盘中。

redo log buffer占用的空间即将达到 innodb_log_buffer_size一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，但写盘动作只是write，而没有调用fsync，只留在了文件系统的page cache。
并行的事务提交的时候，顺带将这个事务的redo log buffer（包含了其他事务的日志一起）持久化到磁盘。

如果把innodb_flush_log_at_trx_commit设置成1，那么redo log在prepare阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于prepare 的redo log，再加上binlog来恢复的。每秒一次后台轮询刷盘，再加上崩溃恢复这个逻辑， InnoDB就认为redo log在commit的时候就不需要fsync了，只会write到文件系统的page cache中就够了

MySQL的“双1”配置
指的就是sync_binlog和innodb_flush_log_at_trx_commit都设置成 1。
也就是说，一个事务完整提交前，需要等待两次刷盘，一次是redo log（prepare 阶段），一次是binlog

组提交（group commit）机制
双1配置两次刷盘，并不会双倍TPS写磁盘，
在并发更新场景下，第一个事务prepare阶段写完redo log buffer以后，等到写盘会根据实时的LSN（日志逻辑序列号log sequence number），带上写redo log buffer以后到fsync调用之前的其他事务的日志一起写盘。

image.png

WAL机制主要得益于两个方面：

redo log 和 binlog都是顺序写，磁盘的顺序写比随机写速度要快；
组提交机制，可以大幅度降低磁盘的IOPS消耗

Q1如果你的MySQL现在出现了性能瓶颈，而且瓶颈在IO上，可以通过哪些方法来提升性能呢？

设置 binlog_group_commit_sync_delay和 binlog_group_commit_sync_no_delay_count参
数，减少binlog的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加
语句的响应时间，但没有丢失数据的风险。
将sync_binlog 设置为大于1的值（比较常见是100~1000）。这样做的风险是，主机掉电时
会丢binlog日志。
将innodb_flush_log_at_trx_commit设置为2(事务提交写道page cache)。这样做的风险是，主机掉电的时候会丢数据。

在什么时候会把线上生产库设置成“非双1”？
目前知道的场景，有以下这些：

业务高峰期。一般如果有预知的高峰期， DBA会有预案，把主库设置成“非双1”。
备库延迟，为了让备库尽快赶上主库。
用备份恢复主库的副本，应用binlog的过程，这个跟上一种场景类似。
批量导入数据的时候。一般情况下，把生产库改成“非双1”配置，是设innodb_flush_logs_at_trx_commit=2、sync_binlog=1000

24. MySQL是怎么保证主备一致的？

一个update语句在节点A执行，然后同步到节点B的完整流程图如下图：

image.png

可以看到：主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写binlog。备库B跟主库A之间维持了一个长连接。主库A内部有一个线程，专门用于服务备库B的这个长连接。
一个事务日志同步的完整过程是这样的：

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

binlog里面到底是什么内容，为什么备库拿过去可以直接执行？？

binlog有两种格式，一种是statement，一种是row。还会有第三种格式， mixed，是前两种格式的混合。
当binlog_format=statement时， binlog里面记录的就是SQL语句的原文；
与statement格式的binlog相比，前后的BEGIN和COMMIT是一样的，但是row格式的binlog里没有了SQL语句的原文，而是替换成了两个event： Table_map和Delete_rows（例子是个删除语句）
1.）Table_map event，用于说明接下来要操作的表是test库的表t;
2.） Delete_rows event，用于定义删除的行为。
binlog里面记录了真实删除行的主键id，这样binlog传到备库去的时候，就肯定会删除id=4的行，不会有主备删除不同行的问题（比如statement 由于使用了不同的索引+limit就可能删错，造成数据不一致）

mixed格式可以利用statment格式的优点，同时又避免了数据不一致的风险

循环复制问题
实际生产上使用比较多的是双M结构，节点A和B之间总是互为主备关系。这样在切换的时候就不用再修改主备关系。
但节点A同时是节点B的备库，相当于又把节点B新生成的binlog拿过来执行了一次，然后节点A和B间，会不断地循环执行这个更新语句，也就是循环复制，可以借助server id 来解决：
日志的执行流就会变成这样：

从节点A更新的事务， binlog里面记的都是A的server id；
传到节点B执行一次以后，节点B生成的binlog 的server id也是A的server id；
再传回给节点A， A判断到这个server id与自己的相同，就不会再处理这个日志。所以，死循
环在这里就断掉了

上述过程其实还是可能出现死循环的：

在一个主库更新事务后，用命令set global server_id=x修改了server_id。等日志再传回来的时候，发现server_id跟自己的server_id不同，就只能执行了。
有三个节点的时候， binlog上的server_id就是B， binlog传给节点 A，然后A和A’搭建了双M结构，就会出现循环复制，这种三节点复制的场景，做数据库迁移的时候会出现。
可以在A或者A’上，执行如下命令：
stop slave；
CHANGE MASTER TO IGNORE_SERVER_IDS=(server_id_of_B);
start slave;
这样这个节点收到日志后就不会再执行。过一段时间后，再执行下面的命令把这个值改回来。
stop slave；
CHANGE MASTER TO IGNORE_SERVER_IDS=();
start slave;