誤刪GreatSQL資料？別慌，Binlog來幫忙

GreatSQL發表於2024-10-14
SQL

誤刪GreatSQL資料？別慌，Binlog來幫忙

資料丟失是每一個資料庫管理員和開發者都不願面對的噩夢。然而，意外總是難免，當不小心刪除了重要的資料，如何才能迅速而有效地進行恢復呢？在資料庫中有二進位制日誌 (Binlog)，它不僅記錄了所有更改資料的事件，還可以幫助將資料庫恢復到任何一個特定的時間點。本篇文章將帶您深入瞭解如何利用 Binlog 來應對資料丟失問題，在面對資料誤刪時不再慌張。

啟用 Binlog

Binlog (二進位制日誌)的介紹在這裡就不過多描述了，不瞭解 Binlog 的同學，可以前往GreatSQL使用者手冊中 GreatSQL 日誌章節檢視：（https://greatsql.cn/docs/8.0.32-26/2-about-greatsql/4-3-greatsql-binary-log.html）

為了利用 Binlog (二進位制日誌) 進行資料恢復，首先需要確保 Binlog 已經在 GreatSQL 資料庫中啟用並正確配置。以下是詳細的配置步驟、狀態檢查方法及 Binlog 檔案的儲存位置和命名規則。

配置 Binlog 的步驟

找到並編輯 GreatSQL 配置檔案my.cnf。該檔案的路徑因系統和安裝方式不同而有所不同，常見路徑包括 /etc/my.cnf、/etc/mysql/my.cnf。

新增或修改以下配置項：

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

  • log-bin：指定啟用 Binlog，並設定 Binlog 檔案的基本名稱。這裡使用 binlog 作為字首。

  • binlog-format：設定 Binlog 的記錄格式。推薦使用 ROW 格式，因為它記錄的是行級別的變更，更詳細和準確。

  • server-id：為伺服器設定唯一的 ID，必須設定該選項才能啟用 Binlog。對於單個伺服器，任何正整數都可以。對於主從複製環境，確保每個伺服器的 server-id 唯一。

推薦 Binlog 配置

但關於 Binlog 的配置還不止這些，在 GreatSQL 推薦 my.cnf 模板(https://greatsql.cn/docs/8.0.32-26/3-quick-start/3-4-quick-start-with-cnf.html)中還有以下幾個關於 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 每次提交事務後都將 binlog 同步到磁碟。確保在系統崩潰時不會丟失已提交的事務，但可能會略微影響效能。同時配合innodb_flush_log_at_trx_commit=1即所說的雙1，這是最安全的設定。

  • binlog_cache_size = 4M

    設定 Binlog 快取大小為 4MB。當事務中的 SQL 語句較多時，事務的所有更改會被暫時儲存在 Binlog 快取中，然後一次性寫入 Binlog 檔案。

  • max_binlog_cache_size = 2G

    設定 Binlog 快取的最大大小為 2GB。這限制了單個事務可以使用的 Binlog 快取大小，防止過大的事務佔用過多記憶體。

  • max_binlog_size = 1G

    設定單個 Binlog 檔案的最大大小為 1GB。當 Binlog 檔案達到此大小時，GreatSQL 會自動建立一個新的 Binlog 檔案。這有助於管理和分割日誌檔案，使其更易於處理和備份。

  • binlog_space_limit = 500G

    設定 Binlog 檔案的總儲存空間限制為 500GB。如果 Binlog 檔案的總大小超過此限制，GreatSQL 會自動刪除最舊的 Binlog 檔案。這可以防止 Binlog 檔案佔用過多磁碟空間。

  • binlog_rows_query_log_events = 1

    啟用 Binlog 中的行查詢日誌事件。這將記錄生成的行更改時的原始 SQL 語句，有助於除錯和審計。

  • binlog_expire_logs_seconds = 604800

    設定 Binlog 檔案的過期時間為 604800 秒（7 天）。超過此時間的 Binlog 檔案將自動刪除。這有助於管理儲存空間並限制 Binlog 檔案的數量。

  • binlog_checksum = CEC32

    控制二進位制日誌 (binlog) 檔案的校驗和機制。啟用 CRC32 校驗和，以確保 Binlog 檔案的資料完整性和正確性

配置完成後需要重啟 GreatSQL 服務，使配置生效

$ systemctl restart greatsql

檢查 Binlog 狀態

配置完 Binlog 並重啟 GreatSQL 服務後，可以透過以下方法檢查 Binlog 是否已正確啟用

登入 GreatSQL 並執行以下命令

  1. 檢查 Binlog 啟用狀態
greatsql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

如果返回結果為 ON，表示 binlog 已啟用。

  1. 檢查 Binlog 格式
greatsql> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

確保 Binlog 格式為 ROW

  1. 檢視 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)

