binlog_format question

[e7868a]

Hi,

mysql-binlog-connector-java is a great tool. I have tried some other mysql replication tools, like Databus(LinkedIn), canal(alibaba).They all need to set binlog_format=ROW.

While using mysql-binlog-connector-java, I tried 'MIXED' and 'ROW'. Both can work.
I got 'WRITE_ROWS' event while using 'ROW' format. 'QUERY' event while using 'MIXED' format.It seems both format are supported.

Is it OK to use binlog_format='MIXED' and mysql-binlog-connector-java, in my production environment?

thanks.

[rtreffer]

I usually recommend to use only ROW if you use replication. QUERY is too unstable.

Basically every query that is non-deterministic may cause your servers to diverse. Non-Deterministic is caused by:

• SUM without order by (numeric stability of e.g. double)
• Any Query that uses rand()
• Any non-aggregated multi-row value with GROUP BY and without ORDER BY (e.g. SELECT hostname FROM hosts GROUP BY domain)

You can get it to work but chances are you'll invest more time and money than you would if you would simply switch to ROW based replication.

[shyiko]

Hello Jian,

Glad to hear you find it useful.

As for the binary log format, it doesn't matter which one is being used. mysql-binlog-connector-java works equally well with ROW, STATEMENT and MIXED. The only thing to keep in mind - (currently) QueryEventDataDeserializer skips status variables (look for "status variable block" under Query_log_event/QUERY_EVENT). As long as you don't need that - you should be perfectly fine. And when you do (if ever) - just let me know :).

Regarding ROW vs MIXED, I wouldn't rush to choose one over the other (if you even have that option). MySQL RM :: 17.1.2 Replication Formats describes a lot of pros and cons, which helps to decide depending on a specific use-case at hand.

Let me know if you have any other questions and if not - please mark this issue as closed.
Thank you.