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

This article mainly tells you how binlog records the DML operations of mysql on transaction tables and non-transaction tables. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope that this article on how binlog records the DML operations of mysql on transaction tables and non-transaction tables can bring you some practical help.

Experimental environment: mysql official community version 5.7.18, operating system centos7.3,binlog log format using row format.

1. Create Myisam table b and Innodb table a.

CREATE TABLE `a` (`id` int (11) NOT NULL, `name` varchar (10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `b` (`id` int (11) DEFAULT NULL, `name` varchar (10) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8

As you can see, the structure of both tables is the same, except for different engines.

2. According to whether GTID is enabled or not, it can be divided into two categories:

Close GTID, explicitly open the transaction and commit the transaction.

The DML operations performed are as follows: root@localhost:mysql3306.sock 16:28: [test] > begin;Query OK, 0 rows affected (0.00 sec) root@localhost:mysql3306.sock 16:28: [test] > insert into a values (1, "x"); Query OK, 1 row affected (0.00 sec) root@localhost:mysql3306.sock 16:29: [test] > insert into b values (1, "x") Query OK, 1 row affected (0.00 sec) root@localhost:mysql3306.sock 16:29: [test] > commit;Query OK, the corresponding binlog logs generated by 0 rows affected (0.00 sec) are as follows: [root@mysql3 binlog] # mysqlbinlog-vv mysql-bin.000001-Base64 speak outputUniverse SET @ @ SESSION.PSEUDOSLAVESLAVEMODEN1Universe Universe 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /! * / # at 4 property 180122 16:28:29 server id 43306 end_log_pos 123 CRC32 0x37c1a2aa Start: binlog v 4, server v 5.7.18-log created 180122 16:28:29 at startup# Warning: this binlog is either in use or was not closed property. Rollback # at 123 18122 16:28:29 server id 43306 end_log_pos 1554 CRC32 0x52be72b2 Previous-GTIDs# [empty] # at 154 18122 16:29:03 server id 43306 end_log_pos 219 CRC32 0x0769b732 Anonymous_GTID last_committed=0 sequence_number=1SET @ @ SESSION.GTID_NEXT= 'ANONYMOUS managers @ @ SESSION.GTID_NEXT= 'ANONYMOUS18122 at 219,180122 16:29:03 server id 43306 end_log_pos 291 CRC32 0x0e3a8a35 Query thread_id=4 exec_time=0 error_code=0SET timestamp 1516609743 SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.session.sqlharmmode2097152, @ set @ @ session.auto_increment_increment=1, @ @ session.autoincrement increment offset1 / *! / *! SET @ @ session.session. Collection timestamps namespace namespaces: session. Session clientbooks 33instructions session. Collationalization connections33instructions session .collationalization serveration33cospact words. # at 291mm 180122 16:29:03 server id 43306 end_log_pos 338 CRC32 0xfe6cd337 Table_map: `test`.`b` mapped to number 224th at 3388180122 16:29:03 server id 43306 end_log_pos 380CRC32 0x0d64e0a4 Write_rows: table id 222flags: STMT_END_F### INSERT INTO `test`.`b` # # SET### @ 1room1 / * INT meta=0 nullable=1 is_null=0 * / # # @ 2roomx' / * VARSTRING (30) meta=30 nullable=1 is _ null=0 * / # at 380,180122 16:29:03 server id 43306 end_log_pos 453,CRC32 0xd4cc34e1 Query thread_id=4 exec_time=0 error_code=0SET timestamp 1516609743 At 453 "180122 16:29:05 server id 43306 end_log_pos 518 CRC32 0xeec8cb53 Anonymous_GTID last_committed=1 sequence_number=2SET @ @ SESSION.GTID_NEXT= 'ANONYMOUS" 16:29:00 server id 43306 end_log_pos 590 TIMESTAMPIN 1516609740 # at 590,180122 16:29:00 server id 43306 end_log_pos 637 CRC32 0xe848c910 Table_map: `test`.`a` mapped to number 224th at 6377180122 16:29:00 server id 43306 end_log_pos 679 CRC32 0xec326782 Write_rows: table id 221 flags: STMT_END_F### INSERT INTO `test`.`a` # # SET### @ 1room1 / * INT meta=0 nullable=0 is_null=0 * / # # @ 2roomx' / * VARSTRING (30) meta=30 nullable=1 is _ null=0 * / # at 679 / 180122 16:29:05 server id 43306 end_log_pos 710 CRC32 0x5b9b8331 Xid = 58COMMIT SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! * /; DELIMITER; # End of log filewise / 50003 SET completed TYPEX / OLDMPLETIONTYPEN SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/

From the point of view of the generated binlog, both Myisam and Innodb engines generate corresponding binlog logs.

Close GTID, explicitly open the transaction and roll back the transaction

The DML operations performed are as follows: root@localhost:mysql3306.sock 16:29: [test] > flush logs;Query OK, 0 rows affected (0.01 sec) root@localhost:mysql3306.sock 16:35: [test] > begin;Query OK, 0 rows affected (0.01 sec) root@localhost:mysql3306.sock 16:35: [test] > insert into a values (2, "x") Query OK, 1 row affected (0.00 sec) root@localhost:mysql3306.sock 16:36: [test] > insert into b values (2, "x"); Query OK, 1 row affected (0.00 sec) root@localhost:mysql3306.sock 16:36: [test] > rollback;Query OK, 0 rows affected, 1 warning (0.01 sec) the current table data are as follows: root@localhost:mysql3306.sock 16:36: [test] > select * from a +-+-+ | id | name | +-+-+ | 1 | x | +-+-+ 1 row in set (0.00 sec) root@localhost:mysql3306.sock 16:37: [test] > select * from b The corresponding binlog logs generated by +-+-+ | id | name | +-+-+ | 1 | x | 2 | x | +-+-+ 2 rows in set (0.00 sec) are as follows: [root@mysql3 binlog] # mysqlbinlog-vv mysql-bin.000002-Base64MushrowsUniverse 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=1*/ / * 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER / *! * / # at 4#180122 16:35:39 server id 43306 end_log_pos 123 CRC32 0xed4918d4 Start: binlog v 4 Server v 5.7.18-log created 180122 16 Warning 35 CRC32 0xe56a83ea Anonymous_GTID last_committed=0 sequence_number=1SET: this binlog is either in use or was not closed properly.# at 123 180 122 16:35:39 server id 43306 end_log_pos 154 CRC32 0x25a5629b Previous-GTIDs# [empty] # at 154 54 180 122 16:36:11 server id 43306 end_log_pos 219 CRC32 0xe56a83ea Anonymous_GTID last_committed=0 sequence_number=1SET @ @ SESSION.GTID_NEXT= 'ANONYMOUS Universe # at 219 session 180122 16:36:11 server id 43306 end_log_pos 291 CRC32 0x322b71cf Query thread_id=4 exec_time=0 error_code=0SET timestamp 1516610171 pick up set @ @ session.pseudothread readreadable license 4x4pxt set @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.autocycle 1way exercise exercise masterset set @ @ session.sqltobacco modeword 2097152 SET @ @ session.auto_increment_increment=1, @ @ session.autoincrement incrementalization offsetbacks 1 racemaker races pinch packs!\ C utf8 * / / *! * /; SET @ @ session.sessions setclientholders 331 copies of session. Collationalization contacts connections3 copies session .collationalization serverals 33 canvasseset @ @ session.lcpacking timetables namespaces @ session.lcpacking timetables namespace cards 0cm session holders racemoset @ session.collationalization databases database packages DEULTGULTGULTGULTGUBINGUBINGER # at 291mm 180122 16:36:11 server id 43306 end_log_pos 338 CRC32 0xd5964b6b Table_map: `test`.`b` mapped to number 224th at 3388180122 16:36:11 server id 43306 end_log_pos 380CRC32 0xa2b7dc67 Write_rows: table id 222flags: STMT_END_F### INSERT INTO `test`.`b` # # SET### @ 1room2 / * INT meta=0 nullable=1 is_null=0 * / # # @ 2roomx' / * VARSTRING (30) meta=30 nullable=1 is _ null=0 * / # at 380,180122 16:36:11 server id 43306 end_log_pos 453CRC32 0x89983e18 Query thread_id=4 exec_time=0 error_code=0SET timestamp 1516610171 / Set @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! * /; DELIMITER; # End of log filecontrol / 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/

From the generated binlog, table a rolls back data, while table b does not roll back data because it is a non-transactional table. At the same time, the data changes in Table b also generate the corresponding binlog log. Table a does not change data or generate binlog due to transaction rollback.

(2.1) Open GTID, explicitly open the transaction and commit the transaction.

The DML operations performed are as follows: root@localhost:mysql3306.sock 16:44: [test] > begin;Query OK, 0 rows affected (0.00 sec) root@localhost:mysql3306.sock 16:44: [test] > insert into a values (3, "x"); Query OK, 1 row affected (0.02 sec) root@localhost:mysql3306.sock 16:44: [test] > insert into b values (3, "x") ERROR 1785 (HY000): Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.root@localhost:mysql3306.sock 16:44: [test] > commit;Query OK, 0 rows affected (0.01 sec)

From the error message, when GTID mode is turned on, the DML operation of non-transactional table can only be performed in the case of autocommitted statements and single-statement transactions, and the DML operation of mixing transactional table and non-transactional table is not allowed in an explicit transaction.

The corresponding binlog logs generated are as follows: [root@mysql3 binlog] # mysqlbinlog-vv mysql-bin.000001-Base64Mutual outputdecodeMurrowsCompact "50530 SET @ SESSION.PSEUDOSLAVESLAVEMod 1" OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /! * / # at 4 property 180122 16:44:07 server id 43306 end_log_pos 123 CRC32 0xf53a5a98 Start: binlog v 4, server v 5.7.18-log created 180122 16:44:07 at startup# Warning: this binlog is either in use or was not closed property. Rollback # at 123mm 180122 16:44:07 server id 43306 end_log_pos 154CRC32 0xb9b802e5 Previous-GTIDs# [empty] # at 154mm 180122 16:44:47 server id 43306 end_log_pos 219 CRC32 0x716ab799 GTID last_committed=0 sequence_number=1SET @ @ SESSION.GTID_NEXT= '5252f852-685e-11e7-8eebLY 000c299fe44c Gloza # at 219 session 180122 16:44:32 server id 43306 end_log_pos 291 CRC32 0x816f8092 Query thread_id=3 exec_time=0 error_code=0SET timestamp 151661067 2 pick up set @ @ session.pseudothread readreadable cards 3Universe set @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session. SET @ @ session.auto_increment_increment=1, @ @ session.autoincrement incrementalization offsetbacks 1 racemaker races pinch packs!\ C utf8 * / / *! * /; SET @ @ session.sessions setclientholders 331 copies of session. Collationalization contacts connections3 copies session .collationalization serverals 33 canvasseset @ @ session.lcpacking timetables namespaces @ session.lcpacking timetables namespace cards 0cm session holders racemoset @ session.collationalization databases database packages DEULTGULTGULTGULTGUBINGUBINGER # at 291mm 180122 16:44:32 server id 43306 end_log_pos 338 CRC32 0x9f700cb0 Table_map: `test`.`a`mapped to number 2" at 338 "180122 16:44:32 server id 43306 end_log_pos 380 CRC32 0x192a0649 Write_rows: table id 219 flags: STMT_END_F### INSERT INTO `test`.`a` # # SET### @ 1room3 / * INT meta=0 nullable=0 is_null=0 * / # # @ 2roomx' / * VARSTRING (30) meta=30 nullable=1 is _ null=0 * / # at 380,180122 16:44:47 server id 43306 end_log_pos 411 CRC32 0x1ef809c7 Xid = 7COMMIT SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! * /; DELIMITER; # End of log filewise / 50003 SET completed TYPEX / OLDMPLETIONTYPEN SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/

From the generated binlog, only the DML change record for table an is generated.

(2.2) Open GTID, explicitly open the transaction and roll back the transaction.

The DML operations performed are as follows: root@localhost:mysql3306.sock 16:52: [test] > flush logs;Query OK, 0 rows affected (0.01 sec) root@localhost:mysql3306.sock 16:52: [test] > begin;Query OK, 0 rows affected (0.00 sec) root@localhost:mysql3306.sock 16:52: [test] > insert into a values (4, "x") Query OK, 1 row affected (0.00 sec) root@localhost:mysql3306.sock 16:52: [test] > insert into b values (4, "x"); ERROR 1785 (HY000): Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.root@localhost:mysql3306.sock 16:52: [test] > rollback;Query OK, 0 rows affected (0.08 sec) root@localhost:mysql3306.sock 16:52: [test] > select * from a +-+ | id | name | +-+-+ | 1 | x | 3 | x | +-+ 2 rows in set (0.00 sec) root@localhost:mysql3306.sock 16:54: [test] > select * from b +-+-+ | id | name | +-+-+ | 1 | x | | 2 | x | +-+-+ 2 rows in set (0.00 sec)

From the error message, when GTID mode is turned on, the DML operation of non-transactional table can only be performed in the case of autocommitted statements and single-statement transactions, and the DML operation of mixing transactional table and non-transactional table is not allowed in an explicit transaction. There are no data changes in tables an and b.

The corresponding binlog logs generated are as follows: [root@mysql3 binlog] # mysqlbinlog-vv mysql-bin.000002-Base64Mutual outputdecodeMurrowsCompact "50530 SET @ SESSION.PSEUDOSLAVESLAVEMod 1" OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /! * / # at 4 hours 180122 16:52:16 server id 43306 end_log_pos 123CRC32 0x946808ab Start: binlog v 4, server v 5.7.18-log created 180122 16 CRC32 0x946808ab Start 52 CRC32 0x946808ab Start Warning: this binlog is either in use or was not closed properly.# at 123 "180 122 16:52:16 server id 43306 end_log_pos 194 CRC32 0x7c37ab8f Previous-GTIDs# 5252f852-685e-11e7-8eeb-000c299fe44c:1SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! * /; DELIMITER # End of log fileholders: 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/: 50003 SET completion types

From the generated binlog, there is no corresponding change record generated.

To sum up, when GTID mode is not enabled in mysql, if the DML of non-transactional tables is included in the explicitly executed transaction, the data of these tables will not be rolled back (non-transactional tables do not have the concept of rollback), and corresponding binlog logs will be generated for the sake of consistency between master and slave data. When GTID mode is turned on, non-transactional tables can only be executed separately in autocommit=1 mode or in transactions that contain only a single SQL. It can be seen that by turning on GTID mode, the transaction ACID feature is better guaranteed by prohibiting non-transaction tables from being mixed with explicit transactions, which is more secure.

How binlog records the DML operations of mysql to transaction tables and non-transaction tables is to stop here. If you want to know about other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.