23MySQL是怎么保证数据不丢的笔记

MySQL如何保证数据不丢失

author：陈镇坤27

创建时间：2021年11月25日16:46:04

编辑时间：2021年11月26日11:43:09、2021年11月28日16:37:12、2021年11月29日10:46:21、2021年11月30日22:09:58、2021年12月2日14:06:56

转载请注明出处

————————————————————————————————

写在前面：本文中，文件录入到文件系统的page cche，意味着“写到磁盘”，但只有fsync时，才说是“持久化到磁盘”。

问：MySQL能够保证数据不丢失的根据是什么？

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

binlog的写入机制

问：binlog是怎么写入磁盘的？

答：事务执行过程中，记录先写到binlog cache中，事务提交时，再从binlog cache同步到binlog。

事务执行过程中，每个线程会分配得到一片binlog cache，由参数binlog_cache_size控制cahce大小（如果超过，则会暂存到磁盘）， 并将记录写到binlog cahce中，由于一个事务中的binlog是不可拆分的，必须一次性全部写入，所以binlog cache在write到page cache前，必定是全部收集事务记录完毕，并以事务为单位进行write工作。

从binlog cache写入磁盘，整个流程分为

“write到文件系统的page cache（写入文件内存页缓存）”

“从page cahce fsync到磁盘（调用OS同步文件内存脏页到磁盘）（fsync才占用磁盘的IOPS）”。

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

答：系统变量sync_binlog

值为0，每次提交都执行到write；（由OS决定fsync）

值为1，每次提交都执行到fsync；

值为N（N>1），每次提交都执行到wirte，然后page cahce中的binlog记录凑齐N个事务后才执行fsync；（OS主机异常时，可能丢失page cache数据）

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

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

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

答：不算，此时客户端收到网络连接失败的反馈。

MySQL的crash-safe指的是：客户端收到事务成功消息，则事务一定持久化；若收到事务失败消息，则事务一定失败；若收到执行异常消息，则等重新连接后，查询当前状态，继续之前的逻辑。crash-safe保证的是数据和日志之间、主库和备库之间数据的一致性。

redolog的写入机制

问：redo log buffer作为redo log的缓存，是不是必须每次都进行磁盘的持久化？

答：不需要。只有提交了才会从redo log buffer同步到redo log中，也就是说没提交的时候在buffer，即使丢失了，数据也可以进行回滚。

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

答：系统变量，innodb_flush_log_at_trx_commit

值为0，每次事务提交，都将只将日志留在redo log buffer中

值为1，每次事务提交，都全部持久化redo log buffer中的记录

值为2，每次事务提交，都只将日志写到磁盘（write into page cache）

问：redo log buffer在事务还没提交之前，是否可能被持久化到磁盘？

答：可能。后台线程每隔一秒，会将redo log buffer中的数据write到page cache，再fsync到磁盘。

此外，如果redo log buffer大小达到innodb_redo_log_buffer_size的大小（16MB）的一半，则后台线程会主动写盘。由于事务还未提交，因此不会持久化到磁盘，仅写到文件系统缓存页。

最后，如果innodb_flush_log_at_trx_commit设置为1，则事务提交时，会将其他尚未提交的redo log buffer中的记录全部持久化到磁盘中。

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

答：为1时，是保证所有redo 记录不丢失，MySQL可以根据redo log的prepare再对binlog的判定来判断事务是否生效，因此prepare的redo log也是可以起到数据恢复作用的，作为记录来说，是innodb_flush_log_at_trx_commit为1时必须保证持久化的数据。

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

答：事务的commit阶段指的是事务提交过程中，最后redo log被标记为commit的阶段。结合后台每隔1秒刷redo log和崩溃恢复的prepare逻辑原理考虑。

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

问：如果事务没提交就从redo log buffer持久化到磁盘，不就是脏数据了吗？

答：脏就脏，这个日志如果没有提交标识，在数据崩溃恢复时，是不会执行的。

问：什么是MySQL的双1设置？

答：sync_binlog和innodb_flush_log_at_trx_commit都为1。此时有两次刷盘：redo log buffer持久化到磁盘，binlog持久化到磁盘。

组提交

（有几个问题找不到答案）

1、lsn具体是在该事务执行提交后生成的本次的LSN，还是上一个事务执行提交后生成的下一次的LSN，还是事务写入redo_log_buffe之后立刻生成的本次的LSN？

2、组提交是否只对prepare的事务生效？

3、没进行提交的仍处于执行中的事务也可能被写入磁盘，那若剩下的事务执行，则剩下的部分是按顺序循环写到redo log中的，那lsn标识没问题吗？

第三个问题我的理解：redo log buffer中只有处于prepare的事务会被清理掉，其他的事务，仍然保留，此时若其他尚未进入prepare的事务被持久化到了redo log，则等该事务真正commit时，在持久化一遍，无碍。

