杜绝删库跑路！MySQL崩溃恢复机制让数据绝对安全！

精选原创

公众号JavaEdge 2023-08-03 22:31:27 博主文章分类：MySQL ©著作权

文章标签 MySQL 数据回滚 文章分类 MySQL 数据库 yyds干货盘点

©著作权归作者所有：来自51CTO博客作者公众号JavaEdge的原创作品，谢绝转载，否则将追究法律责任

1 binlog、redo log配合崩溃恢复

反证法，若无两阶段提交（2PC），会导致MySQL主备数据不一致。

2PC的不同瞬间，MySQL若异常重启了，又该如何保证数据完整性？

1.1 2PC

杜绝删库跑路！MySQL崩溃恢复机制让数据绝对安全！_数据

上图即update语句执行流程，其中的commit语句：

【commit语句】，指MySQL语法中，提交一个事务的命令。一般跟begin/start transaction配对
而图中的【commit步骤】，指事务提交过程中的一个小步骤，即最后一步。当这步骤执行完成后，这事务就提交完成
【commit语句】执行时，包含【commit步骤】

这案例没有显式开启事务，因此update语句自己本就是个事务，执行完成后提交事务时，就会用到【commit步骤】。

1.2 2PC的不同时刻，MySQL异常重启的现象

1.2.1 时刻A

即写入redo log处于prepare阶段后、写binlog前，发生崩溃。此时binlog还没写，redo log也还没提交，所以崩溃恢复时，这事务会回滚。

这时，binlog还没写，所以也不会传到备库。

1.2.2 时刻B

即binlog写完，redo log还没commit前就crash

1.3 崩溃恢复时的判断规则

若redo log里的事务完整，即已有commit标识，则直接提交
若redo log里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并完整： a. 是，则提交事务 b. 否则，回滚事务

时刻B crash即对应2(a)，崩溃恢复过程中事务会被提交。

2 MySQL如何知晓binlog是否完整？

一个事务的binlog的完整格式：

statement格式，最后会有COMMIT
row格式，最后会有XID event

MySQL 5.6.2后，引入binlog-checksum参数，验证binlog内容正确性。对于binlog日志由于磁盘原因，可能会在日志中间出错的case，MySQL通过校验checksum勘察。所以，MySQL有法验证事务binlog完整性。

3 redo log、binlog的关联

共同数据字段XID。崩溃恢复时，会按序扫描redo log，若碰到：

有prepare、有commit的redo log，直接提交
只有parepare、无commit的redo log，拿XID去binlog找对应事务

3 prepare阶段redo log+完整binlog，重启即恢复，为何这么设计?

和反证法说到的数据与备份的一致性有关。

时刻B，即binlog写完以后MySQL崩溃，这时binlog已写入，之后就会被从库（或用这个binlog恢复出来的库）使用。

所以，在主库也要提交这事务。采用这策略，主、备库数据保证了一致性。

4 若如此，为何还2PC？

干脆先redo log写完，再写binlog。崩溃恢复时，须两个日志都完整才可以。是不是一样逻辑？其实，2PC是经典分布式系统问题，并非MySQL独有。

若必须举个场景说明必要性，那就是事务的持久性。InnoDB引擎，若：

redo log提交完成，事务就不能回滚。若允许回滚了，就可能覆盖掉其它事务的更新！
redo log直接提交，然后binlog写入时失败，InnoDB又回滚不了，数据和binlog又不一致

2PC就是为了给所有人一次机会，当每个人都说“我ok”时，再一起提交。

但我就是不想引入两个日志，我觉得就是没2PC必要。

5 只用binlog，不要redo log？

只用binlog支持崩溃恢复，又能支持归档，不就够了？即只保留binlog，可将提交流程改成这样：

… -> “数据更新到内存” -> “写 binlog” -> “提交事务”

是不是也能崩溃恢复？No！

5.1 历史原因

InnoDB并非MySQL原生存储引擎。MySQL原生引擎MyISAM在设计之初就不支持崩溃恢复。InnoDB作为MySQL插件加入MySQL引擎前，就已是一个提供了崩溃恢复、事务支持的引擎。InnoDB接入MySQL后，发现既然binlog无崩溃恢复能力，那就用InnoDB原有的redo log。

5.2 实现原因

就依问题所述，来看只用binlog实现崩溃恢复的流程：

杜绝删库跑路！MySQL崩溃恢复机制让数据绝对安全！_数据_02

此时，binlog还是不能支持崩溃恢复。binlog没法恢复“数据页”。

若在binlog2写完，但整个事务还没commit时crash，重启后：

引擎内部事务2会回滚，应用binlog2尚可补回
但事务1，系统已认为提交完成，不会再应用一次binlog1

但InnoDB使用WAL，执行事务时，写完内存和日志，事务就算完成。若之后才崩溃，要依赖日志来恢复数据页。

即在图中的crash点，事务1也可能丢失，而且是数据页级的丢失。此时，binlog里并没记录数据页的更新细节，补不回来。

那优化binlog内容，让它记录数据页的更改可以吗？但这其实就是又做个redo log出来。

