MySQL使用binlog日志回滾操作失誤的數(shù)據(jù)的操作教程
一、背景
在日常開發(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中使用FREDATED引擎實(shí)現(xiàn)跨數(shù)據(jù)庫(kù)服務(wù)器、跨實(shí)例訪問(wèn)
這篇文章主要介紹了MySQL中使用FREDATED引擎實(shí)現(xiàn)跨數(shù)據(jù)庫(kù)服務(wù)器、跨實(shí)例訪問(wèn),本文講解了FEDERATED存儲(chǔ)引擎的描述、安裝與啟用FEDERATED存儲(chǔ)引擎、準(zhǔn)備遠(yuǎn)程服務(wù)器環(huán)境等內(nèi)容,需要的朋友可以參考下2014-10-10
MYSQL數(shù)據(jù)庫(kù)使用UTF-8中文編碼亂碼的解決辦法
這篇文章主要介紹了MYSQL數(shù)據(jù)庫(kù)使用UTF-8中文編碼亂碼的解決辦法，需要的朋友可以參考下2015-10-10
mvcc多版本并發(fā)控制是一種數(shù)據(jù)庫(kù)的并發(fā)控制機(jī)制,本文主要介紹了MySQL多版本并發(fā)控制mvcc原理淺析,具有一定的參考價(jià)值,感興趣的可以了解一下2024-07-07
K8s中MySQL?數(shù)據(jù)持久化存儲(chǔ)的實(shí)現(xiàn)
本文主要介紹了K8s中MySQL?數(shù)據(jù)持久化存儲(chǔ)的實(shí)現(xiàn)，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01
新裝MySql后登錄出現(xiàn)root帳號(hào)提示mysql ERROR 1045 (28000): Access denied
這篇文章主要介紹了新裝MySql后登錄出現(xiàn)root帳號(hào)提示mysql ERROR 1045 (28000): Access denied for use的解決辦法,需要的朋友可以參考下2017-01-01
winx64下mysql5.7.19的基本安裝流程(詳細(xì))
這篇文章主要介紹了winx64下mysql5.7.19的基本安裝流程,需要的朋友可以參考下2017-10-10
MySQL樂(lè)觀鎖和悲觀鎖具體實(shí)現(xiàn)
這篇文章主要介紹了MySQL樂(lè)觀鎖和悲觀鎖具體實(shí)現(xiàn)，文章圍繞主題展開詳細(xì)的內(nèi)容戒殺，具有一定的參考價(jià)值，需要的小伙伴可以參考一下2022-09-09
Mysql系統(tǒng)變量與狀態(tài)變量詳細(xì)介紹
這篇文章主要介紹了Mysql系統(tǒng)變量與狀態(tài)變量詳細(xì)介紹，能夠在程序運(yùn)行過(guò)程中影響Mysql程序行為的變量稱之為系統(tǒng)變量，想了解更多相關(guān)內(nèi)容的小伙伴可以參考下面文章內(nèi)容2022-09-09
Mysql命令行導(dǎo)入sql數(shù)據(jù)的代碼
Mysql命令行導(dǎo)入sql數(shù)據(jù)的實(shí)現(xiàn)方法是我們經(jīng)常會(huì)用到的，下面就為你詳細(xì)介紹Mysql命令行導(dǎo)入sql數(shù)據(jù)的方法步驟，希望對(duì)您學(xué)習(xí)Mysql命令行方面能有所幫助。2010-12-12
MySQL Version確認(rèn)問(wèn)題(版本確認(rèn))
這篇文章主要介紹了MySQL Version確認(rèn)問(wèn)題(版本確認(rèn))，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-12-12