誤刪 GreatSQL 數(shù)據(jù)？別慌，Binlog來幫忙！

作者：KAiTO 2024-10-14 13:22:15

在管理 GreatSQL 數(shù)據(jù)庫(kù)時(shí)，雖然有 Binlog 等強(qiáng)大的工具來幫助恢復(fù)誤刪的數(shù)據(jù)，但最好的辦法還是預(yù)防。在進(jìn)行任何數(shù)據(jù)操作時(shí)，應(yīng)該保持高度的謹(jǐn)慎，充分備份數(shù)據(jù)，嚴(yán)格遵循操作規(guī)范。

數(shù)據(jù)丟失是每一個(gè)數(shù)據(jù)庫(kù)管理員和開發(fā)者都不愿面對(duì)的噩夢(mèng)。然而，意外總是難免，當(dāng)不小心刪除了重要的數(shù)據(jù)，如何才能迅速而有效地進(jìn)行恢復(fù)呢？在數(shù)據(jù)庫(kù)中有二進(jìn)制日志 (Binlog)，它不僅記錄了所有更改數(shù)據(jù)的事件，還可以幫助將數(shù)據(jù)庫(kù)恢復(fù)到任何一個(gè)特定的時(shí)間點(diǎn)。本篇文章將帶您深入了解如何利用 Binlog 來應(yīng)對(duì)數(shù)據(jù)丟失問題，在面對(duì)數(shù)據(jù)誤刪時(shí)不再慌張。

啟用 Binlog

Binlog (二進(jìn)制日志)的介紹在這里就不過多描述了，不了解 Binlog 的同學(xué)，可以前往GreatSQL用戶手冊(cè)中 GreatSQL 日志章節(jié)查看：（https://greatsql.cn/docs/8.0.32-26/2-about-greatsql/4-3-greatsql-binary-log.html）。

為了利用 Binlog (二進(jìn)制日志) 進(jìn)行數(shù)據(jù)恢復(fù)，首先需要確保 Binlog 已經(jīng)在 GreatSQL 數(shù)據(jù)庫(kù)中啟用并正確配置。以下是詳細(xì)的配置步驟、狀態(tài)檢查方法及 Binlog 文件的存儲(chǔ)位置和命名規(guī)則。

配置 Binlog 的步驟

找到并編輯 GreatSQL 配置文件my.cnf。該文件的路徑因系統(tǒng)和安裝方式不同而有所不同，常見路徑包括 /etc/my.cnf、/etc/mysql/my.cnf。

添加或修改以下配置項(xiàng)：

[mysqld]
log-bin=binlog
binlog-format=ROW
server-id=103306

log-bin：指定啟用 Binlog，并設(shè)置 Binlog 文件的基本名稱。這里使用 binlog 作為前綴。
binlog-format：設(shè)置 Binlog 的記錄格式。推薦使用 ROW 格式，因?yàn)樗涗浀氖切屑?jí)別的變更，更詳細(xì)和準(zhǔn)確。
server-id：為服務(wù)器設(shè)置唯一的 ID，必須設(shè)置該選項(xiàng)才能啟用 Binlog。對(duì)于單個(gè)服務(wù)器，任何正整數(shù)都可以。對(duì)于主從復(fù)制環(huán)境，確保每個(gè)服務(wù)器的 server-id 唯一。

檢查 Binlog 狀態(tài)

配置完 Binlog 并重啟 GreatSQL 服務(wù)后，可以通過以下方法檢查 Binlog 是否已正確啟用；

登錄 GreatSQL 并執(zhí)行以下命令；

檢查 Binlog 啟用狀態(tài)

greatsql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

如果返回結(jié)果為 ON，表示 binlog 已啟用。

檢查 Binlog 格式

greatsql> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

確保 Binlog 格式為 ROW；

查看 Binlog 文件列表

