誤刪 GreatSQL 數(shù)據(jù)？別慌，Binlog來幫忙！
數(shù)據(jù)丟失是每一個數(shù)據(jù)庫管理員和開發(fā)者都不愿面對的噩夢。然而，意外總是難免，當(dāng)不小心刪除了重要的數(shù)據(jù)，如何才能迅速而有效地進行恢復(fù)呢？在數(shù)據(jù)庫中有二進制日志 (Binlog)，它不僅記錄了所有更改數(shù)據(jù)的事件，還可以幫助將數(shù)據(jù)庫恢復(fù)到任何一個特定的時間點。本篇文章將帶您深入了解如何利用 Binlog 來應(yīng)對數(shù)據(jù)丟失問題，在面對數(shù)據(jù)誤刪時不再慌張。
啟用 Binlog
Binlog (二進制日志)的介紹在這里就不過多描述了，不了解 Binlog 的同學(xué)，可以前往GreatSQL用戶手冊中 GreatSQL 日志章節(jié)查看：（https://greatsql.cn/docs/8.0.32-26/2-about-greatsql/4-3-greatsql-binary-log.html）。
為了利用 Binlog (二進制日志) 進行數(shù)據(jù)恢復(fù)，首先需要確保 Binlog 已經(jīng)在 GreatSQL 數(shù)據(jù)庫中啟用并正確配置。以下是詳細的配置步驟、狀態(tài)檢查方法及 Binlog 文件的存儲位置和命名規(guī)則。
配置 Binlog 的步驟
找到并編輯 GreatSQL 配置文件my.cnf。該文件的路徑因系統(tǒng)和安裝方式不同而有所不同，常見路徑包括 /etc/my.cnf、/etc/mysql/my.cnf。
添加或修改以下配置項：
[mysqld]
log-bin=binlog
binlog-format=ROW
server-id=103306- log-bin：指定啟用 Binlog，并設(shè)置 Binlog 文件的基本名稱。這里使用 binlog 作為前綴。
- binlog-format：設(shè)置 Binlog 的記錄格式。推薦使用 ROW 格式，因為它記錄的是行級別的變更，更詳細和準(zhǔn)確。
- server-id：為服務(wù)器設(shè)置唯一的 ID，必須設(shè)置該選項才能啟用 Binlog。對于單個服務(wù)器，任何正整數(shù)都可以。對于主從復(fù)制環(huán)境，確保每個服務(wù)器的 server-id 唯一。
推薦 Binlog 配置
但關(guān)于 Binlog 的配置還不止這些，在 GreatSQL 推薦 my.cnf 模板(https://greatsql.cn/docs/8.0.32-26/3-quick-start/3-4-quick-start-with-cnf.html)中還有以下幾個關(guān)于 Binlog 的配置。
$ cat /etc/my.cnf |grep bin
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
#控制binlog總大小，避免磁盤空間被撐爆
binlog_space_limit = 500G
binlog_rows_query_log_events = 1
binlog_expire_logs_seconds = 604800
binlog_checksum = CRC32- sync_binlog = 1配置 GreatSQL 每次提交事務(wù)后都將 binlog 同步到磁盤。確保在系統(tǒng)崩潰時不會丟失已提交的事務(wù)，但可能會略微影響性能。同時配合innodb_flush_log_at_trx_commit=1即所說的雙1，這是最安全的設(shè)置。
- binlog_cache_size = 4M設(shè)置 Binlog 緩存大小為 4MB。當(dāng)事務(wù)中的 SQL 語句較多時，事務(wù)的所有更改會被暫時保存在 Binlog 緩存中，然后一次性寫入 Binlog 文件。
- max_binlog_cache_size = 2G設(shè)置 Binlog 緩存的最大大小為 2GB。這限制了單個事務(wù)可以使用的 Binlog 緩存大小，防止過大的事務(wù)占用過多內(nèi)存。
- max_binlog_size = 1G設(shè)置單個 Binlog 文件的最大大小為 1GB。當(dāng) Binlog 文件達到此大小時，GreatSQL 會自動創(chuàng)建一個新的 Binlog 文件。這有助于管理和分割日志文件，使其更易于處理和備份。
- binlog_space_limit = 500G設(shè)置 Binlog 文件的總存儲空間限制為 500GB。如果 Binlog 文件的總大小超過此限制，GreatSQL 會自動刪除最舊的 Binlog 文件。這可以防止 Binlog 文件占用過多磁盤空間。
- binlog_rows_query_log_events = 1啟用 Binlog 中的行查詢?nèi)罩臼录＿@將記錄生成的行更改時的原始 SQL 語句，有助于調(diào)試和審計。
- binlog_expire_logs_seconds = 604800設(shè)置 Binlog 文件的過期時間為 604800 秒（7 天）。超過此時間的 Binlog 文件將自動刪除。這有助于管理存儲空間并限制 Binlog 文件的數(shù)量。
- binlog_checksum = CEC32控制二進制日志 (binlog) 文件的校驗和機制。啟用 CRC32 校驗和，以確保 Binlog 文件的數(shù)據(jù)完整性和正確性
配置完成后需要重啟 GreatSQL 服務(wù)，使配置生效；
$ systemctl restart greatsql檢查 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 文件及其大?。?/p>
Binlog 文件的存儲位置和命名規(guī)則
存儲位置：
Binlog 文件的存儲位置通常由 log-bin 選項的值和 GreatSQL 數(shù)據(jù)目錄共同決定。如果在 my.cnf 中未指定路徑，binlog 文件會存儲在 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 選項指定了路徑，則 binlog 文件存儲在該路徑下。例如：
log-bin=/data/GreatSQL/binlog命名規(guī)則：
Binlog 文件名由 log-bin 選項的值和一個數(shù)字序列組成。例如，如果 log-bin 設(shè)置為 binlog，則生成的 binlog 文件名類似于 binlogn.000001、binlog.000002 等。
序列號是自動遞增的，當(dāng)一個 Binlog 文件達到最大大?。ㄓ?max_binlog_size 變量控制）時，GreatSQL 會創(chuàng)建一個新的 Binlog 文件，并將序列號遞增。
模擬數(shù)據(jù)誤刪場景
此次測試環(huán)境情況如下：
- 數(shù)據(jù)庫：GreatSQL 8.0.32-26
- 操作系統(tǒng)：Linux myarch 6.6.3-arch1-1 x86_64 GNU/Linux
創(chuàng)建測試數(shù)據(jù)
創(chuàng)建一個testdb庫和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ù)誤刪除
這時候要刪除一條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 的二進制日志 (Binlog)。二進制日志記錄了所有對數(shù)據(jù)庫進行更改的操作，包括插入、更新和刪除。因此，通過解析和重放 Binlog，可以恢復(fù)誤刪的數(shù)據(jù)。
確定誤刪的時間點
記錄下 GreatSQL 服務(wù)器的時間
greatsql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2024-06-07 14:04:23 |
+---------------------+
1 row in set (0.00 sec)記錄下當(dāng)前時間，以確定誤刪操作發(fā)生的大致時間范圍。
查找相應(yīng)的 Binlog 文件
GreatSQL 的 Binlog 文件按時間順序記錄了所有對數(shù)據(jù)庫的更改。你需要找到包含誤刪操作的 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ù)誤刪操作的大致時間，確定可能包含誤刪操作的 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 語句。
$ 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：對二進制日志文件中的事件進行更詳細的解碼和輸出；
- --verbose：會輸出更詳細的日志信息（簡寫 -v）；
這條命令將從 binlog.000040 文件中提取指定時間范圍內(nèi)的日志，并將其保存到 binlog.sql 文件中。
如果沒辦法確定誤操作的具體時間，可以把 Binlog 全部提取。
打開 binlog.sql 文件，查找并確認(rèn)包含誤刪操作的 SQL 語句。
$ 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 語句
可以手工將查到的誤刪除數(shù)據(jù)轉(zhuǎn)換為INSTER 語句重新插入回 GreatSQL 數(shù)據(jù)庫。
或用以下命令，將解析后的 SQL 文件中的 DELETE 語句全部轉(zhuǎn)換為 INSTER 語句。
$ 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 語句結(jié)果；
$ cat /tmp/binlog_insert.sql
INSERT INTO `testdb`.`employees`
VALUE(
1
,'Alice'
,'Manager'
,60000.0);此時將該 SQL 語句在 GreatSQL 中執(zhí)行即可恢復(fù)被誤刪除的數(shù)據(jù)。
高級恢復(fù)技巧
通過 Pos 恢復(fù)
如果不小心將所有數(shù)據(jù)都刪除了，那就可以使用重放 Binlog 讓數(shù)據(jù)全部回來。
例如本想刪除ID=1的數(shù)據(jù)，結(jié)果忘記加WHERE語句。
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表的建表語句，從建表開始重放 Binlog 到刪除語句為止。
同樣需要解析 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建表語句；
$ 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)
)
/*!*/;可以看到該建表語句的開始 Pos 在428474954 結(jié)束，刪除語句是在428476791開始。確定了起止 Pos 直接重放 Binlog 即可。
$ mysqlbinlog --skip-gtids --start-position=428474954 --stop-position=428476791 --database=testdb /data/GreatSQL/binlog.000040 | mysql -uroot -p因為 GreatSQL 啟用了 GTID 模式（@@GLOBAL.GTID_MODE = ON），而 mysqlbinlog 工具默認(rèn)嘗試將 @@SESSION.GTID_NEXT 設(shè)置為 ANONYMOUS，這與啟用 GTID 模式的服務(wù)器不兼容，所以加上--skip-gtids，在下方 GTID 恢復(fù)也解釋為什么需要添加這個參數(shù)。
因為表沒有被刪除，所以不用重放建表語句。若表也不存在了，可以從建表語句開始重放。
再次查看數(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)識符）的一致性。
同時開啟后，也能看到 gtid_next=AUTOMATIC 可知 GTID 自動遞增。
在 GreatSQL 中，mysql.gtid_executed 表用于記錄已執(zhí)行的 GTID（全局事務(wù)標(biāo)識符）。這是 GTID 復(fù)制機制中的一個關(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)識符。
- interval_start：GTID 范圍的起始值。它表示該事務(wù)范圍的起始 GTID。
- interval_end：GTID 范圍的結(jié)束值。它表示該事務(wù)范圍的結(jié)束 GTID。
一個 DDL 語句會產(chǎn)生一個 GTID ，同樣 DML 一個事務(wù)也會產(chǎn)生一個 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í)行一個 DDL 語句
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)同時也可以查看 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=這樣的命令時，它意味著接下來的事務(wù)（或下一個將被寫入二進制日志（Binlog）的事務(wù)）將被賦予指定的GTID。
啟用GTID后，GreatSQL 在恢復(fù) Binlog 時會過濾掉重復(fù)的 GTID 事務(wù)，這意味著相同的GTID語句只會被執(zhí)行一次。然而，若多個GTID中存在相同的記錄語句，這些語句將被忽略，可能因為語句缺失，從而導(dǎo)致數(shù)據(jù)恢復(fù)失敗。因此，在進行 Binlog 備份時，添加 –skip-gtids 參數(shù)，確保忽略GTID的冪等性檢查。
此時將創(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)因此需要跳過刪除語句，則需要選取 GTID 值小于9815的 GTID 值，既 9814。
$ mysqlbinlog --skip-gtids --include-gtids='9548406d-8ff1-11ee-97ec-ec5c6826bca3:9814' /data/GreatSQL/binlog.000040 | mysql -uroot -p進入 GreatSQL 數(shù)據(jù)庫查看。
greatsql> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| employees |
| test1 |
+------------------+
2 rows in set (0.00 sec)可以看到 test1 表已經(jīng)恢復(fù)了
其它方式恢復(fù)
重放整個 Binlog 恢復(fù)數(shù)據(jù)；
$ mysqlbinlog --database=testdb /data/GreatSQL/binlog.000040 | mysql -uroot -p根據(jù)指定時間恢復(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 語句誤刪除單行/多行數(shù)據(jù)
可以查看 Binlog 日志，把 DELETE 語句轉(zhuǎn)為 INSERT 語句重新插入 GreatSQL 數(shù)據(jù)庫恢復(fù)
- 刪表（DROP / DELETE / TRUNCATE） 只能重新重放 Binlog 或者是找到備份恢復(fù)
- 刪庫 只能重新重放 Binlog 或者是找到備份恢復(fù)
- UPDATE 語句誤修改單行/多行數(shù)據(jù) 只能重新重放 Binlog 或者是找到備份恢復(fù)
避免數(shù)據(jù)丟失的最佳實踐
在管理 GreatSQL 數(shù)據(jù)庫時，避免數(shù)據(jù)丟失是至關(guān)重要的任務(wù)。以上演示的都是基于 Binlog 完整保存的情況下，才可以做到完整恢復(fù)，如果 Binlog 有丟失，則沒有辦法恢復(fù)數(shù)據(jù)，所以完整備份數(shù)據(jù)是至關(guān)重要的工作。
定期備份和測試恢復(fù)
全量備份
定期進行全量備份，通常每周一次。全量備份捕獲數(shù)據(jù)庫的完整狀態(tài)，可以使用 mysqldump 或 xtrabackup 工具。
$ mysqldump -u root -p --all-databases > /backup/alldb_backup.sql若有需要，也可以定期備份一下 Binlog 日志文件。
使用FLUSH LOGS刷新一下日志，此時會啟用一個新的 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)這時再把舊的 Binlog 備份下即可。
測試恢復(fù)
定期測試恢復(fù)，確保備份文件是可用的，并且可以成功恢復(fù)。每個月進行一次恢復(fù)測試，確保備份策略的有效性。
避免誤操作
使用 DELETE 語句或 UPDATE 語句，都需要加上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)此時重開一個會話，使用DELETE 語句沒有加上 WHERE 條件，會報錯
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 語句沒有加上 WHERE 條件，會報錯
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.在管理 GreatSQL 數(shù)據(jù)庫時，雖然有 Binlog 等強大的工具來幫助恢復(fù)誤刪的數(shù)據(jù)，但最好的辦法還是預(yù)防。在進行任何數(shù)據(jù)操作時，應(yīng)該保持高度的謹(jǐn)慎，充分備份數(shù)據(jù)，嚴(yán)格遵循操作規(guī)范。數(shù)據(jù)是企業(yè)的重要資產(chǎn)，任何疏忽都可能帶來巨大的損失。
