對(duì)于 MySQL 來(lái)說(shuō)，日志文件尤為重要。MySQL 常見(jiàn)的日志文件有二進(jìn)制日志（Binlog）、通用查詢?nèi)罩荆℅eneral Log）、慢查詢?nèi)罩荆⊿low Log）、錯(cuò)誤日志（Error Log）、重做日志（Redo Log）、回滾日志（Undo Log）等。今天藍(lán)隊(duì)云會(huì)詳細(xì)介紹這些日志文件。

Binlog

Binlog基礎(chǔ)

Binlog包含描述數(shù)據(jù)庫(kù)修改的語(yǔ)句，如create table、update等數(shù)據(jù)變更語(yǔ)句，不會(huì)記錄類似select、show 等不修改數(shù)據(jù)的語(yǔ)句。如果想記錄所有的 SQL 語(yǔ)句，則可以使用General Log，此部分內(nèi)容將在下一節(jié)進(jìn)行詳細(xì)講解。下面通過(guò)例子（在沒(méi)有其他會(huì)話正在修改數(shù)據(jù)的MySQL環(huán)境中操作）展開(kāi)介紹。查看當(dāng)前Binlog的位點(diǎn)：

創(chuàng)建一張表：

查看Binlog信息（其中的Binlog文件和位點(diǎn)都是通過(guò)執(zhí)行上面的show master status語(yǔ)句獲取的）：

從上面的例子中可以看出，執(zhí)行的create table語(yǔ)句會(huì)記錄到Binlog中。

開(kāi)啟和關(guān)閉Binlog

如果要開(kāi)啟Binlog，就需要在配置文件的[mysqld]中加上如下語(yǔ)句：

表示 Binlog 的存放路徑為“/data/mysql/binlog/”，文件名為 mysql-bin 后接 Binlog的序列號(hào)。例如：

為了跟蹤使用了哪些Binlog文件，mysqld還創(chuàng)建了一個(gè)Binlog索引文件，其中包含Binlog文件的名稱。在默認(rèn)情況下，該名稱與Binlog文件具有相同的基本名稱，擴(kuò)展名為“.index”。如上面查詢的內(nèi)容為mysql-bin.index。在本例中，其內(nèi)容為如下形式：

如果沒(méi)有指定文件名和路徑，在[mysqld]中的配置如下：

log-bin

則默認(rèn)存放在datadir下，Binlog的文件名為主機(jī)名后接Binlog的序列號(hào)，如datadir為“/data/mysql/data/”，主機(jī)名為node1，Binlog的全路徑為如下形式：

一般建議指定一個(gè)基本名稱，防止更改主機(jī)名時(shí)出現(xiàn) Binlog 的文件名與之前不一致的現(xiàn)象。如果要關(guān)閉Binlog，就需要在配置文件中加上如下語(yǔ)句：

skip_log_bin

或者：

disable_log_bin

如果要關(guān)閉當(dāng)前會(huì)話的Binlog，則可以執(zhí)行如下語(yǔ)句

set sql_log_bin=0;

Binlog的作用

Binlog的作用主要有以下兩個(gè)。

• 復(fù)制：主庫(kù)的變更先寫入Binlog，然后傳到從庫(kù)進(jìn)行回放。主從的具體原理會(huì)在第9章中詳細(xì)介紹。

• 災(zāi)備：當(dāng)誤操作后，可以先把全備導(dǎo)入某個(gè)新的實(shí)例中，然后通過(guò)全備時(shí)間點(diǎn)到誤操作中間的Binlog解析出所有事務(wù)（需要注意的是，把誤操作這條SQL語(yǔ)句排除掉），并在新實(shí)例中執(zhí)行這些事務(wù)，達(dá)到恢復(fù)到誤操作前一刻的狀態(tài)。

Binlog記錄的格式

Binlog可以設(shè)置為以下幾種日志格式。

• statement（基于 SQL 語(yǔ)句的格式）：每條會(huì)修改數(shù)據(jù)的 SQL 語(yǔ)句都會(huì)記錄在Binlog中，不需要記錄每行的變化。

• row（基于行）：會(huì)非常清楚地記錄每行數(shù)據(jù)被修改的細(xì)節(jié)。

• mixed（混合模式）：以上兩種格式的混合使用，默認(rèn)采用的 statement 格式保存Binlog，statement格式無(wú)法準(zhǔn)確復(fù)制的操作可以使用row格式保存Binlog。

MySQL會(huì)根據(jù)執(zhí)行的SQL語(yǔ)句選擇保存日志的方式。

幾種日志格式的優(yōu)點(diǎn)和缺點(diǎn)如表所示。

Binlog記錄的格式由參數(shù)binlog_format控制，如果要設(shè)置為row格式，則在[mysqld]中加上如下語(yǔ)句：

當(dāng)然，也支持動(dòng)態(tài)修改，修改參數(shù)binlog_format的全局值的方法如下：

修改參數(shù)binlog_format的會(huì)話級(jí)別的方法如下：

Binlog的解析

Binlog文件不能直接查看，需要通過(guò)mysqlbinlog工具解析。例如，在row格式下，解析Binlog的方法如下。首先執(zhí)行create語(yǔ)句：

解析Binlog：

其中，--start-position表示開(kāi)始位點(diǎn)，-vv表示顯示詳細(xì)信息。查看解析結(jié)果：

由上述內(nèi)容可以看出，執(zhí)行 create table test.b（id int）語(yǔ)句后，該語(yǔ)句就會(huì)記錄在Binlog中。

MySQL 8.0 Binlog加密

