通過binlog檔案恢複mysql資料的三種方式

由于資料庫被攻擊，導緻大量資料被篡改删除，還好做了資料庫的備份，于是記錄一下通過binlog日志恢複資料的過程。

1.首先確定的是資料庫開啟了binlog日志，可先使用指令

show variables like ‘%log_bin%’;

檢視是否開啟，如果Value 為 OFF 則未開啟日志檔案，可在my.cnf或my.ini 中 [mysqld] 添加排至

log-bin = /var/lib/mysql/mysql-bin.log

然後重新開機mysql資料庫即可，如需要可配置日志大小和過期時間等參數，一般來說開啟binlog日志大概會有1%的性能損耗。

2.開啟binlog日志後，會儲存在

/var/lib/mysql

目錄下，檔案名為mysql-bin.000001和mysql-bin.index的檔案，mysql-bin.index該檔案包含所有的二進制日志；mysql-bin.000001日志檔案是會在每次重新開機資料庫或者使用

FLUSH LOGS

指令會生成新的日志檔案，檔案編号+1（mysql-bin.000002），此時就可以通過binlog檔案使用指令恢複資料。

3.通過binlog恢複資料有三種方式，一種是通過binlog中的position id恢複,首先通過備份将資料導入資料庫，然後将後面缺失的資料庫操作通過binlog恢複

使用指令

mysqlbinlog --start-position=100 --stop-position=500 --database=test

/var/lib/mysql/mysql-bin.000001 | /usr/bin/mysql -u root -p passwd -v

database_name

我們可以使用指令檢視binlog檔案中的position id

show binlog events in ‘mysql-bin.000001’;

database_name為要恢複的資料庫名稱

這種是需要知道資料操作儲存在日志中的position id的起始位置，而這起始位置是不好判斷的，如果是重複的操作，那麼執行指令時，會抛出資料重複的異常并停止執行；

這時使用第二種方式，通過時間點恢複是比較好的選擇

mysqlbinlog --start-datetime=“2020-03-20 10:00:00” --stop-datetime=“2020-03-21 10:00:00” mysql-bin.000001 | mysql -u root -passwd database_name

database_name為要恢複的資料庫名稱

–start-datetime 恢複的起始時間

–stop-datetime 恢複的結束時間

但是通過這種方式恢複資料，也會有着相同的問題，如果在同一時間點進行了多個操作導緻資料重複，也會報錯停止執行，這時我們也不好判斷到底是哪條指令導緻重複；

這時第三種方式可能更加适合我們，我們可以先使用指令轉換成sql檔案

mysqlbinlog --start-datetime=“2020-03-20 10:00:00” --stop-datetime=“2020-03-21 10:00:00” mysql-bin.000001 -d database_name> filename_binlog.sql

database_name為binlog檔案中需要恢複資料的mysql資料庫名稱（可能存在多個庫操作儲存在一個binlog檔案）;

如果執行指令報錯:

unknown variable ‘default-character-set=utf8’

是因為mysqlbinlog這個工具無法識别binlog中的配置中的default-character-set=utf8這個指令，我們在指令中加入-no-defaults即可

mysqlbinlog --no-defaults --start-datetime=“2020-03-20 10:00:00” --stop-datetime=“2020-03-21 10:00:00” mysql-bin.000001 -d database_name> filename_binlog.sql

但是轉化成的sql檔案不能直接導入mysql資料庫的方式執行，它本質上還是一個二進制的文本檔案，我們可能通過

source filename_binlog.sql

執行檔案，這種方式在遇到錯誤時會繼續執行，就能比較好的避免上面說到的遇到資料重複時，報錯停止執行的問題，将缺失的資料操作執行下去。

以上，因為是一段時間後記錄，可能出現遺漏和和偏差，歡迎補充指導。