我們常常聽人說，只要你愿意，MySQL 可以恢復(fù)至半個(gè)月甚至一個(gè)月以內(nèi)的任何一個(gè)狀態(tài)。網(wǎng)上也有很多刪庫跑路的段子……

那么今天松哥想和大家來聊一聊 MySQL 中的 binlog，來手把手教大家如何利用 binlog 來恢復(fù) MySQL 中的數(shù)據(jù)，這樣，以后要是不小心刪庫了，那也不用跑路了。

MySQL 中的日志比較重要的有 binlog（歸檔日志）、redo log（重做日志）以及 undo log，那么跟我們本文相關(guān)的主要是 binlog，另外兩個(gè)日志松哥將來有空了再和大家詳細(xì)介紹。

一、binlog

binlog 我們中文一般稱作歸檔日志，如果大家看過松哥之前發(fā)的 MySQL 主從搭建，應(yīng)該對(duì)這個(gè)日志有印象，當(dāng)我們搭建 MySQL 主從的時(shí)候就離不開 binlog。

binlog 是 MySQL Server 層的日志，而不是存儲(chǔ)引擎自帶的日志，它記錄了所有的 DDL 和 DML(不包含數(shù)據(jù)查詢語句)語句，而且是以事件形式記錄，還包含語句所執(zhí)行的消耗的時(shí)間等，需要注意的是：

• binlog 是一種邏輯日志，他里邊所記錄的是一條 SQL 語句的原始邏輯，例如給某一個(gè)字段 +1，注意這個(gè)區(qū)別于 redo log 的物理日志（在某個(gè)數(shù)據(jù)頁上做了什么修改）。
• binlog 文件寫滿后，會(huì)自動(dòng)切換到下一個(gè)日志文件繼續(xù)寫，而不會(huì)覆蓋以前的日志，這個(gè)也區(qū)別于 redo log，redo log 是循環(huán)寫入的，即后面寫入的可能會(huì)覆蓋前面寫入的。
• 一般來說，我們?cè)谂渲?binlog 的時(shí)候，可以指定 binlog 文件的有效期，這樣在到期后，日志文件會(huì)自動(dòng)刪除，這樣避免占用較多存儲(chǔ)空間。

根據(jù) MySQL 官方文檔的介紹，開啟 binlog 之后，大概會(huì)有 1% 的性能損耗，不過這還是可以接受的，一般來說，binlog 有兩個(gè)重要的使用場景：

• 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í)刻。

二、開啟 binlog

為了演示方便，松哥這里在 Docker 中安裝了 MySQL，我們以此為例來開始今天的演示。如果小伙伴們還不懂 docker 的使用，可以在公眾號(hào)后臺(tái)回復(fù) docker，有松哥寫的教程。

首先我們?cè)?docker 中安裝好 MySQL，然后進(jìn)入到容器中，通過如下命令可以查看 binlog 是否開啟：

這個(gè) OFF 就表示 binlog 是一個(gè)關(guān)閉狀態(tài)，沒有開啟，接下來我們來開啟 binlog。

開啟 binlog 主要是修改 MySQL 的配置文件 mysqld.cnf，該文件在容器的 /etc/mysql/mysql.conf.d 目錄下。

針對(duì)該配置文件，我們做如下修改：

# 這個(gè)參數(shù)表示啟用 binlog 功能，并指定 binlog 的存儲(chǔ)目錄
log-bin=javaboy_logbin


# 設(shè)置一個(gè) binlog 文件的最大字節(jié)
# 設(shè)置最大 100MB
max_binlog_size=104857600


# 設(shè)置了 binlog 文件的有效期（單位：天）
expire_logs_days = 7


# binlog 日志只記錄指定庫的更新（配置主從復(fù)制的時(shí)候會(huì)用到）
#binlog-do-db=javaboy_db


# binlog 日志不記錄指定庫的更新（配置主從復(fù)制的時(shí)候會(huì)用到）
#binlog-ignore-db=javaboy_no_db


# 寫緩存多少次，刷一次磁盤，默認(rèn) 0 表示這個(gè)操作由操作系統(tǒng)根據(jù)自身負(fù)載自行決定多久寫一次磁盤
# 1 表示每一條事務(wù)提交都會(huì)立即寫磁盤，n 則表示 n 個(gè)事務(wù)提交才會(huì)寫磁盤
sync_binlog=0


# 為當(dāng)前服務(wù)取一個(gè)唯一的 id（MySQL5.7 之后需要配置）
server-id=1

各項(xiàng)配置的含義松哥已經(jīng)在注視中說明了。截圖如下：

配置完成后，執(zhí)行如下命令重啟 mysql 容器（mysql1 是我這里容器的名字）：

docker restart mysql1

重啟之后，再次執(zhí)行 show variables like 'log_bin%'; 即可看到 binlog 已經(jīng)開啟了。

這里除了 log_bin 變量外，還有兩個(gè)變量名也值得我們關(guān)注：