greatsql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000037 |      2347 | No        |
| binlog.000038 |       220 | No        |
| binlog.000039 |       703 | No        |
| binlog.000040 | 428473707 | No        |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)

該命令將列出所有當(dāng)前存在的 Binlog 文件及其大小；

Binlog 文件的存儲(chǔ)位置和命名規(guī)則

存儲(chǔ)位置：

Binlog 文件的存儲(chǔ)位置通常由 log-bin 選項(xiàng)的值和 GreatSQL 數(shù)據(jù)目錄共同決定。如果在 my.cnf 中未指定路徑，binlog 文件會(huì)存儲(chǔ)在 GreatSQL 數(shù)據(jù)目錄下。

可以通過以下命令查看 GreatSQL 數(shù)據(jù)目錄：

greatsql> SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /data/GreatSQL/ |
+---------------+-----------------+
1 row in set (0.00 sec)

如果 log-bin 選項(xiàng)指定了路徑，則 binlog 文件存儲(chǔ)在該路徑下。例如：

log-bin=/data/GreatSQL/binlog

命名規(guī)則：

Binlog 文件名由 log-bin 選項(xiàng)的值和一個(gè)數(shù)字序列組成。例如，如果 log-bin 設(shè)置為 binlog，則生成的 binlog 文件名類似于 binlogn.000001、binlog.000002 等。

序列號(hào)是自動(dòng)遞增的，當(dāng)一個(gè) Binlog 文件達(dá)到最大大小（由 max_binlog_size 變量控制）時(shí)，GreatSQL 會(huì)創(chuàng)建一個(gè)新的 Binlog 文件，并將序列號(hào)遞增。

模擬數(shù)據(jù)誤刪場(chǎng)景

此次測(cè)試環(huán)境情況如下：

數(shù)據(jù)庫(kù)：GreatSQL 8.0.32-26
操作系統(tǒng)：Linux myarch 6.6.3-arch1-1 x86_64 GNU/Linux

創(chuàng)建測(cè)試數(shù)據(jù)

創(chuàng)建一個(gè)testdb庫(kù)和employees表

greatsql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.01 sec)

greatsql> USE testdb;
Database changed

greatsql> CREATE TABLE employees (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(100),
    ->     position VARCHAR(100),
    ->     salary DECIMAL(10, 2)
    -> );
Query OK, 0 rows affected (0.07 sec)

并插入幾條示例數(shù)據(jù)

greatsql> INSERT INTO employees (name, position, salary) VALUES 
('Alice', 'Manager', 60000.00),
('Bob', 'Developer', 50000.00),
('Charlie', 'Analyst', 40000.00),
('greatsql', 'DBA', 66666.00);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

確認(rèn)插入成功

greatsql> SELECT * FROM employees;
+----+----------+-----------+----------+
| id | name     | position  | salary   |
+----+----------+-----------+----------+
|  1 | Alice    | Manager   | 60000.00 |
|  2 | Bob      | Developer | 50000.00 |
|  3 | Charlie  | Analyst   | 40000.00 |
|  4 | greatsql | DBA       | 66666.00 |
+----+----------+-----------+----------+
4 rows in set (0.00 sec)

模擬數(shù)據(jù)誤刪除

這時(shí)候要?jiǎng)h除一條id=2的字段，而你卻不小心刪除了id=1的字段；

greatsql> DELETE FROM employees WHERE name = 'Alice';

查看表確認(rèn)被誤刪除了；

greatsql> SELECT * FROM employees;
+----+----------+-----------+----------+
| id | name     | position  | salary   |
+----+----------+-----------+----------+
|  2 | Bob      | Developer | 50000.00 |
|  3 | Charlie  | Analyst   | 40000.00 |
|  4 | greatsql | DBA       | 66666.00 |
+----+----------+-----------+----------+
3 rows in set (0.00 sec)

恢復(fù)數(shù)據(jù)的步驟

