mysq如何保证数据的可靠性

目录

1. redo log

2. binlog

3. 两阶段提交

---

MySQL这种关系型数据库中，讲究日志先行策略，只要日志持久化到磁盘，就能保证MySQL异常重启后，数据不丢失。在MySQL中，提到日志不得不提的就是redo log和binlog。

1. redo log

事物在执行过程中，生成的redo log是要先写到redo log buffer的。redo log buffer里面的内容并非每次生成后都要直接持久化到磁盘的。

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

事物还没提交的时候，redo log buffer中的部分日志也有可能被持久化到磁盘。这个问题需要从redo log 可能存在的三种状态说起：

1. 存在redo log buffer中，物理上是在MySQL进程内存中；
2. 写到磁盘（write），但是没有持久化(fsync)，物理上是在文件系统的page cache 里面；
3. 持久化到磁盘，对应的是hard disk。

日志写到redo log buffer是很快的，write到page cache也差不多，但是持久化到磁盘的速度就慢多了。为了控制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，也是可能已经持久化到磁盘的。实际上，除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的食物的redo log 写入到磁盘中：

1、redo log buffer占用的空间即将达到innodb_log_buffer_size一半的时候，后台线程会主动写入到磁盘中。由于这个事物并没有提交，所以这个写盘动作只是write，而没有调用fsync，也就是只留在了文件系统的page cache。

2、并行的事物提交的时候，顺带将这个事物的redo log buffer持久化到磁盘。

 

2. binlog

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

binlog又称二进制日志，记录了对MySQL数据库执行更改的所有操作，不包含select和show操作，主要起到了恢复、复制、审计等功能。Binlog的格式主要有statement、row、mixed三种。

Statement：基于操作的SQL语句记录到binlog中，不建议使用。

Row：基于行的变更情况记录，会记录行更改前后的内容，row模式也是数据库不丢数据的重要保证，推荐使用。

Mixed：混合前两个模式，不建议使用。

Binlog的写入逻辑也比较简单：事务执行过程中，先写入binlog cache,事务提交时再写入binlog文件。binlog cache由binlog_cache_size和max_binlog_size参数控制，每个线程分配一个binlog cache,但是共用binlog文件。

Binlog的写入日志文件的机制由sync_binlog控制：

• sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
• sync_binlog=1 的时候，表示每次提交事务都会执行 fsync，将数据刷盘；
• sync_binlog=N(N>1) 的时候，表示n次事务提交之后，MySQL才进行一次fsync动作，将binlog cache中的数据刷入磁盘。

innodb_flush_log_at_trx_commit和sync_binlog都设置为1是MySQL数据中经典的双一模式，是数据库不丢数据的保障。

MySQL数据采取WAL机制就是为了减少每次脏数据刷盘带来的性能影响，如果设置”双一”策略会不会影响数据库的性能呢？其实这主要得益于redo log和binlog都是顺序写，磁盘的顺序写比随机写的速度要快的多，加上MySQL内部的组提交机制，已经大幅降低了对磁盘的IOPS消耗了。

 

3. 两阶段提交

MySQL引入二阶段提交（two phase commit or 2pc），MySQL内部会将普通事务当做一个XA事务（内部分布式事务）来处理,会自动为每个事务分配一个唯一的ID（XID），COMMIT会被动的分成Prepare和Commit两个阶段。

第一阶段：Transaction Prepare Phase

此时SQL已经成功执行，并生成xid信息及redo和undo的内存日志。然后调用prepare方法完成第一阶段，将事务状态设为TRX_PREPARED，并将redo log刷盘。

第二阶段：Commit Phase

如果事务第一阶段进入prepare阶段，则将产生的binlog写入文件并刷盘，此时事务已经铁定要提交了。

具体异常场景分析：

1. 当事务在prepare阶段crash，数据库recovery的时候该事务未写⼊Binary log并且存储引擎未提交，则该事务rollback。

2. 当事务在binlog阶段crash，此时⽇志还没有成功写⼊到磁盘中，启动时会rollback此事务。

3. 当事务在binlog⽇志已经fsync()到磁盘后crash，但是InnoDB没有来得及commit，此时MySQL数据库recovery的时候将会读出⼆进制⽇志的Xid_log_event，然后告诉InnoDB提交这些XID的事务，InnoDB提交完这些事务后会回滚其它的事务，使存储引擎和⼆进制⽇志始终保持⼀致。

MySQL的二阶段提交就保证了数据库在异常宕机重启后的数据不丢失。

 

参考：

https://www.it610.com/article/1294243353461858304.htm

https://zhuanlan.zhihu.com/p/183731242