• log_bin_basename：這個(gè)是將來產(chǎn)生的 binlog 日志文件的名稱前綴，換句話說，根據(jù)大家目前所看到的配置，將來產(chǎn)生的 binlog 日志文件名為 javaboy_logbin.xxx，這個(gè)文件中將會(huì)用來記錄所有的 DDL 和 DML 語句事件。
• log_bin_index：這個(gè)是 binlog 的索引文件，保存了所有 binlog 的目錄，因?yàn)?binlog 可能會(huì)有多個(gè)。我們可以來查看一下現(xiàn)在的 javaboy_logbin.index 文件：

可以看到，目前只有一個(gè) logbin 文件。

三、常見 binlog 操作

接下來我們?cè)賮斫榻B幾個(gè)常見的 binlog 操作命令。

1、查看所有 binlog 日志

通過如下方式我們可以查看 binlog 日志列表：

show master logs;

可以看到，我這里目前只有一個(gè)日志文件，文件名為 javaboy_logbin.000001，F(xiàn)ile_size 表示這個(gè)文件占用的字節(jié)大小是 154。

2、查看 master 狀態(tài)

這個(gè)命令我們?cè)诖罱?MySQL 主從的時(shí)候經(jīng)常會(huì)用到，如下：

這個(gè)時(shí)候可以看到最新的 binlog 日志文件名稱以及最后一個(gè)操作事件的 Position 值（這個(gè)值有啥用，我們后面會(huì)給大家詳細(xì)介紹）。

3、刷新 binlog

正常來說，一個(gè) binlog 寫滿之后，會(huì)自動(dòng)切換到下一個(gè) binlog 開始寫，不過我們也可以執(zhí)行一個(gè) flush logs 命令來手動(dòng)刷新 binlog，手動(dòng)刷新 binlog 之后，就會(huì)產(chǎn)生一個(gè)新的 binlog 日志文件，接下來所有的 binlog 日志都將記錄到新的文件中。如下：

由上圖可以看到，我們刷新日志之后，再通過 show master logs 去查看日志，發(fā)現(xiàn)日志文件已經(jīng)多了一個(gè)新產(chǎn)生的了，然后再通過 show master status 去查看最新的日志文件信息，發(fā)現(xiàn)也已經(jīng)變?yōu)?javaboy_logbin.000002。

4、重置 binlog

reset 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ò)誤）。

5、查看 binlog

由于 binlog 是二進(jìn)制日志文件，所以要是直接打開，那肯定是看不了的：

沒有看到任何有用的信息。

為了查看 binlog，MySQL 為我們提供了兩個(gè)官方工具，我們一個(gè)一個(gè)來看，首先是 mysqlbinlog 命令，如下：

雖然看起來亂糟糟的，不過仔細(xì)看著其實(shí)都有跡可循。 因?yàn)槲疫@里是一個(gè)新安裝的數(shù)據(jù)庫，里邊只是創(chuàng)建了一個(gè)名為 javaboy 的庫，然后創(chuàng)建了一個(gè)名為 user 的表加了兩條數(shù)據(jù)，其他什么事情都沒做，所以創(chuàng)建庫的腳本我們其實(shí)能夠從紛雜的文件中找到。

產(chǎn)生的日志文件中有一個(gè) end_log_pos 是日志文件的 pos 點(diǎn)，這個(gè)將來在數(shù)據(jù)恢復(fù)的時(shí)候有用。

不過這種查看方式不夠人性化，我們說 binlog 是按照事件來記錄日志的，所以如果我們能夠按照事件的方式查看日志，就會(huì)好很多，我們?cè)賮砜纯慈缦乱粋€(gè)命令：

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

這個(gè)表示以事件的方式來查看 binlog，這里涉及到幾個(gè)參數(shù)：

• log_name：可以指定要查看的 binlog 日志文件名，如果不指定的話，表示查看最早的 binlog 文件。
• pos：從哪個(gè) pos 點(diǎn)開始查看，凡是 binlog 記錄下來的操作都有一個(gè) pos 點(diǎn)，這個(gè)其實(shí)就是相當(dāng)于我們可以指定從哪個(gè)操作開始查看日志，如果不指定的話，就是從該 binlog 的開頭開始查看。
• offset：這是是偏移量，不指定默認(rèn)就是 0。
• row_count：查看多少行記錄，不指定就是查看所有。

我們來看一個(gè)簡單的例子：

show binlog events in 'javaboy_logbin.000001';

這下就清晰多了，我們可以看到之前的所有操作，例如：

• 在 Pos 219-322 之間創(chuàng)建了一個(gè)庫。
• 在 Pos 387-537 之間創(chuàng)建了一張表。
• 在 Pos 677-780 之間添加了一條記錄。
• ...

四、數(shù)據(jù)恢復(fù)實(shí)戰(zhàn)

好啦，有了前面的基礎(chǔ)知識(shí)準(zhǔn)備，接下來松哥來給大家手把手演示一個(gè)刪庫/恢復(fù)的場景。

我先來說說我這個(gè)數(shù)據(jù)庫目前的情況。

這是一個(gè)新安裝的數(shù)據(jù)庫，里邊我新建了一個(gè)數(shù)據(jù)庫名為 javaboy，javabo y 庫中新建了一張表名為 user，user 中有兩條記錄，如下：

