本篇文章適用場景

①、測試環(huán)境少量近期誤刪除或者誤更新的數(shù)據(jù)恢復。

②、測試環(huán)境少量從庫數(shù)據(jù)不一致問題。

備注：大量數(shù)據(jù)的恢復或者復制還是需要使用備份數(shù)據(jù),例如使用mysqldump或者Mydumper、mysqlshell。(本篇文章對此不做討論)

警告：數(shù)據(jù)恢復為DBA專業(yè)人員負責處理的事情，本文章僅為開發(fā)人員測試環(huán)境恢復近期誤操作的少量數(shù)據(jù)提供參考。
請對生產(chǎn)環(huán)境數(shù)據(jù)心存敬畏~

一、下載MyFlash工具

# 創(chuàng)建文件夾
mkdir  /web
cd /web

# 下載壓縮包
wget https://codeload.github.com/Meituan-Dianping/MyFlash/zip/master 

# 安裝編譯相關軟件
yum install gcc -y
yum install glib2 glib2-devel -y

# 解壓縮包
unzip master

# 進入軟件目錄
cd /web/MyFlash-master

# 編譯
sh build.sh

編譯完成后：

驗證：

cd /web/MyFlash-master/binary
./flashback --help

輸入：

MyFlash工具 安裝完成

二、誤刪數(shù)據(jù)恢復

先檢查MySQL有沒有開啟binlog日志

SHOW VARIABLES LIKE 'log_%';

如果發(fā)現(xiàn)數(shù)據(jù)庫未開啟binlog，那么這次恢復數(shù)據(jù)的旅程到此結束~

下面進入亡羊補牢時刻 (如果已經(jīng)開啟了 跳過這個步驟)。

打開MySQL的binlog日志：

# 找到MySQL的配置文件(一般情況下這樣都能找到，找不到就要去問DBA或者運維了)
find / -name my.cnf

一般情況下都是在 下面這個目錄

添加配置：

vi /etc/my.cnf

添加一下配置

# 如果有集群這個id不用重復
server_id =6
# binlog日志位置
log_bin =/web/mysql/binlog/mysql-bin
# binlog日志格式
binlog_format =row

重啟MysQL

# 如果 systemctl 無法重啟  就直接進入mysqld所在的目錄重啟
systemctl restart mysqld

演示誤刪除數(shù)據(jù)

先了解一些命令 后續(xù)要用到

# 查看所有binglog日志
SHOW MASTER LOGS;

# 當前使用的日志
show master status;

# 查看日志記錄
show binlog events in '日志文件名';

下面開始演示：

先看下當前使用的binlog日志是哪個

# 當前使用的日志
show master status;

可以看到我目前的數(shù)據(jù)庫使用的是 mysql-bin.000003 這個binlog日志

現(xiàn)在我有一張表 t_ph_uc_login

假如我誤刪了 t_ph_uc_login 表中的某條數(shù)據(jù)

這里我刪除 id為 10f7a6c619e14b228df0e226bd84db5c 的數(shù)據(jù)

DELETE FROM `t_ph_uc_login` WHERE id = '10f7a6c619e14b228df0e226bd84db5c';

此時再查已經(jīng)查不到這條數(shù)據(jù)了：

一般情況下我們并不知道到底刪除的是哪條數(shù)據(jù)，更不知道刪除數(shù)據(jù)的id。

我一開始說的前提是這種方法適合最近誤操作的數(shù)據(jù)恢復。

下面我們去查看binlog日志。

# 查看日志記錄
SHOW BINLOG EVENTS IN 'mysql-bin.000003';  

我查到了3258行，經(jīng)常更新的表一定比這個數(shù)值大得多。所以我們要查看最后幾十行就行。

這里利用分頁查看最后幾百條數(shù)據(jù)：

SHOW BINLOG EVENTS IN 'mysql-bin.000003' LIMIT 300 OFFSET 3000;

我們可以在最后幾行中找到 刪除相關的語句，并記錄下事務開始設置時的位置 (Pos列的數(shù)字)，和事務提交時的位置(End_log_pos列的數(shù)字)。

**注意圖中藍色標注框 **
開始： 11138303
結束： 11138917

利用MyFlash工具 反寫SQL

把delete語句反寫成insert語句

cd /web/MyFlash-master/binary
./flashback --databaseNames="phoenix-saas" --tableNames="t_ph_uc_login" --sqlTypes="delete" --start-position=11138303 --stop-position=11138917 --binlogFileNames=/web/mysql/binlog/mysql-bin.000003  --outBinlogFileNameBase=/web/recover.log

注意：上面數(shù)據(jù)庫名稱，表名稱，sql類型 要根據(jù)自身需求改動，輸出的文件也可以根據(jù)需求改動，我這里就輸出在/web目錄下了

尤其需要注意 --start-position 和 -stop-position 的數(shù)值 如果填的不對 很可能會報下面的錯

如果報了下面的錯就要好好檢查下 --start-position 和 -stop-position 的數(shù)值 取的對不對了

