51. 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 10: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 工具，介绍了逻辑恢复的两种恢复方式：完全恢复、不完全恢复。

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

以上关于51. MySQL 的逻辑恢复的文章就介绍到这了，更多相关内容请搜索码云笔记以前的文章或继续浏览下面的相关文章，希望大家以后多多支持码云笔记。