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

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