【Mysql】如何透過binlog恢復資料

kamier發表於2023-03-09
MySql

一、前言

在實際工作中，可能有些剛工作不久的同學會誤刪了生產或測試環境的資料，這時怎麼恢復Mysql中誤刪的資料呢？

大部分同學都知道可以透過binlog來恢復資料，但是具體如何操作可能還不是很清楚，接下來就來介紹下如何透過binlog日誌來恢復資料

二、準備工作

這裡我使用的是windows的Mysql5.7版本

2.1 開啟binlog日誌

  1. 首先檢視當前mysql是否已開啟binlog，命令如下

    show variables like 'log_bin';

  2. 如果未開啟，修改配置檔案my.ini 或 my.cnf，新增開啟binlog配置，如下

    [mysqld]
log-bin=mysql-bin
binlog-format=ROW
server_id=1
  3. 重啟Mysql

2.2 建立測試資料並備份

建立一個資料庫test，並建立table1表，表結構如下

CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入5條資料，如下

INSERT INTO `test`.`table1`(`id`, `name`) VALUES (1, '111');
INSERT INTO `test`.`table1`(`id`, `name`) VALUES (2, '222');
INSERT INTO `test`.`table1`(`id`, `name`) VALUES (3, '333');
INSERT INTO `test`.`table1`(`id`, `name`) VALUES (4, '444');
INSERT INTO `test`.`table1`(`id`, `name`) VALUES (5, '555');

OK，到這裡我們的table1表裡已經有5條測試資料了。

現在使用mysqldump工具對test庫進行備份，備份檔名為test.sql，命令如下

mysqldump -uroot -p test --master-data=2 --single-transaction > test.sql

2.3 模擬誤刪操作

接下來繼續往table1表中新增3條資料，如下

INSERT INTO `test`.`table1`(`id`, `name`) VALUES (6, '666');
INSERT INTO `test`.`table1`(`id`, `name`) VALUES (7, '777');
INSERT INTO `test`.`table1`(`id`, `name`) VALUES (8, '888');

這時我們透過清空該表來模擬誤刪操作

truncate table table1;

那麼接下來我們如何來恢復table1表中的資料（8條）？
其實就是透過備份檔案 + binlog的增量資料的方式來恢復，接下來介紹具體步驟

三、具體步驟

  1. 首先我們開啟備份檔案test.sql，找到MASTER_LOG_POS字樣，並記住該偏移量799674，如下圖
    image.png
    這說明我們的備份檔案是在這個位置進行備份的
  2. 在mysql的data目錄下找到對應的binlog檔案，透過mysqlbinlog工具將binlog日誌以明文的方式輸出到test.binlog檔案中，如下
    mysqlbinlog --no-defaults ..\data\mysql-bin.000002 -vv>test.binlog
  3. 開啟test.binlog檔案，找到執行truncate語句的位置並記下來，如下圖
    image.png
    這裡偏移量為800469

  4. 繼續使用mysqlbinlog工具，指定起始位置和終止位置，匯出sql語句到inc.binlog檔案中，命令如下

    mysqlbinlog --no-defaults --database=test ..\data\mysql-bin.000002 --start-position=799674 --stop-position=800469 -vv >inc.binlog

    如果起始位置和終止位置不在同一個binlog中，可以使用如下命令

    mysqlbinlog --no-defaults --database=test ..\data\mysql-bin.000002 ..\data\mysql-bin.000003 --start-position=起始位置 --stop-position=終止位置 -vv >inc.binlog

  5. 在test資料庫下執行備份檔案test.sql，恢復到備份位置

    use test;
source E:\mysql\bin\test.sql

  6. 再執行inc.binlog，恢復到truncate之前的位置，如下

    source E:\mysql\bin\inc.binlog
  7. 最終資料恢復到truncate之前
    image.png

相關文章