2

I have a production system that uses MySQL statement based replication for hot failover should a master database die. Running version 5.5 Percona. I have to use statement based replication for reasons that are immutable for the purposes of this question.

Now, I'd like to see the row-based replication stream of the same data, with the goal of trying to adapt this into an HBase based data store.

Is it possible to set up a MySQL server to slave (read) using statement-based replication, but at the same time be replication master (write to other slaves) using row-based replication? If so, how do I set this up? I've looked in the docs but failed to find it.

0

1 Answer 1

6

I have to use statement based replication for reasons that are immutable for the purposes of this question.

I assume, for the purpose of this question, that there may indeed be compelling reasons for using statement-based logging¹ but it is not generally recommended, because statement-based logging is relatively fragile. On any system where you have the flexibility not to use statement-based logging, don't use it -- use MIXED or ROW

MySQL Server (and compatible systems, like Percona Server, MariaDB, and Aurora for MySQL) automatically "translate" from one format to the other, based on the configuration of each individual server.

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.

https://dev.mysql.com/doc/refman/5.6/en/binary-log-setting.html

To restate this with some additional implications, what you want to do “just works,” because the setting of binlog_format on the slave does not specify what the slave expects. It only sets what the slave will generate.

Configure the slave with binlog_format=ROW and enable log_slave_updates in my.cnf on the slave (which causes incoming events to be rewritten to the slave's binlog).

...and you're done.

The slave will log all of its DML as row-based events in spite of the master's binlog format. You don't really have to do anything else to make a slave also be a master, since every MySQL server with binary logging enable is, essentially, already a master -- it just may happen to be a master without any actual slaves.

Any combination of master and slave binlog_format is valid except for a master configured as ROW and a slave configured as STATEMENT (the opposite of what you're doing here), because while statements can be translated into row events (they affect rows on the slave, after all), the converse isn't true -- you can't necessarily determine the specific statement that changed rows, if your only knowledge is the actual changed data. But for the application you're asking about, the above should do exactly what you intend.

I also discussed the interactions of the possible combinations of master and slave binlog format here, on dba.stackexchange.com in some additional detail.


¹logging is used here rather than "replication" because it's a more accurate description of what is actually being configured, though arguably the meaning is unchanged.

² STATEMENT logs the actual queries that made the change; ROW logs "row images" of the rows inserted/updated/deleted by the query. For updates, boththe old and new values are logged. MIXED mode allows the server to choose the format for each query, always using ROW when there is anything about the query that makes its impact on the database potentially unsafe for statement-based replication, because the replica could potentially interpret it in a way that would cause the replica's data to diverge from the master, because the query isn't deterministic. Examples might include an unordered UPDATE ... LIMIT where the replica might update a different set of rows depending on its index selection, and statements using non-deterministic functions like UUID(). Other seemingly-non-deterministic functions like NOW() and RAND() are compatible with statement-based replication, because there are hints written to the statement log to indicate the master's system time, and the master's random seed, at the time the query was executed.

1
  • 1
    Thank you for this answer. Triple-AAAA+, 10/10, would ask again!
    – Jon Watte
    Feb 5, 2014 at 21:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .