MySQL BinLog如何恢復誤更新刪除數(shù)據(jù)

更新時間：2024年06月01日 11:27:17 作者：niaonao

這篇文章主要介紹了MySQL BinLog如何恢復誤更新刪除數(shù)據(jù)問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

1. 前言

實際開發(fā)、生產(chǎn)場景中會出現(xiàn)，RDS 宕機時數(shù)據(jù)記錄未入庫導致數(shù)據(jù)丟失；

誤更新、誤刪除操作導致記錄被修改或數(shù)據(jù)丟失的情況；

對于 MySQL 我們可以通過 BinLog 找回誤刪除的數(shù)據(jù)。

BinLog 是 MySQL 自帶的日志，偏向邏輯性的日志，記錄的是對哪個表的哪一行做了更新操作，更新前后的值是什么。

2. BinLog 說明

Binary Logging 是個存儲二進制文件，有兩種文件類型

索引文件

文件名后綴為.index;
用于記錄哪些日志文件正在被使用

日志文件

文件名后綴為.00000*;
記錄數(shù)據(jù)庫所有的 DDL 和 DML (除了數(shù)據(jù)查詢語句)語句事件

binlog_format 三種日志格式

Statement

每一條修改數(shù)據(jù)的 sql 都會記錄到 master 的 bin_log 中，slave 在復制的時候 sql 進程會解析成 master 端執(zhí)行過的相同的 sql 在 slave 庫上再次執(zhí)行

Row

日志中會記錄成每一行數(shù)據(jù)修改的形式，然后在slave端再對相同的數(shù)據(jù)進行修改

Mixed

混合模式，根據(jù)實際執(zhí)行語句選擇 Statement 和 Row 的一種進行執(zhí)行。
比如對于修改表結(jié)構(gòu)該表所有記錄都會變更，此時會以 Statement 模式記錄，而不是所有行記錄變更都寫入日志。

	優(yōu)點	缺點
Statement	記錄執(zhí)行語句及上下文信息，不記錄被語句影響到的每一條數(shù)據(jù)的信息，日志量較小	主從同步可能會存在日志上下文信息不正確導致執(zhí)行結(jié)果不一致的問題
Row	記錄每一條數(shù)據(jù)的變化，足夠詳細，不會出現(xiàn)主從復制數(shù)據(jù)不一致的問題	日志量較大
Mixed	混合模式，根據(jù)語句執(zhí)行及 MySQL 優(yōu)化策略選擇一種模式記錄日志，日志量可控	相對 Row 模式不夠詳細

通過設(shè)置配置文件 my.ini 的 log_bin 屬性來開啟日志功能，此時對數(shù)據(jù)庫的操作會記錄 binlog 日志并寫入磁盤文件。

編輯 my.ini 可開啟并配置 Binlog 策略

# Binary Logging.
# binlog 日志文件路徑
log-bin=C:/ProgramData/MySQL/BinlogData
# binlog 日志格式
binlog_format=ROW
# binlog 過期清理時間/天
expire_logs_days=90
# binlog 日志文件大小/個
max_binlog_size=100M
# binlog 緩存大小
binlog_cache_size=4M
max_binlog_cache_size=512M

通過應(yīng)用程序 mysqlbinlog.exe 可以從日志文件中讀取指定時間段的數(shù)據(jù)庫語句變更詳細日志。

mysqlbinlog --base64-output=decode-rows -v --database=<數(shù)據(jù)庫名稱> --start-datetime="<起始時間>" --stop-datetime="<截至時間>" <日志文件> > <輸出文件>

如下所示某時刻的變更詳細日志，某數(shù)據(jù)庫在 211129 16:53:31 時刻一條更新操作日志，根據(jù)該日志可清晰的指定更新前的數(shù)據(jù)，依據(jù)該日志可恢復記錄數(shù)據(jù)到更新前的數(shù)據(jù)。

#211129 16:53:31 server id 1  end_log_pos 743 CRC32 0xd38b2db6 	Update_rows: table id 337 flags: STMT_END_F
### UPDATE `ecrm_jd`.`xxl_job_user`
### WHERE
###   @1=2
###   @2='admin11'
###   @3='e10adc3949ba59abbe56e057f20f883e'
###   @4=1
###   @5=NULL
### SET
###   @1=2
###   @2='niaonao'
###   @3='e10adc3949ba59abbe56e057f20f883e'
###   @4=1
###   @5=NULL

3. BinLog 配置是否被開啟

查看是否開啟 BinLog，屬性 log_bin 的值為 OFF 則沒開啟該功能無法通過本文下面的 BigLog 方式恢復數(shù)據(jù)。

log_bin 的值為 ON 則支持恢復數(shù)據(jù)。

mysql> show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
+---------------------------------+-------+
5 rows in set (0.01 sec)

4. BinLog 配置怎么開啟

