1、What's binlog
* 二進制日志包含描述數據庫更改(如表創建操作或表數據更改)的“ 事件 ”。
* 在row模式下,不記錄DML不匹配任何行的SQL,statement 和mixed記錄
* 二進制日志還包含有關每個語句花費更新數據的時間的信息。
*?二進制日志不用于諸如SELECT或 SHOW不修改數據的語句
*? mysqld還會創建一個二進制日志索引文件,其中包含所有使用的二進制日志文件的名稱。
?2、二進制日志的作用:
* 用于復制,從庫重做從主庫復制的binlog日志,實現主從數據一致性。
* 用于恢復。恢復數據庫全備后執行binlog日志,使數據庫保持最新狀態。
?3、 binlog 記錄模式
3.1 STATEMENT
語句模式,記錄執行的sql,對于模糊函數rand,只記錄這個函數,主從造成數據不一致
1 mysql> set @@session.binlog_format=STATEMENT; 2 mysql> update test set name='dcd3' where id = 2 ; 3 # mysqlbinlog -vv logbinfile.000019 4 # at 609 5 #170210 4:27:09 server id 1 end_log_pos 735 CRC32 0x4d7239cf Query thread_id=62872 exec_time=0 error_code=0 6 use `test`/*!*/; 7 SET TIMESTAMP=1486672029/*!*/; 8 update test set name='dcd3' where id = 2;
3.2 ROW
行模式,基于數據行一行一行的記錄,對于模糊函數能知道最后在行上的值,保證數據一致性。
mysql> set @@session.binlog_format=row; mysql> update test set name='dcd4' where id=2;
1 BINLOG ' 2 3NGcWBMBAAAAMgAAALkDAAAAANoAAAAAAAEABHRlc3QABHRlc3QAAgMPAjwAAuO+e6s= 3 3NGcWB8BAAAAYAAAABkEAAAAANoAAAAAAAEAAgAC///8AgAAAARkY2Qz/AIAAAAEZGNkNPwDAAAA 4 BGRjZDP8AwAAAARkY2Q0/AQAAAAEZGNkM/wEAAAABGRjZDRch6c1 5 '/*!*/; 6 ### UPDATE `test`.`test` 7 ### WHERE 8 ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ 9 ### @2='dcd3' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ 10 ### SET 11 ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ 12 ### @2='dcd4' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
?
3.3 MIXED
混合模式,一般情況下,記錄statement語句,
mysql> set @@session.binlog_format=mixed; mysql> begin; mysql> insert into t2 values (4,now()); mysql> commit;
#170921 23:27:18 server id 1 end_log_pos 855 CRC32 0x1ba5d0e5 Query thread_id=38 exec_time=0 error_code=0 SET TIMESTAMP=1506007638/*!*/; BEGIN /*!*/; # at 855 #170921 23:27:18 server id 1 end_log_pos 968 CRC32 0x621c1f2b Query thread_id=38 exec_time=0 error_code=0 SET TIMESTAMP=1506007638/*!*/; insert into t2 values (4,now()) /*!*/; # at 968 #170921 23:27:20 server id 1 end_log_pos 999 CRC32 0x94dc56be Xid = 495 COMMIT/*!*/;
?
?4、binlog的分析
?4.1. 在行模式下記錄binlog
mysql> create table test.t4 (id int primary key ,name varchar(20)); -- 自動提交第一個事務 mysql> begin; -- 第二個事務 mysql> insert into test.t4 values (1,'a'); mysql> commit; mysql> begin; -- 第三個事務 mysql> insert into test.t4 values (2,'b'); mysql> commit; mysql> show master status \G *************************** 1. row ***************************File: mysql-bin.000004Position: 842Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 59fe7a3e-9dd6-11e7-9d6c-000c29e57c69:1-36 1 row in set (0.00 sec)
?4.2. 找到對應的binlog
DELIMITER /*!*/; # at 4 #170922 5:42:36 server id 1 end_log_pos 120 CRC32 0x7f5ab2c2 Start: binlog v 4, server v 5.6.36-log created 170922 5:42:36 # Warning: this binlog is either in use or was not closed properly. BINLOG ' TDLEWQ8BAAAAdAAAAHgAAAABAAQANS42LjM2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAcKy Wn8= '/*!*/; # at 120 #170922 5:42:36 server id 1 end_log_pos 191 CRC32 0x3921881b Previous-GTIDs # 59fe7a3e-9dd6-11e7-9d6c-000c29e57c69:1-33 -- 在binlog文件開頭表明執行過的GTID # at 191 #170922 5:42:50 server id 1 end_log_pos 239 CRC32 0xc083f02e GTID [commit=yes] SET @@SESSION.GTID_NEXT= '59fe7a3e-9dd6-11e7-9d6c-000c29e57c69:34'/*!*/; -- 設置create table 事務的GTID值 # at 239 #170922 5:42:50 server id 1 end_log_pos 368 CRC32 0xc6e65837 Query thread_id=43 exec_time=0 error_code=0 SET TIMESTAMP=1506030170/*!*/; SET @@session.pseudo_thread_id=43/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1073741824/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create table test.t4 (id int primary key ,name varchar(20)) /*!*/; # at 368 #170922 5:43:09 server id 1 end_log_pos 416 CRC32 0x3c41cd8f GTID [commit=yes] SET @@SESSION.GTID_NEXT= '59fe7a3e-9dd6-11e7-9d6c-000c29e57c69:35'/*!*/; -- 設置insert事務的GTID值 # at 416 #170922 5:43:07 server id 1 end_log_pos 484 CRC32 0x1a1b279a Query thread_id=43 exec_time=0 error_code=0 SET TIMESTAMP=1506030187/*!*/; BEGIN /*!*/; # at 484 #170922 5:43:07 server id 1 end_log_pos 532 CRC32 0x81bf433a Table_map: `test`.`t4` mapped to number 81 # at 532 #170922 5:43:07 server id 1 end_log_pos 574 CRC32 0x672093bd Write_rows: table id 81 flags: STMT_END_FBINLOG ' azLEWRMBAAAAMAAAABQCAAAAAFEAAAAAAAEABHRlc3QAAnQ0AAIDDwIUAAI6Q7+B azLEWR4BAAAAKgAAAD4CAAAAAFEAAAAAAAEAAgAC//wBAAAAAWG9kyBn '/*!*/; ### INSERT INTO `test`.`t4` ### SET ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='a' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */ # at 574 #170922 5:43:09 server id 1 end_log_pos 605 CRC32 0x187dba42 Xid = 536 COMMIT/*!*/; # at 605 #170922 5:43:40 server id 1 end_log_pos 653 CRC32 0x1c079044 GTID [commit=yes] SET @@SESSION.GTID_NEXT= '59fe7a3e-9dd6-11e7-9d6c-000c29e57c69:36'/*!*/; -- 設置insert事務的GTID值 # at 653 #170922 5:43:38 server id 1 end_log_pos 721 CRC32 0x73ec94af Query thread_id=43 exec_time=0 error_code=0 SET TIMESTAMP=1506030218/*!*/; BEGIN /*!*/; # at 721 #170922 5:43:38 server id 1 end_log_pos 769 CRC32 0x4d300601 Table_map: `test`.`t4` mapped to number 81 # at 769 #170922 5:43:38 server id 1 end_log_pos 811 CRC32 0xd4bd7ab4 Write_rows: table id 81 flags: STMT_END_FBINLOG ' ijLEWRMBAAAAMAAAAAEDAAAAAFEAAAAAAAEABHRlc3QAAnQ0AAIDDwIUAAIBBjBN ijLEWR4BAAAAKgAAACsDAAAAAFEAAAAAAAEAAgAC//wCAAAAAWK0er3U '/*!*/; ### INSERT INTO `test`.`t4` ### SET ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='b' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */ # at 811 #170922 5:43:40 server id 1 end_log_pos 842 CRC32 0xac2e4d04 Xid = 540 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog *//*!*/; -- 設置gtid值為AUTOMATIC DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
?
?4.3. 通過分析上面的binlog?
可以得到以下結論:
1. ?row模式下,insert 是對表中每個列進行賦值插入的
2. 在每個binlog文件的末尾會有rollback
3. 在每個事務前binlog 設置了gtid_next的值。
5、binlog相關的變量
?5.1 log_bin
啟動時,--log-bin
log_bin 寫在配置文件中
?5.2 sql_log_bin
1. 此變量控制是否完成對二進制日志的日志記錄,默認值為1(做日志記錄)。
2. mysql> set @@session.sql_log_bin=OFF; 需要具有SUPER權限。
3.?在MySQL 5.7中,不可能在事務或子查詢中進行設置 @@session.sql_log_bin
?5.3 binlog_cache_size
mysql> show variables like 'binlog_cache_size'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | binlog_cache_size | 32768 | -- 基于會話分配,建議16M +-------------------+-------+
在 Binlog_cache_use與 Binlog_cache_disk_use 狀態變量可以用于調整該變量的大小
mysql> show variables like '%binlog_cache%'; +-----------------------+----------------------+ | Variable_name | Value | +-----------------------+----------------------+ | binlog_cache_size | 32768 | | max_binlog_cache_size | 18446744073709547520 | +-----------------------+----------------------+ mysql> show global status like "binlog_cache_disk_use"; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Binlog_cache_disk_use | 0 | +-----------------------+-------+
?
5.4?binlog_stmt_cache_size
此變量確定二進制日志的高速緩存的大小,以保存在事務期間發出的非事務性語句。
mysql> show variables like '%binlog_stmt%'; +----------------------------+----------------------+ | Variable_name | Value | +----------------------------+----------------------+ | binlog_stmt_cache_size | 32768 | | max_binlog_stmt_cache_size | 18446744073709547520 | +----------------------------+----------------------+
5.5 max_binlog_size?
mysql> show variables like '%binlog_size%'; +-----------------+------------+ | Variable_name | Value | +-----------------+------------+ | max_binlog_size | 1073741824 | -- binlog 文件大小,默認1G +-----------------+------------+
5.6 sync_binlog
* sync_binlog=0,系統默認
當事務提交之后,不做磁盤同步,在這種情況下,依賴于文件系統的刷新。
值為0,性能是最好的,但是風險也是最大的。
因為一旦系統Crash,在binlog_cache中的所有binlog信息都會被丟失。,
* sync_binlog=n,
當值不為0時,fdatasync() 同步binlog到磁盤,最安全但是性能損耗最大的設置。
因為當設置為1的時候,即使系統Crash,也最多丟失binlog_cache中未完成的一個事務,對實際數據沒有任何實質性影響。
5.7 expire_logs_days
mysql> show variables like '%expire_logs_days%'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | expire_logs_days | 0 | +--------------------------------+-------+
values:
* 自動刪除binlog 的天數* 0 表示不自動刪除
5.8?innodb_flush_log_at_trx_commit
- 0: log buffer將大約每秒一次地寫入log file中,并且log file的flush(刷到磁盤)操作同時進行.該模式下,在事務提交的時候,不會主動觸發寫入磁盤的操作。
- 1: 每次事務提交時MySQL都會把log buffer的數據寫入log file,并且flush(刷到磁盤)中去.
- 2: 每次事務提交時MySQL都會把log buffer的數據寫入log file.但是flush(刷到磁盤)操作并不會同時進行。
- 該模式下,MySQL會每秒執行一次 flush(刷到磁盤)操作,可以通過把這個值設成2來提高寫入的速度
5.9 xa
* 事務在提交時,redo log 寫入失敗、bin log 寫入成功時,主庫會執行回滾操作,從庫寫入,造成主從數據不一致
* xa參數保證 redo 、binlog 都寫入成功,事務提交成功。
mysql> show variables like '%xa%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | innodb_support_xa | ON |
mysql查看日志,?