在數(shù)據(jù)誤刪后，恢復(fù)數(shù)據(jù)的關(guān)鍵在于使用 GreatSQL 的二進(jìn)制日志 (Binlog)。二進(jìn)制日志記錄了所有對(duì)數(shù)據(jù)庫(kù)進(jìn)行更改的操作，包括插入、更新和刪除。因此，通過解析和重放 Binlog，可以恢復(fù)誤刪的數(shù)據(jù)。

確定誤刪的時(shí)間點(diǎn)

記錄下 GreatSQL 服務(wù)器的時(shí)間

greatsql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-06-07 14:04:23 |
+---------------------+
1 row in set (0.00 sec)

記錄下當(dāng)前時(shí)間，以確定誤刪操作發(fā)生的大致時(shí)間范圍。

查找相應(yīng)的 Binlog 文件

GreatSQL 的 Binlog 文件按時(shí)間順序記錄了所有對(duì)數(shù)據(jù)庫(kù)的更改。你需要找到包含誤刪操作的 Binlog 文件。

greatsql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000037 |      2347 | No        |
| binlog.000038 |       220 | No        |
| binlog.000039 |       703 | No        |
| binlog.000040 | 428477097 | No        |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)

根據(jù)誤刪操作的大致時(shí)間，確定可能包含誤刪操作的 Binlog 文件。例如，如果誤刪操作發(fā)生在最近，可能需要檢查 binlog.000040。

或使用SHOW MASTER STATUS命令確認(rèn)當(dāng)前正在使用的 Binlog；

greatsql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
             File: binlog.000040
         Position: 428477097
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 9548406d-8ff1-11ee-97ec-ec5c6826bca3:1-9775
1 row in set (0.00 sec)

使用 mysqlBinlog 工具讀取 Binlog

使用 mysqlbinlog 工具可以讀取并解析 binlog 文件，以查找并提取相關(guān)的 SQL 語(yǔ)句。

$ mysqlbinlog --base64-output=decode-rows -v --start-datetime="2024-06-07 00:00:00" --stop-datetime="2024-06-07 23:59:59" /data/GreatSQL/binlog.000040 > /tmp/binlog.sql

--base64-output=decode-rows：對(duì)二進(jìn)制日志文件中的事件進(jìn)行更詳細(xì)的解碼和輸出；
--verbose：會(huì)輸出更詳細(xì)的日志信息（簡(jiǎn)寫 -v）；

這條命令將從 binlog.000040 文件中提取指定時(shí)間范圍內(nèi)的日志，并將其保存到 binlog.sql 文件中。

如果沒辦法確定誤操作的具體時(shí)間，可以把 Binlog 全部提取。

打開 binlog.sql 文件，查找并確認(rèn)包含誤刪操作的 SQL 語(yǔ)句。

$ vim /tmp/binlog.sql
# at 428476791
#240607 13:55:21 server id 103306  end_log_pos 428476868 CRC32 0x78030015       Query   thread_id=26    exec_time=0     error_code=0
SET TIMESTAMP=1717739721/*!*/;
BEGIN
/*!*/;
# at 428476868
# at 428476934
#240607 13:55:21 server id 103306  end_log_pos 428477005 CRC32 0xf2c276a8       Table_map: `testdb`.`employees` mapped to number 133
# has_generated_invisible_primary_key=0
# at 428477005
#240607 13:55:21 server id 103306  end_log_pos 428477066 CRC32 0x3eea7695       Delete_rows: table id 133 flags: STMT_END_F
### DELETE FROM `testdb`.`employees`
### WHERE
###   @1=1
###   @2='Alice'
###   @3='Manager'
###   @4=60000.00
# at 428477066
#240607 13:55:21 server id 103306  end_log_pos 428477097 CRC32 0xa495408d       Xid = 10249
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file

生成恢復(fù)數(shù)據(jù)的 SQL 語(yǔ)句

可以手工將查到的誤刪除數(shù)據(jù)轉(zhuǎn)換為INSTER 語(yǔ)句重新插入回 GreatSQL 數(shù)據(jù)庫(kù)。