該命令將列出所有當前存在的 Binlog 檔案及其大小

Binlog 檔案的儲存位置和命名規則

  1. 儲存位置：

Binlog 檔案的儲存位置通常由 log-bin 選項的值和 GreatSQL 資料目錄共同決定。如果在 my.cnf 中未指定路徑，binlog 檔案會儲存在 GreatSQL 資料目錄下。

可以透過以下命令檢視 GreatSQL 資料目錄：

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
  1. 命名規則：

Binlog 檔名由 log-bin 選項的值和一個數字序列組成。例如，如果 log-bin 設定為 binlog，則生成的 binlog 檔名類似於 binlogn.000001、binlog.000002 等。

序列號是自動遞增的，當一個 Binlog 檔案達到最大大小（由 max_binlog_size 變數控制）時，GreatSQL 會建立一個新的 Binlog 檔案，並將序列號遞增。

模擬資料誤刪場景

此次測試環境情況如下：

  • 資料庫：GreatSQL 8.0.32-25
  • 作業系統：Linux myarch 6.6.3-arch1-1 x86_64 GNU/Linux

建立測試資料

建立一個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)

並插入幾條示例資料

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

確認插入成功

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)

模擬資料誤刪除

這時候要刪除一條id=2的欄位，而你卻不小心刪除了id=1的欄位

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

檢視錶確認被誤刪除了

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)

恢復資料的步驟

在資料誤刪後，恢復資料的關鍵在於使用 GreatSQL 的二進位制日誌 (Binlog)。二進位制日誌記錄了所有對資料庫進行更改的操作，包括插入、更新和刪除。因此，透過解析和重放 Binlog，可以恢復誤刪的資料。

確定誤刪的時間點

記錄下 GreatSQL 伺服器的時間

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

記錄下當前時間，以確定誤刪操作發生的大致時間範圍。

查詢相應的 Binlog 檔案

GreatSQL 的 Binlog 檔案按時間順序記錄了所有對資料庫的更改。你需要找到包含誤刪操作的 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)

根據誤刪操作的大致時間，確定可能包含誤刪操作的 Binlog 檔案。例如，如果誤刪操作發生在最近，可能需要檢查 binlog.000040。

或使用SHOW MASTER STATUS命令確認當前正在使用的 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 檔案，以查詢並提取相關的 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 檔案中提取指定時間範圍內的日誌，並將其儲存到 binlog.sql 檔案中。

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

開啟 binlog.sql 檔案，查詢並確認包含誤刪操作的 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

生成恢復資料的 SQL 語句

可以手工將查到的誤刪除資料轉換為INSTER 語句重新插入回 GreatSQL 資料庫。

或用以下命令，將解析後的 SQL 檔案中的 DELETE 語句全部轉換為 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

檢視轉換後的 SQL 語句結果

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

此時將該 SQL 語句在 GreatSQL 中執行即可恢復被誤刪除的資料

高階恢復技巧

透過 Pos 恢復

如果不小心將所有資料都刪除了，那就可以使用重放 Binlog 讓資料全部回來

例如本想刪除ID=1的資料，結果忘記加WHERE語句

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

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

導致了testdb.employees表資料都沒了，但是沒關係。需要找到 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 結束，刪除語句是在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 工具預設嘗試將 @@SESSION.GTID_NEXT 設定為 ANONYMOUS，這與啟用 GTID 模式的伺服器不相容，所以加上--skip-gtids，在下方 GTID 恢復也解釋為什麼需要新增這個引數

因為表沒有被刪除，所以不用重放建表語句。若表也不存在了，可以從建表語句開始重放

