聊聊基於docker部署的mysql如何進行資料恢復

linyb極客之路發表於2022-05-31
DockerMySql資料恢復

前言

偶然的機會朋友說他部門的資料庫誤刪了，想恢復回來，他百度了一些資料，也跟著試了。但發現會報一些錯，於是他就找我幫忙看一下。對於我來說，因為公司的資料庫都是DBA在管控，平時都沒機會操作，基本上都停留在理論上。

但為了維持我在他心中的光輝形象，不能讓他看出我是個半吊子，我就裝逼讓他把錯誤的資訊發給我一下，我好百度一下。於是就有今天這麼一篇水文出現。

朋友的資料庫是基於docker搭建的，朋友一度懷疑是因為docker原因導致他恢復不成功。

資料恢復之binlog前置知識

注： 本文的例子為demo示例，mysql基於docker搭建。版本為mysql 8.0

1、先確認mysql是否已經開啟binlog
SHOW VARIABLES LIKE '%log_bin%';


注： mysql8.0預設就已經開啟binlog，且binlog-format為row格式。如果是mysql8.0以下，需通過/etc/my.cnf進行配置開啟。配置內容如下

##配置binlog日誌路徑以及日誌檔名字首、生成的日誌格式為：binlog.000001
log-bin=/var/lib/mysql/binlog
##注意配置server-id，保證唯一性
server-id=1
##binlog格式，有3種statement,row,mixed
binlog-format=row
2、檢視binlog日誌列表
SHOW MASTER LOGS;

3、檢視當前binlog以及當前位置
SHOW MASTER STATUS;

4、檢視binlog內容

SHOW BINLOG EVENTS IN 'binlog.000008';

5、指定位點查詢，比如從pos為746開始查詢
SHOW BINLOG EVENTS IN 'binlog.000008' FROM 746;

6、指定位點分頁查詢
SHOW BINLOG EVENTS IN 'binlog.000008' FROM 746 LIMIT 0,5

7、重新整理產生新binlog
FLUSH LOGS;

資料恢復案例

有這麼一張使用者表


假設我們誤刪了lisi這條記錄
在這裡插入圖片描述
現在我們想把lisi恢復回來。

1、檢視binlog
SHOW MASTER LOGS;

2、檢視binlog事件
SHOW BINLOG EVENTS IN 'binlog.000010';

在這裡插入圖片描述

3、通過指定位置恢復資料

/usr/bin/mysqlbinlog  --start-position=65945--stop-position=66150 --database=demo_test  /var/lib/mysql/binlog.000010 | /usr/bin/mysql -uroot -p123456 -v demo_test

注：

/usr/bin/mysqlbinlog 為binlog命令
--start-position=65945為恢復的開始位置
--stop-position=66150為恢復的結束位置
--database=demo_test 指定資料庫為demo_test
/var/lib/mysql/binlog.000010 為binlog日誌

| /usr/bin/mysql -uroot -p123456 -v demo_test
通過管道連線資料庫，並通過-v顯示詳細資訊

此時執行下去就會可能會出現

ERROR 1062 (23000) at line 19: Duplicate entry '1' for key 't_user.PRIMARY'

這個報錯也是朋友出現的問題。這種看字面的意思就是出現主鍵衝突了，此時的辦法有2種

a、 方法一：將資料庫產生衝突的記錄刪除，然後再執行恢復語句。此時檢視資料庫

在這裡插入圖片描述
就會發現資料已經恢復回來了

b、 方法二：就是將binlog匯出成sql語句，將insert語句改成replace into。

具體操作如下：

1、將binlog內容輸入到一個sql檔案


/usr/bin/mysqlbinlog  --start-position=65945 --stop-position=66150 --database=demo_test --base64-output=decode-rows -v  /var/lib/mysql/binlog.000010 --result-file=/var/lib/mysql/binlog000010.sql

2、將生成的sql檔案拷貝到宿主機

docker cp e0b7fb702f75:/var/lib/mysql/binlog000010.sql /

3、匯出來的檔案內容如下


注： 如果不加--base64-output=decode-rows，則輸出來的內容為base64加密內容

4、將insert into 改成replace into

5、將改好的sql語句重新匯入到docker裡面

docker cp /binlog000010.sql  e0b7fb702f75:/var/lib/mysql

6、執行sql語句

mysql -uroot -p123456 -f </var/lib/mysql/binlog000010.sql
4、通過時間恢復資料

先通過如下命令，檢視時間點

/usr/bin/mysqlbinlog --no-defaults /var/lib/mysql/binlog.000010 > /var/lib/mysql/bin_log000010.sql

注： 因日誌比較多，就先匯入到檔案再檢視


執行如下語句進行恢復


/usr/bin/mysqlbinlog --no-defaults --start-datetime="2022-04-25 16:10:00" --stop-datetime="2022-04-25 16:11:00" --database=demo_test /var/lib/mysql/binlog.000010 | /usr/bin/mysql -uroot -p123456 -v demo_test

注：

--start-datetime="2022-04-25 16:10:00" 為恢復的開始時間
-stop-datetime="2022-04-25 16:11:00" 為恢復的結束時間

執行語句，觀察資料庫，可以發現資料同樣恢復回來

總結

雖然以前就看過基於mysqlbinlog恢復資料的八股文，但一直沒有去嘗試。剛好借朋友這次機會，嘗試了一把。本文算是一次記錄吧。真的應了一句話紙上得來終覺淺,絕知此事要躬行。

相關文章