圖文結(jié)合帶你搞懂MySQL日志之Binary log（二進(jìn)制日志）

這篇具有很好參考價(jià)值的文章主要介紹了圖文結(jié)合帶你搞懂MySQL日志之Binary log（二進(jìn)制日志）。希望對(duì)大家有所幫助。如果存在錯(cuò)誤或未考慮完全的地方，請(qǐng)大家不吝賜教，您也可以點(diǎn)擊"舉報(bào)違法"按鈕提交疑問。

往期回顧

圖文結(jié)合帶你搞定MySQL日志之Undo log(回滾日志)
圖文結(jié)合帶你搞懂InnoDB MVCC
圖文結(jié)合帶你搞懂MySQL日志之Redo Log(重做日志)
圖文結(jié)合帶你搞懂MySQL日志之Error Log（錯(cuò)誤日志）
圖文結(jié)合帶你搞懂MySQL日志之Slow Query Log（慢查詢?nèi)罩荆?/li>
圖文結(jié)合帶你搞懂MySQL日志之relay log（中繼日志）
圖文結(jié)合帶你搞懂MySQL日志之General Query Log（通用查詢?nèi)罩荆?/li>

圖文結(jié)合帶你搞懂MySQL日志之Binary log（二進(jìn)制日志）

此篇為圖文結(jié)合搞懂MySQL日志的最后一篇，MySQL中共有八大日志，其中數(shù)據(jù)定義語(yǔ)句日志不是給用戶查看的，在此篇尾部簡(jiǎn)單介紹，接下來(lái)圖文結(jié)合系列還會(huì)繼續(xù)推出新文章！

二進(jìn)制日志(Binary log)

binlog可以說(shuō)是MySQL中比較重要的日志了，在日常開發(fā)及運(yùn)維過(guò)程中，經(jīng)常會(huì)遇到。

binlog即binary log，二進(jìn)制日志文件，也叫作變更日志（update log）。它記錄了數(shù)據(jù)庫(kù)所有執(zhí)行的DDL和DML等數(shù)據(jù)庫(kù)更新事件的語(yǔ)句，但是不包含沒有修改任何數(shù)據(jù)的語(yǔ)句（如數(shù)據(jù)查詢語(yǔ)句select、show等）。

它以事件形式記錄并保存在二進(jìn)制文件中。通過(guò)這些信息，我們可以再現(xiàn)數(shù)據(jù)更新操作的全過(guò)程。

如果想要記錄所有語(yǔ)句（例如，為了識(shí)別有問題的查詢)，需要使用通用查詢?nèi)罩尽?/p>

Binary log主要應(yīng)用場(chǎng)景：

一是用于數(shù)據(jù)恢復(fù)，如果MySQL數(shù)據(jù)庫(kù)意外停止，可以通過(guò)二進(jìn)制日志文件來(lái)查看用戶執(zhí)行了哪些操作，對(duì)數(shù)據(jù)庫(kù)服務(wù)器文件做了哪些修改，然后根據(jù)二進(jìn)制日志文件中的記錄來(lái)恢復(fù)數(shù)據(jù)庫(kù)服務(wù)器。
二是用于數(shù)據(jù)復(fù)制，由于日志的延續(xù)性和時(shí)效性，master把它的二進(jìn)制日志傳遞給slaves來(lái)達(dá)到master-slave數(shù)據(jù)一致的目的。

可以說(shuō)MySQL數(shù)據(jù)庫(kù)的數(shù)據(jù)備份、主備、單主、多主、MGR都離不開Binary log,需要依靠Binary log來(lái)同步數(shù)據(jù)，保證數(shù)據(jù)一致性。

圖文結(jié)合帶你搞懂MySQL日志之Binary log（二進(jìn)制日志）

查看默認(rèn)情況

查看記錄二進(jìn)制日志是否開啟：在MySQL8中默認(rèn)情況下，二進(jìn)制文件是開啟的。

mysql>  show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |  //開關(guān)
| log_bin_basename                | /var/lib/mysql/binlog       | // 存放路徑
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | ON                          |//  函數(shù)創(chuàng)建 
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |//變更sql記錄下來(lái)
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)

