MySQL 的邏輯恢復(fù)

邏輯恢復(fù)通常支持兩種恢復(fù)方式：完全恢復(fù)、不完全恢復(fù)，本小節(jié)結(jié)合 mysqldump 和 mysqlbinlog 工具，介紹這兩種恢復(fù)方式的操作步驟。

1. 完全恢復(fù)

MySQL 中，邏輯備份的完全恢復(fù)相對(duì)比較簡(jiǎn)單，一般包含兩個(gè)步驟：

• 恢復(fù)最新的全備文件

shell> mysql -uroot -p < backup.sql

• 恢復(fù)日志文件

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

實(shí)際案例：完整的mysqldump備份與恢復(fù)

1. 中午12點(diǎn)，備份數(shù)據(jù)庫(kù)

[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

參數(shù) --single-transaction 表示給 InnoDB 表生成快照，保持?jǐn)?shù)據(jù)一致性

參數(shù) -F 表示生成一個(gè)新的日志文件

此時(shí)表 customer 的數(shù)據(jù)如下：

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點(diǎn)，表 customer 插入新的數(shù)據(jù)：

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點(diǎn)，數(shù)據(jù)庫(kù)故障，需要恢復(fù)數(shù)據(jù)：

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

恢復(fù)后表 customer 的數(shù)據(jù)如下：

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 日志恢復(fù) 12 點(diǎn)備份以來(lái)的數(shù)據(jù)（mysql-bin.000021 為 12 點(diǎn)備份后產(chǎn)生的新的 binlog 日志）：

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

恢復(fù)日志后，表 customer 的數(shù)據(jù)如下：

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 的數(shù)據(jù)全部恢復(fù)。

2. 不完全恢復(fù)

MySQL 中，不完全恢復(fù)分為基于時(shí)間點(diǎn)的恢復(fù)和基于位置的恢復(fù)。一般來(lái)說(shuō)，不完全恢復(fù)需要跳過(guò)一些語(yǔ)句，比如說(shuō)，由于誤操作刪除了一張表，這時(shí)用完全恢復(fù)是沒(méi)有用的，因?yàn)?binlog 日志還存在誤操作的語(yǔ)句，我們需要跳過(guò)誤操作語(yǔ)句，在恢復(fù)后面的語(yǔ)句，來(lái)完成恢復(fù)。

2.1 基于時(shí)間點(diǎn)恢復(fù)

以下是基于時(shí)間點(diǎn)恢復(fù)的操作步驟：

1. 13點(diǎn)，運(yùn)維人員誤刪除表 customer，可以用備份和 binlog 日志恢復(fù)到故障前（中午 12 點(diǎn)，備份數(shù)據(jù)庫(kù)）

[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. 跳過(guò)故障時(shí)間點(diǎn)，繼續(xù)使用后面的binlog日志完成恢復(fù)。

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

基于時(shí)間的恢復(fù)，稍顯粗糙，因?yàn)橥粫r(shí)間點(diǎn)可能會(huì)有很多條 sql 在執(zhí)行，那就會(huì)跳過(guò)一些正常執(zhí)行的sql。一般我們會(huì)考慮使用更為精確的基于位置的恢復(fù)。

2.2 基于位置恢復(fù)

以下是基于位置恢復(fù)的操作步驟：

1. 執(zhí)行如下 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 找到誤操作語(yǔ)句前后的位置號(hào)為 383 和 437：

2. 恢復(fù)備份文件，使用 binlog 日志跳過(guò)故障語(yǔ)句的位置號(hào)，完成恢復(fù)。

[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. 小結(jié)

本小節(jié)通過(guò) mysqldump 和 mysqlbinlog 工具，介紹了邏輯恢復(fù)的兩種恢復(fù)方式：完全恢復(fù)、不完全恢復(fù)。

• 邏輯備份的完全恢復(fù)相對(duì)簡(jiǎn)單，恢復(fù)最新的全備文件和日志文件即可；
• 不完全恢復(fù)分為基于時(shí)間點(diǎn)的恢復(fù)和基于位置的恢復(fù)，通常適用于人為誤操作的恢復(fù)場(chǎng)景。