MySQL使用binlog日志回滾操作失誤的數(shù)據(jù)的操作教程
一、背景
在日常開發(fā)運(yùn)維中，我們可能會出現(xiàn)一些操作失誤的情況，比如使用了錯(cuò)誤的 sql 語句對 mysql 數(shù)據(jù)進(jìn)行了 update、delete 等操作，失誤操作導(dǎo)致數(shù)據(jù)出現(xiàn)問題，又或者架構(gòu)設(shè)計(jì)上沒有使用軟刪除機(jī)制，用戶誤刪除了數(shù)據(jù)需要恢復(fù)。
那么如果出現(xiàn)這種情況，應(yīng)該如何恢復(fù)失誤/mysql 誤刪數(shù)據(jù)時(shí)，本文將使用幾種常用方法一步一步帶你回滾誤刪/誤操的數(shù)據(jù)
二、準(zhǔn)備測試數(shù)據(jù)
1. 創(chuàng)建測試表
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)建測試數(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ù)庫恢復(fù)
如果你的 mysql 使用的時(shí)云數(shù)據(jù)庫，比如阿里云、騰訊云，那么你可以到云數(shù)據(jù)庫的控制臺操作面板進(jìn)行一鍵數(shù)據(jù)回滾，一般都可以選擇對應(yīng)的恢復(fù)區(qū)間，具體可以咨詢對應(yīng)的云數(shù)據(jù)庫提供商，一般都會提供技術(shù)支持
優(yōu)點(diǎn)：
- 低風(fēng)險(xiǎn)
- 操作簡單
- 有云供應(yīng)商提供技術(shù)支持
缺點(diǎn)：
- 恢復(fù)精度較低，沒辦法恢復(fù)指定的數(shù)據(jù)
- 有可能恢復(fù)不全，云數(shù)據(jù)庫是定期備份，可能還沒來得及備份就被刪了
（二）方案二：手動恢復(fù)
通過解讀 binlog 日志內(nèi)容，進(jìn)行解析然后回放數(shù)據(jù)
binlog 日志：大白話：記錄數(shù)據(jù)庫的每個(gè)修改操作 sql
所以我們只需要找到對應(yīng)的 binlog 日志中我們誤操作的 sql 數(shù)據(jù)，然后編寫對應(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. 找到刪除語句，手動還原為插入語句
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 中找到對應(yīng)的表 test_model 的刪除操作，如果找不到，調(diào)整下--start-datetime 和 stop-datetime
找到刪除的 sql 語句如下：
/*!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ōu)點(diǎn)：
- 適合一些少量數(shù)據(jù)的還原
- 能準(zhǔn)確還原指定的數(shù)據(jù)，操作簡單
- 重新執(zhí)行的 sql，不會對已有的數(shù)據(jù)造成其他損壞
缺點(diǎn)：
- 不適合一些大量數(shù)據(jù)的還原
- 比較復(fù)雜，需要解讀 binlog 日志，并且需要編寫腳本
（三）方案三: 導(dǎo)入還原的 sql（未測試，慎用）
如果你的數(shù)據(jù)庫不是用的云數(shù)據(jù)庫，沒有一鍵恢復(fù)功能，然后你又不想用方案二，覺得太復(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
這里會導(dǎo)出 rec.sql 文件
導(dǎo)入 sql
mysql -u root -p test < rec.sql
優(yōu)點(diǎn)：
- 相比較方案二簡單一些
- 適合范圍恢復(fù)
缺點(diǎn)：
- 恢復(fù)精度較低，比如你可能只需要恢復(fù) 2025-01-16 15:12:00 這個(gè)點(diǎn)的某條錯(cuò)誤sql，但是這個(gè)點(diǎn)可能還有其他操作sql也會被一起恢復(fù)，
- 恢復(fù)期間的操作數(shù)據(jù)可能存在丟失
??：本方案沒有實(shí)際驗(yàn)證過，請自行測試
總結(jié)
如果你用的是云數(shù)據(jù)庫，并且恢復(fù)的數(shù)據(jù)量比較大，推薦【方案一】使用云數(shù)據(jù)庫提供的恢復(fù)功能（非常適用那么刪庫跑路的恢復(fù)，哈哈哈哈）
如果你只是想要恢復(fù)某個(gè)個(gè)點(diǎn)、某些錯(cuò)誤sql，那么可以使用【方案二】
以上就是MySQL使用binlog日志回滾操作失誤的數(shù)據(jù)的操作教程的詳細(xì)內(nèi)容，更多關(guān)于MySQL binlog回滾數(shù)據(jù)的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
MySQL中使用FREDATED引擎實(shí)現(xiàn)跨數(shù)據(jù)庫服務(wù)器、跨實(shí)例訪問
這篇文章主要介紹了MySQL中使用FREDATED引擎實(shí)現(xiàn)跨數(shù)據(jù)庫服務(wù)器、跨實(shí)例訪問,本文講解了FEDERATED存儲引擎的描述、安裝與啟用FEDERATED存儲引擎、準(zhǔn)備遠(yuǎn)程服務(wù)器環(huán)境等內(nèi)容,需要的朋友可以參考下2014-10-10
MYSQL數(shù)據(jù)庫使用UTF-8中文編碼亂碼的解決辦法
這篇文章主要介紹了MYSQL數(shù)據(jù)庫使用UTF-8中文編碼亂碼的解決辦法，需要的朋友可以參考下2015-10-10
mvcc多版本并發(fā)控制是一種數(shù)據(jù)庫的并發(fā)控制機(jī)制,本文主要介紹了MySQL多版本并發(fā)控制mvcc原理淺析,具有一定的參考價(jià)值,感興趣的可以了解一下2024-07-07
K8s中MySQL?數(shù)據(jù)持久化存儲的實(shí)現(xiàn)
本文主要介紹了K8s中MySQL?數(shù)據(jù)持久化存儲的實(shí)現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01
新裝MySql后登錄出現(xiàn)root帳號提示mysql ERROR 1045 (28000): Access denied
這篇文章主要介紹了新裝MySql后登錄出現(xiàn)root帳號提示mysql ERROR 1045 (28000): Access denied for use的解決辦法,需要的朋友可以參考下2017-01-01
winx64下mysql5.7.19的基本安裝流程(詳細(xì))
這篇文章主要介紹了winx64下mysql5.7.19的基本安裝流程,需要的朋友可以參考下2017-10-10
這篇文章主要介紹了MySQL樂觀鎖和悲觀鎖具體實(shí)現(xiàn)，文章圍繞主題展開詳細(xì)的內(nèi)容戒殺，具有一定的參考價(jià)值，需要的小伙伴可以參考一下2022-09-09
Mysql系統(tǒng)變量與狀態(tài)變量詳細(xì)介紹
這篇文章主要介紹了Mysql系統(tǒng)變量與狀態(tài)變量詳細(xì)介紹，能夠在程序運(yùn)行過程中影響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)常會用到的，下面就為你詳細(xì)介紹Mysql命令行導(dǎo)入sql數(shù)據(jù)的方法步驟，希望對您學(xué)習(xí)Mysql命令行方面能有所幫助。2010-12-12
MySQL Version確認(rèn)問題(版本確認(rèn))
這篇文章主要介紹了MySQL Version確認(rèn)問題(版本確認(rèn))，具有很好的參考價(jià)值，希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-12-12