Segmentation fault (core dumped)

如果執(zhí)行成功 就能看到下面紅框的文件：

由于MyFlash工具反寫的數(shù)據(jù)也是二進制文件，所以還需要使用MySQL自帶的 mysqlbinlog工具來執(zhí)行

這里還可以用mysqlbinlog把二進制文件recover.log.flashback 解析成文本文件看下(這一步僅僅是看看 恢復數(shù)據(jù)可跳過這步)

# 先找一下mysqlbinlog在哪
find / -name mysqlbinlog
# 我的mysqlbinlog在/web/mysql/bin/mysqlbinlog文件夾中
cd /web/mysql/bin
# 解析查看二進制文件recover.log.flashback 
mysqlbinlog -v /web/recover.log.flashback

可以看到MyFlash工具 已經(jīng)把

上面我執(zhí)行的delete語句反寫成INSERT語句了

DELETE FROM `t_ph_uc_login` WHERE id = '10f7a6c619e14b228df0e226bd84db5c';

利用mysqlbinlog 執(zhí)行反寫的sql二進制文件

最后一步利用mysqlbinlog 工具執(zhí)行 反寫的二進制文件 recover.log.flashback

# 先到 mysqlbinlog 所在的文件夾
cd /web/mysql/bin

# 恢復數(shù)據(jù)
mysqlbinlog  -v /web/recover.log.flashback | mysql -u用戶名 -p密碼

注意上面要輸入自己數(shù)據(jù)庫的用戶名和密碼

如果不報錯 就說明數(shù)據(jù)恢復完成了。

如果報錯下面的錯：

ERROR 1782 (HY000) at line 17: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.

使用下面的語句查看是否開啟了 全局事務ID (GTID) 功能

SELECT @@GLOBAL.GTID_MODE;

下圖就是開啟了。

可以 通過參數(shù) --skip-gtids 跳過，不把gtid信息寫到binlog中。不過如果數(shù)據(jù)庫是多主或者一主多從的情況可能從庫會出現(xiàn)數(shù)據(jù)不一致。

#  --skip-gtids  跳過gtid信息
mysqlbinlog  -v /web/recover.log.flashback  --skip-gtids  | mysql -u用戶名 -p密碼

恢復完成

執(zhí)行完成后 再查詢被刪除的數(shù)據(jù)，可以發(fā)現(xiàn)已經(jīng)恢復了。

三、誤更新數(shù)據(jù)恢復

基本流程和上面誤刪除的恢復流程一致

演示誤更新數(shù)據(jù)

還是拿id為 10f7a6c619e14b228df0e226bd84db5c 這條數(shù)據(jù)測試：

誤更新前 user_id = 04f1fd53a4554e3fb5c9a40463a4ea4c

開始誤更新

UPDATE `t_ph_uc_login` SET user_id = '小明' WHERE id = '10f7a6c619e14b228df0e226bd84db5c';

誤更新后：

假如我們不知道 誤更新之前的user_id 是什么

查看binlog最近的更新記錄 ，確定起始、結束位置

SHOW MASTER STATUS;

SHOW BINLOG EVENTS IN 'mysql-bin.000004'  

這里再細說下 找position的技巧吧

這次是更新 那就先找 Update_rows ，找到后 往上找 INFO列的 BEGIN，再往上一行, SET @@SESSION.GTID_NEXT 設置全局事務ID的這行就是這次更新事務的始位置。開始position就確定了。

開始： 1159

順著Update_rows 再往下找，找到INFO列的 最近的一個COMMIT，這行的 End_log_pos列就是 結束position

結束： 1929

利用MyFlash工具 反寫SQL

這里update 還是會被反寫成update

cd /web/MyFlash-master/binary
./flashback --databaseNames="phoenix-saas" --tableNames="t_ph_uc_login" --sqlTypes="update" --start-position=1159 --stop-position=1929 --binlogFileNames=/web/mysql/binlog/mysql-bin.000004  --outBinlogFileNameBase=/web/recover.log

利用mysqlbinlog 執(zhí)行反寫的sql二進制文件

#  --skip-gtids  跳過gtid信息
mysqlbinlog  -v /web/recover.log.flashback  --skip-gtids  | mysql -u用戶名 -p密碼

恢復完成

再查一下 可以看到 user_id 已經(jīng)恢復了

補充點 flush logs

如果當前數(shù)據(jù)庫 還有大量連接正在更新 可以執(zhí)行flush logs 重新生成新的binlog日志

比如現(xiàn)在的日志名是001 執(zhí)行 flush logs 后 ，會生成一個002的文件 ，并且當前會使用002文件記錄
那你再去找001文件的position時 就不會受到干擾了

四、警告

非專業(yè)DBA請勿在生產(chǎn)環(huán)境操作上述過程~

到此這篇關于MySQL數(shù)據(jù)誤刪或者誤更新如何恢復的文章就介紹到這了,更多相關MySQL數(shù)據(jù)誤刪或者誤更新恢復內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論