mysql binlog at_mysql binlog 日志

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/AIAAAAEZGNkNPwDAAAA4 BGRjZDP8AwAAAARkY2Q0/AQAAAAEZGNkM/wEAAAABGRjZDRch6c15 '/*!*/;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

/*!*/;

# at855#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())/*!*/;

# at968#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 /*!*/;

# at4#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 binlogis either in use or was notclosed properly.

BINLOG'TDLEWQ8BAAAAdAAAAHgAAAABAAQANS42LjM2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAcKy

Wn8='/*!*/;

# at120#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))/*!*/;

# at368#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

/*!*/;

# at484#170922 5:43:07 server id 1 end_log_pos 532 CRC32 0x81bf433a Table_map: `test`.`t4` mapped to number 81# at532#170922 5:43:07 server id 1 end_log_pos 574 CRC32 0x672093bd Write_rows: table id 81flags: STMT_END_F

BINLOG'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*/# at574#170922 5:43:09 server id 1 end_log_pos 605 CRC32 0x187dba42 Xid = 536

COMMIT/*!*/;

# at605#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

/*!*/;

# at721#170922 5:43:38 server id 1 end_log_pos 769 CRC32 0x4d300601 Table_map: `test`.`t4` mapped to number 81# at769#170922 5:43:38 server id 1 end_log_pos 811 CRC32 0xd4bd7ab4 Write_rows: table id 81flags: STMT_END_F

BINLOG'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*/# at811#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 |