MySQL Binlog Events Under Different Scenarios

1 Tables

CREATE TABLE ta (id int(11) NOT NULL, name varchar(20) DEFAULT NULL, PRIMARY KEY (id)) ENGINE=InnoDB;
CREATE TABLE tb (id int(11) NOT NULL, name varchar(20) DEFAULT NULL, PRIMARY KEY (id)) ENGINE=MyISAM;

 

2 @@binlog_format=STATEMENT

2.1

set @@binlog_format=STATEMENT;
start transaction;
insert into ta values(1, 'ta'); -- InnoDB
commit;

    QueryEvent[...,databaseName=test,sql=BEGIN]
    QueryEvent[...,databaseName=test,sql=insert into ta values(1, 'ta')]
    XidEvent[...,xid=38]

 

2.2

set @@binlog_format=STATEMENT;
insert into tb values(1, 'tb'); -- MyISAM
commit;

    QueryEvent[...,databaseName=test,sql=BEGIN]
    QueryEvent[...,databaseName=test,sql=insert into tb values(1, 'tb')]
    QueryEvent[...,databaseName=test,sql=COMMIT]

 

2.3

set @@binlog_format=STATEMENT;
start transaction;
insert into ta values(7, 'ta'); -- InnoDB
insert into tb values(7, 'tb'); -- MyISAM
commit;

    QueryEvent[,databaseName=test,sql=BEGIN]
    QueryEvent[...,databaseName=test,sql=insert into ta values(7, 'ta')]
    QueryEvent[...,databaseName=test,sql=insert into tb values(7, 'tb')]
    XidEvent[...,xid=64]

 

2.4

set @@binlog_format=STATEMENT;
start transaction;
insert into ta values(8, 'ta'); -- InnoDB
insert into tb values(8, 'tb'); -- MyISAM
rollback;

    QueryEvent[...,databaseName=test,sql=BEGIN]
    QueryEvent[...,databaseName=test,sql=insert into ta values(8, 'ta')]
    QueryEvent[...,databaseName=test,sql=insert into tb values(8, 'tb')]
    QueryEvent[...,databaseName=test,sql=ROLLBACK]

3 @@binlog_format=ROW

3.1

set @@binlog_format=ROW;
start transaction;
insert into ta values(2, 'ta'); -- InnoDB
commit;

    QueryEvent[...,databaseName=test,sql=BEGIN]
    TableMapEvent[...]
    WriteRowsEvent[...,rows=[Row[columns=[2, ta]]]]
    XidEvent[...,xid=45]

 

3.2

set @@binlog_format=ROW;
insert into tb values(2, 'tb'); -- MyISAM

    QueryEvent[...,databaseName=test,sql=BEGIN]
    TableMapEvent[...]
    WriteRowsEvent[...,rows=[Row[columns=[2, tb]]]]
    QueryEvent[...,databaseName=test,sql=COMMIT]

 

3.3

set @@binlog_format=ROW;
start transaction;
insert into ta values(5, 'ta'); -- InnoDB
insert into tb values(5, 'tb'); -- MyISAM
commit;

    QueryEvent[...,databaseName=test,sql=BEGIN]
    TableMapEvent[...]
    WriteRowsEvent[...,rows=[Row[columns=[5, tb]]]]
    QueryEvent[...,databaseName=test,sql=COMMIT]
    QueryEvent[...,databaseName=test,sql=BEGIN]
    TableMapEvent[...]
    WriteRowsEvent[...,rows=[Row[columns=[5, ta]]]]
    XidEvent[...,xid=54]

 

3.4

set @@binlog_format=ROW;
start transaction;
insert into ta values(6, 'ta'); -- InnoDB
insert into tb values(6, 'tb'); -- MyISAM
rollback;

    QueryEvent[...,databaseName=test,sql=BEGIN]
    TableMapEvent[...]
    WriteRowsEvent[...,rows=[Row[columns=[6, tb]]]]
    QueryEvent[...,sql=COMMIT]