log_bin_basename:是binlog日志的基本文件名，后面會(huì)追加標(biāo)識(shí)來(lái)表示每一個(gè)文件
log_bin_index:是binlog文件的素引文件，這個(gè)文件管理了所有的binlog文件的目錄
log_bin_trust_function_creators:限制存儲(chǔ)過(guò)程，前面我們已經(jīng)講過(guò)了，這是因?yàn)槎M(jìn)制日志的一個(gè)重要功能是用于主從復(fù)制，而存儲(chǔ)函數(shù)有可能導(dǎo)致主從的數(shù)據(jù)不一致。所以當(dāng)開啟二進(jìn)制日志后，需要限制存儲(chǔ)函數(shù)的創(chuàng)建、修改、調(diào)用
log_bin_use_v1_row_events此只讀系統(tǒng)變量已棄用。ON表示使用版本1二進(jìn)制日志行，OFF表示使用版本2二進(jìn)制日志行（MySQL5.6的默認(rèn)值為2)。

日志參數(shù)設(shè)置

方式 1 ：永久性方式

修改MySQL的my.cnf或my.ini文件可以設(shè)置二進(jìn)制日志的相關(guān)參數(shù)：

[mysqld]
#啟用二進(jìn)制日志
log-bin=atguigu-bin
binlog_expire_logs_seconds= 600
max_binlog_size=100M

提示:

log-bin=mysql-bin

打開日志(主機(jī)需要打開)，這個(gè)mysql-bin也可以自定義，這里也可以加上路徑，如:/home/www/mysql_bin_log/mysql-bin

binlog_expire_logs_seconds

此參數(shù)控制二進(jìn)制日志文件保留的時(shí)長(zhǎng)單位是秒，默認(rèn)2592000 30天 --14400 4小時(shí);86400 1天; 259200 3天;

max_binlog_size

控制單個(gè)二進(jìn)制日志大小，當(dāng)前日志文件大小超過(guò)此變量時(shí)，執(zhí)行切換動(dòng)作。此參數(shù)的最大和默認(rèn)值是1GB，該設(shè)置并不能嚴(yán)格控制Binlog的大小，尤其是Binlog比較靠近最大值而又遇到一個(gè)比較大事務(wù)時(shí)，為了保證事務(wù)的完整性，可能不做切換日志的動(dòng)作只能將該事務(wù)的所有SQL都記錄進(jìn)當(dāng)前日志，直到事務(wù)結(jié)束。一般情況下可采取默認(rèn)值。

設(shè)置帶文件夾的bin-log日志存放目錄

如果想改變?nèi)罩疚募哪夸浐兔Q，可以對(duì)my.cnf或my.ini中的log_bin參數(shù)修改如下：

[mysqld]
log-bin="/var/lib/mysql/binlog/atguigu-bin"

注意：新建的文件夾需要使用mysql用戶，使用下面的命令即可。

chown -R -v mysql:mysql binlog

提示數(shù)據(jù)庫(kù)文件最好不要與日志文件放在同一個(gè)磁盤上!這樣，當(dāng)數(shù)據(jù)庫(kù)文件所在的磁盤發(fā)生故障時(shí)，可以使用日志文件恢復(fù)數(shù)據(jù)。

方式 2 ：臨時(shí)性方式

如果不希望通過(guò)修改配置文件并重啟的方式設(shè)置二進(jìn)制日志的話，還可以使用如下指令，需要注意的是在mysql 8 中只有會(huì)話級(jí)別的設(shè)置，沒有了global級(jí)別的設(shè)置。

