文章目錄

• 前言

• binlog 基本認(rèn)識

• 預(yù)備工作:開啟binlog日志

• 查看二進制日志是否已開啟

• 常用binlog日志操作命令

• mysqlbinlog命令使用

• binlog的三種工作模式

• 數(shù)據(jù)恢復(fù)操作

• 通過日志方式恢復(fù)部分記錄

• 通過日志導(dǎo)出為sql語句方式回復(fù)記錄

• MySQL Relay log介紹

• 參考資料

前言

刪庫跑路易操作，恢復(fù)數(shù)據(jù)如登天。
前人跑路坦蕩蕩，后人接盤長戚戚。

多少刪庫跑路的故事在坊間流傳，每聞壯舉心中蕩氣回腸，讓人且為驚喜，你心中久久義憤的壯舉終于成為他人隨手一擲而巧妙幻化的煙花般璀璨奪目。但你不是過客你是歸人，你是煙花浪漫之后的清潔工，你是巴山夜雨加班趕路的路人甲，沒有踩局南山下的瀟灑，只有午夜燈明后的落寞和無可奈何。

binlog 基本認(rèn)識

MySQL的二進制日志可以說是MySQL最重要的日志了，它記錄了所有的DDL和DML(除了數(shù)據(jù)查詢語句)語句，以事件形式記錄，還包含語句所執(zhí)行的消耗的時間，MySQL的二進制日志是事務(wù)安全型的。
一般來說開啟二進制日志大概會有1%的性能損耗(參見MySQL官方中文手冊 5.1.24版)。二進制有兩個最重要的使用場景:
其一：MySQL Replication在Master端開啟binlog，Mster把它的二進制日志傳遞給slaves來達到master-slave數(shù)據(jù)一致的目的。
其二：自然就是數(shù)據(jù)恢復(fù)了，通過使用mysqlbinlog工具來使恢復(fù)數(shù)據(jù)。

二進制日志包括兩類文件：
二進制日志索引文件（文件名后綴為.index）用于記錄所有的二進制文件，
二進制日志文件（文件名后綴為.00000*）記錄數(shù)據(jù)庫所有的DDL和DML(除了數(shù)據(jù)查詢語句)語句事件。

預(yù)備工作:開啟binlog日志

vi編輯打開mysql配置文件

確認(rèn)是打開狀態(tài)(值 mysql-bin 是日志的基本名或前綴名)；因為mysql默認(rèn)的是關(guān)閉日志，然后binary-log為產(chǎn)生日志文件的文件名，此文件在C:\ProgramData\MySQL\MySQL Server 5.7\Data下面可以看到 binary-log.000001文件這就是產(chǎn)生的日志二進制文件

重啟mysqld服務(wù)使配置生效

查看二進制日志是否已開啟

通過mysql的變量配置表

如果log_bin是OFF那么你就不要想著能夠回復(fù)數(shù)據(jù)了。因此正式工程項目必須開啟log_bin日志,以及其他盡可能的定期備份和熱備方案。

參數(shù)說明：

常用binlog日志操作命令

常用binlog日志操作命令
1.查看所有binlog日志列表

2.查看master狀態(tài)，即最后(最新)一個binlog日志的編號名稱，及其最后一個操作事件pos結(jié)束點(Position)值

3.刷新log日志，自此刻開始產(chǎn)生一個新編號的binlog日志文件

注：每當(dāng)mysqld服務(wù)重啟時，會自動執(zhí)行此命令，刷新binlog日志；在mysqldump備份數(shù)據(jù)時加 -F 選項也會刷新binlog日志；

4.重置(清空)所有binlog日志,編號也會重新開始,重要數(shù)據(jù)中禁止使用或者提前把日志備份

作用包括：
刪除binlog索引文件中列出的所有binlog文件
清空binlog索引文件
創(chuàng)建一個新的binlog文件
清空系統(tǒng)變量gtid_purged和gtid_executed
在MySQL 5.7.5 及后續(xù)版本中, RESET MASTER還會會清空 mysql.gtid_executed數(shù)據(jù)表。

在slave上執(zhí)行

作用包括：
清除slave 復(fù)制時的master binlog的位置
清空master info, relay log info
刪除所有的relay log文件，并創(chuàng)建一個新的relay log文件。
重置復(fù)制延遲(CHANGE MASTER TO 的 MASTER_DELAY參數(shù)指定的)為0。
另外，
RESET SLAVE 不會改變gtid_executed or gtid_purged.
RESET SLAVE 不會改變復(fù)制連接使用的參數(shù)，例如master host, master port, master user, or master password

5.查看binlog日志內(nèi)容（以表格形式）

mysqlbinlog命令使用

mysqlbinlog功能是將mysql的binlog日志轉(zhuǎn)換成Mysql語句，默認(rèn)情況下binlog日志是二進制文件，無法直接查看。我們直接在mysql目錄的bin目錄下啟動該命令。
　　（在MySQL5.5以下版本使用mysqlbinlog命令時如果報錯，就加上 “–no-defaults”選項）
　　
mysqlbinlog命令部分參數(shù)：

