MySQL使用binlog日志回滾操作失誤的數(shù)據(jù)的操作教程
一、背景
在日常開(kāi)發(fā)運(yùn)維中，我們可能會(huì)出現(xiàn)一些操作失誤的情況，比如使用了錯(cuò)誤的 sql 語(yǔ)句對(duì) mysql 數(shù)據(jù)進(jìn)行了 update、delete 等操作，失誤操作導(dǎo)致數(shù)據(jù)出現(xiàn)問(wèn)題，又或者架構(gòu)設(shè)計(jì)上沒(méi)有使用軟刪除機(jī)制，用戶誤刪除了數(shù)據(jù)需要恢復(fù)。
那么如果出現(xiàn)這種情況，應(yīng)該如何恢復(fù)失誤/mysql 誤刪數(shù)據(jù)時(shí)，本文將使用幾種常用方法一步一步帶你回滾誤刪/誤操的數(shù)據(jù)
二、準(zhǔn)備測(cè)試數(shù)據(jù)
1. 創(chuàng)建測(cè)試表
create table jxy_pms.test_model
(
create_time bigint unsigned default 0 null,
update_time bigint unsigned default 0 null,
delete_time bigint unsigned default 0 null,
test_model_id varchar(255) not null
primary key,
name varchar(255) null,
remark varchar(255) null
)
collate = utf8mb4_unicode_ci;
2. 創(chuàng)建測(cè)試數(shù)據(jù)
insert into test_model (test_model_id, name, remark)
values ('1','test1','test-1'),
('2','test2','test-2'),
('3','test3','test-3');
select * from test_model
test_model_id name remark
1 test1 test-1
2 test2 test-2
3 test3 test-3
三、模擬誤操作
delete from test_model
四、數(shù)據(jù)回滾
（一）方案一：云數(shù)據(jù)庫(kù)恢復(fù)
如果你的 mysql 使用的時(shí)云數(shù)據(jù)庫(kù)，比如阿里云、騰訊云，那么你可以到云數(shù)據(jù)庫(kù)的控制臺(tái)操作面板進(jìn)行一鍵數(shù)據(jù)回滾，一般都可以選擇對(duì)應(yīng)的恢復(fù)區(qū)間，具體可以咨詢對(duì)應(yīng)的云數(shù)據(jù)庫(kù)提供商，一般都會(huì)提供技術(shù)支持
優(yōu)點(diǎn)：
- 低風(fēng)險(xiǎn)
- 操作簡(jiǎn)單
- 有云供應(yīng)商提供技術(shù)支持
缺點(diǎn)：
- 恢復(fù)精度較低，沒(méi)辦法恢復(fù)指定的數(shù)據(jù)
- 有可能恢復(fù)不全，云數(shù)據(jù)庫(kù)是定期備份，可能還沒(méi)來(lái)得及備份就被刪了
（二）方案二：手動(dòng)恢復(fù)
通過(guò)解讀 binlog 日志內(nèi)容，進(jìn)行解析然后回放數(shù)據(jù)
binlog 日志：大白話：記錄數(shù)據(jù)庫(kù)的每個(gè)修改操作 sql
所以我們只需要找到對(duì)應(yīng)的 binlog 日志中我們誤操作的 sql 數(shù)據(jù)，然后編寫對(duì)應(yīng)的回放sql，就可以回滾數(shù)據(jù)
實(shí)操如下：
1. 查詢 binlog 日志
show binary logs; 或 SHOW MASTER STATUS; mysql-bin.000014 80019706 No mysql-bin.000015 1326884 No mysql-bin.000016 3650781 No mysql-bin.000017 81424072 No mysql-bin.000018 46681992 No mysql-bin.000019 1075 No mysql-bin.000020 207322979 No
可以看到最新的日志為：mysql-bin.000020
2. 找到刪除語(yǔ)句，手動(dòng)還原為插入語(yǔ)句
mysqlbinlog --no-defaults --verbose --base64-output=DECODE-ROWS --start-datetime='2025-01-16 15:12:00' --stop-datetime='2025-01-16 15:15:00' /var/lib/mysql/mysql-bin.000027 > /home/DataVolume/rec.sql
- --base64-output=DECODE-ROWS 生成不加密的 sql 文件
- --start-datetime 、stop-datetime 數(shù)據(jù)操作的時(shí)間區(qū)間
從 rec.sql 中找到對(duì)應(yīng)的表 test_model 的刪除操作，如果找不到，調(diào)整下--start-datetime 和 stop-datetime
找到刪除的 sql 語(yǔ)句如下：
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ........ /*!*/; # at 207319108 #240919 10:58:30 server id 1 end_log_pos 207319202 CRC32 0xe7b80345 Table_map: `jxy_pms`.`asynccron_cron_task` mapped to number 306 #240919 10:59:04 server id 1 end_log_pos 207321626 CRC32 0x6b3323a9 Table_map: `jxy_pms`.`test_model` mapped to number 315 # at 207321626 #240919 10:59:04 server id 1 end_log_pos 207321718 CRC32 0xb004dad7 Delete_rows: table id 315 flags: STMT_END_F ### DELETE FROM `jxy_pms`.`test_model` ### WHERE ### @1='1' ### @2='test1' ### @3='test-1' ### DELETE FROM `jxy_pms`.`test_model` ### WHERE ### @1='2' ### @2='test2' ### @3='test-2' ### DELETE FROM `jxy_pms`.`test_model` ### WHERE ### @1='3' ### @2='test3' ### @3='test-3' # at 207321718 #240919 10:59:04 server id 1 end_log_pos 207321749 CRC32 0x90b858f7 Xid = 7486642 COMMIT/*!*/;
然后寫個(gè)腳本，解析這塊 sql，重新翻譯為 insert 語(yǔ)句即可
優(yōu)點(diǎn)：
- 適合一些少量數(shù)據(jù)的還原
- 能準(zhǔn)確還原指定的數(shù)據(jù)，操作簡(jiǎn)單
- 重新執(zhí)行的 sql，不會(huì)對(duì)已有的數(shù)據(jù)造成其他損壞
缺點(diǎn)：
- 不適合一些大量數(shù)據(jù)的還原
- 比較復(fù)雜，需要解讀 binlog 日志，并且需要編寫腳本
（三）方案三: 導(dǎo)入還原的 sql（未測(cè)試，慎用）
如果你的數(shù)據(jù)庫(kù)不是用的云數(shù)據(jù)庫(kù)，沒(méi)有一鍵恢復(fù)功能，然后你又不想用方案二，覺(jué)得太復(fù)雜，你可以直接將 binlog 日志直接全部還原
先導(dǎo)出 binlog 日志
mysqlbinlog --no-defaults --verbose --base64-output=DECODE-ROWS --start-datetime='2025-01-16 15:12:00' --stop-datetime='2025-01-16 15:15:00' /var/lib/mysql/mysql-bin.000027 > /home/DataVolume/rec.sql
這里會(huì)導(dǎo)出 rec.sql 文件
導(dǎo)入 sql
mysql -u root -p test < rec.sql
優(yōu)點(diǎn)：
- 相比較方案二簡(jiǎn)單一些
- 適合范圍恢復(fù)
缺點(diǎn)：
- 恢復(fù)精度較低，比如你可能只需要恢復(fù) 2025-01-16 15:12:00 這個(gè)點(diǎn)的某條錯(cuò)誤sql，但是這個(gè)點(diǎn)可能還有其他操作sql也會(huì)被一起恢復(fù)，
- 恢復(fù)期間的操作數(shù)據(jù)可能存在丟失
??：本方案沒(méi)有實(shí)際驗(yàn)證過(guò)，請(qǐng)自行測(cè)試
總結(jié)
如果你用的是云數(shù)據(jù)庫(kù)，并且恢復(fù)的數(shù)據(jù)量比較大，推薦【方案一】使用云數(shù)據(jù)庫(kù)提供的恢復(fù)功能（非常適用那么刪庫(kù)跑路的恢復(fù)，哈哈哈哈）
如果你只是想要恢復(fù)某個(gè)個(gè)點(diǎn)、某些錯(cuò)誤sql，那么可以使用【方案二】
以上就是MySQL使用binlog日志回滾操作失誤的數(shù)據(jù)的操作教程的詳細(xì)內(nèi)容，更多關(guān)于MySQL binlog回滾數(shù)據(jù)的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
Mysql按條件計(jì)數(shù)多種實(shí)現(xiàn)方法詳解
這篇文章主要介紹了Mysql按條件計(jì)數(shù)多種實(shí)現(xiàn)方法詳解,文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-07-07
這篇文章主要為大家詳細(xì)介紹了linux下使用RPM安裝mysql5.7.17的相關(guān)代碼，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-03-03
MySQL數(shù)據(jù)庫(kù)事務(wù)transaction示例講解教程
這篇文章主要為大家介紹了MySQL數(shù)據(jù)庫(kù)事務(wù)transaction的示例講解教程，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步2021-10-10
Mysql經(jīng)典的“8小時(shí)問(wèn)題”
MySQL 的默認(rèn)設(shè)置下,當(dāng)一個(gè)連接的空閑時(shí)間超過(guò)8小時(shí)后,MySQL 就會(huì)斷開(kāi)該連接,而 c3p0 連接池則以為該被斷開(kāi)的連接依然有效。2015-04-04
解決遠(yuǎn)程連接mysql很慢的方法(mysql_connect 打開(kāi)連接慢)
有次同事提出開(kāi)發(fā)使用的mysql數(shù)據(jù)庫(kù)連接很慢，因?yàn)槲覀兊膍ysql開(kāi)發(fā)數(shù)據(jù)庫(kù)是單獨(dú)一臺(tái)機(jī)器部署的，所以認(rèn)為可能是網(wǎng)絡(luò)連接問(wèn)題導(dǎo)致的。2011-07-07
詳解MySQL事務(wù)的ACID如何實(shí)現(xiàn)
事務(wù)(Transaction)是并發(fā)控制的基本單位,所謂的事務(wù)呢,它是一個(gè)操作序列,這些操作要么都執(zhí)行,要么都不執(zhí)行,它是一個(gè)不可分割的工作單位,本文給大家詳細(xì)介紹了MySQL事務(wù)的ACID如何實(shí)現(xiàn),需要的朋友可以參考下2023-10-10
MySQL普通表轉(zhuǎn)換為分區(qū)表實(shí)戰(zhàn)指南
本文將詳細(xì)指導(dǎo)新手開(kāi)發(fā)者如何將MySQL中的普通表轉(zhuǎn)換為分區(qū)表,分區(qū)表在處理龐大數(shù)據(jù)集時(shí)展現(xiàn)出顯著的性能優(yōu)勢(shì),不僅能大幅提升查詢速度,還能有效簡(jiǎn)化數(shù)據(jù)維護(hù)工作,文中有詳細(xì)的代碼示例供大家參考,需要的朋友可以參考下2024-06-06
在關(guān)系型數(shù)據(jù)庫(kù)里,索引是加速查詢的核心武器,索引失效不是索引壞了,而是優(yōu)化器沒(méi)有或不能利用索引的有序性來(lái)快速定位數(shù)據(jù),本文介紹了索引失效的底層原理與常見(jiàn)場(chǎng)景,掌握對(duì)應(yīng)的修復(fù)方法,能顯著提升 SQL性能,需要的朋友可以參考下2025-08-08
MySQL主要分為server層和存儲(chǔ)引擎層,server層負(fù)責(zé)連接、查詢緩存、分析、優(yōu)化和執(zhí)行,存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)存儲(chǔ)和提取,SQL查詢執(zhí)行流程包括連接、認(rèn)證、權(quán)限檢查、分析、優(yōu)化和執(zhí)行,更新語(yǔ)句執(zhí)行涉及重做日志（redolog）和歸檔日志（binlog）2024-11-11
winx64下mysql5.7.19的基本安裝流程(詳細(xì))
這篇文章主要介紹了winx64下mysql5.7.19的基本安裝流程,需要的朋友可以參考下2017-10-10