Binlog 簡介
MySQL中一般有以下幾種日志：
日志類型寫入日志的信息
錯誤日志記錄在啟動，運行或停止mysqld時遇到的問題
通用查詢?nèi)罩居涗浗⒌目蛻舳诉B接和執(zhí)行的語句
二進制日志記錄更改數(shù)據(jù)的語句
中繼日志從復(fù)制主服務(wù)器接收的數(shù)據(jù)更改
慢查詢?nèi)罩居涗浰袌?zhí)行時間超過?long_query_time?秒的所有查詢或不使用索引的查詢
DDL日志（元數(shù)據(jù)日志）元數(shù)據(jù)操作由DDL語句執(zhí)行
本文主要介紹二進制日志 binlog。
MySQL 的二進制日志 binlog 可以說是 MySQL 最重要的日志，它記錄了所有的?DDL?和?DML語句（除了數(shù)據(jù)查詢語句select、show等），以事件形式記錄，還包含語句所執(zhí)行的消耗的時間，MySQL的二進制日志是事務(wù)安全型的。binlog 的主要目的是復(fù)制和恢復(fù)。
Binlog日志的兩個最重要的使用場景
MySQL主從復(fù)制：MySQL Replication在Master端開啟binlog，Master把它的二進制日志傳遞給slaves來達到master-slave數(shù)據(jù)一致的目的
數(shù)據(jù)恢復(fù)：通過使用 mysqlbinlog工具來使恢復(fù)數(shù)據(jù)
啟用 Binlog
注：筆者實驗的MySQL版本為：5.7.22
一般來說開啟binlog日志大概會有1%的性能損耗。
啟用binlog，通過配置?/etc/my.cnf?或?/etc/mysql/mysql.conf.d/mysqld.cnf?配置文件的?log-bin?選項：
在配置文件中加入?log-bin?配置，表示啟用binlog，如果沒有給定值，寫成?log-bin=，則默認名稱為主機名。（注：名稱若帶有小數(shù)點，則只取第一個小數(shù)點前的部分作為名稱）
[mysqld]
log-bin=my-binlog-name
也可以通過?SET SQL_LOG_BIN=1?命令來啟用 binlog，通過?SET SQL_LOG_BIN=0?命令停用 binlog。啟用 binlog 之后須重啟MySQL才能生效。
常用的Binlog操作命令
#?是否啟用binlog日志
show variables like 'log_bin';
#?查看詳細的日志配置信息
show global variables like '%log%';
#?mysql數(shù)據(jù)存儲目錄
show variables like '%dir%';
#?查看binlog的目錄
show global variables like "%log_bin%";
#?查看當前服務(wù)器使用的biglog文件及大小
show binary logs;
#?查看主服務(wù)器使用的biglog文件及大小
#?查看最新一個binlog日志文件名稱和Position
show master status;
#?事件查詢命令
#?IN?'log_name'?：指定要查詢的binlog文件名(不指定就是第一個binlog文件)
#?FROM?pos?：指定從哪個pos起始點開始查起(不指定就是從整個文件首個pos點開始算)
#?LIMIT?[offset,]?：偏移量(不指定就是0)
#?row_count?：查詢總條數(shù)(不指定就是所有行)
show binlog events [IN'log_name']?[FROMpos]?[LIMIT[offset,]row_count];
#?查看?binlog?內(nèi)容
show binlog events;
#?查看具體一個binlog文件的內(nèi)容?（in?后面為binlog的文件名）
show binlog events in 'master.000003';
#?設(shè)置binlog文件保存事件，過期刪除，單位天
set global expire_log_days=3;
#?刪除當前的binlog文件
reset master;
#?刪除slave的中繼日志
reset slave;
#?刪除指定日期前的日志索引中binlog日志文件
purge master logs before '2019-03-09?14:00:00';
#?刪除指定日志文件
purge master logs to 'master.000003';
寫 Binlog 的時機
對支持事務(wù)的引擎如InnoDB而言，必須要提交了事務(wù)才會記錄binlog。binlog 什么時候刷新到磁盤跟參數(shù)?sync_binlog?相關(guān)。
如果設(shè)置為0，則表示MySQL不控制binlog的刷新，由文件系統(tǒng)去控制它緩存的刷新；
如果設(shè)置為不為0的值，則表示每?sync_binlog?次事務(wù)，MySQL調(diào)用文件系統(tǒng)的刷新操作刷新binlog到磁盤中。
設(shè)為1是最安全的，在系統(tǒng)故障時最多丟失一個事務(wù)的更新，但是會對性能有所影響。
如果?sync_binlog=0?或?sync_binlog大于1，當發(fā)生電源故障或操作系統(tǒng)崩潰時，可能有一部分已提交但其binlog未被同步到磁盤的事務(wù)會被丟失，恢復(fù)程序?qū)o法恢復(fù)這部分事務(wù)。
在MySQL 5.7.7之前，默認值 sync_binlog 是0，MySQL 5.7.7和更高版本使用默認值1，這是最安全的選擇。一般情況下會設(shè)置為100或者0，犧牲一定的一致性來獲取更好的性能。
Binlog 文件以及擴展
binlog日志包括兩類文件:
二進制日志索引文件（文件名后綴為.index）用于記錄所有有效的的二進制文件
二進制日志文件（文件名后綴為.00000*）記錄數(shù)據(jù)庫所有的DDL和DML語句事件
binlog是一個二進制文件集合，每個binlog文件以一個4字節(jié)的魔數(shù)開頭，接著是一組Events:
魔數(shù)：0xfe62696e對應(yīng)的是0xfebin；
Event：每個Event包含header和data兩個部分；header提供了Event的創(chuàng)建時間，哪個服務(wù)器等信息，data部分提供的是針對該Event的具體信息，如具體數(shù)據(jù)的修改；
第一個Event用于描述binlog文件的格式版本，這個格式就是event寫入binlog文件的格式；
其余的Event按照第一個Event的格式版本寫入；
最后一個Event用于說明下一個binlog文件；
binlog的索引文件是一個文本文件，其中內(nèi)容為當前的binlog文件列表
當遇到以下3種情況時，MySQL會重新生成一個新的日志文件，文件序號遞增：
MySQL服務(wù)器停止或重啟時
使用?flush logs?命令；
當 binlog 文件大小超過?max_binlog_size?變量的值時；
max_binlog_size?的最小值是4096字節(jié)，最大值和默認值是 1GB (1073741824字節(jié))。事務(wù)被寫入到binlog的一個塊中，所以它不會在幾個二進制日志之間被拆分。因此，如果你有很大的事務(wù)，為了保證事務(wù)的完整性，不可能做切換日志的動作，只能將該事務(wù)的日志都記錄到當前日志文件中，直到事務(wù)結(jié)束，你可能會看到binlog文件大于 max_binlog_size 的情況。
Binlog 的日志格式
記錄在二進制日志中的事件的格式取決于二進制記錄格式。支持三種格式類型：
STATEMENT：基于SQL語句的復(fù)制（statement-based replication, SBR）
ROW：基于行的復(fù)制（row-based replication, RBR）
MIXED：混合模式復(fù)制（mixed-based replication, MBR）
在?MySQL 5.7.7?之前，默認的格式是?STATEMENT，在?MySQL 5.7.7?及更高版本中，默認值是?ROW。日志格式通過?binlog-format?指定，如?binlog-format=STATEMENT、binlog-format=ROW、binlog-format=MIXED。
Statement
每一條會修改數(shù)據(jù)的sql都會記錄在binlog中
優(yōu)點：不需要記錄每一行的變化，減少了binlog日志量，節(jié)約了IO, 提高了性能。
缺點：由于記錄的只是執(zhí)行語句，為了這些語句能在slave上正確運行，因此還必須記錄每條語句在執(zhí)行的時候的一些相關(guān)信息，以保證所有語句能在slave得到和在master端執(zhí)行的時候相同的結(jié)果。另外mysql的復(fù)制，像一些特定函數(shù)的功能，slave與master要保持一致會有很多相關(guān)問題。
Row
5.1.5版本的MySQL才開始支持?row level?的復(fù)制,它不記錄sql語句上下文相關(guān)信息，僅保存哪條記錄被修改。
優(yōu)點： binlog中可以不記錄執(zhí)行的sql語句的上下文相關(guān)的信息，僅需要記錄那一條記錄被修改成什么了。所以row的日志內(nèi)容會非常清楚的記錄下每一行數(shù)據(jù)修改的細節(jié)。而且不會出現(xiàn)某些特定情況下的存儲過程，或function，以及trigger的調(diào)用和觸發(fā)無法被正確復(fù)制的問題.
缺點:所有的執(zhí)行的語句當記錄到日志中的時候，都將以每行記錄的修改來記錄，這樣可能會產(chǎn)生大量的日志內(nèi)容。
注：將二進制日志格式設(shè)置為ROW時，有些更改仍然使用基于語句的格式，包括所有DDL語句，例如CREATE TABLE， ALTER TABLE，或 DROP TABLE。
Mixed
從5.1.8版本開始，MySQL提供了Mixed格式，實際上就是Statement與Row的結(jié)合。?
在Mixed模式下，一般的語句修改使用statment格式保存binlog，如一些函數(shù)，statement無法完成主從復(fù)制的操作，則采用row格式保存binlog，MySQL會根據(jù)執(zhí)行的每一條具體的sql語句來區(qū)分對待記錄的日志形式，也就是在Statement和Row之間選擇一種。
mysql binlog 命令的使用
服務(wù)器以二進制格式將binlog日志寫入binlog文件，如何要以文本格式顯示其內(nèi)容，可以使用 mysql binlog 命令。
#?mysqlbinlog?的執(zhí)行格式
mysqlbinlog?[options]?log_file?...
#
?查看bin-log二進制文件（shell方式）
mysqlbinlog?-v?--base64-output=decode-rows?/var/lib/mysql/master.000003
#
?查看bin-log二進制文件（帶查詢條件）
mysqlbinlog?-v?--base64-output=decode-rows?/var/lib/mysql/master.000003?\
--start-datetime="2019-03-01?00:00:00"??\
--stop-datetime="2019-03-10?00:00:00"???\
--start-position="5000"????\
--stop-position="20000"
設(shè)置日志格式為ROW時，在我的機器上輸出了以下信息
/*!50530?SET?@@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003?SET?@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER/*!*/;
#?at?4
#190308?10:05:03?server?id?1??end_log_pos?123?CRC32?0xff02e23d?????Start:?binlog?v?4,?server?v?5.7.22-log?created?190308?10:05:03
#?Warning:?this?binlog?is?either?in?use?or?was?not?closed?properly.
#?at?123
#190308?10:05:03?server?id?1??end_log_pos?154?CRC32?0xb81da4c5?????Previous-GTIDs
#?[empty]
#?at?154
#190308?10:05:09?server?id?1??end_log_pos?219?CRC32?0xfb30d42c?????Anonymous_GTID??last_committed=0????sequence_number=1???rbr_only=yes
/*!50718?SET?TRANSACTION?ISOLATION?LEVEL?READ?COMMITTED*//*!*/;
SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;
#?at?219
...
...
#?at?21019
#190308?10:10:09?server?id?1??end_log_pos?21094?CRC32?0x7a405abc?????Query???thread_id=113???exec_time=0?error_code=0
SETTIMESTAMP=1552011009/*!*/;
BEGIN
/*!*/;
#?at?21094
#190308?10:10:09?server?id?1??end_log_pos?21161?CRC32?0xdb7a2b35?????Table_map:?`maxwell`.`positions`?mapped?to?number?110
#?at?21161
#190308?10:10:09?server?id?1??end_log_pos?21275?CRC32?0xec3be372?????Update_rows:?table?id?110?flags:?STMT_END_F
###?UPDATE?`maxwell`.`positions`
###?WHERE
###???@1=1
###???@2='master.000003'
###???@3=20262
###???@4=NULL
###???@5='maxwell'
###???@6=NULL
###???@7=1552011005707
###?SET
###???@1=1
###???@2='master.000003'
###???@3=20923
###???@4=NULL
###???@5='maxwell'
###???@6=NULL
###???@7=1552011009790
#?at?21275
#190308?10:10:09?server?id?1??end_log_pos?21306?CRC32?0xe6c4346d?????Xid?=?13088
COMMIT/*!*/;
SET@@SESSION.GTID_NEXT='AUTOMATIC'/*?added?by?mysqlbinlog?*//*!*/;
DELIMITER?;
#?End?of?log?file
/*!50003?SET?COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530?SET?@@SESSION.PSEUDO_SLAVE_MODE=0*/;
截取其中的一段進行分析：
#?at?21019
#190308?10:10:09?server?id?1??end_log_pos?21094?CRC32?0x7a405abc?????Query???thread_id=113???exec_time=0?error_code=0
SETTIMESTAMP=1552011009/*!*/;
BEGIN
/*!*/;
上面輸出包括信息：
position: 位于文件中的位置，即第一行的（# at 21019）,說明該事件記錄從文件第21019個字節(jié)開始
timestamp: 事件發(fā)生的時間戳，即第二行的（#190308 10:10:09）
server id: 服務(wù)器標識（1）
end_log_pos 表示下一個事件開始的位置（即當前事件的結(jié)束位置+1）
thread_id: 執(zhí)行該事件的線程id （thread_id=113）
exec_time: 事件執(zhí)行的花費時間
error_code: 錯誤碼，0意味著沒有發(fā)生錯誤
type:事件類型Query
Binlog 事件類型
binlog 事件的結(jié)構(gòu)主要有3個版本：
v1: 在 MySQL 3.23 中使用
v3: 在 MySQL 4.0.2 到 4.1 中使用
v4: 在 MySQL 5.0 及以上版本中使用
現(xiàn)在一般不會使用MySQL5.0以下版本，所以下面僅介紹v4版本的binlog事件類型。binlog 的事件類型較多，本文在此做一些簡單的匯總
事件類型說明
UNKNOWN_EVENT此事件從不會被觸發(fā)，也不會被寫入binlog中；發(fā)生在當讀取binlog時，不能被識別其他任何事件，那被視為UNKNOWN_EVENT
START_EVENT_V3每個binlog文件開始的時候?qū)懭氲氖录?，此事件被用在MySQL3.23 – 4.1，MYSQL5.0以后已經(jīng)被 FORMAT_DESCRIPTION_EVENT 取代
QUERY_EVENT執(zhí)行更新語句時會生成此事件，包括：create，insert，update，delete；
STOP_EVENT當mysqld停止時生成此事件
ROTATE_EVENT當mysqld切換到新的binlog文件生成此事件，切換到新的binlog文件可以通過執(zhí)行flush logs命令或者binlog文件大于?max_binlog_size?參數(shù)配置的大??；
INTVAR_EVENT當sql語句中使用了AUTO_INCREMENT的字段或者LAST_INSERT_ID()函數(shù)；此事件沒有被用在binlog_format為ROW模式的情況下
LOAD_EVENT執(zhí)行LOAD DATA INFILE 語句時產(chǎn)生此事件，在MySQL 3.23版本中使用
SLAVE_EVENT未使用
CREATE_FILE_EVENT執(zhí)行LOAD DATA INFILE 語句時產(chǎn)生此事件，在MySQL4.0和4.1版本中使用
APPEND_BLOCK_EVENT執(zhí)行LOAD DATA INFILE 語句時產(chǎn)生此事件，在MySQL4.0版本中使用
EXEC_LOAD_EVENT執(zhí)行LOAD DATA INFILE 語句時產(chǎn)生此事件，在MySQL4.0和4.1版本中使用
DELETE_FILE_EVENT執(zhí)行LOAD DATA INFILE 語句時產(chǎn)生此事件，在MySQL4.0版本中使用
NEW_LOAD_EVENT執(zhí)行LOAD DATA INFILE 語句時產(chǎn)生此事件，在MySQL4.0和4.1版本中使用
RAND_EVENT執(zhí)行包含RAND()函數(shù)的語句產(chǎn)生此事件，此事件沒有被用在binlog_format為ROW模式的情況下
USER_VAR_EVENT執(zhí)行包含了用戶變量的語句產(chǎn)生此事件，此事件沒有被用在binlog_format為ROW模式的情況下
FORMAT_DESCRIPTION_EVENT描述事件，被寫在每個binlog文件的開始位置，用在MySQL5.0以后的版本中，代替了START_EVENT_V3
XID_EVENT支持XA的存儲引擎才有，本地測試的數(shù)據(jù)庫存儲引擎是innodb，所有上面出現(xiàn)了XID_EVENT；innodb事務(wù)提交產(chǎn)生了QUERY_EVENT的BEGIN聲明，QUERY_EVENT以及COMMIT聲明，如果是myIsam存儲引擎也會有BEGIN和COMMIT聲明，只是COMMIT類型不是XID_EVENT
BEGIN_LOAD_QUERY_EVENT執(zhí)行LOAD DATA INFILE 語句時產(chǎn)生此事件，在MySQL5.0版本中使用
EXECUTE_LOAD_QUERY_EVENT執(zhí)行LOAD DATA INFILE 語句時產(chǎn)生此事件，在MySQL5.0版本中使用
TABLE_MAP_EVENT用在binlog_format為ROW模式下，將表的定義映射到一個數(shù)字，在行操作事件之前記錄（包括：WRITE_ROWS_EVENT，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT）
PRE_GA_WRITE_ROWS_EVENT已過期，被 WRITE_ROWS_EVENT 代替
PRE_GA_UPDATE_ROWS_EVENT已過期，被 UPDATE_ROWS_EVENT 代替
PRE_GA_DELETE_ROWS_EVENT已過期，被 DELETE_ROWS_EVENT 代替
WRITE_ROWS_EVENT用在binlog_format為ROW模式下，對應(yīng) insert 操作
UPDATE_ROWS_EVENT用在binlog_format為ROW模式下，對應(yīng) update 操作
DELETE_ROWS_EVENT用在binlog_format為ROW模式下，對應(yīng) delete 操作
INCIDENT_EVENT主服務(wù)器發(fā)生了不正常的事件，通知從服務(wù)器并告知可能會導(dǎo)致數(shù)據(jù)處于不一致的狀態(tài)
HEARTBEAT_LOG_EVENT主服務(wù)器告訴從服務(wù)器，主服務(wù)器還活著，不寫入到日志文件中
Binlog 事件的結(jié)構(gòu)
一個事件對象分為事件頭和事件體，事件的結(jié)構(gòu)如下：
+=====================================+
|?event??|timestamp0:4|
|
header?+----------------------------+
|????????|type_code4:1|
|
+----------------------------+
|????????|server_id5:4|
|
+----------------------------+
|????????|event_length9:4|
|
+----------------------------+
|????????|next_position13:4|
|
+----------------------------+
|????????|flags17:2|
|
+----------------------------+
|????????|extra_headers19:?x-19|
+=====================================+
|
event|?fixed?part????????x?:?y????|
|?data???+----------------------------+
|
|?variable?part??????????????|
+=====================================+
如果事件頭的長度是?x?字節(jié)，那么事件體的長度為?(event_length - x)?字節(jié)；設(shè)事件體中?fixed part?的長度為?y?字節(jié)，那么?variable part?的長度為?(event_length - (x + y))字節(jié)
Binlog Event 簡要分析
從一個最簡單的實例來分析Event，包括創(chuàng)建表，插入數(shù)據(jù)，更新數(shù)據(jù)，刪除數(shù)據(jù)；
CREATE TABLE `test`(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`age` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into test values(1,22,"小旋鋒");
update test set name='whirly' where id=1;
delete from test where id=1;
日志格式為STATEMENT，查看所有的Event
STATEMENT格式下create、insert、update、delete操作產(chǎn)生的binlog事件
日志格式為ROW時是下面這樣，可以發(fā)現(xiàn)又有一些不同
ROW格式下create、insert、update、delete操作產(chǎn)生的binlog事件
關(guān)于Event的分析，有需要可以查看參考文檔進行推算。
參考文檔
MySQL 5.7參考手冊.二進制日志
MySQL Internals Manual.The Binary Log
朱小廝.MySQL Binlog解析
七把刀.MySQL binlog格式解析
散盡浮華.Mysql之binlog日志說明及利用binlog日志恢復(fù)數(shù)據(jù)操作記錄
MySql Binlog 初識
MySQL5.7殺手級新特性：GTID原理與實戰(zhàn)
MySQL 5.7 基于 GTID 的主從復(fù)制實踐
