MySQL 的逻辑恢复

逻辑恢复通常支持两种恢复方式：完全恢复、不完全恢复，本小节结合 mysqldump 和 mysqlbinlog 工具，介绍这两种恢复方式的操作步骤。

1. 完全恢复

MySQL 中，逻辑备份的完全恢复相对比较简单，一般包含两个步骤：

• 恢复最新的全备文件

shell> mysql -uroot -p < backup.sql

• 恢复日志文件

shell> mysqlbinlog binlog-file | mysql -uroot -p

实际案例：完整的mysqldump备份与恢复

1. 中午12点，备份数据库

[mysql@localhost ~]$ mysqldump --single-transaction -F -uroot -p --databases tempdb > /tmp/db_tempdb.sql
Enter password: 

[mysql@localhost ~]$ ls -lrt db_tempdb.sql 
-rw-r--r-- 1 mysql mysql 19602842 Jul 23 12:01 db_tempdb.sql

参数 --single-transaction 表示给 InnoDB 表生成快照，保持数据一致性

参数 -F 表示生成一个新的日志文件

此时表 customer 的数据如下：

mysql> select * from customer;
+----+-----------+------------+------------+--------+---------+
| id | last_name | first_name | birth_date | gender | balance |
+----+-----------+------------+------------+--------+---------+
|  1 | 111       | 111        | 1998-01-25 | 1      |      10 |
|  2 | 222       | 222        | 2020-07-15 | 1      |      20 |
+----+-----------+------------+------------+--------+---------+
2 rows in set (0.00 sec)

2. 13点，表 customer 插入新的数据：

mysql> insert into customer(id,last_name,first_name,birth_date,gender,balance) values(3,333,333,'2020-08-10',1,30);
Query OK, 1 row affected (0.00 sec)

mysql> insert into customer(id,last_name,first_name,birth_date,gender,balance) values(4,444,444,'2020-09-10',1,40);
Query OK, 1 row affected (0.00 sec)

3. 14点，数据库故障，需要恢复数据：

[mysql@localhost ~]$ mysql -uroot -p tempdb < /tmp/db_tempdb.sql
Enter password: 

恢复后表 customer 的数据如下：

mysql> select * from customer;
+----+-----------+------------+------------+--------+---------+
| id | last_name | first_name | birth_date | gender | balance |
+----+-----------+------------+------------+--------+---------+
|  1 | 111       | 111        | 1998-01-25 | 1      |      10 |
|  2 | 222       | 222        | 2020-07-15 | 1      |      20 |
+----+-----------+------------+------------+--------+---------+
2 rows in set (0.00 sec)

4. 从 binlog 日志恢复 12 点备份以来的数据（mysql-bin.000021 为 12 点备份后产生的新的 binlog 日志）：

[mysql@localhost ~]$ mysqlbinlog mysql-bin.000021 | mysql -uroot -p tempdb
Enter password: 

恢复日志后，表 customer 的数据如下：

mysql> select * from customer;
+----+-----------+------------+------------+--------+---------+
| id | last_name | first_name | birth_date | gender | balance |
+----+-----------+------------+------------+--------+---------+
|  1 | 111       | 111        | 1998-01-25 | 1      |      10 |
|  2 | 222       | 222        | 2020-07-15 | 1      |      20 |
|  3 | 333       | 333        | 2020-08-10 | 1      |      30 |
|  4 | 444       | 444        | 2020-09-10 | 1      |      40 |
+----+-----------+------------+------------+--------+---------+
4 rows in set (0.00 sec)

表 customer 的数据全部恢复。

2. 不完全恢复

MySQL 中，不完全恢复分为基于时间点的恢复和基于位置的恢复。一般来说，不完全恢复需要跳过一些语句，比如说，由于误操作删除了一张表，这时用完全恢复是没有用的，因为 binlog 日志还存在误操作的语句，我们需要跳过误操作语句，在恢复后面的语句，来完成恢复。

2.1 基于时间点恢复

以下是基于时间点恢复的操作步骤：

1. 13点，运维人员误删除表 customer，可以用备份和 binlog 日志恢复到故障前（中午 12 点，备份数据库）

[mysql@localhost ~]$ mysql -uroot -p tempdb < /tmp/db_tempdb.sql
Enter password: 

[mysql@localhost ~]$ mysqlbinlog --stop-datetime="2020-07-23 11:59:59" mysql-bin.000021 | mysql -uroot -p tempdb
Enter password:

2. 跳过故障时间点，继续使用后面的binlog日志完成恢复。

[mysql@localhost ~]$ mysqlbinlog --start-datetime="2020-07-23 12:01:00" mysql-bin.000021 | mysql -uroot -p tempdb
Enter password:

基于时间的恢复，稍显粗糙，因为同一时间点可能会有很多条 sql 在执行，那就会跳过一些正常执行的sql。一般我们会考虑使用更为精确的基于位置的恢复。

2.2 基于位置恢复

以下是基于位置恢复的操作步骤：

1. 执行如下 mysqlbinlog 命令：

[mysql@localhost ~]$ mysqlbinlog --start-datetime="2020-07-23 11:55:00" --stop-datetime="2020-07-23 12:05:00" mysql-bin.000021 > /tmp/temp_restore.sql

从 temp_restore.sql 找到误操作语句前后的位置号为 383 和 437：

2. 恢复备份文件，使用 binlog 日志跳过故障语句的位置号，完成恢复。

[mysql@localhost ~]$ mysql -uroot -p tempdb < /tmp/db_tempdb.sql
Enter password: 

[mysql@localhost ~]$ mysqlbinlog --stop-position="383" mysql-bin.000021 | mysql -uroot -p tempdb
Enter password:

[mysql@localhost ~]$ mysqlbinlog --start-position="437" mysql-bin.000021 | mysql -uroot -p tempdb
Enter password:

3. 小结

本小节通过 mysqldump 和 mysqlbinlog 工具，介绍了逻辑恢复的两种恢复方式：完全恢复、不完全恢复。

• 逻辑备份的完全恢复相对简单，恢复最新的全备文件和日志文件即可；
• 不完全恢复分为基于时间点的恢复和基于位置的恢复，通常适用于人为误操作的恢复场景。