MySQL 数据安全性 ---redo log& binlog_综合

MySQL 数据安全性

学习检测

binlog 的写入机制及策略？
redo log 的写入机制及策略？
写了binlog 没写redolog的主备问题？
双1 的意思？
组提交

学习总结

binlog 写入 binlog cache 》事务提交后写入binlog files（文件系统的pagecache）》fsync 到disk

策略sync_binlog
- 为0的时候直接，写入磁盘page cache，由系统自己决定fsync时间
- 为1的时候，表示每次写入 disk
- 为N的时候，是表示够N个的时候才会刷入磁盘
redo log 也是先写入redo log buffer，根据策略刷入磁盘

策略 innodb_flush_log_at_trx_commit
- 为 0的时候，表示存在redo log buffer中，后台purge线程每秒去刷磁盘
- 为1的时候，表示事务提交的时候，写入磁盘
- 为2的时候，表示事务提交的时候写入磁盘的page cache中，由系统自己去控制刷disk的时间
当不是双1 的时候 redo log prapare后持久化磁盘失败，但是binlog记录成功，这时候备库同步数据的时候就会出现主备不一致的情况，此时

? innodb_support_xa就是为了保证数据的一致性，默认开启，根据XID来对比redo log 和binlog 中的事务完整性
innodb_flush_log_at_trx_commit 为1 sync_binlog为1
组提交，是为了减少频繁的IO，redo log 在buffer中，先去写binlog 然后在fsync（拖时间）binlog也是如此

binlog 的写入机制

每个线程一个binlog cache > 事务提交写入binlog files（文件系统page cache）> fsync 到disk

其实，binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了 binlog cache 的保存问题。

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。状态如图所示。
在这里插入图片描述
可以看到，每个线程有自己binlog cache，但是共用同一份bin log文件。

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

binlog 写入磁盘策略

write写的是文件系统的page cache

fsync是有文件系统的page cache写入到磁盘

write 和fsync的时机，是由参数sync_binlog控制的：

sync_binlog = 0的时候，表示每次提交事务都只write，不fsync，文件系统自己控制缓存的刷新
- 对应风险：系统异常重启，会丢失数据
sync_binlog = 1的时候，表示每次提交事务都会fsync；每个事务同步磁盘，不使用page cache
- 对应风险：系统异常重启，会丢失一条
sync_binlog = N（N >1) 的时候，表示每次提交事务都write，但累计N个事务后才fsync；累计一定数量
- 对应风险：系统异常重启，会丢失N条

因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。

但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

redo log 写入磁盘策略

redo log 刷新策略对比

innodb_flush_log_at_trx_commit：

为1，每次commit都会把redo log从redo log buffer写入redo log file到system fsync到disk，并fsync刷新到磁盘文件中。
- 对应风险：****
为2，每次事务提交时MySQL会把日志从redo log buffer到system OS cache，但只写入到file system buffer，由系统内部来fsync到磁盘文件。如果数据库实例crash，不会丢失redo log，但是如果服务器crash，由于file system buffer还来不及fsync到磁盘文件，所以会丢失这一部分的数据。
- 对应风险： 服务重启没影响，系统重启丢失数据
为0，事务发生过程，日志一直记录在redo log buffer中，跟其他设置一样，但是在事务提交时，不产生redo 写操作，而是MySQL内部每秒操作一次，从redo log buffer写入到系统中的磁盘文件。如果发生crash，即丢失1s内的事务修改操作。
- 对应风险：系统重启和服务重启都会丢失1秒内数据

☆sync_binlog 的重启问题处理

原文链接

https://blog.csdn.net/zbszhangbosen/article/details/9132833

感谢大佬的分享，转载如有不妥，请联系删除！

官方文档解释

“EnablesInnoDBsupport for two-phase commit in XA transactions, causing an extra disk flush for transaction preparation. This set-ting is the default. The XA mechanism is used internally and is essential for any server that has its binary log turned on and is accepting changes to its data from more than one thread. If you turn it off, transactions can be written to the binary log in a different order from the one in which the live database is committing them. This can produce different data when the binary log is replayed in disaster recovery or on a replication slave. Do not turn it off on a replication master server unless you have an unusual setup where only one thread is able to change data.”
翻译结果
" EnablesInnoDBsupport在XA事务中提交两阶段，导致额外的磁盘刷新事务准备。这种设置是默认的。XA机制在内部使用，对于打开二进制日志并接受来自多个线程的数据更改的任何服务器都是必需的。如果关闭它，事务可以以不同于动态数据库提交事务的顺序写入二进制日志。当在灾难恢复或复制从属服务器上重播二进制日志时，可能会产生不同的数据。不要在复制主服务器上关闭它，除非您有一个不寻常的设置，其中只有一个线程能够更改数据。

