背景

生產(chǎn)數(shù)據(jù)庫(kù)執(zhí)行 SQL 腳本，一般會(huì)經(jīng)過(guò)正規(guī)的審批流程才能運(yùn)行。但有些情況是例外的，業(yè)務(wù)部門(mén)在提出一些刪除數(shù)據(jù)的需求后打算撤回，或者在運(yùn)營(yíng)后臺(tái)不小心刪除了一些數(shù)據(jù)，然后找到 DBA 團(tuán)隊(duì)協(xié)助，希望能恢復(fù)數(shù)據(jù)。

經(jīng)調(diào)研，binlog2sql 是大眾點(diǎn)評(píng)開(kāi)源的一款用于解析 MySQL binlog 的工具，根據(jù)不同選項(xiàng)，可以得到原始SQL、回滾SQL、去除主鍵的INSERT SQL 等，適用于數(shù)據(jù)快速回滾（閃回）和主從切換后新 Master 丟數(shù)據(jù)的修復(fù)工作。

目標(biāo)

驗(yàn)證 binlog2sql 工具是否可以快速恢復(fù)數(shù)據(jù)。

步驟

準(zhǔn)備工作

安裝 binlog2sql 工具。

> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql

# > yum install python3-pip
# > whereis pip
# > pip3.6 install -r requirements.txt
> pip install -r requirements.txt

MySQL 服務(wù)端配置以下參數(shù)，請(qǐng)注意，binlog2sql 僅支持 row 格式。

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

指定執(zhí)行腳本的數(shù)據(jù)庫(kù)用戶(hù)授權(quán)。

-- SELECT 權(quán)限：查詢(xún) information_schema.COLUMNS
-- REPLICATION SLAVE：通過(guò) BINLOG_DUMP 協(xié)議獲取 binlog 內(nèi)容
-- REPLICATION CLIENT：執(zhí)行 SHOW MASTER STATUS 獲取 binlog 信息
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO user

準(zhǔn)備一張用戶(hù)表 user，并填充 1W 條數(shù)據(jù)。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `gmt_create` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

DELIMITER $$

CREATE PROCEDURE InsertRandomData()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE randomName CHAR(10);
    DECLARE randomDate DATE;

    WHILE i <= 10000 DO
        -- 生成隨機(jī) name (隨機(jī)字符串)
        SET randomName = CONCAT(
            CHAR(FLOOR(RAND() * 26) + 65), 
            CHAR(FLOOR(RAND() * 26) + 65), 
            CHAR(FLOOR(RAND() * 26) + 65), 
            CHAR(FLOOR(RAND() * 26) + 65), 
            CHAR(FLOOR(RAND() * 26) + 65)
        );

        -- 生成隨機(jī)日期 (2013-11-11 起始，隨機(jī)范圍約為一年內(nèi))
        SET randomDate = DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY);

        -- 插入數(shù)據(jù)
        INSERT INTO `user` (`name`, `gmt_create`) VALUES (randomName, randomDate);

        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

-- 調(diào)用存儲(chǔ)過(guò)程
CALL InsertRandomData();

查看大于 11 月份的數(shù)據(jù)總數(shù)，共 363 條。

mysql > SELECT count(*) FROM user WHERE gmt_create > '2023-11-01 00:00:00';

+----------+
| count(*) |
+----------+
|      363 |
+----------+

模擬誤刪除，假設(shè)在 15:30 左右刪除了 11 月份之后的數(shù)據(jù)。

mysql > DELETE FROM user WHERE gmt_create > '2023-11-01 00:00:00';

恢復(fù)數(shù)據(jù)

查看主庫(kù) binlog 狀態(tài)，最新的文件為 mysql-bin.000003。

-- 低版本使用 SHOW MASTER STATUS;
mysql > SHOW BINARY LOGS;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |      1871 | No        |
| mysql-bin.000002 |       181 | No        |
| mysql-bin.000003 |    917878 | No        |
+------------------+-----------+-----------+
3 rows in set (0.04 sec)

篩選出需要回滾的SQL，誤操作人一般知道大致的誤操作時(shí)間，我們首先根據(jù)時(shí)間做一次過(guò)濾。

shell> python binlog2sql/binlog2sql.py -h地址 -P端口 -u用戶(hù) -p'密碼' -d庫(kù)民 -t表名 --start-file='mysql-bin.000003' --start-datetime='2023-11-02 15:00:00' --stop-datetime='2023-11-02 16:00:00' > /tmp/raw.sql