或用以下命令，將解析后的 SQL 文件中的 DELETE 語(yǔ)句全部轉(zhuǎn)換為 INSTER 語(yǔ)句。

$ cat /tmp/binlog.sql | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/;INSERT INTO/g;s/WHERE/VALUE(/g;' | sed 's/@1=//g'| sed 's/@[1-9]=/,/g' | sed 's/@[1-9][0-9]=/,/g' | sed 's/ \+/ /g'|sed ":a;N;s/\,\n ,/\n,/g;s/\,\n;/);/g;ta"|sed  '0,/;/s/;//'|sed '$ s/.$/);/'  > binlog_insert.sql

查看轉(zhuǎn)換后的 SQL 語(yǔ)句結(jié)果；

$ cat /tmp/binlog_insert.sql
INSERT INTO `testdb`.`employees`
VALUE(
 1
 ,'Alice'
 ,'Manager'
 ,60000.0);

此時(shí)將該 SQL 語(yǔ)句在 GreatSQL 中執(zhí)行即可恢復(fù)被誤刪除的數(shù)據(jù)。

高級(jí)恢復(fù)技巧

通過 Pos 恢復(fù)

如果不小心將所有數(shù)據(jù)都刪除了，那就可以使用重放 Binlog 讓數(shù)據(jù)全部回來。

例如本想刪除ID=1的數(shù)據(jù)，結(jié)果忘記加WHERE語(yǔ)句。

greatsql> DELETE FROM testdb.employees;
Query OK, 3 rows affected (0.05 sec)

greatsql> SELECT * FROM testdb.employees;
Empty set (0.00 sec)

導(dǎo)致了testdb.employees表數(shù)據(jù)都沒了，但是沒關(guān)系。需要找到 Binlog 中employees表的建表語(yǔ)句，從建表開始重放 Binlog 到刪除語(yǔ)句為止。

同樣需要解析 Binlog；

$ mysqlbinlog --base64-output=decode-rows -v --start-datetime="2024-06-07 00:00:00" --stop-datetime="2024-06-07 23:59:59" /data/GreatSQL/binlog.000040 > /tmp/binlog.sql

找到employees建表語(yǔ)句；

$ cat /tmp/binlog.sql
# at 428474721
#240607 13:46:42 server id 103306  end_log_pos 428474954 CRC32 0x2ce790d3       Query   thread_id=26    exec_time=0     error_code=0    Xid = 10238
use `testdb`/*!*/;
SET TIMESTAMP=1717739202/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100),
    salary DECIMAL(10, 2)
)
/*!*/;

可以看到該建表語(yǔ)句的開始 Pos 在428474954 結(jié)束，刪除語(yǔ)句是在428476791開始。確定了起止 Pos 直接重放 Binlog 即可。

$ mysqlbinlog --skip-gtids --start-position=428474954 --stop-position=428476791 --database=testdb /data/GreatSQL/binlog.000040 | mysql -uroot -p

因?yàn)?GreatSQL 啟用了 GTID 模式（@@GLOBAL.GTID_MODE = ON），而 mysqlbinlog 工具默認(rèn)嘗試將 @@SESSION.GTID_NEXT 設(shè)置為 ANONYMOUS，這與啟用 GTID 模式的服務(wù)器不兼容，所以加上--skip-gtids，在下方 GTID 恢復(fù)也解釋為什么需要添加這個(gè)參數(shù)。

因?yàn)楸頉]有被刪除，所以不用重放建表語(yǔ)句。若表也不存在了，可以從建表語(yǔ)句開始重放。

再次查看數(shù)據(jù)，可以看到數(shù)據(jù)都回來了

