第二十三章 MySQL是怎么保证数据不丢的？

来源：恒创科技编辑：恒创科技编辑部

2024-02-03 18:48:59

MySQL 能够保证数据不丢失的根据是什么 ?

根据 WAL 机制，只要 redo log 和 binlog 能保证持久化到磁盘，就能保证数据不丢失

binlog 的写入机制

binlog 是怎么写入磁盘的 ?

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache 写到binlog 文件中事务执行过程中，每个线程会分配得到一片 binlog cache，由参数binlog_cache_size 控制binlog cache 大小（如果超过，则会暂存到磁盘）并将记录写到binlog cahce 中，由于一个事务中的 binlog 是不可拆分的，必须一次性全部写入所以binlog cache 在 write 到 page cache 前，必定是全部收集事务记录完毕，并以事务为单位进行 write 工作下图write，是把日志写入到文件系统的 page cache (写入文件内存页缓存)，没有持久化到磁盘，所以速度比较快图中的fsync，是将数据持久化到磁盘 (调用OS同步文件内存脏页到磁盘)，占用磁盘的IOPS

第二十三章 MySQL是怎么保证数据不丢的？_mysql

binlog 的同步日志到磁盘，有哪些设置参数，介绍一下 ?

系统变量sync_binlogsync_binlog=0 的时候，表示每次提交事务都执行到 write（由OS决定 fsync 的时机）sync_binlog=1 的时候，表示每次提交事务都会执行到 fsyncsync_binlog=N(N>1) 的时候，表示每次提交事务都执行到 write，然后 page cahce 中的binlog 记录凑齐 N 个事务后才执行 fsync注意：将 sync_binlog 设置为 N，如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志

为什么 binlog 写入数据是一次性全部写入，不能被打断 ?

binlog 写入的前提条件是事务被提交，事务至少进入了 prepare 状态如果一个事务的 binlog 能拆分写，则意味着在备库执行时，就相当于拆分成了多个事务段执行此时就破坏了事务的原子性，可能会导致一些不可预知的问题redo log 的写入机制

redo log buffer 里面的内容，是不是每次生成后都要直接持久化到磁盘呢 ?

不需要只有事务提交了才会从 redo log buffer 同步到 redo log 中事务执行过程中写入 redo log buffer，还没有提交，如果数据库崩溃，由于事务没有提交，因此会回滚，所以 redo log buffer 中数据丢失也没有关系

redo log 可能存在的三种状态 ?

第二十三章 MySQL是怎么保证数据不丢的？_binlog_02

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

redo log 的同步日志到磁盘，有哪些设置参数，介绍一下 ?

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

事务还没提交的时候，redo log buffer 中的部分日志有没有可能被持久化到磁盘呢 ?

有可能InnoDB 有一个后台线程，每隔 1 秒，就会把redo log buffer 中的日志，调用write 写到文件系统的page cache，然后调用fsync 持久化到磁盘事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程一起持久化到磁盘也就是说，一个没有提交的事务的 redo log，也是可能已经持久化到磁盘的如果redo log buffer 大小达到innodb_redo_log_buffer_size 的大小（16MB）的一半，则后台线程会主动写盘注意：由于事务还没有提交，因此不会持久化到磁盘，仅写到文件系统缓存页 (page cache)如果innodb_flush_log_at_trx_commit 设置为1，则事务提交时，会将其他事务尚未提交的redo log buffer 中的记录全部持久化到磁盘中

当 innodb_flush_log_at_trx_commit 设置为1时，为什么 redo log 在 prepare 阶段就要持久化一次到磁盘 ?

为1时，是保证所有 redo log 记录不丢失MySQL 可以根据 redo log 的 prepare 和对 binlog 的判定来判断事务是否生效binlog 写完，redo log 还没 commit 前发生crash：如果redo log 里面的事务是完整的，也就是已经有了commit 标识，则直接提交如果redo log 里面的事务只有完整的prepare，则判断对应的事务 binlog 是否存在并完整：a. 如果是，则提交事务；b. 否则，回滚事务因此prepare 阶段的 redo log 也是可以起到数据恢复作用的