raw.sql輸出：
DELETE FROM `test`.`user` WHERE `gmt_create`='2023-11-01 00:00:00' AND `id`=1351 AND `name`='TPUDJ' LIMIT 1; #start 105311 end 262311 time 2023-11-02 15:31:10
DELETE FROM `test`.`user` WHERE `gmt_create`='2023-11-01 00:00:00' AND `id`=1352 AND `name`='YKIIS' LIMIT 1; #start 105311 end 262311 time 2023-11-02 15:31:10
...
DELETE FROM `test`.`user` WHERE `gmt_create`='2023-12-31 00:00:00' AND `id`=1714 AND `name`='SHKBC' LIMIT 1; #start 105311 end 265754 time 2023-11-02 15:31:10

根據(jù) raw.sql 的位置信息，可以判斷誤操作的 SQL 來(lái)自同一個(gè)事務(wù)，準(zhǔn)確位置在 105311-265754 之間，根據(jù)位置過(guò)濾，使用 -B 選項(xiàng)生成回滾 SQL。

shell> python binlog2sql/binlog2sql.py -h地址 -P端口 -u用戶(hù) -p'密碼' -d庫(kù)民 -t表名 --start-file='mysql-bin.000003' --start-position=105311 --stop-position=265754 -B > /tmp/rollback.sql

rollback.sql輸出：
INSERT INTO `test`.`user`(`gmt_create`, `id`, `name`) VALUES ('2023-11-01 00:00:00', 1351, 'TPUDJ'); #start 105311 end 262311 time 2023-11-02 15:31:10
INSERT INTO `test`.`user`(`gmt_create`, `id`, `name`) VALUES ('2023-11-01 00:00:00', 1352, 'YKIIS'); #start 105311 end 262311 time 2023-11-02 15:31:10
...
INSERT INTO `test`.`user`(`gmt_create`, `id`, `name`) VALUES ('2023-12-31 00:00:00', 1714, 'SHKBC'); #start 105311 end 265754 time 2023-11-02 15:31:10

結(jié)果驗(yàn)證

確認(rèn)回滾 SQL 總行數(shù)是否對(duì)應(yīng)誤刪除的 363 條。

shell> wc -l /tmp/rollback.sql

363 /tmp/rollback.sql

與業(yè)務(wù)方確認(rèn)回滾 SQL 沒(méi)問(wèn)題，執(zhí)行回滾語(yǔ)句。登錄 MySQL，確認(rèn)回滾成功。

shell> mysql -h地址 -P端口 -u用戶(hù) -p'密碼' < /tmp/rollback.sql

mysql> SELECT count(*) FROM user WHERE gmt_create > '2023-11-01 00:00:00';
+----------+
| count(*) |
+----------+
|    363   |
+----------+

結(jié)論

binlog2sql 適用于在線恢復(fù)誤操作的數(shù)據(jù)，但不適用于以下情況：

• 數(shù)據(jù)恢復(fù)建議控制在 50W 以?xún)?nèi)，數(shù)據(jù)量越大，逆向生成的語(yǔ)句越多，超過(guò)這個(gè)數(shù)值，恢復(fù)時(shí)間可能會(huì)超過(guò) 15 分鐘。
• 不支持 DDL 恢復(fù)操作。因?yàn)榧词乖?row 模式下，binlog對(duì)于 DDL 操作不會(huì)記錄每行數(shù)據(jù)的變化。要實(shí)現(xiàn) DDL 快速回滾，必須修改 MySQL 源碼，使得在執(zhí)行 DDL 前先備份老數(shù)據(jù)。阿里林曉斌團(tuán)隊(duì)提交了 patch 給 MySQL 官方，相關(guān)實(shí)現(xiàn)方案可以查閱 MySQL閃回方案討論及實(shí)現(xiàn)。
• 根據(jù)官方說(shuō)法，在線召回?cái)?shù)據(jù)推薦使用 binlog2sql 工具，離線解析使用 mysqlbinlog 工具，MySQL 閃回特性最早由阿里彭立勛開(kāi)發(fā)。

到此這篇關(guān)于MySQL使用binlog2sql工具實(shí)現(xiàn)在線恢復(fù)數(shù)據(jù)功能的文章就介紹到這了,更多相關(guān)MySQL binlog2sql恢復(fù)數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！