再次檢視資料，可以看到資料都回來了

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)

以上展示的是根據指定位置恢復資料

透過 GTID 恢復

檢視 GTID 開啟前的設定

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 的開啟和關閉。
  • enforce_gtid_consistency：確保 GTID（全域性事務識別符號）的一致性。

同時開啟後，也能看到 gtid_next=AUTOMATIC 可知 GTID 自動遞增

在 GreatSQL 中，mysql.gtid_executed 表用於記錄已執行的 GTID（全域性事務識別符號）。這是 GTID 複製機制中的一個關鍵部分，用於跟蹤伺服器上已經執行的所有 GTID，以確保資料一致性和事務的順利複製。

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 的源伺服器 UUID。這是生成 GTID 的伺服器的唯一識別符號。

  • interval_start：GTID 範圍的起始值。它表示該事務範圍的起始 GTID。

  • interval_end：GTID 範圍的結束值。它表示該事務範圍的結束 GTID。

一個 DDL 語句會產生一個 GTID ，同樣 DML 一個事務也會產生一個 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)

# 執行一個 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)

當看到SET @@SESSION.GTID_NEXT=這樣的命令時，它意味著接下來的事務（或下一個將被寫入二進位制日誌（Binlog）的事務）將被賦予指定的GTID。

啟用GTID後，GreatSQL 在恢復 Binlog 時會過濾掉重複的 GTID 事務，這意味著相同的GTID語句只會被執行一次。然而，若多個GTID中存在相同的記錄語句，這些語句將被忽略，可能因為語句缺失，從而導致資料恢復失敗。因此，在進行 Binlog 備份時，新增 –skip-gtids 引數，確保忽略GTID的冪等性檢查。

此時將建立test1表刪除，接著在使用 GTID 恢復

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 值已經到了 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 資料庫檢視

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

可以看到 test1 表已經恢復了

其它方式恢復

重放整個 Binlog 恢復資料

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

根據指定時間恢復資料

$ 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

恢復總結

  1. DELETE 語句誤刪除單行/多行資料

​ 可以檢視 Binlog 日誌，把 DELETE 語句轉為 INSERT 語句重新插入 GreatSQL 資料庫恢復

  1. 刪表（DROP / DELETE / TRUNCATE）
    只能重新重放 Binlog 或者是找到備份恢復
  2. 刪庫
    只能重新重放 Binlog 或者是找到備份恢復
  3. UPDATE 語句誤修改單行/多行資料
    只能重新重放 Binlog 或者是找到備份恢復

避免資料丟失的最佳實踐

在管理 GreatSQL 資料庫時，避免資料丟失是至關重要的任務。以上演示的都是基於 Binlog 完整儲存的情況下，才可以做到完整恢復，如果 Binlog 有丟失，則沒有辦法恢復資料，所以完整備份資料是至關重要的工作。

定期備份和測試恢復

全量備份

定期進行全量備份，通常每週一次。全量備份捕獲資料庫的完整狀態，可以使用 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 備份下即可

測試恢復

定期測試恢復，確保備份檔案是可用的，並且可以成功恢復。每個月進行一次恢復測試，確保備份策略的有效性。

避免誤操作

使用 DELETE 語句或 UPDATE 語句，都需要加上WHERE 條件，避免誤刪除/誤修改資料。

可以開啟sql_safe_updates引數，

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 資料庫時，雖然有 Binlog 等強大的工具來幫助恢復誤刪的資料，但最好的辦法還是預防。在進行任何資料操作時，應該保持高度的謹慎，充分備份資料，嚴格遵循操作規範。資料是企業的重要資產，任何疏忽都可能帶來巨大的損失。因此，務必對每一次操作保持敬畏之心，防患於未然:)

Enjoy GreatSQL 😃

關於 GreatSQL

GreatSQL是適用於金融級應用的國內自主開源資料庫，具備高效能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併相容MySQL或Percona Server。

相關連結： GreatSQL社群 Gitee GitHub Bilibili

GreatSQL社群：

社群部落格有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html

image-20230105161905827

技術交流群：

微信：掃碼新增GreatSQL社群助手微信好友，傳送驗證資訊加群。

image-20221030163217640

相關文章