PS：事务执行commit语句时，才redo log buffer中的对应事务才开始主动写入磁盘的redo log文件。

问：介绍一下日志逻辑序列号。

答：log sequence number，简称lsn。

lsn单调递增，对应每个redo log写入点，值为上一个写入点+本次写入的redo log长度。

数据页的变动redo log的lsn同样也会记录到数据页上，以此作为后期数据恢复时是否已经应用变动记录的判定。

问：双1设置的MySQL服务器，若TPS能力有2w，同时磁盘能力却只有2w，求如何实现。

答：（确定性未明确）当设置innodb_flush_log_at_trx_commit为1后，一个redo log buffer的事务提交时，会将该buffer中其他尚未提交的事务一并持久化到磁盘，实际上，执行持久化的命令中，提交的事务的lsn（提交那一刻buffer中最大的lsn号）是其参数，引擎根据lsn持久化对应的buffer og，等持久化完毕后，其他redo log buffer的事务提交时，其自身真正的lsn做判定，在已提交的lsn号之后，则直接返回。也就是说，组提交提高了其性能。

PS：组员越多，节约IOPS的效果越好。

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

答：事务提交的时候，流程是

redo log prepare：write

binlog：write

redo logprepare：fsync

binlog：fsync

redo log commit：write

binlog执行fsync时，也会将其他的位于page cache的binlog进行fsync，但由于binlog的write和fsync间隔很短，所以往往效果不明显。

MySQL提供了两个参数：

binlog_group_commit_sync_delay：微秒（表示多少时间后才能fsync）

binlog_group_commit_sync_no_delay_count：事务次数（表示积累多少个以后才能fsync）

两者是或关系

PS：虽然redo log和binlog写磁盘，但它们是磁盘写，且得益于组提交，节约了大量IOPS
PS：引用一个思考评论的答案：（在 sync_binlog=0 的情况下，设置 sync_delay 和 sync_no_delay_count 的现象，点赞这种发现边界的意识和手动验证的好习惯。是这样的：sync_delay 和 sync_no_delay_count 的逻辑先走，因此该等还是会等。等到满足了这两个条件之一，就进入 sync_binlog 阶段。这时候如果判断 sync_binlog=0，就直接跳过，还是不调 fsync。）

问：组提交内部是保证顺序的吗？

答：是的，默认行为是opt_binlog_order_commits=ON，是保证顺序的。

问：如果MySQL出现性能瓶颈，可以从日志的哪些角度进行优化？

答：将binlog的提交延迟设置大一点，增多组提交的组员，减少写盘次数；

将sync_binlog设置大于1，但这可能导致出现数据丢失的风险（主机宕机时）；

将innodb_flush_log_at_trx_commit设置为2，但这可能导致出现数据丢失的风险（主机宕机时）；

问：设置innodb_flush_log_at_trx_commit为0会怎么样？

答：每次事务提交只将日志留在redo log buffer中，只能依赖本身后台线程的同步操作，这样MySQL宕机时，都有可能丢失数据。

问：什么时候把线上生产库设置为双非1？

答：业务高峰期，把主库设为双非1；备库延迟；用备库恢复主库的副本时，将主库设为双非1；批量导入大量数据；一般设置参数如下：

set innodb_flush_logs_at_trx_commit=2;
set sync_binlog=1000

扩展资料

Page Cache是OS关于磁盘IO的缓存，位于内核中，不适用于大文件传输，因为大文件传输page cache的命中率比较低，这个时候page cache不仅没有起到作用还增加了一次数据从磁盘buffer到内核page cache的开销高版本的Linux系统中已经把Buffer跟虚拟文件系统的page cache合并在一起了，因此也就没有从磁盘buffer拷贝到内核page cache的开销page cache是Linux系统的机制，MySQL异常重启，这部分数据还在，但主机异常重启，会丢失

fsync函数同步内存中所有已修改的文件数据到储存设备 一般情况下，对硬盘（或者其他持久存储设备）文件的write操作，更新的只是内存中的页缓存（page cache），而脏页面不会立即更新到硬盘中，而是由操作系统统一调度，如由专门的flusher内核线程在满足一定条件时（如一定时间间隔、内存中的脏页达到一定比例）内将脏页面同步到硬盘上（放入设备的IO请求队列）。 因为write调用不会等到硬盘IO完成之后才返回，因此如果OS在write调用之后、硬盘同步之前崩溃，则数据可能丢失
新到硬盘中，而是由操作系统统一调度，如由专门的flusher内核线程在满足一定条件时（如一定时间间隔、内存中的脏页达到一定比例）内将脏页面同步到硬盘上（放入设备的IO请求队列）。 因为write调用不会等到硬盘IO完成之后才返回，因此如果OS在write调用之后、硬盘同步之前崩溃，则数据可能丢失