Binlog數(shù)據(jù)恢復(fù)實戰(zhàn)，刪庫不跑路
一、binLog恢復(fù)數(shù)據(jù)
根據(jù) MySQL 官方文檔的介紹，開啟 binlog 之后，大概會有 1% 的性能損耗，不過這還是可以接受的，一般來說，binlog 有兩個重要的使用場景：
MySQL主從復(fù)制時：在主機(jī)上開啟 binlog，主機(jī)將 binlog 同步給從機(jī)，從機(jī)通過 binlog 來同步數(shù)據(jù)，進(jìn)而實現(xiàn)主機(jī)和從機(jī)的數(shù)據(jù)同步。
MySQL 數(shù)據(jù)恢復(fù)，通過使用 mysqlbinlog 工具再結(jié)合 binlog 文件，可以將數(shù)據(jù)恢復(fù)到過去的某一時刻。
1.開啟 binlog
我將使用docker演示，配置和配置位置都是一樣的，沒啥區(qū)別。
(1)配置/etc/mysql/mysql.conf.d/mysqld.cnf ，編輯以下參數(shù)：
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log在其中，server-id 是服務(wù)器的唯一標(biāo)識符，log_bin 是binlog文件的路徑和名稱。你可以根據(jù)需要更改這些值。
(2)保存更改后的配置文件，并重新啟動MySQL服務(wù)，使更改生效。
(3)確認(rèn)binlog已經(jīng)成功開啟，可以使用以下命令登錄MySQL并執(zhí)行：
SHOW MASTER STATUS;如果輸出類似如下信息，則表示binlog已經(jīng)成功開啟：
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | test | |
+------------------+----------+--------------+------------或者以下命令：可以看到log_bin的狀態(tài)是ON。
show variables like 'log_bin%';mysql> show variables like 'log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+--------------------------------+
5 rows in set (0.00 sec)2.編輯配置
#這個參數(shù)用來啟用binlog，并指定了binlog的文件名前綴。在這個例子中，binlog文件會以 dx_logbin 開頭命名。binlog文件記錄了數(shù)據(jù)庫的所有更改操作，包括增刪改等。
log-bin=adx_logbin
#這個參數(shù)指定了單個binlog文件的最大大小，單位是字節(jié)。當(dāng)binlog文件大小達(dá)到這個值時，MySQL會自動創(chuàng)建一個新的binlog文件來繼續(xù)記錄日志。
max_binlog_size=104857600
#這個參數(shù)指定了binlog文件的過期時間，單位是天。超過指定天數(shù)的binlog文件會被自動刪除。這個設(shè)置有助于控制磁盤空間的使用。
expire_logs_days=7
#這個參數(shù)用來指定需要記錄binlog的數(shù)據(jù)庫名稱。在這個例子中，adx_db 是需要記錄binlog的數(shù)據(jù)庫，
#binlog-do-db=adx_db
#這個參數(shù)用來指定不需要記錄binlog的數(shù)據(jù)庫名稱。在這個例子中，javaboy_no_db 是不需要記錄binlog的數(shù)據(jù)庫，但是由于前面有#注釋了，所以實際上是被注釋掉了，不會生效。
#binlog-ignore-db=javaboy_no_db
#這個參數(shù)用來控制binlog的寫入方式。當(dāng)設(shè)置為0時，表示不強(qiáng)制立即將binlog日志寫入磁盤。這樣會提高性能，但在數(shù)據(jù)庫宕機(jī)時可能會丟失一部分?jǐn)?shù)據(jù)。
sync_binlog=0
#這個參數(shù)指定了MySQL服務(wù)器的唯一標(biāo)識符。在復(fù)制和多主模式下，每個服務(wù)器都需要有一個唯一的ID來標(biāo)識自己。
server-id=1配置完成后，執(zhí)行如下命令重啟 mysql 容器（mysql是你的容器名稱）。
docker restart mysql在看一下是否開啟binlog。
圖片
其中我們還要關(guān)注兩個屬性。
log_bin_basename: /var/lib/mysql/adx_logbin
這個配置指定了二進(jìn)制日志文件的基本名字為adx_logbin，不包括文件擴(kuò)展名。實際的二進(jìn)制日志文件會以這個基本名字開頭，后面緊跟一個數(shù)字標(biāo)識，再加上文件擴(kuò)展名（通常是.log）。例如，可能生成的二進(jìn)制日志文件包括adx_logbin.000001、adx_logbin.000002等。
這個設(shè)置意味著生成的二進(jìn)制日志文件將以adx_logbin作為基本名字。
log_bin_index: /var/lib/mysql/adx_logbin.index
這個配置指定了二進(jìn)制日志索引文件的名字為二進(jìn)制日志索引文件記錄了所有的二進(jìn)制日志文件名字及其對應(yīng)的位置信息，通常以.index作為文件擴(kuò)展名。通過這個索引文件，MySQL可以快速地定位到各個二進(jìn)制日志文件，并進(jìn)行相應(yīng)的操作，比如數(shù)據(jù)庫恢復(fù)、復(fù)制等。查看一下現(xiàn)在的 adx_logbin.index 文件：
圖片
3.常用binlog相關(guān)命令
3.1 查看所有 binlog 日志列表
show master logs;
圖片
3.2 查看 master 狀態(tài)
用于查看當(dāng)前主服務(wù)器的二進(jìn)制日志（binlog）信息。執(zhí)行這個命令可以獲取以下信息，F(xiàn)ile：當(dāng)前正在寫入的二進(jìn)制日志文件名，Position：在當(dāng)前二進(jìn)制日志文件中的位置，即已經(jīng)寫入的字節(jié)數(shù)。
show master status;
圖片
3.3 刷新 binlog
用于關(guān)閉當(dāng)前的二進(jìn)制日志文件，將當(dāng)前的日志文件重命名為一個舊的日志文件，例如通過添加一個序號或時間戳。并創(chuàng)建一個新的空的二進(jìn)制日志文件，用于接收后續(xù)的二進(jìn)制日志事件。
flush logs這個命令在進(jìn)行數(shù)據(jù)庫備份時特別有用，因為它可以確保備份操作可以在一個一致的時間點開始，并且不會受到正在寫入的二進(jìn)制日志的影響。另外，當(dāng)你希望重新開始二進(jìn)制日志的記錄時，也可以使用這個命令來關(guān)閉當(dāng)前的日志文件并開啟一個新的日志文件。
圖片
需要注意的是，執(zhí)行 FLUSH LOGS; 會導(dǎo)致當(dāng)前的二進(jìn)制日志文件被關(guān)閉，這可能會影響到主從復(fù)制的正常運(yùn)行。因此，在執(zhí)行這個命令之前，需要謹(jǐn)慎考慮是否會對數(shù)據(jù)庫的其他操作產(chǎn)生影響。
3.4 重置 binlog
reset masterreset master 可以重置 binlog 日志文件，讓日志重新從 000001 開始記錄，不過如果當(dāng)前主機(jī)有一個或者多個從機(jī)在運(yùn)行，那么該命令就運(yùn)行不了（因為從機(jī)是通過 binlog 來實現(xiàn)數(shù)據(jù)庫同步的，主機(jī)把 binlog 清空了，從機(jī)會報找不到 binlog 的錯誤）。
執(zhí)行 RESET MASTER; 可以確保清除所有的舊的日志文件，防止日志文件過多占用磁盤空間，并從頭開始記錄新的二進(jìn)制日志事件。
圖片
3.5 查看 binlog
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];該命令用于顯示指定二進(jìn)制日志文件中的二進(jìn)制日志事件。這個命令可以提供對二進(jìn)制日志中存儲的操作和更改的詳細(xì)信息。
下面是各參數(shù)的含義：
IN 'log_name': 可選參數(shù)，指定要查看的特定二進(jìn)制日志文件名。
FROM pos: 可選參數(shù)，指定從日志文件中的特定位置開始顯示日志事件。
LIMIT [offset,] row_count: 可選參數(shù)，限制要顯示的日志事件的數(shù)量，并可以設(shè)置偏移量。
show binlog events in 'adx_logbin.000001';
圖片
通過這個工具就能查看你執(zhí)行過什么sql，例如327行我執(zhí)行了創(chuàng)建庫操作，586行我執(zhí)行了創(chuàng)建表操作。
4.基于binLog恢復(fù)數(shù)據(jù)
通常數(shù)據(jù)庫會做定時備份，假設(shè)每天凌晨1:00點定時備份全量數(shù)據(jù)庫，如果第二天數(shù)據(jù)丟失了，可以先通過備份，先將數(shù)據(jù)恢復(fù)到當(dāng)天的凌晨1:00的數(shù)據(jù)，再基于binlog恢復(fù)凌晨一點到數(shù)據(jù)丟失的那一刻的數(shù)據(jù)，這樣就完全找回數(shù)據(jù)了。
備份命令：
mysqldump -uroot -p --flush-logs --lock-tables -B student2>/root/student2.bak.sqlmysqldump: 這是用于備份數(shù)據(jù)庫的命令。
-uroot: 表示使用 root 用戶身份連接到數(shù)據(jù)庫進(jìn)行備份。
-p: 是一個選項，表示在輸入密碼之前會提示用戶輸入密碼。
--flush-logs: 這個選項表示在備份完成后將刷新日志文件，確保備份過程中的日志都被記錄下來。
--lock-tables: 這個選項表示在備份時對數(shù)據(jù)庫表進(jìn)行鎖定，以確保備份的一致性。
-B student2: 表示備份名為 student2 的數(shù)據(jù)庫。
> /root/student2.sql: 這部分表示將備份內(nèi)容導(dǎo)出到 /root 目錄下的 student2.sql 文件中。
圖片
可以通過cat命令查看導(dǎo)出的sql，老鐵沒毛病。
圖片
假設(shè)我現(xiàn)在刪除了student2這張表。
圖片
如何恢復(fù)？先查詢最新的binlog（最后一個binlog）。
圖片
show binlog events in 'adx_logbin.000002';
圖片
可以看到，在 1403-1507 這個 Pos 中發(fā)生了刪庫跑路事件，那么我們只需要回放該文件將數(shù)據(jù)恢復(fù)到 1403 這個位置即可。
由于 adx_logbin.000002 文件是在當(dāng)前凌晨1:00備份之后產(chǎn)生的新文件，因此這個文件從起始到 1403 這個 Pos 之間的操作，就是凌晨1:00到刪庫之前的操作了。
那么我們來看下通過 binlog 來恢復(fù)數(shù)據(jù)的命令：（沒有這個student2庫的話先手動建立一個）
mysqlbinlog /var/lib/mysql/adx_logbin.000002 --stop-positinotallow=1403 --database=student2 | mysql -uroot -p命令解釋：
mysqlbinlog: 是用于解析 MySQL 二進(jìn)制日志文件的工具。通過這個命令，你可以查看和分析二進(jìn)制日志中的內(nèi)容。
/var/lib/mysql/adx_logbin.000002: 這是指定的二進(jìn)制日志文件路徑，即要解析的二進(jìn)制日志文件。
--stop-positinotallow=1403: 這個選項指定了解析二進(jìn)制日志文件時要停止的位置。在這種情況下，命令會解析從文件開頭到指定位置（1403）之間的內(nèi)容。
--database=student2: 這個選項指定了只解析屬于數(shù)據(jù)庫 'student2' 的相關(guān)操作。這意味著命令只會處理涉及 'student2' 數(shù)據(jù)庫的內(nèi)容。
|: 這是管道符號，用于將 mysqlbinlog 命令的輸出傳遞給后面的 mysql 命令。
mysql -uroot -p: 這是執(zhí)行實際恢復(fù)操作的部分。它使用 mysql 命令以 root 用戶身份連接到 MySQL 數(shù)據(jù)庫，并執(zhí)行從 mysqlbinlog 命令得到的結(jié)果。
mysqlbinlog: 是用于解析 MySQL 二進(jìn)制日志文件的工具。通過這個命令，你可以查看和分析二進(jìn)制日志中的內(nèi)容。
/var/lib/mysql/adx_logbin.000002: 這是指定的二進(jìn)制日志文件路徑，即要解析的二進(jìn)制日志文件。
--stop-positinotallow=1403: 這個選項指定了解析二進(jìn)制日志文件時要停止的位置。在這種情況下，命令會解析從文件開頭到指定位置（1403）之間的內(nèi)容。
--database=student2: 這個選項指定了只解析屬于數(shù)據(jù)庫 'student2' 的相關(guān)操作。這意味著命令只會處理涉及 'student2' 數(shù)據(jù)庫的內(nèi)容。
|: 這是管道符號，用于將 mysqlbinlog 命令的輸出傳遞給后面的 mysql 命令。
mysql -uroot -p: 這是執(zhí)行實際恢復(fù)操作的部分。它使用 mysql 命令以 root 用戶身份連接到 MySQL 數(shù)據(jù)庫，并執(zhí)行從 mysqlbinlog 命令得到的結(jié)果。
圖片
圖片