# global 級(jí)別
mysql> set global sql_log_bin= 0 ;
ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION variable and can`t be used
with SET GLOBAL

# session級(jí)別
mysql> SET sql_log_bin = 0 ;
Query OK, 0 rows affected (0.01 秒)

查看日志

當(dāng)MySQL創(chuàng)建二進(jìn)制日志文件時(shí)，先創(chuàng)建一個(gè)以“filename”為名稱、以“.index”為后綴的文件，再創(chuàng)建一個(gè)以“filename”為名稱、以“.000001”為后綴的文件。

MySQL服務(wù)重新啟動(dòng)一次，以“.000001”為后綴的文件就會(huì)增加一個(gè)，并且后綴名按 1 遞增。即日志文件的數(shù)與MySQL服務(wù)啟動(dòng)的次數(shù)相同；如果日志長(zhǎng)度超過(guò)了max_binlog_size的上限（默認(rèn)是1GB），就會(huì)創(chuàng)建一個(gè)新的日志文件。

查看當(dāng)前的二進(jìn)制日志文件列表及大小。指令如下：

mysql> SHOW BINARY LOGS;
+--------------------+-----------+-----------+
| Log_name           | File_size | Encrypted |
+--------------------+-----------+-----------+
| atguigu-bin.000001 | 156       | No        |
+--------------------+-----------+-----------+
1 rows in set (0.00 sec)

所有對(duì)數(shù)據(jù)庫(kù)的修改都會(huì)記錄在binglog中。但binlog是二進(jìn)制文件，無(wú)法直接查看，借助mysqlbinlog命令工具了。指令如下:在查看執(zhí)行，先執(zhí)行一條sQL語(yǔ)句，如下

update student set name='張三_back' where id=1;
[root@localhost ~]$ cd /var/lib/mysql
[root@localhost ~]$ mysqlbinlog  "/var/lib/mysql/lqhdb-binlog.000001"

執(zhí)行結(jié)果可以看到，這是一個(gè)簡(jiǎn)單的日志文件，日志中記錄了用戶的一些操作，這里并沒有出現(xiàn)具體的SQL語(yǔ)句，這是因?yàn)閎inlog關(guān)鍵字后面的內(nèi)容是經(jīng)過(guò)編碼后的二進(jìn)制日志。

這里一個(gè)update語(yǔ)句包含如下事件

Query事件負(fù)責(zé)開始一個(gè)事務(wù)(BEGIN)
Table_map事件負(fù)責(zé)映射需要的表
Update_rows事件負(fù)責(zé)寫入數(shù)據(jù)
Xid事件負(fù)責(zé)結(jié)束事務(wù)

下面命令將行事件以偽SQL的形式表現(xiàn)出來(lái)

mysqlbinlog -v "/var/lib/mysql/binlog/test.000002"

前面的命令同時(shí)顯示binlog格式的語(yǔ)句，使用如下命令不顯示它

mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/test.000002"

關(guān)于mysqlbinlog工具的使用技巧還有很多，例如只解析對(duì)某個(gè)庫(kù)的操作或者某個(gè)時(shí)間段內(nèi)的操作等。簡(jiǎn)單分享幾個(gè)常用的語(yǔ)句，更多操作可以參考官方文檔。

# 可查看參數(shù)幫助
mysqlbinlog --no-defaults --help

# 查看最后 100 行
mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |tail - 100

# 根據(jù)position查找
mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |grep -A 
20 '4939002'

上面這種辦法讀取出binlog日志的全文內(nèi)容比較多，不容易分辨查看到pos點(diǎn)信息，下面介紹一種更為方便的查詢命令：

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

IN 'log_name'：指定要查詢的binlog文件名（不指定就是第一個(gè)binlog文件）
FROM pos：指定從哪個(gè)pos起始點(diǎn)開始查起（不指定就是從整個(gè)文件首個(gè)pos點(diǎn)開始算）
LIMIT [offset]：偏移量(不指定就是 0 )
row_count :查詢總條數(shù)（不指定就是所有行）

上面這條語(yǔ)句可以將指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos點(diǎn)的起始偏移，查詢條數(shù)。其它舉例:

#a、查詢第一個(gè)最早的binlog日志:
show binlog events\G ;

#b、指定查詢mysql-bin.088802這個(gè)文件
show binlog events in 'atguigu-bin. 008002'\G;

#c、指定查詢mysql-bin. 080802這個(gè)文件，從pos點(diǎn):391開始查起:
show binlog events in 'atguigu-bin.008802' from 391\G;

#d、指定查詢mysql-bin.000802這個(gè)文件，從pos點(diǎn):391開始查起，查詢5條（即5條語(yǔ)句)
show binlog events in 'atguigu-bin.000882' from 391 limit 5\G

#e、指定查詢 mysql-bin.880002這個(gè)文件，從pos點(diǎn):391開始查起，偏移2行〈即中間跳過(guò)2個(gè)）查詢5條（即5條語(yǔ)句)。
show binlog events in 'atguigu-bin.088882' from 391 limit 2,5\G;

binlog格式查看

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   | //行格式
+---------------+-------+
1 rows in set (0.00 sec)

除此之外，binlog還有 2 種格式，分別是Statemen和Mixed

Statement 每一條會(huì)修改數(shù)據(jù)的sql都會(huì)記錄在binlog中。優(yōu)點(diǎn)：不需要記錄每一行的變化，減少了binlog日志量，節(jié)約了IO，提高性能。
Row 5.1.5版本的MySQL才開始支持row level 的復(fù)制，它不記錄sql語(yǔ)句上下文相關(guān)信息，僅保存哪條記錄被修改。優(yōu)點(diǎn)：row level 的日志內(nèi)容會(huì)非常清楚的記錄下每一行數(shù)據(jù)修改的細(xì)節(jié)。而且不會(huì)出現(xiàn)某些特定情況下的存儲(chǔ)過(guò)程，或function，以及trigger的調(diào)用和觸發(fā)無(wú)法被正確復(fù)制的問題。
Mixed 從5.1.8版本開始，MySQL提供了Mixed格式，實(shí)際上就是Statement與Row的結(jié)合。

使用日志恢復(fù)數(shù)據(jù)

mysqlbinlog恢復(fù)數(shù)據(jù)的語(yǔ)法如下：

mysqlbinlog [option] filename|mysql –uuser -ppass;

這個(gè)命令可以這樣理解：使用mysqlbinlog命令來(lái)讀取filename中的內(nèi)容，然后使用mysql命令將這些內(nèi)容恢復(fù)到數(shù)據(jù)庫(kù)中。

filename：是日志文件名。
option：可選項(xiàng)，比較重要的兩對(duì)option參數(shù)是–start-date、–stop-date 和 --start-position、–stop-position。
- –start-date 和 - -stop-date：可以指定恢復(fù)數(shù)據(jù)庫(kù)的起始時(shí)間點(diǎn)和結(jié)束時(shí)間點(diǎn)。
- –start-position和–stop-position：可以指定恢復(fù)數(shù)據(jù)的開始位置和結(jié)束位置。

注意：使用mysqlbinlog命令進(jìn)行恢復(fù)操作時(shí)，必須是編號(hào)小的先恢復(fù)，例如atguigu-bin.000001必須在atguigu-bin.000002之前恢復(fù)。

flush logs; #可以生成新的binLog 文件，不然這個(gè)文件邊恢復(fù)邊變大是不行的。

show binary logs; # 顯示有哪些binLog 文件

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

mysqlbinlog [option] filename|mysql –uuser -ppass;

mysqlbinlog --no-defaults  --start-position=236  --stop-position=1071 --database=my_db1 /var/lib/mysql/lqhdb-bin.000002 | /usr/bin/mysql -root -p123456 -v my_db1

刪除二進(jìn)制日志

MySQL的二進(jìn)制文件可以配置自動(dòng)刪除，同時(shí)MySQL也提供了安全的手動(dòng)刪除二進(jìn)制文件的方法。PURGE MASTER LOGS只刪除指定部分的二進(jìn)制日志文件，RESET MASTER刪除所有的二進(jìn)制日志文件。具體如下：

1.PURGE MASTER LOGS：刪除指定日志文件

PURGE MASTER LOGS語(yǔ)法如下：

PURGE {MASTER | BINARY} LOGS TO ‘指定日志文件名’

PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’

舉例 :使用PURGE MASTER LOGS語(yǔ)句刪除創(chuàng)建時(shí)間比binlog.000005早的所有日志

(1)多次重新啟動(dòng)MysSQL服務(wù)，便于生成多個(gè)日志文件。然后用SHOW語(yǔ)句顯示二進(jìn)制日志文件列表

SHOW BINARY LOGS;

(2）執(zhí)行PURGE MASTER LOGS語(yǔ)句刪除創(chuàng)建時(shí)間比binlog.000005早的所有日志

PURGE MASTER LOGS T0 "binlog. 000005";

(3）顯示二進(jìn)制日志文件列表

SHGW BINARY LOGS;

舉例:使用PURGE MASTER LOGS語(yǔ)句刪除2023年3月17日前創(chuàng)建的所有日志文件。具體步驟如下:

(1) 顯示二進(jìn)制日志文件列表

SHOW BINARY LOGS;

(2）執(zhí)行mysqlbinlog命令查看二進(jìn)制日志文件binlog.000005的內(nèi)容

mysqlbinlog --no-defaults "/var/lib/mysql/binlog/atguigu-bin.000005"

(3）使用PURGE MASTER LOGS語(yǔ)句刪除2023年3月17日前創(chuàng)建的所有日志文件

PURGE MASTER LOGS before "20220317";

(4）顯示二進(jìn)制日志文件列表

SHOW BINARY LOGS;

2022年01月05號(hào)之前的二進(jìn)制日志文件都已經(jīng)被刪除，最后一個(gè)沒有刪除，是因?yàn)楫?dāng)前在用，還未記錄最后的時(shí)間，所以未被刪除。

2.RESET MASTER:刪除所有二進(jìn)制日志文件

reset master;

其它場(chǎng)景

二進(jìn)制日志可以通過(guò)數(shù)據(jù)庫(kù)的全量備份和二進(jìn)制日志中保存的增量信息，完成數(shù)據(jù)庫(kù)的無(wú)損失恢復(fù)。但是，如果遇到數(shù)據(jù)量大、數(shù)據(jù)庫(kù)和數(shù)據(jù)表很多（比如分庫(kù)分表的應(yīng)用）的場(chǎng)景，用二進(jìn)制日志進(jìn)行數(shù)據(jù)恢復(fù)，是很有挑戰(zhàn)性的，因?yàn)槠鹬刮恢貌蝗菀坠芾怼?/p>

在這種情況下，一個(gè)有效的解決辦法是配置主從數(shù)據(jù)庫(kù)服務(wù)器，甚至是一主多從的架構(gòu)，把二進(jìn)制日志文件的內(nèi)容通過(guò)中繼日志，同步到從數(shù)據(jù)庫(kù)服務(wù)器中，這樣就可以有效避免數(shù)據(jù)庫(kù)故障導(dǎo)致的數(shù)據(jù)異常等問題。

深入理解二進(jìn)制日志

寫入機(jī)制

binlog的寫入時(shí)機(jī)也非常簡(jiǎn)單，事務(wù)執(zhí)行過(guò)程中，先把日志寫到binlog cache，事務(wù)提交的時(shí)候，再把binlog cache寫到binlog文件中。因?yàn)橐粋€(gè)事務(wù)的binlog不能被拆開，無(wú)論這個(gè)事務(wù)多大，也要確保一次性寫入，所以系統(tǒng)會(huì)給每個(gè)線程分配一個(gè)塊內(nèi)存作為binlog cache。

我們可以通過(guò)binlog_cache_size參數(shù)控制單個(gè)線程binlog cache大，如果存儲(chǔ)內(nèi)容超過(guò)了這個(gè)參數(shù)，就要暫存到磁盤(Swap)。binlog日志刷盤流程如下:

圖文結(jié)合帶你搞懂MySQL日志之Binary log（二進(jìn)制日志）

上圖的write，是指把日志寫入到文件系統(tǒng)的page cache，并沒有把數(shù)據(jù)持久化到磁盤，所以速度比較快。

上圖的fsync，才是將數(shù)據(jù)持久化到磁盤的操作

write和fsync的時(shí)機(jī)，可以由參數(shù)sync_binlog控制，默認(rèn)是 0 。

為 0 的時(shí)候，表示每次提交事務(wù)都只write，由系統(tǒng)自行判斷什么時(shí)候執(zhí)行fsync。雖然性能得到提升，但是機(jī)器宕機(jī)，page cache里面的binglog 會(huì)丟失。如下圖：

為了安全起見，可以設(shè)置為 1 ，表示每次提交事務(wù)都會(huì)執(zhí)行fsync，就如同 redo log 刷盤流程一樣。最后還有一種折中方式，可以設(shè)置為N(N>1)，表示每次提交事務(wù)都write，但累積N個(gè)事務(wù)后才fsync。

在出現(xiàn)IO瓶頸的場(chǎng)景里，將sync_binlog設(shè)置成一個(gè)比較大的值，可以提升性能。同樣的，如果機(jī)器宕機(jī)，會(huì)丟失最近N個(gè)事務(wù)的binlog日志。

binlog與redolog對(duì)比

redo log 它是物理日志，記錄內(nèi)容是“在某個(gè)數(shù)據(jù)頁(yè)上做了什么修改”，屬于 InnoDB 存儲(chǔ)引擎層產(chǎn)生的。
而 binlog 是邏輯日志，記錄內(nèi)容是語(yǔ)句的原始邏輯，類似于“給 ID=2 這一行的 c 字段加 1”，屬于MySQL Server 層
雖然它們都屬于持久化的保證，但是則重點(diǎn)不同。
- redo log讓InnoDB存儲(chǔ)引擎擁有了崩潰恢復(fù)能力。
- binlog保證了MySQL集群架構(gòu)的數(shù)據(jù)一致性。

Enjoy GreatSQL ??

關(guān)于 GreatSQL

GreatSQL是由萬(wàn)里數(shù)據(jù)庫(kù)維護(hù)的MySQL分支，專注于提升MGR可靠性及性能，支持InnoDB并行查詢特性，是適用于金融級(jí)應(yīng)用的MySQL分支版本。

相關(guān)鏈接： GreatSQL社區(qū) Gitee GitHub Bilibili

GreatSQL社區(qū)：

社區(qū)博客有獎(jiǎng)?wù)鞲逶斍椋篽ttps://greatsql.cn/thread-100-1-1.html

圖文結(jié)合帶你搞懂MySQL日志之Binary log（二進(jìn)制日志）

技術(shù)交流群：

微信：掃碼添加GreatSQL社區(qū)助手微信好友，發(fā)送驗(yàn)證信息加群。

圖文結(jié)合帶你搞懂MySQL日志之Binary log（二進(jìn)制日志）文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-471819.html

到了這里，關(guān)于圖文結(jié)合帶你搞懂MySQL日志之Binary log（二進(jìn)制日志）的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！