MySQL：如何将“删库跑路”的损失降到最低

最新推荐文章于 2022-11-20 14:38:59 发布

OceanStar的学习笔记

最新推荐文章于 2022-11-20 14:38:59 发布

阅读量190

点赞数

分类专栏：数据库文章标签： mysql 数据库 database

原文链接：https://time.geekbang.org/

版权

数据库专栏收录该内容

289 篇文章 20 订阅

订阅专栏

一般来讲，存储系统导致的比较严重的损失主要有两种情况，一是数据丢失造成的直接财产损失，比如大量的坏账；二是由于存储系统损坏，造成整个业务系统停止服务而带来的损失。

那么如果预防，将“删库跑路”等这类问题导致的损失降到最低呢？

如何更安全的做数据备份和恢复

保证数据安全，最简单而有效的手段是定期备份数据，这样出现任何问题导致的数据损失，都可以通过备份来恢复数据。但是如果备份，才能最大程度的保证数据安全呢？

在MySQL中，最简单的方式是全量备份（使用mysqldump）。备份的时候，把所有的数据复制一份，存放在文件中，恢复的时候再把文件中的数据复制回去，这样可以保证恢复之后数据库中的数据和备份是完全一样的。

比如我们要全量备份数据库 test：

$mysqldump -uroot -p test > test.sql

备份出来的文件就是一个 SQL 文件，就是创建数据库、表，写入数据等等这些 SQL，如果要恢复数据，直接执行这个备份的 SQL 文件就可以了：

$mysql -uroot test < test.sql

不过，全量备份的代价非常高：

首先，备份文件包含数据库中的所有数据，占用的磁盘空间非常大
其次，每次备份操作都要拷贝大量数据，备份过程中会占用数据库服务器大量的CPU、磁盘IO资源，并且为了保证数据的一致性，还有可能会锁表，这些都会导致备份期间，数据库本身的性能严重下降。

所以，我们不能经常对数据库执行全量备份。一般来说，每天执行一次全量备份已经是非常频率了。这就意味着，如果数据库中的数据丢了，那只能恢复到最近一次全量备份的那个时间点，这个时间段之后的数据还是丢了。也就是说，全量备份不能做到完全无损的恢复。

既然全量备份代价太刀，不能频繁执行，那有没有代价低一点儿的备份方法，能让我们少丢甚至不丢数据呢？有，那就是增量备份。相比于全量备份，增量备份每次只备份相对于上一次备份变化的那部分数据，所以每次增量备份速度更快。

MySQL自带了binlog，就是一种实时的增量备份。Binlog里面记录的就是MySQL数据的变更的操作日志，开启binlog之后，我们对MySQL中的每次更新数据操作，都会被记录到binlog中。

binlog是可以回放的，回放binlog，就相当于把之前对数据库所有数据更新操作按照顺序重新执行了以便，回放之后数据自然就恢复了。这就是binlog增量备份的基本原理。

下面通过一个例子看一下如何使用 binlog进行备份和恢复。首先使用“show variables
like ‘%log_bin%’”命令确认一下是否开启了 binlog功能：


mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------+
| Variable_name                   | Value                             |
+---------------------------------+-----------------------------------+
| log_bin                         | ON                                |
| log_bin_basename                | /usr/local/var/mysql/binlog       |
+---------------------------------+-----------------------------------+
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |    18745 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

可以看到当前这个数据库已经开启了 binlog，log_bin_basename 表示 binlog文件在服务器磁盘上的具体位置。然后用“show master status”命令可查看当前 binlog的状态，显示正在写入的 binlog文件，及当前的位置。假设我们每天凌晨用 mysqldump 做一个全量备份，然后开启了 binlog，有了这些，我们就可以把数据恢复到全量备份之后的任何一个时刻。

下面我们做一个简单的备份恢复演示。我们先模拟一次“删库跑路”的场景，直接把账户余额表清空：

mysql> truncate table account_balance;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from account_balance;
Empty set (0.00 sec)

然后我们来进行数据恢复，首先执行一次全量恢复，把数据库恢复到今天凌晨的状态。

$mysql -uroot test < dump.sql
mysql> select * from account_balance;
+---------+---------+---------------------+--------+
| user_id | balance | timestamp | log_id |
+---------+---------+---------------------+--------+
| 0 | 100 | 2020-02-13 20:24:33 | 3 |
+---------+---------+---------------------+--------+

可以看到，表里面的数据已经恢复了，但还是比较旧的数据。然后我们再用 Binlog 把数据恢复到删库跑路之前的那个时刻：


$mysqlbinlog --start-datetime "2020-02-20 00:00:00" --stop-datetime "2020-02-20 15:09:00" /usr/local/var/mysql/binlog.000001 | mysql -uroot