找到 MySQL 的配置文件，配置文件路徑 C:\ProgramData\MySQL\MySQL Server 5.6\my.ini，只需要把 log-bin 開放后(去除屬性前面的 # 注釋)重啟服務(wù)即可，不生效則確認 my.ini 配置無誤多次重啟后生效。

可指定文件生成路徑，默認在相對路徑下。此處指定 binlog 文件生成配置 C:\ProgramData\MySQL\BinlogData，日志記錄格式配置為 ROW，單文件最大 100M，三個月清理歷史文件。

log-bin 不指定時，默認使用的設(shè)置是 log-bin=mysql-bin；binlog_format 默認使用 STATEMENT；

# Binary Logging.
# binlog 日志文件
log-bin=C:\ProgramData\MySQL\BinlogData
# binlog 日志格式
binlog_format=ROW
# binlog 過期清理時間/天
expire_logs_days=90
# binlog 日志文件大小/個
max_binlog_size=100M
# binlog 緩存大小
binlog_cache_size=4M
max_binlog_cache_size=512M

重啟服務(wù)

服務(wù)名稱就是 MySQL56，可通過 WIN+R，輸入 services.msc 查看服務(wù)。

PS C:\Users\Lenovo> net stop MySQL56
MySQL56 服務(wù)正在停止.
MySQL56 服務(wù)已成功停止。

PS C:\Users\Lenovo> net start MySQL56
MySQL56 服務(wù)正在啟動 .
MySQL56 服務(wù)已經(jīng)啟動成功。

PS C:\Users\Lenovo> mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | C:\ProgramData\MySQL\BinlogData       |
| log_bin_index                   | C:\ProgramData\MySQL\BinlogData.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
+---------------------------------+---------------------------------------+
5 rows in set (0.00 sec)

可以通過 show variables like 'log_bin%' 看到此時配置 log_bin 已開啟為 ON.

5. 誤更新或刪除數(shù)據(jù)

以數(shù)據(jù)庫 ecrm_jd 的用戶表 xxl_job_user 演示，更新一條記錄，刪除兩條記錄。

再根據(jù) binlog 日志來追蹤數(shù)據(jù)。

mysql> use ecrm_jd;
Database changed

mysql> select * from xxl_job_user;
+----+----------+----------------------------------+------+------------+
| id | username | password                         | role | permission |
+----+----------+----------------------------------+------+------------+
|  1 | admin    | e10adc3949ba59abbe56e057f20f883e |    1 | NULL       |
+----+----------+----------------------------------+------+------------+
1 row in set (0.00 sec)

mysql> insert xxl_job_user(username,password,role) values('admin11', 'e10adc3949ba59abbe56e057f20f883e', 1),('admin12', 'e10adc3949ba59abbe56e057f20f883e', 1),('admin13', 'e10adc3949ba59abbe56e057f20f883e', 0);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from xxl_job_user;
+----+----------+----------------------------------+------+------------+
| id | username | password                         | role | permission |
+----+----------+----------------------------------+------+------------+
|  1 | admin    | e10adc3949ba59abbe56e057f20f883e |    1 | NULL       |
|  2 | admin11  | e10adc3949ba59abbe56e057f20f883e |    1 | NULL       |
|  3 | admin12  | e10adc3949ba59abbe56e057f20f883e |    1 | NULL       |
|  4 | admin13  | e10adc3949ba59abbe56e057f20f883e |    0 | NULL       |
+----+----------+----------------------------------+------+------------+
4 rows in set (0.00 sec)

mysql> update xxl_job_user set username = 'niaonao' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from xxl_job_user where id in (1,4);
Query OK, 2 rows affected (0.01 sec)

mysql> select * from xxl_job_user;
+----+----------+----------------------------------+------+------------+
| id | username | password                         | role | permission |
+----+----------+----------------------------------+------+------------+
|  2 | niaonao  | e10adc3949ba59abbe56e057f20f883e |    1 | NULL       |
|  3 | admin12  | e10adc3949ba59abbe56e057f20f883e |    1 | NULL       |
+----+----------+----------------------------------+------+------------+
2 rows in set (0.00 sec)

6. binlog 日志跟蹤查找被刪除的數(shù)據(jù)

這里是 2021-11-29 16:56 左右修改的，在 C:\ProgramData\MySQL 下找到 <filename>.000003 日志文件。

通過應(yīng)用程序 mysqlbinlog 查看 binlog。

去安裝路徑下找到應(yīng)用程序 ~\MySQL Server 5.6\bin\mysqlbinlog.exe

WIN+R 輸入 cmd 打開命令行窗口，切換到 mysqlbinlog 所在目錄，執(zhí)行以下命令導出腳本。

mysqlbinlog --base64-output=decode-rows -v --database=<數(shù)據(jù)庫名稱> --start-datetime="<起始時間>" --stop-datetime="<截至時間>" <日志文件> > <輸出文件>

此處從文件 BinlogData.000003 中解析導出數(shù)據(jù)庫 ecrm_jd 在 2021-11-29 16:50:00 ~ 2021-11-29 17:30:00 時間內(nèi)的日志，輸出到文件 binlog202111291650.sql

C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin>mysqlbinlog --base64-output=decode-rows -v --database=ecrm_jd --start-datetime="2021-11-29 16:50:00" --stop-datetime="2021-11-29 17:30:00" C:\ProgramData\MySQL\BinlogData.000003 > binlog202111291650.sql

打開文件內(nèi)容如下：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#211129 15:20:44 server id 1  end_log_pos 120 CRC32 0x7b673bd6 	Start: binlog v 4, server v 5.6.21-log created 211129 15:20:44 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 120
#211129 16:52:19 server id 1  end_log_pos 195 CRC32 0xeeb79b0f 	Query	thread_id=4	exec_time=0	error_code=0
SET TIMESTAMP=1638175939/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C gbk *//*!*/;
SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 195
#211129 16:52:19 server id 1  end_log_pos 263 CRC32 0xd1715424 	Table_map: `ecrm_jd`.`xxl_job_user` mapped to number 337
# at 263
#211129 16:52:19 server id 1  end_log_pos 439 CRC32 0x93f08743 	Write_rows: table id 337 flags: STMT_END_F
### INSERT INTO `ecrm_jd`.`xxl_job_user`
### SET
###   @1=2
###   @2='admin11'
###   @3='e10adc3949ba59abbe56e057f20f883e'
###   @4=1
###   @5=NULL
### INSERT INTO `ecrm_jd`.`xxl_job_user`
### SET
###   @1=3
###   @2='admin12'
###   @3='e10adc3949ba59abbe56e057f20f883e'
###   @4=1
###   @5=NULL
### INSERT INTO `ecrm_jd`.`xxl_job_user`
### SET
###   @1=4
###   @2='admin13'
###   @3='e10adc3949ba59abbe56e057f20f883e'
###   @4=0
###   @5=NULL
# at 439
#211129 16:52:19 server id 1  end_log_pos 470 CRC32 0x48cdfe14 	Xid = 391
COMMIT/*!*/;
# at 470
#211129 16:53:31 server id 1  end_log_pos 545 CRC32 0xdc9527f7 	Query	thread_id=4	exec_time=0	error_code=0
SET TIMESTAMP=1638176011/*!*/;
BEGIN
/*!*/;
# at 545
#211129 16:53:31 server id 1  end_log_pos 613 CRC32 0x7b2ee120 	Table_map: `ecrm_jd`.`xxl_job_user` mapped to number 337
# at 613
#211129 16:53:31 server id 1  end_log_pos 743 CRC32 0xd38b2db6 	Update_rows: table id 337 flags: STMT_END_F
### UPDATE `ecrm_jd`.`xxl_job_user`
### WHERE
###   @1=2
###   @2='admin11'
###   @3='e10adc3949ba59abbe56e057f20f883e'
###   @4=1
###   @5=NULL
### SET
###   @1=2
###   @2='niaonao'
###   @3='e10adc3949ba59abbe56e057f20f883e'
###   @4=1
###   @5=NULL
# at 743
#211129 16:53:31 server id 1  end_log_pos 774 CRC32 0x0423f88b 	Xid = 397
COMMIT/*!*/;
# at 774
#211129 16:54:21 server id 1  end_log_pos 849 CRC32 0x6280ce3c 	Query	thread_id=4	exec_time=0	error_code=0
SET TIMESTAMP=1638176061/*!*/;
BEGIN
/*!*/;
# at 849
#211129 16:54:21 server id 1  end_log_pos 917 CRC32 0xd4ee0972 	Table_map: `ecrm_jd`.`xxl_job_user` mapped to number 337
# at 917
#211129 16:54:21 server id 1  end_log_pos 1044 CRC32 0xcc058cef 	Delete_rows: table id 337 flags: STMT_END_F
### DELETE FROM `ecrm_jd`.`xxl_job_user`
### WHERE
###   @1=1
###   @2='admin'
###   @3='e10adc3949ba59abbe56e057f20f883e'
###   @4=1
###   @5=NULL
### DELETE FROM `ecrm_jd`.`xxl_job_user`
### WHERE
###   @1=4
###   @2='admin13'
###   @3='e10adc3949ba59abbe56e057f20f883e'
###   @4=0
###   @5=NULL
# at 1044
#211129 16:54:21 server id 1  end_log_pos 1075 CRC32 0x23f55056 	Xid = 401
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

通過程序 mysqlbinlog 恢復的日志中可以查看指定時間段內(nèi)的數(shù)據(jù)庫操作語句，找到誤 UPDATE、DELETE 語句可以看到被刪除的記錄屬性和屬性值，依據(jù)該日志可恢復該記錄。