所以，至少现在的binlog不支持崩溃恢复。

6 只用redo log，不要binlog？

若只从崩溃恢复角度，可以。可把binlog关掉，就没有 2PC，但系统依然crash-safe。

但了解业界使用场景，就会发现在正式生产库，binlog都是开着。因为binlog有redo log无法替代的功能：

6.1 归档

redo log是循环写，这样历史日志没法保留，redo log做不到归档。

6.2 MySQL系统依赖于binlog

binlog作为MySQL早期功能，被用在很多地方。MySQL高可用基础就是binlog复制。

异构系统（如一些数据分析系统），这些系统就靠消费MySQL binlog更新自己的数据。关掉binlog，这些下游系统就没法输入。

由于现在包括MySQL高可用在内的很多系统机制都依赖于binlog，所以“鸠占鹊巢”redo log做不到。生态就这么重要！

7 redo log设多大？

redo log太小，会导致很快被写满，然后不得不强行刷redo log，WAL就发挥不出来。

若现在常见的几T磁盘，就不要太小气，直接将redo log设为4个文件、每个文件1G。

8 正常运行实例，数据写入后的最终落盘

是从redo log更新过来的
or从buffer pool更新过来的

这涉及“redo log里到底是什么”。redo log并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，就不存在“数据最终落盘，是由redo log更新过去”的情况。

如正常运行的实例，数据页被修改后，跟磁盘的数据页不一致，即脏页。最终数据落盘，就是把内存中的数据页写盘。这过程与redo log无关
崩溃恢复场景，InnoDB如判断到一个数据页可能在崩溃恢复时丢失了更新，就会将它读到内存，然后让redo log更新内存内容。更新完成后，内存页变成脏页，就回到第一种情况

9 先修改内存，还是先写redo log文件

一个事务更新过程中，日志要写多次：

begin;
 insert into t1 ...
 insert into t2 ...
 commit;

该事务往两个表插记录，插入过程中，生成的日志都得先保存，但又不能在还没commit时就直接写到redo log文件（为防止 sql 有问题？那么 redo log 也会报错）。

redo log buffer就是块内存，用来先存redo日志。即执行第一个insert时，数据的内存被修改了，redo log buffer也写入日志。

但真正把日志写到redo log文件（文件名=ib_logfile+数字），是在执行commit时。

事务执行过程中不会“主动去刷盘”，以减少不必要IO消耗。但可能“被动写盘”，如内存不够、其他事务提交。

单独执行一个更新语句，InnoDB会自己启动一个事务，在语句执行完成时提交。

10 业务设计

A、B两个用户，互相关注，则成为好友：

like表
friend表
like表有user_id、liker_id两个字段，我设置为复合唯一索引即uk_user_id_liker_id。语句执行逻辑是这样的：

以A关注B为例：第一步，先查询对方有没有关注自己（B有没有关注A） select * from like where user_id = B and liker_id = A;
如果有，则成为好友 insert into friend;
没有，则只是单向关注关系 insert into like;
但是如果A、B同时关注对方，会出现不会成为好友的情况。因为上面第1步，双方都没关注对方。第1步即使使用了排他锁也不行，因为记录不存在，行锁无法生效。请问这种情况，在MySQL锁层面有没有办法处理？

表模拟

CREATE TABLE `like` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `user_id` int(11) NOT NULL,
   `liker_id` int(11) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `uk_user_id_liker_id` (`user_id`,`liker_id`)
 ) ENGINE=InnoDB;
 
 CREATE TABLE `friend` (
   id` int(11) NOT NULL AUTO_INCREMENT,
   `friend_1_id` int(11) NOT NULL,
   `firned_2_id` int(11) NOT NULL,
   UNIQUE KEY `uk_friend` (`friend_1_id`,`firned_2_id`)
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB;

虽然这个题干中，并没有说到friend表的索引结构。但我猜测friend_1_id和friend_2_id也有索引，为便于描述，我给加上唯一索引。

顺便说明一下，“like”是关键字，我一般不建议使用关键字作为库名、表名、字段名或索引名。

我把他的疑问翻译一下，在并发场景下，同时有两个人，设置为关注对方，就可能导致无法成功加为朋友关系。

现在，我用你已经熟悉的时刻顺序表的形式，把这两个事务的执行语句列出来：

杜绝删库跑路！MySQL崩溃恢复机制让数据绝对安全！_回滚_03

图3 并发“喜欢”逻辑操作顺序

由于一开始A和B之间没有关注关系，所以两个事务里面的select语句查出来的结果都是空。

因此，session 1的逻辑就是“既然B没有关注A，那就只插入一个单向关注关系”。session 2也同样是这个逻辑。

这个结果对业务来说就是bug了。因为在业务设定里面，这两个逻辑都执行完成以后，是应该在friend表里面插入一行记录的。

如提问里面说的，“第1步即使使用了排他锁也不行，因为记录不存在，行锁无法生效”。不过，我想到了另外一个方法，来解决这个问题。

首先，要给“like”表增加一个字段，比如叫作 relation_ship，并设为整型，取值1、2、3。

值是1的时候，表示user_id 关注 liker_id; 值是2的时候，表示liker_id 关注 user_id; 值是3的时候，表示互相关注。

然后，当 A关注B的时候，逻辑改成如下所示的样子：

应用代码里面，比较A和B的大小，如果A<B，就执行下面的逻辑

begin; /*启动事务*/
 insert into `like`(user_id, liker_id, relation_ship) values(A, B, 1) on duplicate key update relation_ship=relation_ship | 1;
 select relation_ship from `like` where user_id=A and liker_id=B;
 /*代码中判断返回的 relation_ship，
   如果是1，事务结束，执行 commit
   如果是3，则执行下面这两个语句：
   */
 insert ignore into friend(friend_1_id, friend_2_id) values(A,B);
 commit;

如果A>B，则执行下面的逻辑

begin; /*启动事务*/
 insert into `like`(user_id, liker_id, relation_ship) values(B, A, 2) on duplicate key update relation_ship=relation_ship | 2;
 select relation_ship from `like` where user_id=B and liker_id=A;
 /*代码中判断返回的 relation_ship，
   如果是2，事务结束，执行 commit
   如果是3，则执行下面这两个语句：
 */
 insert ignore into friend(friend_1_id, friend_2_id) values(B,A);
 commit;

这个设计里，让“like”表里的数据保证user_id < liker_id，这样不论是A关注B，还是B关注A，在操作“like”表的时候，如果反向的关系已经存在，就会出现行锁冲突。

然后，insert … on duplicate语句，确保了在事务内部，执行了这个SQL语句后，就强行占住了这个行锁，之后的select 判断relation_ship这个逻辑时就确保了是在行锁保护下的读操作。

操作符 “|” 是按位或，连同最后一句insert语句里的ignore，是为了保证重复调用时的幂等性。

这样，即使在双方“同时”执行关注操作，最终数据库里的结果，也是like表里面有一条关于A和B的记录，而且relation_ship的值是3，并且friend表里面也有了A和B的这条记录。

不知道你会不会吐槽：之前明明还说尽量不要使用唯一索引，结果这个例子一上来我就创建了两个。这里我要再和你说明一下，之前文章我们讨论的，是在“业务开发保证不会插入重复记录”的情况下，着重要解决性能问题的时候，才建议尽量使用普通索引。

而像这个例子里，按照这个设计，业务根本就是保证“我一定会插入重复数据，数据库一定要要有唯一性约束”，这时就没啥好说的了，唯一索引建起来吧。

11 问题

表t，并插入一行，然后对这一行做修改。

CREATE TABLE `t` (
 `id` int(11) NOT NULL primary key auto_increment,
 `a` int(11) DEFAULT NULL
 ) ENGINE=InnoDB;
 insert into t values(1,2);

这时候，表t里有唯一的一行数据(1,2)。假设，我现在要执行：

update t set a=2 where id=1;

你会看到这样的结果：

杜绝删库跑路！MySQL崩溃恢复机制让数据绝对安全！_回滚_04

结果显示，匹配(rows matched)了一行，修改(Changed)了0行。

仅从现象上看，MySQL内部在处理这个命令的时候，可以有以下三种选择：

更新都是先读后写的，MySQL读出数据，发现a的值本来就是2，不更新，直接返回，执行结束；
MySQL调用了InnoDB引擎提供的“修改为(1,2)”这个接口，但是引擎发现值与原来相同，不更新，直接返回；
InnoDB认真执行了“把这个值修改成(1,2)"这个操作，该加锁的加锁，该更新的更新。

实际是哪种？可否用构造实验证明？MySQL为什么要选择这种策略？

12 用一个计数表记录一个业务表的总行数，在往业务表插入数据的时候，需要给计数值加1。

逻辑实现上是启动一个事务，执行两个语句：

insert into 数据表
update 计数表，计数值加1

并发角度考虑，怎么安排这两个语句的顺序？

应先插入操作记录，再更新计数表。因为更新计数表涉及行锁竞争，先插入、再更新，最大程度减少事务间的锁等待，提升并发度。

有人说，应将update计数表放后面，因为这个计数表可能保存了多个业务表的计数值。如果把update计数表放到事务的第一个语句，多个业务表同时插入数据的话，等待时间会更长。结论对，但理解不太正确。即使我们用一个计数表记录多个业务表的行数，也肯定会给表名字段加唯一索引：

CREATE TABLE `rows_stat` (
   `table_name` varchar(64) NOT NULL,
   `row_count` int(10) unsigned NOT NULL,
   PRIMARY KEY (`table_name`)
 ) ENGINE=InnoDB;

在更新计数表的时候，一定会传入where table_name=$table_name，使用主键索引，更新加行锁只会锁在一行上。

而在不同业务表插入数据，是更新不同的行，不会有行锁。

虽然引入事务，避免看到”业务上还没提交的更新”，但是Redis的计数被提前看到了。核心原因还是两个系统，不支持一致性视图。

count(id)也可以走普通索引得到。