如果不希望通過修改配置文件并重啟的方式設(shè)置二進制日志的話，還可以使用如下指令，需要注意的是在MySQL 8 中只有會話級別的設(shè)置，沒有了global級別的設(shè)置。
此篇為圖文結(jié)合搞懂MySQL日志的最后一篇，MySQL中共有八大日志，其中數(shù)據(jù)定義語句日志不是給用戶查看的，在此篇尾部簡單介紹，接下來圖文結(jié)合系列還會繼續(xù)推出新文章！
二進制日志(Binary log)
binlog可以說是MySQL中比較重要的日志了，在日常開發(fā)及運維過程中，經(jīng)常會遇到。
binlog即binary log，二進制日志文件，也叫作變更日志（update log）。它記錄了數(shù)據(jù)庫所有執(zhí)行的DDL和DML等數(shù)據(jù)庫更新事件的語句，但是不包含沒有修改任何數(shù)據(jù)的語句（如數(shù)據(jù)查詢語句select、show等）。
它以事件形式記錄并保存在二進制文件中。通過這些信息，我們可以再現(xiàn)數(shù)據(jù)更新操作的全過程。
如果想要記錄所有語句（例如，為了識別有問題的查詢)，需要使用通用查詢?nèi)罩尽?/p>
Binary log主要應用場景：
- 一是用于數(shù)據(jù)恢復
- 二是用于數(shù)據(jù)復制，由于日志的延續(xù)性和時效性，master把它的二進制日志傳遞給slaves來達到master-slave數(shù)據(jù)一致的目的。
可以說MySQL數(shù)據(jù)庫的數(shù)據(jù)備份、主備、單主、多主、MGR都離不開Binary log,需要依靠Binary log來同步數(shù)據(jù)，保證數(shù)據(jù)一致性。
查看默認情況
查看記錄二進制日志是否開啟：在MySQL8中默認情況下，二進制文件是開啟的。
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記錄下來
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)
- log_bin_basename:是binlog日志的基本文件名，后面會追加標識來表示每一個文件
- log_bin_index:是binlog文件的素引文件，這個文件管理了所有的binlog文件的目錄
- log_bin_trust_function_creators:限制存儲過程，前面我們已經(jīng)講過了，這是因為二進制日志的一個重要功能是用于主從復制，而存儲函數(shù)有可能導致主從的數(shù)據(jù)不一致。所以當開啟二進制日志后，需要限制存儲函數(shù)的創(chuàng)建、修改、調(diào)用
- log_bin_use_v1_row_events此只讀系統(tǒng)變量已棄用。ON表示使用版本1二進制日志行，OFF表示使用版本2二進制日志行（MySQL5.6的默認值為2)。
日志參數(shù)設(shè)置
方式 1 ：永久性方式
修改MySQL的my.cnf或my.ini文件可以設(shè)置二進制日志的相關(guān)參數(shù)：
[mysqld]
#啟用二進制日志
log-bin=atguigu-bin
binlog_expire_logs_secnotallow= 600
max_binlog_size=100M
提示:
log-bin=mysql-bin
打開日志(主機需要打開)，這個mysql-bin也可以自定義，這里也可以加上路徑，如:/home/www/mysql_bin_log/mysql-bin
binlog_expire_logs_seconds
此參數(shù)控制二進制日志文件保留的時長單位是秒，默認2592000 30天 --14400 4小時;86400 1天; 259200 3天;
max_binlog_size
控制單個二進制日志大小，當前日志文件大小超過此變量時，執(zhí)行切換動作。此參數(shù)的最大和默認值是1GB，該設(shè)置并不能嚴格控制Binlog的大小，尤其是Binlog比較靠近最大值而又遇到一個比較大事務時，為了保證事務的完整性，可能不做切換日志的動作只能將該事務的所有SQL都記錄進當前日志，直到事務結(jié)束。一般情況下可采取默認值。
設(shè)置帶文件夾的bin-log日志存放目錄
如果想改變?nèi)罩疚募哪夸浐兔Q，可以對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ù)庫文件最好不要與日志文件放在同一個磁盤上!這樣，當數(shù)據(jù)庫文件所在的磁盤發(fā)生故障時，可以使用日志文件恢復數(shù)據(jù)。
方式 2 ：臨時性方式
如果不希望通過修改配置文件并重啟的方式設(shè)置二進制日志的話，還可以使用如下指令，需要注意的是在mysql 8 中只有會話級別的設(shè)置，沒有了global級別的設(shè)置。
# global 級別
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級別
mysql> SET sql_log_bin = 0 ;
Query OK, 0 rows affected (0.01 秒)
查看日志
當MySQL創(chuàng)建二進制日志文件時，先創(chuàng)建一個以“filename”為名稱、以“.index”為后綴的文件，再創(chuàng)建一個以“filename”為名稱、以“.000001”為后綴的文件。
MySQL服務重新啟動一次，以“.000001”為后綴的文件就會增加一個，并且后綴名按 1 遞增。即日志文件的數(shù)與MySQL服務啟動的次數(shù)相同；如果日志長度超過了max_binlog_size的上限（默認是1GB），就會創(chuàng)建一個新的日志文件。
查看當前的二進制日志文件列表及大小。指令如下：
mysql> SHOW BINARY LOGS;
+--------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+--------------------+-----------+-----------+
| greatsql-bin.000001 | 156 | No |
+--------------------+-----------+-----------+
1 rows in set (0.00 sec)
所有對數(shù)據(jù)庫的修改都會記錄在binglog中。但binlog是二進制文件，無法直接查看，借助mysqlbinlog命令工具了。指令如下:在查看執(zhí)行，先執(zhí)行一條sQL語句，如下
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é)果可以看到，這是一個簡單的日志文件，日志中記錄了用戶的一些操作，這里并沒有出現(xiàn)具體的SQL語句，這是因為binlog關(guān)鍵字后面的內(nèi)容是經(jīng)過編碼后的二進制日志。
這里一個update語句包含如下事件
- Query事件負責開始一個事務(BEGIN)
- Table_map事件負責映射需要的表
- Update_rows事件負責寫入數(shù)據(jù)
- Xid事件負責結(jié)束事務
下面命令將行事件以偽SQL的形式表現(xiàn)出來
mysqlbinlog -v "/var/lib/mysql/binlog/test.000002"
前面的命令同時顯示binlog格式的語句，使用如下命令不顯示它
mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/test.000002"
關(guān)于mysqlbinlog工具的使用技巧還有很多，例如只解析對某個庫的操作或者某個時間段內(nèi)的操作等。簡單分享幾個常用的語句，更多操作可以參考官方文檔。
# 可查看參數(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點信息，下面介紹一種更為方便的查詢命令：
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
- IN 'log_name'：
- FROM pos：指定從哪個pos起始點開始查起（不指定就是從整個文件首個pos點開始算）
- LIMIT [offset]：偏移量(不指定就是 0 )
- row_count :查詢總條數(shù)（不指定就是所有行）
上面這條語句可以將指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos點的起始偏移，查詢條數(shù)。其它舉例:
#a、查詢第一個最早的binlog日志:
show binlog eventsG ;
#b、指定查詢mysql-bin.088802這個文件
show binlog events in 'atguigu-bin. 008002'G;
#c、指定查詢mysql-bin. 080802這個文件，從pos點:391開始查起:
show binlog events in 'atguigu-bin.008802' from 391G;
#d、指定查詢mysql-bin.000802這個文件，從pos點:391開始查起，查詢5條（即5條語句)
show binlog events in 'atguigu-bin.000882' from 391 limit 5G
#e、指定查詢 mysql-bin.880002這個文件，從pos點:391開始查起，偏移2行〈即中間跳過2個）查詢5條（即5條語句)。
show binlog events in 'atguigu-bin.088882' from 391 limit 2,5G;
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 每一條會修改數(shù)據(jù)的sql都會記錄在binlog中。 優(yōu)點：不需要記錄每一行的變化，減少了binlog日志量，節(jié)約了IO，提高性能。
Row 5.1.5版本的MySQL才開始支持row level 的復制，它不記錄sql語句上下文相關(guān)信息，僅保存哪條記錄被修改。 優(yōu)點：row level 的日志內(nèi)容會非常清楚的記錄下每一行數(shù)據(jù)修改的細節(jié)。而且不會出現(xiàn)某些特定情況下的存儲過程，或function，以及trigger的調(diào)用和觸發(fā)無法被正確復制的問題。
Mixed 從5.1.8版本開始，MySQL提供了Mixed格式，實際上就是Statement與Row的結(jié)合。
使用日志恢復數(shù)據(jù)
mysqlbinlog恢復數(shù)據(jù)的語法如下：
mysqlbinlog [option] filename|mysql –uuser -ppass;
這個命令可以這樣理解：使用mysqlbinlog命令來讀取filename中的內(nèi)容，然后使用mysql命令將這些內(nèi)容恢復到數(shù)據(jù)庫中。
- filename：是日志文件名。
- option：可選項，比較重要的兩對option參數(shù)是–start-date、–stop-date 和 --start-position、–stop-position。
–start-date 和 - -stop-date：可以指定恢復數(shù)據(jù)庫的起始時間點和結(jié)束時間點。
–start-position和–stop-position：可以指定恢復數(shù)據(jù)的開始位置和結(jié)束位置。
注意：使用mysqlbinlog命令進行恢復操作時，必須是編號小的先恢復，例如atguigu-bin.000001必須在atguigu-bin.000002之前恢復。
flush logs; #可以生成新的binLog 文件，不然這個文件邊恢復邊變大是不行的。
show binary logs; # 顯示有哪些binLog 文件
恢復數(shù)據(jù)
mysqlbinlog [option] filename|mysql –uuser -ppass;
mysqlbinlog --no-defaults --start-positinotallow=236 --stop-positinotallow=1071 --database=my_db1 /var/lib/mysql/lqhdb-bin.000002 | /usr/bin/mysql -root -p123456 -v my_db1
刪除二進制日志
MySQL的二進制文件可以配置自動刪除，同時MySQL也提供了安全的手動刪除二進制文件的方法。PURGE MASTER LOGS只刪除指定部分的二進制日志文件，RESET MASTER刪除所有的二進制日志文件。具體如下：
1.PURGE MASTER LOGS：刪除指定日志文件
PURGE MASTER LOGS語法如下：
PURGE {MASTER | BINARY} LOGS TO ‘指定日志文件名’
PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’
**舉例 :**使用PURGE MASTER LOGS語句刪除創(chuàng)建時間比binlog.000005早的所有日志
(1)多次重新啟動MysSQL服務，便于生成多個日志文件。然后用SHOW語句顯示二進制日志文件列表
SHOW BINARY LOGS;
(2）執(zhí)行PURGE MASTER LOGS語句刪除創(chuàng)建時間比binlog.000005早的所有日志
PURGE MASTER LOGS T0 "binlog. 000005";
(3）顯示二進制日志文件列表
SHGW BINARY LOGS;
舉例:使用PURGE MASTER LOGS語句刪除2023年3月17日前創(chuàng)建的所有日志文件。具體步驟如下:
(1) 顯示二進制日志文件列表
SHOW BINARY LOGS;
(2）執(zhí)行mysqlbinlog命令查看二進制日志文件binlog.000005的內(nèi)容
mysqlbinlog --no-defaults "/var/lib/mysql/binlog/atguigu-bin.000005"
(3）使用PURGE MASTER LOGS語句刪除2023年3月17日前創(chuàng)建的所有日志文件
PURGE MASTER LOGS before "20220317";
(4）顯示二進制日志文件列表
SHOW BINARY LOGS;
2022年01月05號之前的二進制日志文件都已經(jīng)被刪除，最后一個沒有刪除，是因為當前在用，還未記錄最后的時間，所以未被刪除。
2.RESET MASTER:刪除所有二進制日志文件
reset master;
其它場景
二進制日志可以通過數(shù)據(jù)庫的全量備份和二進制日志中保存的增量信息，完成數(shù)據(jù)庫的無損失恢復。但是，如果遇到數(shù)據(jù)量大、數(shù)據(jù)庫和數(shù)據(jù)表很多（比如分庫分表的應用）的場景，用二進制日志進行數(shù)據(jù)恢復，是很有挑戰(zhàn)性的，因為起止位置不容易管理。
在這種情況下，一個有效的解決辦法是配置主從數(shù)據(jù)庫服務器，甚至是一主多從的架構(gòu)，把二進制日志文件的內(nèi)容通過中繼日志，同步到從數(shù)據(jù)庫服務器中，這樣就可以有效避免數(shù)據(jù)庫故障導致的數(shù)據(jù)異常等問題。
深入理解二進制日志
寫入機制
binlog的寫入時機也非常簡單，事務執(zhí)行過程中，先把日志寫到binlog cache，事務提交的時候，再把binlog cache寫到binlog文件中。因為一個事務的binlog不能被拆開，無論這個事務多大，也要確保一次性寫入，所以系統(tǒng)會給每個線程分配一個塊內(nèi)存作為binlog cache。
我們可以通過binlog_cache_size參數(shù)控制單個線程binlog cache大，如果存儲內(nèi)容超過了這個參數(shù)，就要暫存到磁盤(Swap)。binlog日志刷盤流程如下:
上圖的write，是指把日志寫入到文件系統(tǒng)的page cache，并沒有把數(shù)據(jù)持久化到磁盤，所以速度比較快。
上圖的fsync，才是將數(shù)據(jù)持久化到磁盤的操作
write和fsync的時機，可以由參數(shù)sync_binlog控制，默認是 0 。
為 0 的時候，表示每次提交事務都只write，由系統(tǒng)自行判斷什么時候執(zhí)行fsync。雖然性能得到提升，但是機器宕機，page cache里面的binglog 會丟失。如下圖：
為了安全起見，可以設(shè)置為 1 ，表示每次提交事務都會執(zhí)行fsync，就如同 redo log 刷盤流程 一樣。最后還有一種折中方式，可以設(shè)置為N(N>1)，表示每次提交事務都write，但累積N個事務后才fsync。
在出現(xiàn)IO瓶頸的場景里，將sync_binlog設(shè)置成一個比較大的值，可以提升性能。同樣的，如果機器宕機，會丟失最近N個事務的binlog日志。
binlog與redolog對比
- redo log 它是物理日志，記錄內(nèi)容是“在某個數(shù)據(jù)頁上做了什么修改”，屬于 InnoDB 存儲引擎層產(chǎn)生的。
- 而 binlog 是邏輯日志，記錄內(nèi)容是語句的原始邏輯，類似于“給 ID=2 這一行的 c 字段加 1”，屬于MySQL Server 層
- 雖然它們都屬于持久化的保證，但是則重點不同。
- redo log讓InnoDB存儲引擎擁有了崩潰恢復能力。
- binlog保證了MySQL集群架構(gòu)的數(shù)據(jù)一致性。
