MySQL如何保证数据不丢失

最新推荐文章于 2024-06-07 23:00:33 发布

南秋同学

最新推荐文章于 2024-06-07 23:00:33 发布

阅读量91

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/csdn_demo_java/article/details/134421161

版权

本章内容

MySQL的数据可靠性主要通过redo log和bin log来保证。

bin log（归档日志）

bin log（binary log），二进制日志文件，又称归档日志，是MySQL Server层日志，它记录了所有更新数据库的语句（如：ddl和dml语句）并以二进制的形式保存在磁盘中，但是不包含没有修改任何数据的语句（如：数据查询语句select、show等）。

bin log的主要作用是数据备份和数据复制。

日志格式

bin log是逻辑日志，日志格式通过参数binlog_format进行设置。

写入机制

在事务执行过程中，先将日志写到binlog cache，事务提交时，再将binlog cache写到binlog文件中。

binlog cache是为了保证一个事务的所有操作能够一次性写入bin log文件而设置的缓存，每个线程对应一个binlog cache，其大小由binlog_cache_size参数控制，如果日志超过了该参数设定的大小，则需要暂存到磁盘。事务提交时，执行器将binlog cache中的完整事务写入到binlog文件中，并清空binlog cache。

如图所示：

图中，每个线程有自己binlog cache，所有线程共用同一份binlog文件。

write：将日志写入到文件系统的page cache，此时并没有将数据持久化到磁盘，所以速度比较快。
fsync：将数据持久化到磁盘的操作。一般情况下fsync才占磁盘的IOPS。

write和fsync的执行时机由参数sync_binlog控制：

sync_binlog=0：表示每次提交事务只执行write，不执行fsync。
sync_binlog=1：表示每次提交事务都会执行fsync。
sync_binlog=N(N>1)：表示每次提交事务都执行write，累积N个事务后才执行fsync。

因此，在出现IO瓶颈的场景中，将sync_binlog设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般将该参数设置为100~1000中的某个值。将sync_binlog设置为N对应的风险是：如果主机发生宕机，会丢失最近N个事务的binlog日志。

redo log（重做日志）

redo log是InnoDB引擎特有的日志。它是一种物理日志，记录在某个数据页上做了什么修改，其大小固定（可以通过参数innodb_log_file_size设置），通过顺序写入的方式提高写入性能。

比如：可以配置为一组4个文件，每个文件的大小为1GB，redo log一共可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写。

如图所示：

其中：

write pos：当前记录的位置，一边写入一边后移，写到第3号文件末尾后就回到0号文件开头。
checkpoint：当前要擦除的位置，一边擦除一边后移，擦除记录前需要将记录更新到数据文件中。
write pos和checkpoint之间部分用来记录新的操作。

如果write pos追上checkpoint，则说明redo log已满，不能再执行新的操作，需要将checkpoint往前推进。

通过redo log日志，InnoDB就可以保证即使数据库发生宕机，之前提交的记录也不会丢失，这个能力称为crash-safe。

写入机制

在事务执行过程中，先将生成的redo log写入redo log buffer。redo log buffer不是每次生成后都直接持久化到磁盘，如果事务执行期间MySQL发生宕机，会丢失这部分未持久化到磁盘的日志。由于此时事务并没有提交，因此这部分日志即使丢失也不会造成影响。

某个事务还未提交时，其对应的redo log buffer中的部分日志有可能已经被持久化到磁盘中，具体情况根据redo log的三种状态而定。这三种状态分别是：

日志存储在redo log buffer中。
日志存储在fs page cache中。
日志存储在磁盘中。

如图所示：

日志写到redo log buffer和 page cache中很快，但是持久化到磁盘旧慢多了。

redo log的写入策略由参数innodb_flush_log_at_trx_commit控制：

设置为0：表示每次事务提交时只将日志写入redo log buffer中。
设置为1：表示每次事务提交时都将日志直接持久化到磁盘中。
设置为2：表示每次事务提交时都将日志写入fs page cache中。

InnoDB有一个后台线程，每隔1秒将redo log buffer中的日志通过调用write写入fs page cache中，再通过调用fsync持久化到磁盘中。因此，当innodb_flush_log_at_trx_commit设置为1时，某个还未提交的事务日志可能已经被持久化到磁盘中。