備注：myslqlbinlog分庫導(dǎo)出binlog，如使用-d參數(shù)，更新數(shù)據(jù)時必須使用use database
例：解析yj-test數(shù)據(jù)庫的binlog日志并寫入my.sql文件

使用位置精確解析binlog日志

binlog的三種工作模式

一共有三種模式
Row，Statement （默認(rèn)） ，Mixed
（1）Row level
日志中會記錄成每一行數(shù)據(jù)被修改的形式（不包括查詢），然后在slave端再對相同的數(shù)據(jù)進行修改，只記錄要修改的數(shù)據(jù)，不會有sql多表關(guān)聯(lián)的情況。
優(yōu)點：在row模式下，bin-log中可以不記錄執(zhí)行的sql語句的上下文相關(guān)的信息，僅僅只需要記錄那一條記錄被修改了，修改成什么樣了，所以row的日志內(nèi)容會非常清楚的記錄下每一行數(shù)據(jù)修改的細節(jié)，非常容易理解。而且不會出現(xiàn)某些特定情況下的存儲過程和function，以及trigger的調(diào)用和出發(fā)無法被正確復(fù)制問題。
缺點：在row模式下，所有的執(zhí)行的語句當(dāng)記錄到日志中的時候，都將以每行記錄的修改來記錄，這樣可能會產(chǎn)生大量的日志內(nèi)容。

（2）Statement level（默認(rèn)）
　　每一條被修改數(shù)據(jù)的sql都會記錄到master的bin-log中，slave在復(fù)制的時候sql進程會解析成和原來master端執(zhí)行過的相同的sql再次執(zhí)行

優(yōu)點：解決了 Row level下的缺點，不需要記錄每一行的數(shù)據(jù)變化，減少bin-log日志量，節(jié)約磁盤IO，提高性能
缺點：在statement模式下，由于他是記錄的執(zhí)行語句，所以，為了讓這些語句在slave端也能正確執(zhí)行，那么他還必須記錄每條語句在執(zhí)行的時候的一些相關(guān)信息，也就是上下文信息，以保證所有語句在slave端被執(zhí)行的時候能夠得到和在master端執(zhí)行時候相同的結(jié)果。版本和兼容性問題會造成部分函數(shù)無法執(zhí)行從而影響復(fù)制效果：

由于mysql現(xiàn)在發(fā)展比較快，很多的新功能不斷的加入，使mysql的復(fù)制遇到了不小的挑戰(zhàn)，自然復(fù)制的時候涉及到越復(fù)雜的內(nèi)容，bug也就越容易出現(xiàn)。在statement中，目前已經(jīng)發(fā)現(xiàn)不少情況會造成Mysql的復(fù)制出現(xiàn)問題，主要是修改數(shù)據(jù)的時候使用了某些特定的函數(shù)或者功能的時候會出現(xiàn)，比如：sleep()函數(shù)在有些版本中就不能被正確復(fù)制，在存儲過程中使用了last_insert_id()函數(shù)，可能會使slave和master上得到不一致的id等等。由于row是基于每一行來記錄的變化，所以不會出現(xiàn)，類似的問題。

（3）Mixed（混合模式）
　　結(jié)合了Row level和Statement level的優(yōu)點。
　　在默認(rèn)情況下是statement,但是在某些情況下會切換到row狀態(tài)，如當(dāng)一個DML更新一個ndb引擎表，或者是與時間用戶相關(guān)的函數(shù)等。在主從的情況下，在主機上如果是STATEMENT模式，那么binlog就是直接寫now()，然而如果這樣的話，那么從機進行操作的時間，也執(zhí)行now()，但明顯這兩個時間不會是一樣的，所以對于這種情況就必須把STATEMENT模式更改為ROW模式，因為ROW模式會直接寫值而不是寫語句（該案例是錯誤的，即使是STATEMENT模式也可以使用now()函數(shù)，具體原因以后再分析）。同樣ROW模式還可以減少從機的相關(guān)計算，如在主機中存在統(tǒng)計寫入等操作時，從機就可以免掉該計算把值直接寫入從機。

一般企業(yè)binlog模式的選擇：
互聯(lián)網(wǎng)公司使用MySQL的功能較少（不用存儲過程、觸發(fā)器、函數(shù)），選擇默認(rèn)的Statement level；
用到MySQL的特殊功能（存儲過程、觸發(fā)器、函數(shù)）則選擇Mixed模式；
用到MySQL的特殊功能（存儲過程、觸發(fā)器、函數(shù)），又希望數(shù)據(jù)最大化一直則選擇Row模式；

MySql中查詢?nèi)罩鞠嚓P(guān)：
show variables like ‘log_bin’;
show variables like ‘%general_log%’;
show variables like ‘%log_%’;

數(shù)據(jù)恢復(fù)操作

主要包括三種方式
0.使用備份數(shù)據(jù)恢復(fù)歷史數(shù)據(jù)
1.直接使用日志文件節(jié)點回復(fù)
2.通過日志文件轉(zhuǎn)成sql語句執(zhí)行sql語句恢復(fù)

