面试官：如果 MySQL 数据库中的数据丢失，有哪些补救的办法呢？

以前去企业面试的时候，经常被问一些关于 MySQL 数据库相关的问题，其中最典型的就是关于 MySQL 数据库数据安全的问题。

例如：如何才能保证 MySQL 数据库的数据安全？MySQL 数据库如果发生数据丢失可能会发生在什么地方？如果 MySQL 数据库出现了数据丢失该如何挽救？

问这些问题的主要目的就是考验求职者的生产经验；但是就我面试的过程而言，能够完整答出来的求职者微乎其微。

出现这种情况主要是因为大多数求职者对于 MySQL 数据库底层数据存储的运行机制了解得不够清楚，那么今天我们就来针对于上述的几个问题跟大家详细讨论一下 MySQL 数据库的底层存储运行机制。

MySQL 数据库在什么情况下可能会发生数据丢失

在介绍 MySQL 数据库在什么情况下可能会丢失数据之前，我们首先回顾一下写入一条数据到 MySQL 数据库中所经历的模块，具体如下：

• 第一个模块：将修改的数据逻辑保存在 change buffer 之中。

• 第二个模块：将修改的数据保存在 binlog cache 之中。

• 第三个模块：将修改的数据保存在 redo log 之中。

在这三个模块中，change buffer 是用来保存修改数据的逻辑的，在修改之后，通过 merge 的方式写入磁盘。

在这个模块中，有没有可能发生数据丢失呢？其实微乎其微，这主要是因为 MySQL 数据库为了防止数据丢失而增加了 redo log 这个模块，其主要的作用就是防止数据丢失。那么，我们就一起来聊一聊 redo log 是怎么保存数据的。

redo log 主要分为两个部分，分别是 redo log 和 redo log buffer 两个部分。redo log 和 redo log buffer 的功能我们之前多次说过，这里不再赘述。下面我们主要介绍 redo log 是怎么保存数据的。

当一条数据写入数据库之前，为了防止数据丢失，首先会将该条数据保存在 redo log buffer 之中，然后再保存在 redo log 之中，以便当数据库宕机之时作数据恢复使用。

那么这个时候我们就要问，在这个过程中 redo log 有没有可能会发生数据丢失呢？

这是有可能的。假如一个事务在执行一半的时候突然 MySQL 数据库宕机，此时 redo log buffer 中的所有数据将会全部丢失，不过一般这种情况只会发生在事务未提交的情况下，所有数据丢失也影响不大。

此时，我相信你会问，如果恰好在事务提交之时，MySQL 数据库发生宕机会不会丢失数据呢？

显然，这也是有可能的。下面我们根据 MySQL 数据库保存 redo log buffer 中的数据来分析丢失数据的可能。

在 MySQL 数据库中，redo log buffer 有三种保存数据的状态，分别是：

• redo log buffer 将数据保存在 MySQL 数据库的内存中，也就是 InnoDB 存储引擎的 buffer pool 之中。这其实跟上述的情况一致，保存的是未提交的数据，此时如果 MySQL 发生宕机，丢失的是未提交的事务信息，对于 MySQL 数据库整体而言，没有大的影响。

• redo log buffer 将数据保存在 page cache 之中，也就是磁盘缓存之中。此时 MySQL 中的事务已经提交，假设恰巧运行 MySQL 数据库的服务器在此时发生宕机，那么很显然已经提交的事务数据就会发生丢失。在这种情况下发生的数据丢失是无法恢复的。

• redo log buffer 将数据保存在磁盘之中，这种情况下一般只有磁盘不发生异常，是不会发生数据丢失的。

注意：系统磁盘也是有缓存的，通常我们称之为：page cacge。

MySQL 针对于 redo log buffer 保存数据的三种状态又提供了名为 innodb_flush_log_at_trx_commit 的参数，这个参数有三个值，最主要的功能是告诉 MySQL 该将 redo log buffer 中的数据保存在哪里，具体如下：