innodb_support_xa的作用是分两类：

第一支持多实例分布式事务（外部xa事务），这个一般在分布式数据库环境中用得较多。
第二，支持内部xa事务，说白了也就是说支持binlog与innodb redo log之间数据一致性。今天的重点是讨论第二类内部xa事务。

首先我们需要明白为什么需要保持binlog与redo log之间数据一致性，这里分两个方面来解释

? 大致意思就是，在事务提交的两阶段，写了redo log 的prepare，没写binlog，异常重启后，mysql回滚这条事务的时候也会记录binlog，然后从库用binlog同步，导致数据不一致

第一，保证binlog里面存在的事务一定在redo log里面存在，也就是binlog里不会比redo log多事务（可以少，因为redo log里面记录的事务可能有部分没有commit，这些事务最终可能会被rollback）。先来看这样一个场景（后面的场景都是假设binlog开启）：在一个AB复制环境下主库crash，然后进行crash recovery，此时如果binlog里面的的事务信息与redo log里面的信息不一致，那么就会出现主库利用redo log进行恢复后，然后binlog部分的内容复制到从库去，然后出现主从数据不一致状态。所以需要保证binlog与redo log两者事务一致性。
第二，保证binlog里面事务顺序与redo log事务顺序一致性。这也是很重要的一点，假设两者记录的事务顺序不一致，那么会出现类似于主库事务执行的顺序是ta, tb, tc,td，但是binlog里面记录的是ta,tc, tb, td，binlog复制到从库后导致主从的数据不一致。当然也由于当初蹩脚的设计导致BGC被打破，这里就不详说了。

解决方法–内部xa事务

mysql处理事务流程

1. prepare,然后将redo log 持久化到磁盘
2. 如果prapare成功，那么在继续将事务日志持久化到binlog
3. 如果前面成功，那么在redo log里面写上一个commit记录

那么假如在进行着三步时又任何一步失败，crash recovery是怎么进行的呢?

此时会先从redo log将最近一个检查点开始的事务读出来，然后参考binlog里面的事务进行恢复。如果是在1 crash，那么自然整个事务都回滚；
如果是在2 crash，那么也会整个事务回滚；
如果是在3 crash（仅仅是commit记录没写成功），那么没有关系因为2中已经记录了此次事务的binlog，所以将这个进行commit。所以总结起来就是redo log里凡是prepare成功，但commit失败的事务都会先去binlog查找判断其是否存在（通过XID进行判断，是不是经常在binlog里面看到Xid=xxxx？这就是xa事务id），如果有则将这个事务commit，否则rollback。

binlog 参数

是否开启bin_log

show variables like  'log_bin';
log_bin                   = /var/lib/mysql/bin-log
log_bin_index             = /var/lib/mysql/mysql-bin.index
expire_logs_days          = 7
server_id                 = 0002
binlog_format             = ROW

在这里插入图片描述

log_bin = /var/lib/mysql/bin-log

开启 Binlog 并写明存放日志的位置；默认使用的设置是“log-bin=mysql-bin”，这样日志是存放在默认的位置上的，一般是放在data目录中。
log_bin_index = /var/lib/mysql/mysql-bin.index

指定索引文件的位置，每个bin-log都会有对应的index文件
expire_logs_days = 7

删除超出这个变量保留期之前的全部日志被删除
server_id = 0002

指定一个集群内的mysql服务器ID，如果做数据库集群那么必须全局唯一，一般来说不推荐指定 server_id 等于 1。
max_binlog_size = 1G

单个二进制文件的最大值，如果超过该值，会产生新的二级制文件，后缀名+1，并记录到.index文件，默认值1G
binlog_cache_size = 32K

mysql 会为每个线程开启一个binlog cache，超过该值的时候回记录到临时文件中，默认32K

binlog_cache_disk_use

SHOW GLOBAL STATUS 查看，该参数是使用临时文件的次数 二进志日志缓存的已经存在硬盘的条数
binlog_cache_use

二进制日志已缓存的条数（内存中）
Binlog_stmt_cache_disk_use

（非事务类）二进志日志缓存的已经存在硬盘的条数
Binlog_stmt_cache_use

（非事务类）二进制日志已缓存的条数（内存中） 非事务型的语句，都存在这儿，比如MYISAM引擎的表，插入记录就存在这儿

max_binlog_cache_size

最大能有多少事务cache在内存中
binlog_do_db和binlog_ingore_db

是一对控制对哪些数据库进行收集的选项。示例：

binlog_do_db=fujie

binlog_do_db=fujieace

查看binlog 日志列表

show master logs；

redo log写入机制

事务开始 =====> redo log prapare（落盘） ====> binlog =====>redo log commit

