Shulou(Shulou.com)06/01 Report--

Three modes of binlog

Statement mode

Features:

(1) RU,RC isolation level is not supported in this mode

(2) the end point of the previous thing in the binglog log file is the starting point of the next thing.

(3) all DML,DDL statements will be displayed in clear text

(4) some system functions can not be copied accurately or cannot be copied, such as load_file (), uuid (), user (), found_rows (), sysdate (), note (now () can be copied;)

(5) when the master database executes delete from T1 where c1=xxx limit 1 statement mode, the slave database will do the same, which may cause different rows of data to be deleted.

(6) the master database has two rows of id=1 and id=10 data, and the slave database has four rows of id=1,2,3,10 data. The master database executes delete from T1 where idselect * from T10. +-+-+ | C1 | c2 | +-+-+ | 1 | bbb | | 2 | 2017-04-08 14:40:49 | | 3 | 2017-04-08 14:40:49 | | 4 | 4d76efa5-1c26-11e7-bc58-000c29c1b8a9 | +-+-+ from | Library: root@localhost [testdb] > select * from T10 +-+-+ | C1 | c2 | +-+-+ | 1 | bbb | | 2 | 2017-04-08 14:40:49 | | 3 | 2017-04-14 13:12:19 | | 4 | ef119323-20d0-11e7-aef6-000c29565380 | +-+-- + can be found | Under statument log format, the master-slave data is inconsistent due to the use of some functions. For example, the beginning of 2:update is the end of at1581 of insert. The point at the end of the update is the point at1842; [root@Darren2 logs] # mysqlbinlog-- start-position=1581-- stop-position=1842 mysqlkobin.000022. BEGINAccording to the server id / at 1729o170408 14:40:49 server id 330622 end_log_pos 1841 CRC32 0xb443cf1e Query thread_id=55 exec_time=0 error_code=0use `testdb` / *! * /; the SET Timestamp 1491633649cm lead leads to the endpoint update t10 set c2 / bbb' where C1 # at 1841170408 14:40:49 server id 330622 end_log_pos 1872 CRC32 0xd06c40f5 Xid = 1756COMMITUniplicatureSet @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! * /; DELIMITER; # End of log filedeband / 50003COMPLEIONTYPENTYPERATION Example 3: when you look at the position point before commit, you will see the rollback status, indicating that the intercepted thing is incomplete: [root@Darren2 logs] # mysqlbinlog-- start-position=1581-- stop-position=1841 mysqlmurbin.000022 is not complete. Server id 330622 end_log_pos 1841 CRC32 0xb443cf1e Query thread_id=55 exec_time=0 error_code=0use `testdb` / *! * / at 14:40:49 server id 330622 end_log_pos 1841 TIMESTAMPlease 1491633649 SET TIMESTAMPPlease 1491633649ax to update t10 update t10 set c2 to update t10 bbb' where C1 ROLLBACK / * added by mysqlbinlog * / / *! * /; SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! * /; DELIMITER; # End of log filebank / 50003 SET completion TYPEX SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/

Row mode

(1) it is more secure than statement

(2) replication is faster when the table has a primary key

(3) the special function of the system can also be copied.

(4) fewer locks, only row locks

(5) the binlog file is relatively large. If you update 200000 rows of data in a single statement, it may take half an hour, or you may hang up the main database.

(6) the SQL statement executed by the user cannot be seen from binog (after mysql 5.6, you can see the executed SQL statement in the binlog log in row by setting binlog_rows_query_log_events=on. )

(7) 5.7 the default log mode is row

(8) the DDL statement is displayed in clear text, and the DML statement is encrypted.

(9) DML is encrypted by base64. You need to use the parameter-- base64-output=decode-rows-- verbose.

(10) Old historical data can be seen in statements modified by update.

