本篇文章適用場(chǎng)景

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

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

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

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

一、下載MyFlash工具

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

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

# 安裝編譯相關(guān)軟件
yum install gcc -y
yum install glib2 glib2-devel -y

# 解壓縮包
unzip master

# 進(jìn)入軟件目錄
cd /web/MyFlash-master

# 編譯
sh build.sh

編譯完成后：

驗(yàn)證：

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

輸入：

MyFlash工具 安裝完成

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

先檢查MySQL有沒(méi)有開(kāi)啟binlog日志

SHOW VARIABLES LIKE 'log_%';

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

下面進(jìn)入亡羊補(bǔ)牢時(shí)刻 (如果已經(jīng)開(kāi)啟了 跳過(guò)這個(gè)步驟)。

打開(kāi)MySQL的binlog日志：

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

一般情況下都是在 下面這個(gè)目錄

添加配置：

vi /etc/my.cnf

添加一下配置

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

重啟MysQL

# 如果 systemctl 無(wú)法重啟  就直接進(jìn)入mysqld所在的目錄重啟
systemctl restart mysqld

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

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

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

# 當(dāng)前使用的日志
show master status;

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

下面開(kāi)始演示：

先看下當(dāng)前使用的binlog日志是哪個(gè)

# 當(dāng)前使用的日志
show master status;

可以看到我目前的數(shù)據(jù)庫(kù)使用的是 mysql-bin.000003 這個(gè)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';

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

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

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

下面我們?nèi)ゲ榭碽inlog日志。

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

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

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

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

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

**注意圖中藍(lán)色標(biāo)注框 **
開(kāi)始： 11138303
結(jié)束： 11138917

利用MyFlash工具 反寫(xiě)SQL

把delete語(yǔ)句反寫(xiě)成insert語(yǔ)句

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ù)庫(kù)名稱(chēng)，表名稱(chēng)，sql類(lèi)型 要根據(jù)自身需求改動(dòng)，輸出的文件也可以根據(jù)需求改動(dòng)，我這里就輸出在/web目錄下了

尤其需要注意 --start-position 和 -stop-position 的數(shù)值 如果填的不對(duì) 很可能會(huì)報(bào)下面的錯(cuò)

如果報(bào)了下面的錯(cuò)就要好好檢查下 --start-position 和 -stop-position 的數(shù)值 取的對(duì)不對(duì)了

Segmentation fault (core dumped)

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

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

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

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

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

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

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

利用mysqlbinlog 執(zhí)行反寫(xiě)的sql二進(jìn)制文件

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

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

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

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

如果不報(bào)錯(cuò) 就說(shuō)明數(shù)據(jù)恢復(fù)完成了。

如果報(bào)錯(cuò)下面的錯(cuò)：

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

使用下面的語(yǔ)句查看是否開(kāi)啟了 全局事務(wù)ID (GTID) 功能

SELECT @@GLOBAL.GTID_MODE;

下圖就是開(kāi)啟了。

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

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

恢復(fù)完成

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

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

基本流程和上面誤刪除的恢復(fù)流程一致

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

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

誤更新前 user_id = 04f1fd53a4554e3fb5c9a40463a4ea4c

開(kāi)始誤更新

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

誤更新后：

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

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

SHOW MASTER STATUS;

SHOW BINLOG EVENTS IN 'mysql-bin.000004'  

這里再細(xì)說(shuō)下 找position的技巧吧

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

開(kāi)始： 1159

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

結(jié)束： 1929

利用MyFlash工具 反寫(xiě)SQL

這里update 還是會(huì)被反寫(xiě)成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í)行反寫(xiě)的sql二進(jìn)制文件

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

恢復(fù)完成

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

補(bǔ)充點(diǎn) flush logs

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

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

四、警告

非專(zhuān)業(yè)DBA請(qǐng)勿在生產(chǎn)環(huán)境操作上述過(guò)程~

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

最新評(píng)論