現(xiàn)在假設(shè)我們定期（每周三凌晨三點(diǎn)）對(duì)數(shù)據(jù)庫進(jìn)行備份。

現(xiàn)在凌晨三點(diǎn)了，數(shù)據(jù)庫自動(dòng)備份開始了，我們通過如下命令將數(shù)據(jù)庫備份成 SQL 腳本，如下：

這里有幾個(gè)參數(shù)跟大家解釋下：

• -u、-p 這兩個(gè)就不用說了。
• --flush-logs：這個(gè)表示在導(dǎo)出之前先刷新 binlog，刷新 binlog 之后將會(huì)產(chǎn)生新的 binlog 文件，后續(xù)的操作都存在新的 binlog 中。
• --lock-tables：這個(gè)表示開始導(dǎo)出前，鎖定所有表。需要注意的是當(dāng)導(dǎo)出多個(gè)數(shù)據(jù)庫時(shí)，--lock-tables 分別為每個(gè)數(shù)據(jù)庫鎖定表，因此這個(gè)選項(xiàng)不能保證導(dǎo)出文件中的表在數(shù)據(jù)庫之間的邏輯一致性，不同數(shù)據(jù)庫表的導(dǎo)出狀態(tài)可以完全不同。
• -B：這個(gè)表示指定導(dǎo)出的數(shù)據(jù)庫名稱，如果使用 --all-databases 或者 -A 代替 -B 表示導(dǎo)出所有的數(shù)據(jù)庫。

以上命令執(zhí)行完成后，會(huì)在 /root 目錄下生成一個(gè) javaboy.bak.sql 文件，該文件就是備份的 sql 文件了。

這是星期三凌晨三點(diǎn)發(fā)生的事情。

接下來到了星期四早上，來上班了，一頓操作后，往數(shù)據(jù)庫中又添加了兩條操作，如下：

接下來，小 X 今天跟領(lǐng)導(dǎo)吵架了很不爽，決定刪除跑路：

領(lǐng)導(dǎo)發(fā)現(xiàn)了大驚，當(dāng)即要求立馬恢復(fù)數(shù)據(jù)。 這時(shí)候該你表現(xiàn)了。

首先，我們有星期三凌晨的備份文件，先用那個(gè)文件進(jìn)行數(shù)據(jù)恢復(fù)：

恢復(fù)之后，現(xiàn)在到星期三早上凌晨三點(diǎn)的數(shù)據(jù)有了。

從星期三早上凌晨三點(diǎn)到星期四的數(shù)據(jù)現(xiàn)在沒了。

這個(gè)時(shí)候我們就要借助于 binlog 來恢復(fù)了。大家還記得，我們星期三凌晨三點(diǎn)執(zhí)行備份的時(shí)候，用了一個(gè)參數(shù)叫做 --flush-logs，使用了該參數(shù)表示從備份那一刻起，新的 binlog 將產(chǎn)生在一個(gè)新的日志文件中，對(duì)于我們這里來說，新的 binlog 文件當(dāng)然就是 javaboy_logbin.000002 了，我們?nèi)ゲ榭匆幌略撐募?/p>

我這里生成的該文件比較長，我截取其中一部分：

可以看到，在 764-865 這個(gè) Pos 中發(fā)生了刪庫跑路事件，那么我們只需要回放該文件將數(shù)據(jù)恢復(fù)到 764 這個(gè)位置即可。

由于 javaboy_logbin.000002 文件是在星期三凌晨三點(diǎn)備份之后產(chǎn)生的新文件，因此這個(gè)文件從起始到 764 這個(gè) Pos 之間的操作，就是星期三凌晨三點(diǎn)到刪庫之前的操作了。

那么我們來看下通過 binlog 來恢復(fù)數(shù)據(jù)的命令：

那么這里涉及到兩個(gè)參數(shù)：

• --stop-position=764 表示恢復(fù)到 764 這個(gè) Pos，不指定的話就把按整個(gè)文件恢復(fù)了，如果按當(dāng)前文件恢復(fù)的話，由于這個(gè) binlog 文件中有刪除數(shù)據(jù)庫的語句，那么就會(huì)導(dǎo)致執(zhí)行完該 binlog 之后，javaboy 庫又被刪除了。
• --database=javaboy 表示恢復(fù) javaboy 這個(gè)庫。

另外還有一個(gè)我們這里沒用到的參數(shù)叫做 --start-position，這個(gè)表示起始的 Pos，不指定的話表示從頭開始數(shù)據(jù)恢復(fù)。

好啦，弄完之后，再來查看數(shù)據(jù)庫：

數(shù)據(jù)恢復(fù)啦～

所有操作之前，記得該備份就備份（防止你操作錯(cuò)了又回不去），松哥為了省事上面省略了一些備份操作。

五、小結(jié)

好啦，今天這篇文章主要是和小伙伴們分享了 MySQL 的 binlog 日志，并通過一個(gè)小案例來演示如何通過 binlog 實(shí)現(xiàn)數(shù)據(jù)庫的刪庫恢復(fù)。