3

I have replication setup between an on-prem MySQL 5.6 server (A) to another on-prem MySQL 5.6 server (B). I was setting up an AWS DMS instance (C) that I wanted to migrate and then replicate. It told me that I had, then, to have my binlog_format set to ROW-based replication.

Is this feasible? Can I expect changes on A to replicate correctly to C?

4

STATEMENTROW is absolutely a valid configuration.

The thing you've probably overlooked is that Server B needs log_slave_updates enabled, or it won't replicate anything from A to C.


But to address the question at hand, consider the following table, which describes the comparison between configured @@binlog_format and what is actually written to the server's binlog. Assume A is master, B is slave of A, no changes are made directly to B, and of course log_slave_updates is enabled on B:

A binlog_format | A actually writes | B binlog_format | B actually writes
-------------------------------------------------------------------------
STATEMENT         STATEMENT           STATEMENT         STATEMENT
STATEMENT         STATEMENT           MIXED             MIXED
STATEMENT         STATEMENT           ROW               ROW

MIXED             MIXED               STATEMENT         TOTALLY FAILS EVENTUALLY
MIXED             MIXED               MIXED             MIXED
MIXED             MIXED               ROW               ROW

ROW               ROW                 STATEMENT         TOTALLY FAILS VERY QUICKLY
ROW               ROW                 MIXED             ROW
ROW               ROW                 ROW               ROW

What's going on here?

When B writes to its binary log, it writes an entry to replicate the change it made. It writes these entries in the format its local optimizer determines should be used. When STATEMENT or ROW mode is in effect, that's the only choice, so incoming STATEMENT becomes outgoing ROW, in your case. Server B easily translates because it executes the incoming event and then simply logs which rows it inserted/updated/deleted in its local data, and the values of the columns in those rows.

Each MySQL Server can set its own and only its own binary logging format (true whether binlog_format is set with global or session scope). This means that changing the logging format on a replication master does not cause a slave to change its logging format to match. [...]

Changing the binary logging format on the master while replication is ongoing, or without also changing it on the slave can cause replication to fail with errors such as Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'

http://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html

That's what I was referring to, above, with TOTALLY FAILS. The only invalid configuration is for a server to be in STATEMENT mode while at least one upstream master is in ROW or MIXED.

Why? Every possible query that can be represented in STATEMENT format can always be subsequently represented in ROW format, because ROW format is absolutely deterministic. Conversely, no query replicated in ROW format can ever be subsequently replicated in STATEMENT format.

Note that MIXED format simply gives the local optimizer the option on a query by query basis of how to replicate -- STATEMENT or ROW -- but if the incoming replication event is ROW the only outgoing choice is also ROW.


Also, STATEMENT mode is seriously terrible. Get away from it as soon as you can. Before then, it's vitally critical to verify that the data on B is identical to the data on A in every way, because the data on B can slip out of sync much easier and go undetected when A is in STATEMENT mode.

  • Thanks for such a clear explanation. I forgot to mention that I have log_slave_updates enabled; that was the first thing I tried. Still this explanation clearly explains that my configuration should be valid, and why. – Aaron R. Jan 4 '17 at 5:10
  • And now, I went to the trouble to validate this by standing up a new Server C on prem and it's working fine, A->B->C. So this is likely an issue with the AWS DMS tooling, and not with MySQL replication. – Aaron R. Jan 4 '17 at 5:36
  • What's DMS doing? Complaining? Or acting like everything is fine, except just "not working" (in what sense?) – Michael - sqlbot Jan 4 '17 at 11:15
  • The symptoms were that there were no errors, but a value change on A wasn't propagated through to C. Just like log_slave_updates would have shown. It's actually working now, though. I didn't make any config changes but after validating a different "server C" worked just fine, I went back and found the Aurora instance connected through DMS is now suddenly working, as well. – Aaron R. Jan 4 '17 at 15:12
2

Personally, I am not comfortable with different binlog_format settings between Master and Slave (See my old post from 1.5 years ago MySQL v5.1.73 - can the binlog_format betweem Master & slave be different?)

In your particular case, I would make the Slave STATEMENT or the Master ROW. You could probably get away with MIXED on the Slave. If the Slave is ROW or MIXED, just be prepared for bigger logs.

I did mention in my earlier post that you would have to experiment.

Here is what I believe would work

  • MIXED Slave could replicate from ROW Master or STATEMENT Master
  • ROW Slave could replicate from ROW Master or MIXED Master
  • STATEMENT Slave could replicate from STATEMENT Master only

You should test this on Staging Servers and see.

  • Thanks for your recommendations. In my case, though, changing the binlog_format is outside the question; Server A (the prod master) I didn't want to touch while it's live, and Server C (The DMS migration instance) requires that Server B be set to ROW. It isn't working though and I wanted to know if this configuration is valid. – Aaron R. Jan 4 '17 at 0:08
1

Short answer - yes they can and replication A->B->C will work correctly. Pros and cons of ROW and STATEMENT will apply respectively.

I can't justify using STATEMENT though.

  • This is what I wanted to hear, but I haven't been able to find any documentation explaining why it should work. I have my own wild theories, of course. – Aaron R. Jan 4 '17 at 0:09
  • I look at this setup as two master/slave couples: A->B and B->C. A slave can replicate from a master regardless the binlog format. Thus, I see no reason why it shouldn't work (again, besides usual stuff like non-deterministic queries and the STATEMENT format). If you experience opposite please share your findings. It would be interesting to know. – akuzminsky Jan 4 '17 at 0:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.