• 当该参数的值设置为 0 时，redo log buffer 将会把所有的数据保存在 buffer pool 之中；也就是全部保存在内存之中，此时性能是最好的，但是一旦数据库发生了重启，redo log buffer 中的数据也就随即全部丢失。

• 当该参数的值设置为 1 时，redo log buffer 将会把所有的数据直接保存在磁盘之中，此时数据是最安全的，但是性能却是最差的。

• 当该参数的值设置为 2 时，redo log buffer 将会把所有的数据保存在 page cache 之中；也就是说会将数据全部保存在磁盘缓存之中，此时性能跟设置为 0 时的性能相差无几，但是如果此时部署 MySQL 数据库的服务器发生了宕机，数据也会随即丢失。

注意：在实际的应用中，也并非只有事务发生了提交，才会将数据保存到磁盘之中。还有如下两种情况。

第一种情况：如果有多个事务并行之时，会将已经保存在 redo log buffer 中的数据全部持久化。

第二种情况：如果 redo log buffer 占用 InnoDB 存储引擎的 buffer pool 内存空间的一半，MySQL 也会将数据持久化。

上面我们介绍了 redo log 可能会发生数据丢失的场景，下面我们再来了解一下 binlog 中可能发生数据丢失的情况。

相对于 redo log 来说，binlog 写数据相对简单。

首先需要说明的是，binlog 每一次写入都是将整个事务同时写入 binlog 文件中，这主要是因为 MySQL 数据库中的事务具有原子性，所以在一个事务未执行完成之前，MySQL 数据库是将其写入 binlog cache 之中。

其中 binlog cache 是 MySQL 中的一块内存空间，那么此时就带来了一个新的问题，就是如果 binlog cache 的空间不足以承载某一个事务所包含的所有数据时，MySQL 会将该事务中所有的数据全部暂存到磁盘中（此时就会不得已而产生磁盘 IO，随即就会导致一定的性能问题）。

为了解决这个问题，MySQL 数据库为我们提供了一个 binlog_cache_size 参数，这个参数主要是用来设置 binlog cache 的空间大小的。如果当 binlog cache 中的数据大小超过了 binlog_cache_size 设置的大小时，MySQL 会将该事务中所有的数据全部暂存到磁盘中。

与 redo log 相同的是，binlog cache 也有保存数据的三种状态，并且 MySQL 提供了 sync_binlog 这个参数来控制这种状态。这三种状态分别是：

• 当该值等于 0 时，每次事务提交之后，保存在 page cache；

• 当该值等于 1 时，每次事务提交之后，保存到磁盘之中；

• 当该值等于 N（N > 1） 时，每次事务提交之后，都会保存在 page cache 之中，并且累计 N 次之后写入磁盘。

于是，我们不难看出，当 N 越大时，相关的性能就会越好；相反，如果在数据提交期间发生了数据库宕机，随即带来的后果就是会丢失保存在 binlog cache 中的数据。

总结

今天，我们主要介绍了在 MySQL 数据库运行过程中可能会发生数据丢失的几种情况。

首先是 redo log，在 redo log 中最可能丢失数据的情况就是当 redo log buffer 中的数据保存在 MySQL 内存之中，也就是当 innodb_flush_log_at_trx_commit 设置成 0 时；所以，为了安全和性能两个方面考虑，建议将其设置成 2， 一般 MySQL 重启，而部署 MySQL 的服务器不会重启。

其次是 binlog，与 redo log 相同的是 binlog 也有三种保存数据的状态，同样为了安全和性能两个方面考虑，我建议你将 sync_binlog 设置成 N。

在日常的生产环境之中，一般会将 sync_binlog 设置为：100 ~ 1000 之间，具体要看服务器性能，如果服务器的内存有空余，可以将其按需调大。

作者：Mche

链接：https://juejin.cn/post/7188423924894400569

来源：稀土掘金