greatsql> SELECT * FROM employees;
+----+----------+-----------+----------+
| id | name     | position  | salary   |
+----+----------+-----------+----------+
|  1 | Alice    | Manager   | 60000.00 |
|  2 | Bob      | Developer | 50000.00 |
|  3 | Charlie  | Analyst   | 40000.00 |
|  4 | greatsql | DBA       | 66666.00 |
+----+----------+-----------+----------+
4 rows in set (0.00 sec)

以上展示的是根據(jù)指定位置恢復(fù)數(shù)據(jù)。

通過 GTID 恢復(fù)

查看 GTID 開啟前的設(shè)定；

greatsql> SHOW VARIABLES LIKE '%gtid%';
+--------------------------------------------------+---------------------------------------------+
| Variable_name                                    | Value                                       |
+--------------------------------------------------+---------------------------------------------+
| binlog_gtid_simple_recovery                      | ON                                          |
| enforce_gtid_consistency                         | ON                                          |
| group_replication_broadcast_gtid_executed_period | 1000                                        |
| group_replication_gtid_assignment_block_size     | 1000000                                     |
| gtid_executed                                    | 9548406d-8ff1-11ee-97ec-ec5c6826bca3:1-9813 |
| gtid_executed_compression_period                 | 0                                           |
| gtid_mode                                        | ON                                          |
| gtid_next                                        | AUTOMATIC                                   |
| gtid_owned                                       |                                             |
| gtid_purged                                      | 9548406d-8ff1-11ee-97ec-ec5c6826bca3:1-9755 |
| secondary_engine_read_delay_gtid_threshold       | 100                                         |
| session_track_gtids                              | OFF                                         |
+--------------------------------------------------+---------------------------------------------+
12 rows in set (0.00 sec)

主要是 gtid_mode 與 enforce_gtid_consistency 需要開啟。

gtid_mode：控制 GTID 的開啟和關(guān)閉。
enforce_gtid_consistency：確保 GTID（全局事務(wù)標(biāo)識(shí)符）的一致性。

同時(shí)開啟后，也能看到 gtid_next=AUTOMATIC 可知 GTID 自動(dòng)遞增。

在 GreatSQL 中，mysql.gtid_executed 表用于記錄已執(zhí)行的 GTID（全局事務(wù)標(biāo)識(shí)符）。這是 GTID 復(fù)制機(jī)制中的一個(gè)關(guān)鍵部分，用于跟蹤服務(wù)器上已經(jīng)執(zhí)行的所有 GTID，以確保數(shù)據(jù)一致性和事務(wù)的順利復(fù)制。

greatsql> DESC mysql.gtid_executed;
+----------------+----------+------+-----+---------+-------+
| Field          | Type     | Null | Key | Default | Extra |
+----------------+----------+------+-----+---------+-------+
| source_uuid    | char(36) | NO   | PRI | NULL    |       |
| interval_start | bigint   | NO   | PRI | NULL    |       |
| interval_end   | bigint   | NO   |     | NULL    |       |
+----------------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

source_uuid：表示 GTID 的源服務(wù)器 UUID。這是生成 GTID 的服務(wù)器的唯一標(biāo)識(shí)符。
interval_start：GTID 范圍的起始值。它表示該事務(wù)范圍的起始 GTID。
interval_end：GTID 范圍的結(jié)束值。它表示該事務(wù)范圍的結(jié)束 GTID。

一個(gè) DDL 語(yǔ)句會(huì)產(chǎn)生一個(gè) GTID ，同樣 DML 一個(gè)事務(wù)也會(huì)產(chǎn)生一個(gè) GTID。

# 目前為 1-9813
greatsql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: binlog.000040
         Position: 428495255
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 9548406d-8ff1-11ee-97ec-ec5c6826bca3:1-9813
1 row in set (0.00 sec)

# 執(zhí)行一個(gè) DDL 語(yǔ)句
greatsql> CREATE TABLE test1 (id int);
Query OK, 0 rows affected (0.03 sec)
# 可以看到 GTID 值增加了
greatsql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: binlog.000040
         Position: 428495580
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 9548406d-8ff1-11ee-97ec-ec5c6826bca3:1-9814
1 row in set (0.00 sec)

