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