從MySQL 8.0.14開(kāi)始，可以對(duì)Binlog文件和中繼日志文件進(jìn)行加密，從而保護(hù)敏感數(shù)據(jù)?？梢酝ㄟ^(guò)在配置文件的[mysqld]中加上如下語(yǔ)句開(kāi)啟Binlog加密：

查看Binlog列表：

由此可以發(fā)現(xiàn)，最新的Binlog Encrypted已經(jīng)變?yōu)閅es。下面嘗試通過(guò)mysqlbinlog解析最新的Binlog，具體如下：

由此可以發(fā)現(xiàn)，已經(jīng)解析不出結(jié)果。應(yīng)該使用MySQL的用戶密碼進(jìn)行解析才可以，具體如下：

--read-from-remote-server參數(shù)表示從MySQL服務(wù)中讀取Binlog，而不是讀取本地日志文件。讀取遠(yuǎn)程MySQL的Binlog要求遠(yuǎn)程MySQL實(shí)例正在運(yùn)行。

Binlog的清除

對(duì)于一個(gè)繁忙的MySQL實(shí)例，其Binlog增長(zhǎng)也是比較快的，因此，需要設(shè)置其保留天數(shù)，如果磁盤即將滿，那么可能還要單獨(dú)刪除歷史 Binlog。本節(jié)主要介紹 Binlog的清除?？梢允褂?purge binary logs 語(yǔ)句來(lái)刪除指定 Binlog 的文件名或指定時(shí)間之前的Binlog文件，具體示例如下。

示例一，刪除指定Binlog之前的文件：

示例二，刪除指定時(shí)間之前的Binlog文件。查看Binlog文件：

刪除指定時(shí)間之前的Binlog文件：

當(dāng)然，一般還是建議設(shè)置expire_logs_days參數(shù)或binlog_expire_logs_seconds參數(shù)。expire_logs_days參數(shù)定義了日志保留天數(shù)。binlog_expire_logs_seconds參數(shù)定義了日志保留秒數(shù)，MySQL 8.0建議設(shè)置這個(gè)參數(shù)，在未來(lái)的版本中可能會(huì)廢除expire_logs_days參數(shù)。

當(dāng)Binlog存在的時(shí)間超過(guò)binlog_expire_logs_seconds參數(shù)設(shè)置的時(shí)間時(shí)，則自動(dòng)刪除。有

時(shí)Binlog占用的磁盤空間會(huì)過(guò)大，如果要降低其保留時(shí)間，則可以進(jìn)行如下操作：

如上所示，縮短保留時(shí)間后，需要執(zhí)行flush logs語(yǔ)句才能刪除之前的Binlog

Binlog的落盤

Binlog同步到磁盤的頻率由sync_binlog參數(shù)控制。sync_binlog參數(shù)大致有以下幾種配置。

• sync_binlog=0，禁用MySQL服務(wù)將Binlog同步到磁盤的功能，是由操作系統(tǒng)控制 Binlog 的刷盤。在這種情況下，性能比較好，但是當(dāng)操作系統(tǒng)崩潰時(shí)可能會(huì)丟失部分事務(wù)。- sync_binlog=1，每個(gè)事務(wù)都會(huì)同步到磁盤。這是最安全的設(shè)置，但是磁盤寫入次數(shù)的增加可能會(huì)導(dǎo)致性能下降。

• sync_binlog=N，表示每N個(gè)事務(wù)Binlog同步一次到磁盤。當(dāng)操作系統(tǒng)崩潰時(shí)，服務(wù)器提交的事務(wù)可能沒(méi)有被刷新到 Binlog 中，此時(shí)可能會(huì)丟失部分事務(wù)，雖然設(shè)置比較大的值可以提高性能，但是數(shù)據(jù)丟失的風(fēng)險(xiǎn)也會(huì)增加。

Binlog相關(guān)的參數(shù)

• max_binlog_size：?jiǎn)蝹€(gè)Binlog文件大小的最大值。

• log-slave-update：從庫(kù)從主庫(kù)接收的更新是否記錄到從庫(kù)自身的Binlog中，如果從庫(kù)后面又接了從庫(kù)，或者在從庫(kù)上做備份，或者M(jìn)ySQL 5.6主從復(fù)制使用了GTID模式（具體原因見(jiàn)9.2.1節(jié)），那么建議開(kāi)啟這個(gè)參數(shù)。

• binlog-do-db：后面接庫(kù)名，表示當(dāng)前數(shù)據(jù)庫(kù)只記錄該參數(shù)設(shè)置的庫(kù)的 Binlog，其他庫(kù)都不記錄。

• binlog-ignore-db：后面接庫(kù)名，表示當(dāng)前數(shù)據(jù)庫(kù)不記錄該參數(shù)設(shè)置的庫(kù)的Binlog，其他庫(kù)都記錄。

綜上所述，二進(jìn)制日志（Binlog）在 MySQL 中扮演著極為重要的角色，無(wú)論是主從復(fù)制、數(shù)據(jù)災(zāi)備，還是日常的數(shù)據(jù)管理，都離不開(kāi)它。了解 Binlog 的各項(xiàng)特性、配置方法及相關(guān)操作，有助于數(shù)據(jù)庫(kù)管理員更好地管理 MySQL 數(shù)據(jù)庫(kù)，保障數(shù)據(jù)的安全與穩(wěn)定。

想了解更多相關(guān)技術(shù)小分享可以上藍(lán)隊(duì)云官網(wǎng)查閱，更多技術(shù)問(wèn)題，也可以直接咨詢。同時(shí)，藍(lán)隊(duì)云整理了運(yùn)維必備的工具包免費(fèi)分享給大家使用，需要的朋友可以直接咨詢。