binlog相關 MySQL 的二進位日誌 binlog 可以說是 MySQL 最重要的日誌，它記錄了所有的 DDL 和 DML 語句（除了數據查詢語句select、show等），以事件形式記錄，還包含語句所執行的消耗的時間，MySQL的二進位日誌是事務安全型的。binlog 的主要目的是複製和恢復 ...
binlog相關
MySQL 的二進位日誌 binlog 可以說是 MySQL 最重要的日誌，它記錄了所有的 DDL 和 DML 語句（除了數據查詢語句select、show等），以事件形式記錄，還包含語句所執行的消耗的時間，MySQL的二進位日誌是事務安全型的。binlog 的主要目的是複製和恢復。
Binlog日誌的兩個最重要的使用場景
MySQL主從複製：MySQL Replication在Master端開啟binlog，Master把它的二進位日誌傳遞給slaves來達到master-slave數據一致的目的
數據恢復：通過使用 mysqlbinlog工具來使恢複數據
總體的關於binlog的參數如下
sync_binlog = 1 log-bin = mysql-bin binlog_format = row expire_logs_days = 10 binlog_cache_size =4M max_binlog_cache_size =8M max_binlog_size =1024M
相關參數講解
log-bin
決定了msyql 的binlog的名字，⽣成的binlog名字為mysql-bin.000001
binlog_format
規定binlog的格式， binlog有三種格式statement， row以及mixed，預設使⽤預設使⽤statement（5.7.7之前），建議使⽤row格式（5.7.7之後預設）
expire_logs_days
過期時間
binlog_do_db
此參數表示只記錄指定資料庫的⼆進位⽇志
binlog_ignore_db
此參數表示不記錄指定的資料庫的⼆進位⽇志
sync_binlog
在提交n次事務後，進⾏binlog的落盤， 0為不進⾏強⾏的刷新操作，⽽是由⽂件系統控制刷新⽇志⽂件
如果是線上交易和帳有關的數據建議設置成1, 如果是其它數據可以保持為０即可。
max_binlog_size
binlog⽂件的最⼤值，預設和最⼤是1GB，並不能嚴格限定⼆進位⽂件的⼤⼩
max_binlog_cache_size 表示的是binlog 能夠使⽤的最⼤cache 記憶體⼤⼩
當我們執⾏多語句事務的時候 所有session的使⽤的記憶體超過max_binlog_cache_size的值時
就會報錯： “Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”
mysqlbinlog格式
statment格式
特點：記錄每⼀條數據的SQL，將執⾏的每⼀條SQL記錄在binlog中，減少⽇志量，節省IO,提⾼性能。
缺點：某些SQL中的函數⽆法使⽤，⽐如SYSDATE()，在同步過程中會出現⽆法同步的問題。
mixed格式
特點：⼀般的語句使⽤SQL語句來記錄，遇到特殊的語句使⽤row格式來記錄，保證數據的⼀致性和複製的準確性。
row格式
特點： binlog中僅僅記錄哪⼀條記錄被修改，不記錄dml的sql語句，會詳細記錄每⼀行的更改細節，不會出現⽆法複製的問題。
缺點：因為要記錄每⼀條修改記錄的⽇志，所以⼤量占⽤磁碟IO和⼤量使⽤硬碟空間。
註：將二進位日誌格式設置為ROW時，有些更改仍然使用基於語句的格式，包括所有DDL語句，例如CREATE TABLE， ALTER TABLE，或 DROP TABLE。
開啟binlog
由於log_bin是個只讀系統變數，不能動態的修改，只能再my.cnf里[mysqld]模塊添加log-bin 配置，表示啟用binlog，如果沒有給定值，寫成 log-bin=，則預設名稱為主機名。（註：名稱若帶有小數點，則只取第一個小數點前的部分作為名稱）
[mysqld] log-bin=mysql-bin
此方法需要重啟資料庫方能生效，如果想臨時關掉binlog而又不想重啟資料庫，可以使用sql_log_bin，sql_log_bin 是一個動態變數，修改該變數時，可以只對當前會話生效（Session），也可以是全局的（Global），當全局修改這個變數時，只會對新的會話生效（這意味當對當前會話也不會生效），因此一般全局修改了這個變數後，都要把原來的所有連接 kill 掉，全局只在MariaDB 5.5之前和MySQL 5.6.22之前生效，後面的版本都已經不支持全局，因為太危險了。
用處：
當還原資料庫的時候，如果不關閉二進位日誌，那麼你還原的過程仍然會記錄在二進位日誌裡面，不僅浪費資源，那麼增加了磁碟的容量，還沒有必要（特別是利用二進位還原資料庫的時候）所以一般還原的時候會選擇關閉二進位日誌，可以通過修改配置文件，重啟關閉二進位日誌。
也可以動態命令關閉sql_log_bin,然後導入資料庫。
set sql_log_bin=0；# 設為0後，在當前會話上執行的語句都不記錄binlog set sql_log_bin=1; # 取消限制
常用的binlog命令
# 是否啟用binlog日誌 show variables like 'log_bin'; # 查看詳細的日誌配置信息 show global variables like '%log%'; # 查看binlog的目錄 show global variables like "%log_bin%"; # 查看當前伺服器使用的biglog文件及大小 show binary logs; # 查看最新一個binlog日誌文件名稱和Position show master status; # 清除所有的binlog⽂件，並且重置為⼀個 reset master
清理binlog日誌
自動清理：
通過binlog參數（expire_logs_days ）來實現mysql⾃動刪除binlog
show binary logs; show variables like 'expire_logs_days'; set global expire_logs_days=3; # 過期刪除，單位是天
手動清理：
1、使⽤reset master 重置binlog⽂件（#reset master後，會造成slave⽆法找到master的嚴重後果）
2、直接rm本地刪除binlog
# 查看正在使用的binlog
show master status;
直接使⽤rm 命令刪除不是當前使⽤的binlog(切記不要刪除正在使⽤的binlog)
mysqlbinlog命令
root@localhost localhost 15:23:43 (none)>show global variables like "%log_bin%"; +---------------------------------+-----------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------+ | log_bin | ON | | log_bin_basename | /data/mysql/mysql-bin | | log_bin_index | /data/mysql/mysql-bin.index | | log_bin_trust_function_creators | ON | | log_bin_use_v1_row_events | OFF | +---------------------------------+-----------------------------+ 5 rows in set (0.00 sec) root@localhost localhost 15:29:21 test1>show global variables like "binlog_format"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.01 sec) root@localhost localhost 15:25:06 abc>create database test1; Query OK, 1 row affected (0.00 sec) root@localhost localhost 15:25:28 abc>use test1; Database changed root@localhost localhost 15:25:34 test1>CREATE TABLE `abc` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `c1` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`), -> KEY `shouji` (`c1`) USING BTREE -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec) root@localhost localhost 15:26:12 test1>insert into abc(c1) values (1); Query OK, 1 row affected (0.13 sec) root@localhost localhost 15:26:23 test1>insert into abc(c1) values (2),(3); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 root@localhost localhost 15:26:43 test1>delete from abc where id=2; Query OK, 1 row affected (0.00 sec) root@localhost localhost 15:26:58 test1>select * from abc; +----+------+ | id | c1 | +----+------+ | 1 | 1 | | 3 | 3 | +----+------+ 2 rows in set (0.00 sec)
root@localhost localhost 15:47:38 test1>show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 | 1361 | | | 0f943fa6-3117-11ea-a98d-005056b351ef:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
root@localhost localhost 15:48:27 test1>show binlog events in 'mysql-bin.000001'\G;
*************************** 1. row ***************************
Log_name: mysql-bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 1007231
End_log_pos: 123
Info: Server ver: 5.7.19-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000001
Pos: 123
Event_type: Previous_gtids
Server_id: 1007231
End_log_pos: 150
Info:
*************************** 3. row ***************************
Log_name: mysql-bin.000001
Pos: 150
Event_type: Gtid
Server_id: 1007231
End_log_pos: 211
Info: SET @@SESSION.GTID_NEXT= '0f943fa6-3117-11ea-a98d-005056b351ef:1'
*************************** 4. row ***************************
Log_name: mysql-bin.000001
Pos: 211
Event_type: Query
Server_id: 1007231
End_log_pos: 304
Info: create database test1
*************************** 5. row ***************************
Log_name: mysql-bin.000001
Pos: 304
Event_type: Gtid
Server_id: 1007231
End_log_pos: 365
Info: SET @@SESSION.GTID_NEXT= '0f943fa6-3117-11ea-a98d-005056b351ef:2'
*************************** 6. row ***************************
Log_name: mysql-bin.000001
Pos: 365
Event_type: Query
Server_id: 1007231
End_log_pos: 629
Info: use `test1`; CREATE TABLE `abc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `shouji` (`c1`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
*************************** 7. row ***************************
Log_name: mysql-bin.000001
Pos: 629
Event_type: Gtid
Server_id: 1007231
End_log_pos: 690
Info: SET @@SESSION.GTID_NEXT= '0f943fa6-3117-11ea-a98d-005056b351ef:3'
*************************** 8. row ***************************
Log_name: mysql-bin.000001
Pos: 690
Event_type: Query
Server_id: 1007231
End_log_pos: 759
Info: BEGIN
*************************** 9. row ***************************
Log_name: mysql-bin.000001
Pos: 759
Event_type: Table_map
Server_id: 1007231
End_log_pos: 803
Info: table_id: 220 (test1.abc)
*************************** 10. row ***************************
Log_name: mysql-bin.000001
Pos: 803
Event_type: Write_rows
Server_id: 1007231
End_log_pos: 843
Info: table_id: 220 flags: STMT_END_F
*************************** 11. row ***************************
Log_name: mysql-bin.000001
Pos: 843
Event_type: Xid
Server_id: 1007231
End_log_pos: 870
Info: COMMIT /* xid=52 */
*************************** 12. row ***************************
Log_name: mysql-bin.000001
Pos: 870
Event_type: Gtid
Server_id: 1007231
End_log_pos: 931
Info: SET @@SESSION.GTID_NEXT= '0f943fa6-3117-11ea-a98d-005056b351ef:4'
*************************** 13. row ***************************
Log_name: mysql-bin.000001
Pos: 931
Event_type: Query
Server_id: 1007231
End_log_pos: 1000
Info: BEGIN
*************************** 14. row ***************************
Log_name: mysql-bin.000001
Pos: 1000
Event_type: Table_map
Server_id: 1007231
End_log_pos: 1044
Info: table_id: 220 (test1.abc)
*************************** 15. row ***************************
Log_name: mysql-bin.000001
Pos: 1044
Event_type: Write_rows
Server_id: 1007231
End_log_pos: 1093
Info: table_id: 220 flags: STMT_END_F
*************************** 16. row ***************************
Log_name: mysql-bin.000001
Pos: 1093
Event_type: Xid
Server_id: 1007231
End_log_pos: 1120
Info: COMMIT /* xid=53 */
*************************** 17. row ***************************
Log_name: mysql-bin.000001
Pos: 1120
Event_type: Gtid
Server_id: 1007231
End_log_pos: 1181
Info: SET @@SESSION.GTID_NEXT= '0f943fa6-3117-11ea-a98d-005056b351ef:5'
*************************** 18. row ***************************
Log_name: mysql-bin.000001
Pos: 1181
Event_type: Query
Server_id: 1007231
End_log_pos: 1250
Info: BEGIN
*************************** 19. row ***************************
Log_name: mysql-bin.000001
Pos: 1250
Event_type: Table_map
Server_id: 1007231
End_log_pos: 1294
Info: table_id: 220 (test1.abc)
*************************** 20. row ***************************
Log_name: mysql-bin.000001
Pos: 1294
Event_type: Delete_rows
Server_id: 1007231
End_log_pos: 1334
Info: table_id: 220 flags: STMT_END_F
*************************** 21. row ***************************
Log_name: mysql-bin.000001
Pos: 1334
Event_type: Xid
Server_id: 1007231
End_log_pos: 1361
Info: COMMIT /* xid=54 */
21 rows in set (0.00 sec)
下麵是mysqlbinlog命令的參數，摘自官方文檔
| 選項名稱 | 描述 | 引用 | 棄用 |
|---|---|---|---|
| --base64-output |
使用base-64編碼列印二進位日誌條目 | ||
| --bind-address | 使用指定的網路介面連接到MySQL Server | ||
| --binlog-row-event-max-size | 二進位日誌最大事件大小 | ||
| --character-sets-dir | 字元集的安裝目錄 | ||
| --connection-server-id | 用於測試和調試。有關適用的預設值和其他詳細信息，請參見文本 | 5.7.5 | |
| --database |
僅列出該資料庫的條目 | ||
| --debug | 編寫調試日誌 | ||
| --debug-check | 程式退出時列印調試信息 | ||
| --debug-info | 程式退出時列印調試信息，記憶體和CPU統計信息 | ||
| --default-auth | 身份驗證插件使用 | ||
| --defaults-extra-file | 除了通常的選項文件，還讀取命名的選項文件 | ||
| --defaults-file | 只讀命名的選項文件 | ||
| --defaults-group-suffix | 選項組尾碼值 | ||
| --disable-log-bin | 禁用二進位日誌記錄 | ||
| --exclude-gtids | 不要顯示提供的GTID集中的任何組 | ||
| --force-if-open | 讀取二進位日誌文件，即使打開或未正確關閉 | ||
| --force-read | 如果mysqlbinlog讀取了無法識別的二進位日誌事件，則會輸出警告 | ||
| --get-server-public-key | 從伺服器請求RSA公鑰 | 5.7.23 | |
| --help | 顯示幫助信息並退出 | ||
| --hexdump | 顯示登錄註釋的十六進位轉儲 | ||
| --host | MySQL伺服器所在的主機 | ||
| --idempotent | 僅在處理來自此會話的二進位日誌更新時，導致伺服器使用冪等模式 | 5.7.0 | |
| --include-gtids | 僅顯示提供的GTID集中的組 | ||
| --local-load | 在指定目錄中為LOAD DATA準備本地臨時文件 | ||
| --login-path | 從.mylogin.cnf中讀取登錄路徑選項 | ||
| --no-defaults | 不讀取選項文件 | ||
| --offset | 跳過日誌中的前N個條目 | ||
| open_files_limit | 指定要保留的打開文件描述符的數量 | ||
| --password | 連接伺服器時使用的密碼 | ||
| --plugin-dir | 安裝插件的目錄 | ||
| --port | 用於連接的TCP / IP埠號 | ||
| --print-defaults | 列印預設選項 | ||
| --protocol | 使用的連接協議 | ||
| --raw | 將事件以原始（二進位）格式寫入輸出文件 | ||
| --read-from-remote-master | 從MySQL主伺服器讀取二進位日誌，而不是讀取本地日誌文件 | ||
| --read-from-remote-server | 從MySQL伺服器而不是本地日誌文件中讀取二進位日誌 | ||
| --result-file | 直接輸出到命名文件 | ||
| --rewrite-db | 從基於行的格式編寫的日誌中播放時，為資料庫創建重寫規則。可多次使用 | 5.7.1 | |
| --secure-auth | 不要以舊（4.1之前）格式向伺服器發送密碼 | 5.7.4 | 5.7.5 |
| --server-id | 僅提取由具有給定伺服器ID的伺服器創建的那些事件 | ||
| --server-id-bits | 告訴mysqlbinlog當mysqld編寫的server-id-bits設置為小於最大值時，如何解釋二進位日誌中的伺服器ID；僅受MySQL Cluster版本的mysqlbinlog支持 | ||
| --server-public-key-path | 包含RSA公鑰的文件的路徑名 | 5.7.23 | |
| --set-charset | 在輸出中添加SET NAMES charset_name語句 | ||
| --shared-memory-base-name | 用於共用記憶體連接的共用記憶體的名稱 | ||
| --short-form | 僅顯示日誌中包含的語句 | ||
| --skip-gtids | 不要列印任何GTID。從包含GTID的二進位日誌寫入轉儲文件時使用此功能 | ||
| --socket | Unix套接字文件或Windows命名管道使用 | ||
| --ssl | 啟用連接加密 | 5.7.3 | |
| --ssl-ca | 包含受信任的SSL證書頒發機構列表的文件 | 5.7.3 | |
| --ssl-capath | 包含受信任的SSL證書頒發機構證書文件的目錄 | 5.7.3 | |
| --ssl-cert | 包含X.509證書的文件 | 5.7.3 | |
| --ssl-cipher | 連接加密的允許密碼 | 5.7.3 | |
| --ssl-crl | 包含證書吊銷列表的文件 | ||
| --ssl-crlpath | 包含證書吊銷列表文件的目錄 | ||
| --ssl-key | 包含X.509密鑰的文件 | 5.7.3 | |
| --ssl-mode | 與伺服器連接的所需安全狀態 | 5.7.11 | |
| --ssl-verify-server-cert | 根據伺服器證書的公用名身份驗證主機名 | 5.7.3 | |
| --start-datetime | 從第一個事件中讀取時間戳等於或晚於datetime參數的二進位日誌 | ||
| --start-position | 從位置等於或大於參數的第一個事件讀取二進位日誌 | ||
| --stop-datetime | 在時間戳等於或大於datetime參數的第一個事件時停止讀取二進位日誌 | ||
| --stop-never | 讀取最後一個二進位日誌文件後保持與伺服器的連接 | ||
| --stop-never-slave-server-id | 連接到伺服器時要報告的從伺服器ID | ||
| --stop-position | 在第一個事件中停止讀取二進位日誌，且位置等於或大於參數 | ||
| --tls-version | 允許的TLS協議進行加密連接 | 5.7.10 | |
| --to-last-log | 不要在從MySQL伺服器請求的二進位日誌的結尾處停止，而要繼續列印到最後一個二進位日誌的結尾 | ||
| --user | 連接伺服器時要使用的MySQL用戶名 | ||
| --verbose | 將行事件重建為SQL語句 | ||
| --verify-binlog-checksum | 驗證二進位日誌中的校驗和 |
[root@localhost mysql]# mysqlbinlog mysql-bin.000001 >/data/tmp/mysqlbinlog.sql [root@localhost mysql]# cat /data/tmp/mysqlbinlog.sql /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #200107 15:45:15 server id 1007231 end_log_pos 123 Start: binlog v 4, server v 5.7.19-log created 200107 15:45:15 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' CzcUXg9/Xg8AdwAAAHsAAAABAAQANS43LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAALNxReEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AEJXKj0= '/*!*/; # at 123 #200107 15:45:15 server id 1007231 end_log_pos 150 Previous-GTIDs # [empty] # at 150 #200107 15:45:53 server id 1007231 end_log_pos 211 GTID last_committed=0 sequence_number=1 rbr_only=no SET @@SESSION.GTID_NEXT= '0f943fa6-3117-11ea-a98d-005056b351ef:1'/*!*/; # at 211 #200107 15:45:53 server id 1007231 end_log_pos 304 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1578383153/*!*/; SET @@session.pseudo_thread_id=3/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1344274432/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8mb4 *//*!*/; SET @@session.character_set_client=224,@@session.collation_connection=224,@@session.collation_server=224/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create database test1 /*!*/; # at 304 #200107 15:46:38 server id 1007231 end_log_pos 365 GTID last_committed=1 sequence_number=2 rbr_only=no SET @@SESSION.GTID_NEXT= '0f943fa6-3117-11ea-a98d-005056b351ef:2'/*!*/; # at 365 #200107 15:46:38 server id 1007231 end_log_pos 629 Query thread_id=3 exec_time=0 error_code=0 use `test1`/*!*/; SET TIMESTAMP=1578383198/*!*/; CREATE TABLE `abc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `shouji` (`c1`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 /*!*/; # at 629 #200107 15:47:15 server id 1007231 end_log_pos 690 GTID last_committed=2 sequence_number=3 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= '0f943fa6-3117-11ea-a98d-005056b351ef:3'/*!*/; # at 690 #200107 15:47:15 server id 1007231 end_log_pos 759 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1578383235/*!*/; BEGIN /*!*/; # at 759 #200107 15:47:15 server id 1007231 end_log_pos 803 Table_map: `test1`.`abc` mapped to number 220 # at 803 #200107 15:47:15 server id 1007231 end_log_pos 843 Write_rows: table id 220 flags: STMT_END_F BINLOG ' gzcUXhN/Xg8ALAAAACMDAAAAANwAAAAAAAEABXRlc3QxAANhYmMAAgMDAAI= gzcUXh5/Xg8AKAAAAEsDAAAAANwAAAAAAAEAAgAC//wBAAAAAQAAAA== '/*!*/; # at 843 #200107 15:47:15 server id 1007231 end_log_pos 870 Xid = 52 COMMIT/*!*/; # at 870 #200107 15:47:23 server id 1007231 end_log_pos 931 GTID last_committed=3 sequence_number=4 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= '0f943fa6-3117-11ea-a98d-005056b351ef:4'/*!*/; # at 931 #200107 15:47:23 server id 1007231 end_log_pos 1000 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1578383243/*!*/; BEGIN /*!*/; # at 1000 #200107 15:47:23 server id 1007231 end_log_pos 1044 Table_map: `test1`.`abc` mapped to number
