mysql binlog events_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]

分享到：

2011-12-30 14:27

浏览 2102

分类:数据库

评论