redo log buffer 里面的内容，是不是每次都同步到磁盘？

不需要

如果事务执行期间 MySQL 发生异常重启，那这部分日志就丢了。由于事务并没有提交，所以这时日志丢了也不会有损失。

事务未提交的时候，redo log buffer日志有没有可能被持久化到磁盘？

会有

redo log的三种状态
在这里插入图片描述

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

日志写到 redo log buffer 是很快的，wirte 到 page cache 也差不多，但是持久化到磁盘的速度就慢多了。

redo log 落地三种策略

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

设置为0的时候，标识事务提交时候只把redo log 留在redo log buffer中
- 后台线程pruge，每隔1秒，就会把redo log buffer中的日志，调用write写到文件系统的page cache，然后fsync持久化到磁盘
设置为1的时候，表示事务每次把redo log 直接持久化到磁盘
设置为2的时候，表示事务每次提交时候都只是把redo log写到page cache；
- 由系统线程持久化到磁盘

注意，事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的 redo log，也是可能已经持久化到磁盘的。

一种是，redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。
另一种是，并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 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 中就够了。

双1的意思

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

组提交

这意味着我从 MySQL 看到的 TPS 是每秒两万的话，每秒就会写四万次磁盘。但是，我用工具测试出来，磁盘能力也就两万左右，怎么能实现两万的 TPS？

解释这个问题，就要用到组提交（group commit）机制了。

日志逻辑序列号

日志逻辑序列号（log sequence number，LSN）的概念。LSN 是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。

LSN也会写到InnoDB的数据页中，来确保数据页不会被多次执行重复的redo log。

如图所示，是三个并发事务 (trx1, trx2, trx3) 在 prepare 阶段，都写完 redo log buffer，持久化到磁盘的过程，对应的 LSN 分别是 50、120 和 160。
在这里插入图片描述

trx1是第一个到达的，会被选为这组的leader；
等trx1要开始写盘的时候，这个组里面已经有了三个事务，这时候LSN也变成了160；
trx1去写盘的时候，带的就是LSN=160，因此等trx1返回时，所有LSN小于等于160的redo log，都已经被持久化到磁盘；
这时候trx2 和trx3 就可以返回了。

所以，一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好。但如果只有单线程压测，那就只能老老实实地一个事务对应一次持久化操作了。

在并发更新场景下，第一个事务写完 redo log buffer 以后，接下来这个 fsync 越晚调用，组员可能越多，节约 IOPS 的效果就越好。

为了让一次 fsync 带的组员更多，MySQL 有一个很有趣的优化：拖时间。在介绍两阶段提交的时候，我曾经给你画了一个图，现在我把它截过来。
在这里插入图片描述
写 binlog”当成一个动作。但实际上，写 binlog 是分成两步的：

先把 binlog 从 binlog cache 中写到磁盘上的 binlog 文件；
调用 fsync 持久化。

MySQL 为了让组提交的效果更好，把 redo log 做 fsync 的时间拖到了步骤 1 之后。也就是说，上面的图变成了这样：
在这里插入图片描述
这么一来，binlog 也可以组提交了。在上图中第 4 步把 binlog fsync 到磁盘时，如果有多个事务的 binlog 已经写完了，也是一起持久化的，这样也可以减少 IOPS 的消耗。

不过通常情况下第 3 步执行得会很快，所以 binlog 的 write 和 fsync 间的间隔时间短，导致能集合到一起持久化的 binlog 比较少，因此 binlog 的组提交的效果通常不如 redo log 的效果那么好。

如果你想提升 binlog 组提交的效果，可以通过设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 来实现。

binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用fsync；
binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

这两个条件是或的关系，也就是说只要有一个满足条件就会调用 fsync。

所以，当 binlog_group_commit_sync_delay 设置为 0 的时候，

binlog_group_commit_sync_no_delay_count 也无效了。

之前有同学在评论区问到，WAL 机制是减少磁盘写，可是每次提交事务都要写 redo log 和 binlog，这磁盘读写次数也没变少呀？

现在你就能理解了，WAL 机制主要得益于两个方面：

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

提升性能方法

设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。 写了redo log prepare 没写binlog 就回滚，写了bin log 内存丢了，就回滚（innodb_support_xa）XID来办证redo binlog顺序一致性
将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。
将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。

我不建议你把 innodb_flush_log_at_trx_commit 设置成 0。因为把这个参数设置成 0，表示 redo log 只保存在内存中，这样的话 MySQL 本身异常重启也会丢数据，风险太大。而 redo log 写到文件系统的 page cache 的速度也是很快的，所以将这个参数设置成 2 跟设置成 0 其实性能差不多，但这样做 MySQL 异常重启时就不会丢数据了，相比之下风险会更小。