Monday, May 6, 2013


Introduction

MySQL replication slave features  a powerful capability of ignoring conflicts like duplicate key error, key not found errors etc. while applying row events.  This is exceptionally useful while doing row based replication(RBR) from the master when the slave already contains some data which may conflict with the data coming from the master. In MySQL 5.7 we extend this capability while applying row events from mysql-binlog files using mysqlbinlog tool.  This enhancement will prevent such errors from aborting mysql client in case of conflicts like the ones mentioned above.

Rationale

Prior to MySQL 5.7 we have been using the mysqlbinlog tool as follows.

shell$> mysqlbinlog master.000001|mysql -uroot -hexample.com -ps3cret  

This allows us to pipe the output of mysqlbinlog to mysql, and works as long as the row events from the mysqlbinlog do not conflict with the data already present on the mysql server on example.com. However in case of conflicts, the mysql client aborts and the we have to restart the whole process again skipping the event and starting from the next event using the start-position option.  This is a problem in case of multiple conflicts.

How to use

The enhancement is pretty simple to use. mysqlbinlog in MySQL 5.7.0 provides command line options to enable this feature.

--idempotent, -i 

To use this from the command line  simply use the short option

shell$> mysqlbinlog master.000001 -i | mysql -uroot -hexample.com -ps3cret  

or use full option
shell$> mysqlbinlog master.000001 --idempotent | mysql -uroot -hexample.com -ps3cret  

How does it work

In MySQL server version  5.7.1 we have introduced a new session system variable as rbr_exec_mode which can be set to STRICT or IDEMPOTENT. When set to IDEMPOTENT, the server does not throw out any conflict errors for that particular session.

When executed with -i or --idempotent  option the mysql binlog writes
SET SESSION RBR_EXEC_MODE=IDEMPOTENT;
to the output file or to the stdout. When piped into MySQL client this directs the server to use the idempotent mode while applying row events.

This enhancement is available in MySQL 5.7.1 which can be downloaded from the MySQL download page. So go try it out and let us know your valuable feedback using the comment section.
Reactions:

About the author

Rohit Kalhans is a Software Development Engineer based out of Banglore India. His area of focus revolves around Row-based replication and Multi-threaded parallel slave. His interests include system programming, highly-available and scalable systems. In his free time, he plays guitar and piano and loves to write short stories and poems. More Information can be found on his homepage.

4 comments :

  1. See the following feature requests which are related to this post:

    http://bugs.mysql.com/bug.php?id=54250
    http://bugs.mysql.com/bug.php?id=69101
    http://bugs.mysql.com/bug.php?id=69102

    That is provide some counters so you can at least see when breakage happens and what type of breakage it was.

    Also idempotent mode seems to basically "ignore any replication errors that happen and hope for the best". Bug#52450 is a slightly different flavour which tries to allow MySQL to continue for failed statements which if executed will not make the data any worse than it was before. That is in theory they will make the slave more _consistent_ with the master if executed, rather than any other errors which will potentially make the master and slaves' database contents differ. So replication may fail with "auto-repair mode" but if it does you need to manually figure out what the fix is.

    I would really like to see an auto-repair mode as after an RBR breakage just blindly using idempotent mode is hoping that things will be better and ignoring any problems and I'd rather be a bit more cautious than that.

    ReplyDelete
    Replies
    1. Thank you Simon for your valuable feedback.

      Delete
  2. how is this better than just running mysqlbinlog ... | mysql -f

    ReplyDelete
    Replies
    1. This option directs the server to ignore the RBR errors. This means that mysql client will stop if there are other kind of error which ensures that we do not ignore important errors.
      Just to clarify, this mode is an idempotent mode of mysqlbinlog and not an "ignore all errors" mode.

      Delete