By: Daniël van Eeden

Daniël van Eeden — Sun, 09 Aug 2015 07:57:31 +0000

It is possible to get info about RBR events with binlog_rows_query_log_events.

mysql-5.6.25-log > CREATE TABLE mytable1 (id int auto_increment primary key, name varchar(100));
Query OK, 0 rows affected (0.02 sec)

mysql-5.6.25-log > INSERT INTO mytable1(name) VALUES(‘test1′);
Query OK, 1 row affected (0.01 sec)

mysql-5.6.25-log > SET binlog_format=’ROW’;
Query OK, 0 rows affected (0.00 sec)

mysql-5.6.25-log > INSERT INTO mytable1(name) VALUES(‘test2’);
Query OK, 1 row affected (0.00 sec)

mysql-5.6.25-log > SET binlog_rows_query_log_events=ON;
Query OK, 0 rows affected (0.00 sec)

mysql-5.6.25-log > INSERT INTO mytable1(name) VALUES(‘test3’);
Query OK, 1 row affected (0.01 sec)

mysql-5.6.25-log > SHOW BINLOG ┌─────────── │ Log_name │ Pos │ Event_type ├─────────── │ binlog.000001 │ 4 │ Format_desc │ │ binlog.000001 │ 120 │ Query │ binlog.000001 │ 270 │ Query │ binlog.000001 │ 349 │ Intvar │ binlog.000001 │ 381 │ Query │ binlog.000001 │ 497 │ Xid │ binlog.000001 │ 528 │ Query │ binlog.000001 │ 600 │ Table_map │ │ binlog.000001 │ 654 │ Write_rows │ │ binlog.000001 │ 700 │ Xid │ binlog.000001 │ 731 │ Query │ binlog.000001 │ 803 │ Rows_query │ │ binlog.000001 │ 869 │ Table_map │ │ binlog.000001 │ 923 │ Write_rows │ │ binlog.000001 │ 969 │ Xid └─────────── 15 rows in set (0.00 sec) EVENTS;
────┬─────┬─────────────┬───────────┬─────────────┬──────────────────────────────────────────────────────────────────────────────────────────┐
│ Server_id │ End_log_pos │ Info │
────┼─────┼─────────────┼───────────┼─────────────┼──────────────────────────────────────────────────────────────────────────────────────────┤
10 │ 120 │ Server ver: 5.6.25-log, Binlog ver: 4 │
│ 10 │ 270 │ use `test`; CREATE TABLE mytable1 (id int auto_increment primary key, name varchar(100)) │
│ 10 │ 349 │ BEGIN │
│ 10 │ 381 │ INSERT_ID=1 │
│ 10 │ 497 │ use `test`; INSERT INTO mytable1(name) VALUES(‘test1’) │
│ 10 │ 528 │ COMMIT /* xid=141 */ │
│ 10 │ 600 │ BEGIN │
10 │ 654 │ table_id: 100 (test.mytable1) │
10 │ 700 │ table_id: 100 flags: STMT_END_F │
│ 10 │ 731 │ COMMIT /* xid=143 */ │
│ 10 │ 803 │ BEGIN │
10 │ 869 │ # INSERT INTO mytable1(name) VALUES(‘test3’) │
10 │ 923 │ table_id: 100 (test.mytable1) │
10 │ 969 │ table_id: 100 flags: STMT_END_F │
│ 10 │ 1000 │ COMMIT /* xid=145 */ │
────┴─────┴─────────────┴───────────┴─────────────┴──────────────────────────────────────────────────────────────────────────────────────────┘

Comments on: On SHOW BINLOG/RELAYLOG EVENTS

By: Daniël van Eeden