同時(shí)也可以查看 Binlog。

greatsql> SHOW BINLOG EVENTS IN 'binlog.000040' FROM 428495255\G
*************************** 1. row ***************************
   Log_name: binlog.000040
        Pos: 428495255
 Event_type: Gtid
  Server_id: 103306
End_log_pos: 428495334
       Info: SET @@SESSION.GTID_NEXT= '9548406d-8ff1-11ee-97ec-ec5c6826bca3:9814'
*************************** 2. row ***************************
   Log_name: binlog.000040
        Pos: 428495334
 Event_type: Query
  Server_id: 103306
End_log_pos: 428495580
       Info: use `testdb`; CREATE TABLE `test1` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) /* xid=10933 */
2 rows in set (0.00 sec)

當(dāng)看到SET @@SESSION.GTID_NEXT=這樣的命令時(shí)，它意味著接下來的事務(wù)（或下一個(gè)將被寫入二進(jìn)制日志（Binlog）的事務(wù)）將被賦予指定的GTID。

啟用GTID后，GreatSQL 在恢復(fù) Binlog 時(shí)會(huì)過濾掉重復(fù)的 GTID 事務(wù)，這意味著相同的GTID語(yǔ)句只會(huì)被執(zhí)行一次。然而，若多個(gè)GTID中存在相同的記錄語(yǔ)句，這些語(yǔ)句將被忽略，可能因?yàn)檎Z(yǔ)句缺失，從而導(dǎo)致數(shù)據(jù)恢復(fù)失敗。因此，在進(jìn)行 Binlog 備份時(shí)，添加 –skip-gtids 參數(shù)，確保忽略GTID的冪等性檢查。

此時(shí)將創(chuàng)建test1表刪除，接著在使用 GTID 恢復(fù)；

greatsql> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| employees        |
| test1            |
+------------------+
2 rows in set (0.00 sec)
greatsql> DROP TABLE test1;
Query OK, 0 rows affected (0.06 sec)

查詢 Binlog 可以看到 GTID 值已經(jīng)到了 9815。

greatsql> SHOW BINLOG EVENTS IN 'binlog.000040' FROM 428495255\G
*************************** 1. row ***************************
   Log_name: binlog.000040
        Pos: 428495255
 Event_type: Gtid
  Server_id: 103306
End_log_pos: 428495334
       Info: SET @@SESSION.GTID_NEXT= '9548406d-8ff1-11ee-97ec-ec5c6826bca3:9814'
*************************** 2. row ***************************
   Log_name: binlog.000040
        Pos: 428495334
 Event_type: Query
  Server_id: 103306
End_log_pos: 428495580
       Info: use `testdb`; CREATE TABLE `test1` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) /* xid=10933 */
*************************** 3. row ***************************
   Log_name: binlog.000040
        Pos: 428495580
 Event_type: Gtid
  Server_id: 103306
End_log_pos: 428495657
       Info: SET @@SESSION.GTID_NEXT= '9548406d-8ff1-11ee-97ec-ec5c6826bca3:9815'
*************************** 4. row ***************************
   Log_name: binlog.000040
        Pos: 428495657
 Event_type: Query
  Server_id: 103306
End_log_pos: 428495791
       Info: use `testdb`; DROP TABLE `test1` /* generated by server */ /* xid=10950 */
4 rows in set (0.00 sec)

因此需要跳過刪除語(yǔ)句，則需要選取 GTID 值小于9815的 GTID 值，既 9814。

$ mysqlbinlog --skip-gtids --include-gtids='9548406d-8ff1-11ee-97ec-ec5c6826bca3:9814' /data/GreatSQL/binlog.000040 | mysql -uroot -p

進(jìn)入 GreatSQL 數(shù)據(jù)庫(kù)查看。