当 innodb_flush_log_at_trx_commit 设置为1时，为什么说事务 commit 阶段不用再 fsync 到磁盘了 ?

事务的commit 阶段指的是事务提交过程中，最后 redo log 被标记为 commit 的阶段后台线程每隔1秒会将redo log buffer 中的数据刷到page cahce，再fsync 到磁盘此时这些记录包含了 prepare 标识的数据，但 MySQL 在重放日志时候，可以根据 redo log的 prepare 标识再判断 binlog 的完整性等等从而进行恢复所以这时候，这些 prepare 表示的 redo log 提交时，就不会再fsync到磁盘一次这样做的目的是减少磁盘IO开销

什么是 MySQL 的双1设置 ?

sync_binlog 和innodb_flush_log_at_trx_commit 都设置成 1一个事务完整提交前，需要等待两次刷盘：redo log（prepare 阶段）持久化到磁盘binlog 持久化到磁盘组提交（group commit）

介绍一下日志逻辑序列号 ?

log sequence number，简称 LSNLSN 是单调递增的，对应每个 redo log 的写入点，值为上一个写入点 +本次写入的 redo log 长度LSN 也会写到 InnoDB 的数据页中，来确保数据页不会被多次执行重复的 redo log

两阶段提交

第二十三章 MySQL是怎么保证数据不丢的？_binlog_03

写 binlog 其实是分成两步的：

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

对于组提交中，组员越多，节约IOPS效果越好这一点，你知道MySQL还做了什么优化吗 ?

第二十三章 MySQL是怎么保证数据不丢的？_mysql_04

binlog 执行fsync 时，也会将其他的位于 page cache 的binlog 一并进行fsync，但由于binlog 的write 和fsync 间隔很短，所以往往效果不明显MySQL 提供了两个参数：binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsyncbinlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync

WAL 机制是减少磁盘写，但是每次提交事务都要写 redo log 和 binlog，这样读写次数不是很多吗 ?

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

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

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

将 binlog 的提交延迟设置大一点，增加组提交的组员，减少写盘次数将sync_binlog 设置为大于1的值，但这可能导致出现数据丢失的风险（主机宕机时）将innodb_flush_log_at_trx_commit 设置为2，但这可能导致出现数据丢失的风险（主机宕机时）

设置 innodb_flush_log_at_trx_commit 为 0 会怎么样 ?

不建议把innodb_flush_log_at_trx_commit 设置成 0设置成 0，表示 redo log 是保存在 MySQL 进程中的内存缓存的，如果 MySQL 异常重启数据直接就丢失了设置成 2，表示 redo log 是写到了操作系统的page cache了，会在合适时候 fsync 到磁盘，所以即使MySQL 进程重启了也不影响

为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的 ?

binlog 是不能“被打断的”。一个事务的 binlog 必须连续写，因此要整个事务完成后，再一起写到文件里binlog 是一种逻辑性的日志，记录的是一个事务完整的语句当用来做主从同步时，如果分散写，可能造成事务不完整，分多次执行，从而导致不可预知的问题而 redo log 属于物理性的日志，记录的是物理地址的变动。因此，分散写也不会改变最终的结果

事务执行期间，还没到提交阶段，如果发生 crash 的话，redo log 肯定丢了，这会不会导致主备不一致呢 ?

不会因为这时候 binlog 也还在 binlog cache 里，没发给备库crash 以后 redo log 和 binlog 都没有了，从业务角度看这个事务也没有提交，所以数据是一致的

一个极端的场景：若事务提交后，binlog 写完，主库同步 binlog 给备库执行，但主库尚未给客户端答复时，主库挂掉了，那么事务算失败了吗 ?

不算，此时客户端会收到网络连接失败的反馈数据库的crash-safe 保证的是：如果客户端收到事务成功的消息，事务就一定持久化了如果客户端收到事务失败（比如主键冲突、回滚等）的消息，事务就一定失败了如果客户端收到 “执行异常” 的消息，应用需要重连后通过查询当前状态来继续后续的逻辑。此时数据库只需要保证内部（数据和日志之间，主库和备库之间）一致就可以了

本文地址：https://www.henghost.com/jishu/21142/