MySQL 5.6.2 DM: Binlog Informational Events

In some cases it would be really useful to have additional information in the binary log, mostly for debugging purposes. Starting with MySQL 5.6.2 DM release, the mysql replication event set was extended to include a new type of event that is basically used for transferring data around that does not actually changes state at the slaves. It can be safely ignored for the actual replication procedures, but it can be extremely relevant for conducting debugging…

Row-based Replication

Quite frequently, one finds someone out there requesting that in row based replication, the original statement is written into the binary log, alongside with the row event(s) it generated.

Starting with MySQL 5.6.2 DM, and by making use of the Informational Events facility, users can now do exactly that. While turning on the switch --binlog-rows-query-log-events (or activating it through the correspondent session variable) the mysql replication layer is instructed to write the statement to the binary log as a special event. These events do not require any extra processing from the slave(s), well… maybe just to relay them, and only if the user wants them to be relayed...

Lets have a look how it works! In the following example, the option to log statements is activated while logging in ROW, and then some rows are inserted into a table. This generates some row events entries in the binary log! As expected they show up when one issues the command 'SHOW BINLOG EVENTS'. In addition, the new event type Rows_query comes up in the output, showing the original query!

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

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

mysql> CREATE TABLE t1 (a INT);
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t1 VALUES (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SHOW BINLOG EVENTS;
+-------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| Log_name          | Pos | Event_type  | Server_id | End_log_pos | Info                                          |
+-------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| master-bin.000001 |   4 | Format_desc |         1 |         114 | Server ver: 5.6.3-m5-debug-log, Binlog ver: 4 |
| master-bin.000001 | 114 | Query       |         1 |         200 | use `test`; CREATE TABLE t1 (a INT)           |
| master-bin.000001 | 200 | Query       |         1 |         268 | BEGIN                                         |
| master-bin.000001 | 268 | Rows_query  |         1 |         323 | # INSERT INTO t1 VALUES (1), (2), (3)         |
| master-bin.000001 | 323 | Table_map   |         1 |         364 | table_id: 54 (test.t1)                        |
| master-bin.000001 | 364 | Write_rows  |         1 |         408 | table_id: 54 flags: STMT_END_F                |
| master-bin.000001 | 408 | Query       |         1 |         477 | COMMIT                                        |
+-------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
7 rows in set (0.00 sec)

Actually, 'SHOW BINLOG EVENTS' is not the only way to display these events, one can also check them through the mysqlbinlog tool. There is a catch however, mysqlbinlog will only output the special event if one sets the verbosity level to 2 (-vv) or more.

shell> mysqlbinlog -vv data/master-bin.000001
(...)
# at 268
#110401 14:24:29 server id 1  end_log_pos 323   Rows_query
# INSERT INTO t1 VALUES (1), (2), (3)
# at 323
#110401 14:24:29 server id 1  end_log_pos 364   Table_map: `test`.`t1` mapped to number 54
# at 364
#110401 14:24:29 server id 1  end_log_pos 408   Write_rows: table id 54 flags: STMT_END_F
(...)
shell>

Summary

This blog post introduces the user to the new type of (informational) events that MySQL 5.6.2 DM provides. Furthermore, by making use of such type of events mysql replication can now log the original statements along with the rows it generated to the binary log. This is something that is asked every now and then, for a long time… So there you have it, you can find it in the MySQL 5.6.2 DM release.

Enjoy!

References

You can find detailed information about this feature in:

• http://forge.mysql.com/worklog/task.php?id=4033