Sunday, 12 May 2013

New rewrite-db option for mysqlbinlog

Introduction

With the wonderful and the best ever mysql release of mysql-5.6 the developers here at mysql have started working towards development of new features for the next release to make it even better. There are some new and cool features in the new MySQL 5.7.1 DMR, including one that allows the user to instruct mysqlbinlog to rewrite an event's database when it parses a binary log and  outputs the textual representation of the data.

mysqlbinlog is a client utility to process binary logs files. The server contains number of binary log files which contain events that describe the modifications to the database contents. Server writes these changes in the binary format. In order to read the contents from the binary file we use the
mysqlbinlog utility with number of options as listed here :

http://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog.html

Events can be logged in the binlogs in two formats :

1) Statement based and
2) Row based

This new option has been added for Row based replication. In Row based replication  the events in the binary log is in a base64 encode form :

BINLOG '
dfLtTBMBAAAAPQAAANoFAAAAAGsAAAAAAAEAA210cgARdGVzdF9zdXBwcmVzc2lvbnMAAQ8C/wAB
+0M1Dw==
dfLtTB4BAAAAnwAAAHkGAAAAAGsAAAAAAAcAAgAB//56U2xhdmUgU1FMLipDb3VsZCBub3QgZXhl
Y3V0ZSBXcml0ZV9yb3dzX3YxIGV2ZW50IG9uIHRhYmxlIHRlc3QudDE7IER1cGxpY2F0ZSBlbnRy
eSAuMi4gZm9yIGtleSAuUFJJTUFSWS4sIEVycm9yX2NvZGU6IDEwNjL4eWse
'/*!*/;

which contains the table map event and the actual row event (insert/update/delete). Table_map event records important information about the database, table, the number of column, metadata block length and few other value.
When the event is replicated on to the slave the table map event is first read and the corresponding mapping to the slave's database followed by the mapping to the table to which the changes belong to and finally the changes are applied.

Use case

One of the use of this option is a sharding implementation in which one shard is one database and one server may have many shards. When cloning shards, the user will seed a new database with a snapshot of the parent database.
We then keep the clone shard up to date by applying the binlogs from the parent  shard. So the new option (rewrite-db) allows the user to apply the changes of one database on to another database.

eg :

shell> mysqlbinlog --rewrite-db="from_db1->to_db1" 
           --rewrite-db="from_db2->to_db2"  binlog.000001 > /tmp/statements.sql

shell> mysql -u root -p -e "source /tmp/statements.sql"

So how does this option work

So in order to get this rewriting we need to process the table_map event which contains the database information of the originating database and replace that with the new option and keep the extra information as it is :

    Before Rewriting

    +-------------+-----------+-------------+----------+----------+-------+-------------+
    |common_header|post_header|database_info|table_info|extra_info|
    +-------------+-----------+-------------+----------+----------+-------+-------------+

    After Rewriting :

    +-------------+-----------+-----------------+----------+----------+----------+-------------+
    |common_header|post_header|new_database_info|table_info|extra_info|
    +-------------+-----------+-----------------+----------+----------+----------+-------------+


How to use the new option

shell> ./mysqlbinlog  --rewrite-db="db1->db2" master-bin.000001 > applier.sql
shell> ./mysql -u root -p -d --database=db2 < applier.sql

In case the user wishes to rewrite multiple databases and rewrite each one to a
different destination database the syntax should be :

shell> mysqlbinlog --rewrite-db="from_db1->to_db1"  
             --rewritedb="from_db2->to_db2" binlog.000001 > /tmp/statements.sql

Conclusion

This post discusses the details about the details of the new rewrite-db option of mysql available in latest 5.7.1 milestone release available at

http://dev.mysql.com/downloads/mysql/

Go and download the first development release after 5.6 was declared GA. Try it out and send in your feedback. Be an active part of MySQL 5.7.

No comments:

Post a Comment