mysql如何保证数据不丢失_23 | MySQL是怎么保证数据不丢的？

高峰期临时提升性能方法。跟可靠性有关。如何保证binlog 和 redo log 写入磁盘。

WAL 机制(第 2 篇、第 9 篇、第 12 篇和第 15 篇): redo log 和 binlog 持久化磁盘，异常重启可恢复。

一、binlog的写入机制

日志写到 binlog cache(write)，binlog cache写 binlog(提交时，并清空cache)

一个事务binlog 不能被拆开，确保一次性写入。给 binlog cache内存(每线程一个，共用 binlog)，binlog_cache_size控制线程 binlog cache大小，超过暂存磁盘。

图 1 binlog 写盘状态

fsync：持久化到磁盘占 IOPS。

sync_binlog控制 write 和 fsync 时机：

(1)0 只 write，不 fsync； //考虑到丢失日志量可控性，不设 0，常100~1000 。IO 瓶颈设置大，提升性能。

(2)1 fsync；

(3)=N(N>1)  write，累积 N 个事务fsync。//风险：异常重启，丢失 N 个事务 binlog 日志。

二、redo log的写入机制

redo log 写到 redo log buffer (不需每次持久化磁盘，异常重启丢。没提交没损失)，也可能持久化到磁盘

图 2 MySQL redo log 存储状态

1. 红： redo log buffer 中，MySQL 进程内存(快)

2. 黄：写到磁盘 (write)，没有持久化(fsync)， page cache 里(快)

3. 绿：持久化到磁盘 hard disk(慢)

2.1 innodb_flush_log_at_trx_commit控制 redo log 写入：

1.0，只是把redo log 留 redo log buffer中;

2.  1，将 redo log 持久化磁盘；

3.  2 只是把 redo log 写到 page cache

后台线程每隔 1 秒把 redo log buffer 日志，write 到 page cache， fsync到磁盘。

除此还有两种场景：没有提交事务redo log，也可到磁盘：

1. redo log buffer占用空间快达到innodb_log_buffer_size一半时，后台线程会主动写盘。只是 write，没 fsync

2.  并行事务提交，顺带将redo logbuffer 到磁盘。事务 A 执行一半，已写一些 redo log 到 buffer 中，另外事务 B 提交，innodb_flush_log_at_trx_commit =1，B 持久化带 A

2.2两阶段提交

先 prepare：写 binlog，最后redo log commit。

innodb_flush_log_at_trx_commit =1，redo log  prepare 就持久化一次，崩溃恢复依赖于 prepare  redo log，再加 binlog (第 15 篇文章)。

每秒一次后台轮询刷盘，加崩溃恢复，commit 时不需 fsync ，只write

“双 1”配置 sync_binlog和innodb_flush_log_at_trx_commit都 1。提交前两次刷盘，redo log(prepare 阶段)、 binlog。

2.3组提交(group commit)机制

TPS 每秒两万，就会写四万次磁盘。用具测磁盘能力两万，怎么能实现两万TPS？

日志序列号(log sequence number，LSN)单调递增，对应 redo log 一个个写入点。每写入长度 length 的 redo log， LSN 加上 length。

也写数据页中，确保数据页不执行重复 redo log

图 3 redo log 组提交

三个并发事务 (trx1, trx2, trx3)  prepare ，写完 redo log buffer持久化到磁盘，LSN  50、120 和 160。

1. trx1 第一到达，选为leader；

2.  trx1 开始写盘，已有三个事务，LSN变160；

3.  trx1写盘时 LSN=160， trx1 返回时，所有 LSN<=160 redo log都持久化到磁盘；trx2 、 trx3 直接返回

组提交里，组员越多，节约磁盘 IOPS 越好。单线程压测，一个事务对应一次持久化操作了。

拖时间优化：redo log buffer 后，fsync 越晚调用，组员越多

图 4 两阶段提交

写 binlog 两步：从 binlog cache中写到磁盘上 binlog 文件；fsync持久化。

图 5 两阶段提交细化

多个事务binlog 写完一起持久化，减少 IOPS 消耗。

第 3 步快，binlog  write 和 fsync 间隔时间短，一起持久化binlog 比较少，binlog 组提交效果不如 redo log 效果好。

2.4 提升 binlog 组提交

1.  binlog_group_commit_sync_delay 延迟多少秒后调用fsync;

2.  binlog_group_commit_sync_no_delay_count 累积多少次调用 fsync。

一个满足就ok；binlog_group_commit_sync_delay0 count无效

WAL 减少磁盘写，每次提交都写 redo log 和 binlog，读写次数也没少呀？WAL 机制主要得益于：

1. redo log 和 binlog 都顺序写，比随机写快；

2. 组提交降低磁盘IOPS 消耗

2.5 IO性能瓶颈提升方法

1.  设置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count减少 binlog 的写盘次数。可能会增加语句响应时间，没有丢失数据风险。

2.  sync_binlog设置>1 的值(比较常见是 100~1000)。

3.  将innodb_flush_log_at_trx_commit设置为 2。和0 性能差不多，异常重启不会丢数据，主机掉电会

小结

第 2 篇和第 15 篇：MySQL 如何保证 crash-safe(redo log 和 binlog 完整)。今天：MySQL 怎么保证 redo log 和 binlog 完整”。三篇串起对 crash-safe更清晰理解

问题1： update 后，hexdump 看 ibd 文件内容，为什么没有看到数据改变？

WAL 机制原因。update 后，InnoDB 只写完redo log、内存，没来得及写磁盘。

问题2：为什么 binlog cache是线程自己维护，redo log buffer 全局共用？

binlog 不能打断。完成后一起写到文件。

问题3：事务执行期间，没提交，crash，redo log肯定丢，会不会导致主备不一致？

不会。binlog 也在 binlog cache里，crash后都没有

数据库crash-safe 保证：

1. 客户端收到事务成功消息，事务持久化；

2.  客户端收到事务失败(主键冲突、回滚等)的消息，事务失败；

3.  客户端收到“执行异常”消息，重连后查询当前状态来继续后续。保证内部(数据和日志之间，主库和备库之间)一致

思考题

生产库设置是“双 1”吗？ 什么场景下改成“非双 1”？基于什么决定的？

sync_binlog和innodb_flush_log_at_trx_commit都 1。提交前两次刷盘，redo log(prepare 阶段)、 binlog。

设置可能有损，异常止损方案是什么？

1.  业务高峰期。

2. 备库延迟，尽快赶上主库。

3.  备份恢复主库副本，应用 binlog 过程，这个跟上一种场景类似。

4.  批量导入数据时

生产库innodb_flush_log_at_trx_commit=2、sync_binlog=1000。

从库设置binlog_group_commit_sync_delay和 binlog_group_commit_sync_no_delay_count导致一直延迟。主库设置减少 binlog 写盘压力。备库设置受拖累，尤其“快要追上”时。追主备用“非双 1”(追上改回)。

评论1

sync_binlog和binlog_group_commit_sync_no_delay_count区别

sync_binlog = N

binlog_group_commit_sync_no_delay_count = M

binlog_group_commit_sync_delay = 很大值

fsync什么时候发生呀，min(N,M)吗？

sync_binlog搭配binlog_group_commit_sync_delay也可以实现组提交？

答：N次后刷盘，再进入(sync_delay和no_delay_count)逻辑；Sync_delay很大达到no_delay_count才刷；

sync_binlog = 0  binlog_group_commit_sync_no_delay_count = 10 累计10个事务fsync一次？答：等待，等完不调fsync😄