后兩種方式都需要啟用了日志記錄，否則無法操作。同樣，日志記錄理論上只能回復(fù)日志記錄中的數(shù)據(jù)，如果在此之前沒有日志記錄（沒有啟用日志或者已經(jīng)刪除了歷史日志）那么也無法還原數(shù)據(jù)。

如同數(shù)據(jù)量非常之大，那么數(shù)據(jù)整理恢復(fù)的過程將異常的艱辛，因此盡量做到定期備份和日志記錄同時開啟為妙。這樣恢復(fù)數(shù)據(jù)時先啟用備份，而后從備份數(shù)據(jù)節(jié)點之后的開始執(zhí)行到刪庫和刪數(shù)據(jù)之前的節(jié)點即可。

通過日志方式恢復(fù)部分記錄

1.查看數(shù)據(jù)庫是否開啟了binglog日志，如果為NO則繼續(xù)往下看，否則就別想了，除非你只想恢復(fù)備份過的數(shù)據(jù)，如果連備份的數(shù)據(jù)都沒有那就啥都不用做了

2.查看日志的當(dāng)前記錄

3.根據(jù)日志查找最近刪除的數(shù)據(jù)記錄pos

Event_type：會記錄每次插入，修改，和刪除記錄，刪除表，刪庫的記錄類型
Write_rows ：插入記錄
Update_rows：修改記錄
Delete_rows：刪除記錄
Query：包含了創(chuàng)建庫，創(chuàng)建表和刪除庫刪除表的記錄（select是不包含的沒有記錄的必要）

4.查找關(guān)鍵節(jié)點的pos記錄（盡量包含始末節(jié)點位置）
以這里我們刪除的記錄Delete_rows 為例，要恢復(fù)它刪除的數(shù)據(jù)需要執(zhí)行到它的記錄之前的pos,即1877

5.回復(fù)記錄
如果我們不設(shè)置起始pos則它默認(rèn)從日志記錄的最開始的pos開始記錄，由于所有的表創(chuàng)建語句都在其中那么這個數(shù)據(jù)回復(fù)記錄會出異常：
Table ‘test’ already exists；同理，如果回復(fù)的語句中存在已經(jīng)插入過的數(shù)據(jù)記錄則也會造成主鍵沖突Duplicate entry ‘1’ for key ‘PRIMARY’

因此我們只執(zhí)行第三條記錄插入前的pos開始執(zhí)行，到它提交成功為止，如下則數(shù)據(jù)插入成功，可以看到記錄已經(jīng)回來了

通過日志導(dǎo)出為sql語句方式回復(fù)記錄

另一種方式，把日志記錄導(dǎo)出sql語句，整理清除一些建表，插入數(shù)據(jù)的記錄，主要是避免這些語句會造成的建表沖突和數(shù)據(jù)插入沖突
先導(dǎo)出日志記錄到mysql，sql語句整理（刪除一些建表語句，數(shù)據(jù)插入語句）
然后通過mysql source操作進行數(shù)據(jù)恢復(fù)

MySQL Relay log介紹

在MySQL主從復(fù)制結(jié)構(gòu)下，Slave服務(wù)器會產(chǎn)生三種日志文件，用來保存主庫的二進制日志事件以及relay log已執(zhí)行到的位置和狀態(tài)。
1、relay log 文件：由IO thread線程從主庫讀取的二進制日志事件組成，該日志被Slave上的SQL thread線程執(zhí)行，從而實現(xiàn)數(shù)據(jù)的復(fù)制。
2、master info log：該文件保存slave連接master的狀態(tài)以及配置信息，如用戶名，密碼，日志執(zhí)行的位置等。在5.6版本之前，都是使用master.info文件，從5.6開始，通過在my.cnf 中配置 --master-info-repository=TABLE。這些信息會被寫入mysql.slave_master_info 表中，代替原來的master.info文件了。
3、relay log info log：該文件保存slave上relay log的執(zhí)行位置。在5.6版本之前，都是使用relay-log.info文件，從5.6開始，通過在my.cnf中配置 --relay-log-info-repository=TABLE，使用mysql.slave_relay_log_info表代替原來的文件。每次當(dāng)slave上執(zhí)行start slave時，就會讀取該表中的位置信息。

新版本使用表來代替原來的文件，主要為了crash-safe replication，從而大大提高從庫的可靠性。為了保證意外情況下從庫的可靠性，mysql.slave_master_info和mysql.slave_relay_log_info表必須為事務(wù)性的表，從5.6.6起，這些表默認(rèn)使用InnoDB存儲引擎。在5.6.5及之前的版本默認(rèn)使用MyISAM引擎，可用下面語句進行轉(zhuǎn)換：

ALTER TABLE mysql.slave_master_info ENGINE=InnoDB;
ALTER TABLE mysql.slave_relay_log_info ENGINE=InnoDB;
【注意】不要試圖手工的更新、插入、刪除以上兩個表的內(nèi)容，以免出現(xiàn)意料不到的問題。

參考資料

https://blog.csdn.net/a7442358/article/details/47355515