实际上，除了后台线程每秒1次的轮询操作外，还有两种场景也会使得某个未提交的事务日志被持久化到磁盘中。

1）redo log buffer占用的空间即将达到innodb_log_buffer_size一半时，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是write，而没有调用fsync。即：日志保留在fs page cache中。
2）并行事务提交时，会顺带将这个未提交事务的日志持久化到磁盘。假设一个事务A执行到一半，已经写了部分日志到redo log buffer中，此时有另外一个线程的事务B提交，如果参数innodb_flush_log_at_trx_commit设置为1，那么事务B需要将redo log buffer中的日志全部持久化到磁盘。此时，就会带上事务A在redo log buffer中的日志一起持久化到磁盘中。

redo log与bin log区别

redo log与bin log主要区别：

redo log是InnoDB引擎特有的日志；而binlog是在MySQL的Server层实现的日志，所有引擎都可以使用。
redo log是物理日志，记录的是在某个数据页上做了什么修改；而binlog是逻辑日志，记录的是这个语句的原始逻辑（如：给ID=2这一行的c字段加1 ）。
redo log采用循环写入的方式记录日志，空间固定，会用完（crash-safe能力）；而binlog采用追加写入的方式记录日志。追加写入指的是binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志（归档能力）。

两阶段提交

WAL

WAL的全称为Write-Ahead Logging，它的核心思想是先写日志、再写磁盘。

当有一条记录需要更新时，InnoDB引擎就会先将该记录写到redo log中，并更新内存，InnoDB引擎会在系统比较空闲时将这个操作记录更新到磁盘中。

两阶段提交处理流程

以一条update语句为例：

mysql> update T set c=c+1 where ID=2;

执行流程，如图所示：

图中：

浅蓝色部分表示在执行器中执行。
浅红色部分表示在InnoDB引擎中执行。

执行步骤：

1）执行器通过引擎获取ID=2的数据行。ID是主键，引擎直接使用树搜索找到这数据行。如果ID=2的数据行所在的数据页在内存中，则直接将数据行返回给执行器；否则从磁盘读取ID=2的数据行到内存中，再返回。
2）执行器获取到引擎返回的ID=2的数据行，先将数据行中的字段c+1，再调用引擎接口写入该行新数据。
3）引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log中（此时redo log处于prepare状态），再告知执行器提交事务。
4）执行器生成这个操作的binlog，并将binlog写入磁盘。
5）执行器调用引擎的提交事务接口，将刚刚写入的redo log状态更新为提交（commit状态），更新完成。

其中，最后三步将redo log的写入拆成了两个步骤：prepare和commit，这就是两阶段提交。

小提示：如果将redo log的参数innodb_flush_log_at_trx_commit设置为1，则redo log在prepare阶段就会持久化一次。每秒一次后台轮询刷盘时，redo log在commit阶段不需要进行fsync，只需将日志write到fs page cache中即可。

MySQL的双1配置指的是参数sync_binlog和innodb_flush_log_at_trx_commit都设置为1。即：一个事务完整提交前，需要等待两次刷盘，一次是redo log（prepare 阶段），一次是binlog。

崩溃恢复逻辑

如果日志在写入redo log处于prepare阶段后、写binlog前发生了崩溃（crash），由于此时binlog还未写入，redo log也未提交，所以崩溃恢复时会回滚该事务。

如果日志在写入bin log后、redo log commit前发生了崩溃（crash），则需要根据如下规则进行判断：

如果事务的redo log完整（即：有commit标识），则直接提交。
如果事务的redo log只有完整的prepare，则判断事务对应的binlog是否存在并完整：
- a. 存在并完整，则提交事务。
- b. 不存在或不完整，则回滚事务。

如何判断binlog是否完整？

1）statement格式的完整binlog，最后会有commit。
2）row格式的完整binlog，最后会有一个xid event。
3）在MySQL 5.6.2版本以后，还引入了参数binlog-checksum，用来验证binlog内容的正确性。对于binlog日志，由于磁盘原因可能会存在日志中间出错的情况，MySQL可以通过校验checksum的结果来进行判断。

redo log和bin log如何进行关联？

redo log和bin log有一个共同的数据字段：xid。崩溃恢复时会按顺序扫描redo log：