MySQL log format split log analysis

1st logs divided into three different formats

Statement statement-based

Row-based

Mixed compound.

Difference:
Simple and lean-based statement, the server writes data modification events as SQL statements to binary, content is easier to understand

The statement line provides fine-grained control over what data to modify, but it is not easy to understand that the reason for this is that some statements may not be clear enough to perform differently on the primary server and from the server

Based on the mixed server will switch using statement-based or row-based formats when appropriate

2 How to view the current system format

Mysql> show global variables like '%format% ';

3 How to Modify

Temporarily modify the current session or another session

Mysql> Set Session Binlog_format= ' mixed ';

mysql> set global binlog_format= ' mixed ';

Restarting the mysqld will not take effect.

Permanent effect

In the configuration file written to My.cnf

binlog_format=

4 presentation of different formatting effects

[[email protected] logs]# mysqlbinlog master_bin.000003 and constant set modification Format_log

Mysql> Show Binlog events in ' master_bin.000003 '; Easy view of what is being done during each POS

Based on the statement statement

# at 781
#141116 22:40:42 Server ID end_log_pos 897 CRC32 0x63842d06 Query thread_id=3 exec_time=0 error_code=0
SET timestamp=1416148842/*!*/;
Insert Student (Id,name) VALUES (1, ' Andy ')
/*!*/;

|  master_bin.000003 | 781 |       Query |        100 | 897 | Use ' mydb '; Insert Student (Id,name) VALUES (1, ' Andy ')

Row-based

# at 1254
#141116 22:43:38 server ID end_log_pos 1303 CRC32 0x115dc0d4 write_rows:table ID flags:stmt_end_f

BINLOG '
Grhovbnkaaaanqaaaoyeaaaaaeyaaaaaaaeabg15zgiab3n0dwrlbnqaagp+av4ua9tlv8c=
grhovb5kaaaamqaaabcfaaaaaeyaaaaaaaeaagac//wcaaaacgn1awrlahvh1mbdeq==

In fact, this operation is also an INSERT statement, but we can not read

| master_bin.000003 | 1254 |       Write_rows |        100 | 1303 | Table_id:70 Flags:stmt_end_f

Based on mixed, here it automatically chooses the appropriate statement-based statement

| master_bin.000003 | 1492 |       Query |        100 | 1615 | Use ' mydb '; Insert Student (Id,name) VALUES (4, ' zhangxueyou ')

MySQL log format split log analysis