greatsql> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| employees        |
| test1            |
+------------------+
2 rows in set (0.00 sec)

可以看到 test1 表已經(jīng)恢復(fù)了

其它方式恢復(fù)

重放整個(gè) Binlog 恢復(fù)數(shù)據(jù)；

$ mysqlbinlog --database=testdb /data/GreatSQL/binlog.000040 | mysql -uroot -p

根據(jù)指定時(shí)間恢復(fù)數(shù)據(jù)；

mysqlbinlog --start-datetime="2024-06-07 00:00:00" --stop-datetime="2024-06-07 23:59:59" --database=testdb /data/GreatSQL/binlog.000040 | mysql -uroot -p

恢復(fù)總結(jié)

DELETE 語(yǔ)句誤刪除單行/多行數(shù)據(jù)

可以查看 Binlog 日志，把 DELETE 語(yǔ)句轉(zhuǎn)為 INSERT 語(yǔ)句重新插入 GreatSQL 數(shù)據(jù)庫(kù)恢復(fù)

刪表（DROP / DELETE / TRUNCATE）只能重新重放 Binlog 或者是找到備份恢復(fù)
刪庫(kù) 只能重新重放 Binlog 或者是找到備份恢復(fù)
UPDATE 語(yǔ)句誤修改單行/多行數(shù)據(jù) 只能重新重放 Binlog 或者是找到備份恢復(fù)

避免數(shù)據(jù)丟失的最佳實(shí)踐

在管理 GreatSQL 數(shù)據(jù)庫(kù)時(shí)，避免數(shù)據(jù)丟失是至關(guān)重要的任務(wù)。以上演示的都是基于 Binlog 完整保存的情況下，才可以做到完整恢復(fù)，如果 Binlog 有丟失，則沒有辦法恢復(fù)數(shù)據(jù)，所以完整備份數(shù)據(jù)是至關(guān)重要的工作。

定期備份和測(cè)試恢復(fù)

全量備份

定期進(jìn)行全量備份，通常每周一次。全量備份捕獲數(shù)據(jù)庫(kù)的完整狀態(tài)，可以使用 mysqldump 或 xtrabackup 工具。

$ mysqldump -u root -p --all-databases > /backup/alldb_backup.sql

若有需要，也可以定期備份一下 Binlog 日志文件。

使用FLUSH LOGS刷新一下日志，此時(shí)會(huì)啟用一個(gè)新的 Binlog。

greatsql> FLUSH LOGS
Query OK, 0 rows affected (0.03 sec)

greatsql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000040 | 428496353 | No        |
| binlog.000041 |       197 | No        |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)

這時(shí)再把舊的 Binlog 備份下即可。

測(cè)試恢復(fù)

定期測(cè)試恢復(fù)，確保備份文件是可用的，并且可以成功恢復(fù)。每個(gè)月進(jìn)行一次恢復(fù)測(cè)試，確保備份策略的有效性。

避免誤操作

使用 DELETE 語(yǔ)句或 UPDATE 語(yǔ)句，都需要加上WHERE 條件，避免誤刪除/誤修改數(shù)據(jù)。

可以開啟sql_safe_updates參數(shù)。

greatsql> SET GLOBAL sql_safe_updates = ON;
Query OK, 0 rows affected (0.00 sec)

greatsql> SHOW GLOBAL VARIABLES LIKE "sql_safe_updates";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | ON    |
+------------------+-------+
1 row in set (0.01 sec)

此時(shí)重開一個(gè)會(huì)話，使用DELETE 語(yǔ)句沒有加上 WHERE 條件，會(huì)報(bào)錯(cuò)

greatsql> DELETE FROM testdb.employees ;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

使用 UPDATE 語(yǔ)句沒有加上 WHERE 條件，會(huì)報(bào)錯(cuò)

greatsql> UPDATE employees SET salary =1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

責(zé)任編輯：武曉燕來源： GreatSQL社區(qū)