1. Binlog 日誌介紹
Binlog 是記錄所有資料庫表結構變更以及表資料修改的二進位制 日誌，不會記錄 SELECT 和 SHOW 這類操作。Binlog 日誌是以事件形式記錄，還包含語句所執行的 消耗時間。開啟 Binlog 日誌有以下兩個最重要的使用場景。
主從複製：在主庫中開啟 Binlog 功能，這樣主庫就可以把 Binlog 傳遞給從庫，從庫拿到 Binlog 後實現資料恢復達到主從資料一致性。
資料恢復：透過 mysqlbinlog 工具來恢復資料。
Binlog 檔名預設為“主機名_binlog-序列號”格式，例如 oak_binlog-000001，也可以在配置檔案 中指定名稱。檔案記錄模式有 STATEMENT、ROW 和 MIXED 三種，具體含義如下。
ROW（row-based replication, RBR）：日誌中會記錄每一行資料被修改的情況，然後在 slave 端對相同的資料進行修改。
優點：能清楚記錄每一個行資料的修改細節，能完全實現主從資料同步和資料的恢復。
缺點：批次操作，會產生大量的日誌，尤其是 alter table 會讓日誌暴漲。
STATMENT（statement-based replication, SBR）：每一條被修改資料的 SQL 都會記錄到 master 的 Binlog 中，slave 在複製的時候 SQL 程序會解析成和原來 master 端執行過的相同的 SQL 再次執行。簡稱 SQL 語句複製。
優點：日誌量小，減少磁碟 IO，提升儲存和恢復速度
缺點：在某些情況下會導致主從資料不一致，比如 last_insert_id()、now()等函式。
MIXED（mixed-based replication, MBR）：以上兩種模式的混合使用，一般會使用 STATEMENT 模式儲存 binlog，對於 STATEMENT 模式無法複製的操作使用 ROW 模式儲存 binlog，MySQL 會根據執行的 SQL 語句選擇寫入模式。
- 理念
首先使用binlog恢復資料，指的不是透過回滾binlog日誌實現恢復的，而是指的是將刪除的資料，透過一定的方式找回後，重新匯入到該資料庫，進行恢復的。
3、實戰
3.1 Binlog 狀態檢視
show variables like 'log_bin';
我這裡是已經開啟的，如果未開啟，請按照下面方法開啟
3.2 開啟 Binlog 功能
修改 /etc/my.cnf 或 my.ini 配置檔案，在[mysqld]下面增加 log_bin=mysql-bin，重啟 MySQL 服務。
log-bin=mysql-bin
binlog_format=ROW
3.3 再次檢視
show variables like 'log_bin';
常用命令
show binary logs; //等價於show master logs;
show master status;
show binlog events;
show binlog events in 'mysql-bin.000001';
在伺服器中也可以直接使用如下命令：
mysqlbinlog （引數選項）logfilename
引數選項：
-d :指定資料庫名稱，只列出指定的資料庫相關操作。
-o : 忽 略 掉 日 志 中 的 前 n 行 命 令 。
-v :將事件重構成sql語句
-vv ： 將事件重構成sql語句，並且輸出註釋資訊
4、方案一
4.1、前提條件：
a:開啟了binlog日誌功能
b:之前的binlog日誌未清除
c:此種方案可以實現binlog_format 為MIXED 和ROW的資料恢復
4.2、開始恢復
檢視日誌，大致確定下需要恢復的資料位於哪個日誌檔案中
show binary logs;
檢視 binlog 日誌事件並確定資料的開始和結束的offset
注意：我們恢復資料，這種方式不是透過刪除的事件恢復的，是透過之前建立事件恢復的，所以我們要看清楚offset的位置
執行恢復：在 mysql 資料所在的檔案下，也可以不在，如果不在請補全檔案路徑
//按指定時間恢復
mysqlbinlog --no-defaults --start-datetime="2021-07-09 12:10:49" --stopdatetime="2021-07-09 12:17:37" mysql-binlog.000008 | mysql -uroot -proot
//按事件位置號恢復
mysqlbinlog --no-defaults --start-position=234 --stop-position=1373 mysql-binlog.000008 | mysql -uroot -proot
#如果出現警告是密碼直接輸入不安全，可以先不輸入密碼，回車之後輸入
--start-datetime：從二進位制日誌中讀取指定等於時間戳或者晚於本地伺服器的時間
--stop-datetime：從二進位制日誌中讀取指定小於時間戳或者等於本地伺服器的時間 取值和上述一樣
--start-position：從二進位制日誌中讀取指定 position 事件位置作為開始。
--stop-position：從二進位制日誌中讀取指定 position 事件位置作為事件截至
--database=lanebin 指定只恢復 lanebin 資料庫(一臺主機上往往有多個資料庫，只限本地 log 日誌)
不常用選項：
-u --user=name 連線到遠端主機的使用者名稱
-p --password[=name] 連線到遠端主機的密碼
-h --host=name 從遠端主機上獲取 binlog 日誌
--read-from-remote-server 從某個 MySQL 伺服器上讀取 binlog 日誌
5.方案二
針對於方案一的情況，如果資料量非常大的情況，恢復速度會很慢，過程有些困難。第二種方案就比較簡潔，效率更快一些。
前置條件：
a.使用的binlog_format為ROW級別
開始操作：
檢視日誌，大致確定下需要恢復的資料位於哪個日誌檔案中
show binary logs;
檢視日誌並確定刪除範圍
show binlog events in 'binlog.000011';
使用命令匯出日誌記錄
按照offset匯出
mysqlbinlog --no-defaults --start-position=236 --stop-position=747 --base64-output=decode-rows --skip-gtids=true -v /var/lib/mysql/binlog.000011 | sed -n '/### DELETE FROM lanebin.user/,/COMMIT/p' > /root/mysql/resultsql-new.sql
按照時間匯出
mysqlbinlog --no-defaults --start-datetime="2021-07-09 12:10:49" --stopdatetime="2021-07-09 12:17:37" --base64-output=decode-rows --skip-gtids=true -v /var/lib/mysql/binlog.000011 | sed -n '/### DELETE FROM lanebin.user/,/COMMIT/p' > /root/mysql/resultsql-new.sql
匯出全部
mysqlbinlog --no-defaults --base64-output=decode-rows --skip-gtids=true -v /var/lib/mysql/binlog.000011 | sed -n '/### DELETE FROM lanebin.user/,/COMMIT/p' > /root/mysql/resultsql-new.sql
正則匹配，清除無效資料
cat resultsql-new.sql | sed -n '/###/p' | sed 's/### //g;s//*./,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@4.),/\1;/g' | sed 's/@[1-30]=//g' > t1.sql
也可以自己在編輯軟體中，直接編輯resultsql-new.sql 將它修改成sql後，放到資料庫中進行恢復資料
最終效果：
