MySQL BinLog如何恢復(fù)誤更新刪除數(shù)據(jù)
1. 前言
實(shí)際開發(fā)、生產(chǎn)場景中會(huì)出現(xiàn),RDS 宕機(jī)時(shí)數(shù)據(jù)記錄未入庫導(dǎo)致數(shù)據(jù)丟失;
誤更新、誤刪除操作導(dǎo)致記錄被修改或數(shù)據(jù)丟失的情況;
對于 MySQL 我們可以通過 BinLog 找回誤刪除的數(shù)據(jù)。
BinLog 是 MySQL 自帶的日志,偏向邏輯性的日志,記錄的是對哪個(gè)表的哪一行做了更新操作,更新前后的值是什么。
2. BinLog 說明
Binary Logging 是個(gè)存儲(chǔ)二進(jìn)制文件,有兩種文件類型
索引文件
- 文件名后綴為.index;
- 用于記錄哪些日志文件正在被使用
日志文件
- 文件名后綴為.00000*;
- 記錄數(shù)據(jù)庫所有的 DDL 和 DML (除了數(shù)據(jù)查詢語句)語句事件
binlog_format 三種日志格式
Statement
- 每一條修改數(shù)據(jù)的 sql 都會(huì)記錄到 master 的 bin_log 中,slave 在復(fù)制的時(shí)候 sql 進(jìn)程會(huì)解析成 master 端執(zhí)行過的相同的 sql 在 slave 庫上再次執(zhí)行
Row
- 日志中會(huì)記錄成每一行數(shù)據(jù)修改的形式,然后在slave端再對相同的數(shù)據(jù)進(jìn)行修改
Mixed
- 混合模式,根據(jù)實(shí)際執(zhí)行語句選擇 Statement 和 Row 的一種進(jìn)行執(zhí)行。
- 比如對于修改表結(jié)構(gòu)該表所有記錄都會(huì)變更,此時(shí)會(huì)以 Statement 模式記錄,而不是所有行記錄變更都寫入日志。
優(yōu)點(diǎn) | 缺點(diǎn) | |
---|---|---|
Statement | 記錄執(zhí)行語句及上下文信息,不記錄被語句影響到的每一條數(shù)據(jù)的信息,日志量較小 | 主從同步可能會(huì)存在日志上下文信息不正確導(dǎo)致執(zhí)行結(jié)果不一致的問題 |
Row | 記錄每一條數(shù)據(jù)的變化,足夠詳細(xì),不會(huì)出現(xiàn)主從復(fù)制數(shù)據(jù)不一致的問題 | 日志量較大 |
Mixed | 混合模式,根據(jù)語句執(zhí)行及 MySQL 優(yōu)化策略選擇一種模式記錄日志,日志量可控 | 相對 Row 模式不夠詳細(xì) |
通過設(shè)置配置文件 my.ini 的 log_bin 屬性來開啟日志功能,此時(shí)對數(shù)據(jù)庫的操作會(huì)記錄 binlog 日志并寫入磁盤文件。
編輯 my.ini 可開啟并配置 Binlog 策略
# Binary Logging. # binlog 日志文件路徑 log-bin=C:/ProgramData/MySQL/BinlogData # binlog 日志格式 binlog_format=ROW # binlog 過期清理時(shí)間/天 expire_logs_days=90 # binlog 日志文件大小/個(gè) max_binlog_size=100M # binlog 緩存大小 binlog_cache_size=4M max_binlog_cache_size=512M
通過應(yīng)用程序 mysqlbinlog.exe 可以從日志文件中讀取指定時(shí)間段的數(shù)據(jù)庫語句變更詳細(xì)日志。
mysqlbinlog --base64-output=decode-rows -v --database=<數(shù)據(jù)庫名稱> --start-datetime="<起始時(shí)間>" --stop-datetime="<截至?xí)r間>" <日志文件> > <輸出文件>
如下所示某時(shí)刻的變更詳細(xì)日志,某數(shù)據(jù)庫在 211129 16:53:31 時(shí)刻一條更新操作日志,根據(jù)該日志可清晰的指定更新前的數(shù)據(jù),依據(jù)該日志可恢復(fù)記錄數(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 方式恢復(fù)數(shù)據(jù)。
log_bin 的值為 ON 則支持恢復(fù)數(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ù)即可,不生效則確認(rèn) my.ini 配置無誤多次重啟后生效。
可指定文件生成路徑,默認(rèn)在相對路徑下。此處指定 binlog 文件生成配置 C:\ProgramData\MySQL\BinlogData,日志記錄格式配置為 ROW,單文件最大 100M,三個(gè)月清理歷史文件。
log-bin 不指定時(shí),默認(rèn)使用的設(shè)置是 log-bin=mysql-bin;binlog_format 默認(rèn)使用 STATEMENT;
# Binary Logging. # binlog 日志文件 log-bin=C:\ProgramData\MySQL\BinlogData # binlog 日志格式 binlog_format=ROW # binlog 過期清理時(shí)間/天 expire_logs_days=90 # binlog 日志文件大小/個(gè) 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ù)正在啟動(dòng) . MySQL56 服務(wù)已經(jīng)啟動(dò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%'
看到此時(shí)配置 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í)行以下命令導(dǎo)出腳本。
mysqlbinlog --base64-output=decode-rows -v --database=<數(shù)據(jù)庫名稱> --start-datetime="<起始時(shí)間>" --stop-datetime="<截至?xí)r間>" <日志文件> > <輸出文件>
此處從文件 BinlogData.000003 中解析導(dǎo)出數(shù)據(jù)庫 ecrm_jd 在 2021-11-29 16:50:00 ~ 2021-11-29 17:30:00 時(shí)間內(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 恢復(fù)的日志中可以查看指定時(shí)間段內(nèi)的數(shù)據(jù)庫操作語句,找到誤 UPDATE、DELETE 語句可以看到被刪除的記錄屬性和屬性值,依據(jù)該日志可恢復(fù)該記錄。
總結(jié)
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
- MySQL開啟配置binlog及通過binlog恢復(fù)數(shù)據(jù)步驟詳析
- Mysql BinLog存儲(chǔ)機(jī)制與數(shù)據(jù)恢復(fù)方式
- MySQL誤刪后使用binlog恢復(fù)數(shù)據(jù)的實(shí)現(xiàn)方法
- MySQL使用binlog日志進(jìn)行數(shù)據(jù)庫遷移和數(shù)據(jù)恢復(fù)
- Mysql如何通過binlog日志恢復(fù)數(shù)據(jù)詳解
- mysql5.7使用binlog 恢復(fù)數(shù)據(jù)的方法
- mysql通過binlog定時(shí)備份數(shù)據(jù)庫與恢復(fù)的方法
相關(guān)文章
MySQL INSERT INTO SELECT時(shí)自增Id不連續(xù)問題及解決
這篇文章主要介紹了INSERT INTO SELECT時(shí)自增Id不連續(xù)問題及解決方案,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12發(fā)現(xiàn)mysql一個(gè)用法,比較有用
mysql下用select實(shí)現(xiàn)列運(yùn)算2008-02-02使用JS+HTML/CSS實(shí)現(xiàn)虛擬滾動(dòng)和分頁加載效果
虛擬滾動(dòng)和分頁加載是一種優(yōu)化大型數(shù)據(jù)集的常見技術(shù),用于在Web應(yīng)用程序中提高性能和用戶體驗(yàn),在本文中,我將演示如何使用JavaScript和HTML/CSS來實(shí)現(xiàn)虛擬滾動(dòng)和分頁加載,同時(shí)提供示例代碼和詳細(xì)解釋,需要的朋友可以參考下2023-10-10MySQL事務(wù)實(shí)現(xiàn)可重復(fù)讀
MySQL數(shù)據(jù)庫的默認(rèn)事務(wù)隔離級別是可重復(fù)讀,本文主要介紹了MySQL事務(wù)實(shí)現(xiàn)可重復(fù)讀,具有一定的參考價(jià)值,感興趣的可以了解一下2023-12-12MySQL數(shù)據(jù)庫主從復(fù)制與讀寫分離
大家好,本篇文章主要講的是MySQL數(shù)據(jù)庫主從復(fù)制與讀寫分離,感興趣的同學(xué)趕快來看一看吧,對你有幫助的話記得收藏一下,方便下次瀏覽2021-12-12