mysql> select * from  account_balance;
+---------+---------+---------------------+--------+
| user_id | balance | timestamp           | log_id |
+---------+---------+---------------------+--------+
|       0 |     200 | 2020-02-20 15:08:12 |      0 |
+---------+---------+---------------------+--------+

这时候，数据已经恢复到当天的 15 点了。通过定期的全量备份，配合 Binlog，我们就可以把数据恢复到任意一个时间点，再也不怕程序员删库跑路了。

在执行备份和恢复的时候，有几个要点你需要特别的注意。

第一，也是最重要的，“不要把所有的鸡蛋放在同一个篮子中”，无论是全量备份还是binlog，都不要和数据库存放在同一个服务器上。最好能做到不同机房，甚至不同城市，离得越远越好。这样即使出现机房着火、光缆被挖断甚至地震也不怕。

第二，在回放 binlog的时候，指定的起始时间可以比全量备份的时间稍微提前一点儿，确保全量备份之后的所有操作都在恢复的 binlog范围内，这样可以保证恢复的数据的完整性。

因为回放binlog的操作是幂等的，多次操作和一次操作对系统的影响是一样的，所以重复回放的那部分binlog不会影响数据的准确性

配置MySQL HA实现高可用

通过全量备份加上binlog，我们可以将数据库恢复到任何一个时间点，这样至少不会丢数据了。如果说，数据库服务器宕机了，因为我们有备份数据，完全可以启用一个新的数据库服务器，把备份数据恢复到新的数据库上，这样新的数据库就可以替代宕机的数据库，继续提供服务。

但是，这个恢复数据的时间是比较长的，如果数据量比较大的话，有可能需要恢复几个小时，这几个小时，我们的系统是一直不可用的，这样肯定不行。

这个问题怎么解决？很简单，不要等到数据库宕机了才开始恢复，我们完全可以提前来做恢复这些事。

我们准备一台备用的数据库，把它的数据恢复成主库一样，然后实时的在主备数据库之间来同步斌咯个，主库做了一次数据变更，生成一条binlog，我们就把这条binlog复制到备用库并立即回放，这样就可以让备用库里面的数据和主库中的数据一直保持是一样的。一旦主库宕机，就可以立即恢复到备用库上继续提供服务。这就是MySQL的高可用方案，也叫做MySQL HA。

MySQL自身就提供了主从复制的功能，通过配置就可以让一主一备两台MySQL的数据库保持数据同步。

接下来我们说这个方案的问题。当我们对主库进行一次更新操作时，主从两个数据库更新数据实际的时序是这样的：

在主库的磁盘上写入binlog
主库更新存储引擎中的数据
给客户端返回成功响应
主库把binlog复制到从库
从库回放binlog，更新存储引擎中的数据

也就是说，从库的数据是可能比主库上的数据旧一点，这个主从之间复制数据的延迟，称为“主从延迟”。正常情况下，主从延迟基本都是毫秒级别，你可以认为主从就是实时保持同步的。不正常情况下，比如主库或者从库繁忙的时候，就可能出现明显的主从延迟。

而很多情况下，数据库都不是突然宕机的，而是先繁忙，性能下降，最终宕机。这种情况下，很有可能主从延迟很大，如果我们把业务直接切到从库上继续读写，主从延迟这部分数据就丢失了，并且这个数据丢失是不可逆的。即使事后你找回了当时从库的binlog也是无法做到自动恢复的，因为它和从库的数据是冲突的。

简单来说，如果主库宕机并主从存在延迟的情况下，切换到从库继续读写，可以保证业务的可用性，但是主从延迟这部分数据就丢失了。

这个时候就要做一个选择题了：

A：保证不丢数据，牺牲可用性，暂时停止服务，想办法把binlog恢复到从库上再提供服务
B：冒着丢一些数据的风险，保证可用性，第一时间切换到从库继续提供服务。

那能不能即保证数据不丢，还能做到高可用呢？也是可以的，代价是牺牲一些性能。MySQL也支持同步复制，开启同步复制之后，MySQL主库会等待是数据成功复制到从库之后，再给客户端响应。

如果说，牺牲的这点儿性能我不在乎，这个方案是不是就完美了呢？也不是，新的问题又来了！这种情况下从库宕机了怎么办？本来从库宕机对主库是没有影响的，因为现在主库要等待从库写入成功之后再返回，从库宕机，主库就会一直等待从库，主库也卡死了。

这个问题也有解决方法，那就是再增加一个从库，把主库配置成：成功复制到任意一个从库就返回，只要有一个从库还活着，就不会影响主库写入数据，这就解决了从库宕机阻塞租库的问题。如果主库发生宕机，在两个从库中，至少有一个从库中的数据是和主库完全一样的，可以把这个库作为新的主库，继续提供服务。为此你需要付出的代价是，你要至少用三台数据库服务器，并且这三台服务器提供的服务性能，还不如一台服务器高。

上面这三种典型的 HA 方案总结如下面表格：
在这里插入图片描述