For example, 1:set tx_isolation='repeatable-read';set binlog_format='row';flush logs;create table T10 (C1 int,c2 varchar (50)); insert into T10 values (1Magi now ()); insert into T10 values (2 Magi now ()); insert into T10 values (3 Magi Sysdate ()); insert into T10 values (4 Magi uid ()); update T10 set c2 recording bbb' where C1 Only create,alter can be seen without parameters. DDL statements such as drop: view mysqlbinlog mysql-bin.000023 with parameters: [root@Darren2 logs] # mysqlbinlog-vv-- base64-output=decode-rows mysql-bin.000023.create table T10 (C1 int) C2 varchar (50)) # INSERT INTO `testdb`.`t10` # SET### @ 1mm 1mm # @ 2mm 2017-04-08 15SET### 111mm # INSERT INTO `testdb`.`t10` # # SET### @ 1mm 41mm # INSERT INTO `testdb`. `t10` # SET### @ 1mm 3mm # @ 2mm 2017-04-08 # INSERT INTO `testdb`.`t10` # SET### @ 1mm # @ 2='9d96b424-1c2a-11e7-bc58-000c29c1b8a9'### UPDATE `testdb`.`t10` # WHERE### @ 1mm # @ 2mm, 2017-04-08 151V 111V 111V # SET### @ 1mm # @ 2mm bbbb` example 2: enable the binlog_rows_query_log_events parameter The executed SQL statement is displayed This parameter is turned off by default and does not display the executed SQLroot@localhost [testdb] > set binlog_rows_query_log_events=on. [root@Darren2 logs] # mysqlbinlog-vv-- base64-output=decode-rows mysql-bin.000024.create table T10 (C1 int,c2 varchar (50)) # insert into T10 values (1MagneNow ()) # INSERT INTO `testdb`.`t10` # SET### @ 1mm 1 / * INT meta=0 nullable=1 is_null=0 * / # @ 2mm 2017-04-08 15 VARSTRING 26 VARSTRING 09'/ * VARSTRING (150) meta=150 nullable=1 is_null=0 * / # insert into T10 values (2) Now () # INSERT INTO `testdb`.`t10` # SET### @ 1mm 2 / * INT meta=0 nullable=1 is_null=0 * / # @ 2mm / 041515 SET### 09' / * VARSTRING (150) meta=150 nullable=1 is_null=0 * / # at 103 "insert into t10 values (3) Sysdate () # INSERT INTO `testdb`.`t10` # SET### @ 1k3 / * INT meta=0 nullable=1 is_null=0 * / # @ 2mm / 041515 VARSTRING 2619 9' / * VARSTRING (150) meta=150 nullable=1 is_null=0 * / # insert into T10 values (4) Uuid () # INSERT INTO `testdb`.`t10` # SET### @ 1q4 / * INT meta=0 nullable=1 is_null=0 * / # @ 2='a2b570b8-1c2c-11e7-bc58-000c29c1b8a9' / * VARSTRING meta=150 nullable=1 is_null=0 * / # update t10 set c2 / bbbb`where c1o1 / # UPDATE `testdb`.`t10` # # WHERE### @ 1 / * INT meta=0 nullable=1 is_null=0 * / # @ 2mm / 04-08 15:26: 09' / * VARSTRING (150) meta=150 nullable=1 is_null=0 * / # SET### @ 1 / * INT meta=0 nullable=1 is_null=0 * / # # @ 2 / VARSTRING (150) meta=150 nullable=1 is_null=0 * /

Mixed mode

Features:

(1) innodb engine. If the isolation level is RU or RC, mixed mode will be converted to Row mode storage.

(2) in mixed mode, the SBR mode of binlog will be automatically converted into RBR mode in the following situations:

When updating a NDB table

When a function contains the uuid () function

When 2 or more tables containing auto_increment fields are updated

When you must use RBR in a view, such as using the uuid () function when creating a view

Example 1: when the isolation level is read-committed, mixed mode will be converted to row mode storage: set tx_isolation='read-committed';set binlog_format='mixed';flush logs;create table T10 (C1 int,c2 varchar (50)); insert into T10 values (1jie now ()); insert into T10 values (2je now ()); insert into T10 values (3je Sysdate ()); insert into T10 values (4Movie uid ()); update T10 set c2 recording bbb' where C1 storage 1 [root@Darren2 logs] # mysqlbinlog-vv-- base64-output=decode-rows mysql-bin.000028.### UPDATE `testdb`.`t10` # WHERE### @ 1mm 1 / * INT meta=0 nullable=1 is_null=0 * / # @ 2mm meta=150 nullable=1 is_null=0 * / # # SET### @ 1mm 1 / * INT meta=0 nullable=1 is_null=0 * / # # @ 2mm / * VARSTRING (150) meta=150 nullable=1 is_null=0 * /. Example 2: when the isolation level is repeatable-read, the mixed mode is converted to statement mode storage set tx_isolation='repeatable-read';set binlog_format='mixed';flush logs;create table t10 (C1 int,c2 varchar (50)); insert into t10 values (1jie now ()); insert into T10 values (2je now ()); insert into T10 values (3dje Sysdate ()); insert into T10 values (4je uid ()); update T10 set c2stores stores bbb' where C1 [root@Darren2 logs] # mysqlbinlog mysql-bin.000029.update t10 set c2 / bbb` where C1 / 1.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.