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?
Best Answer
STATEMENT
→ROW
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 courselog_slave_updates
is enabled on B: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
orROW
mode is in effect, that's the only choice, so incomingSTATEMENT
becomes outgoingROW
, 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.That's what I was referring to, above, with
TOTALLY FAILS
. The only invalid configuration is for a server to be inSTATEMENT
mode while at least one upstream master is inROW
orMIXED
.Why? Every possible query that can be represented in
STATEMENT
format can always be subsequently represented inROW
format, becauseROW
format is absolutely deterministic. Conversely, no query replicated inROW
format can ever be subsequently replicated inSTATEMENT
format.Note that
MIXED
format simply gives the local optimizer the option on a query by query basis of how to replicate --STATEMENT
orROW
-- but if the incoming replication event isROW
the only outgoing choice is alsoROW
.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 inSTATEMENT
mode.