Reference manual for MySQL 5.6 states that " Some changes, however, still use the statement-based format. Examples include all DDL (data definition language) statements such as CREATE TABLE, ALTER TABLE, or DROP TABLE. "

Does this statement means that even if we have ROW format for binary logs all DDLs will be logged in binary log as statement based? How does this affect replication?

Kindly help me to understand this.

2 Answers 2


Even though binlog_format is a global variable, it doesn't exactly set the "format" of the binary log itself -- it sets the format in which DML events are logged for execution by the slave.

In STATEMENT mode, the actual DML queries that change data on the master are written to the log as they were received by the master, and subsequently executed on the slave.

In ROW mode, DML queries are not logged as SQL; instead, "images" of the actual changed rows are written to the log, which the slave applies directly to its data set. When rows are deleted, the data contained in the deleted row is written to the log; when rows are inserted, the inserted rows are logged, and when rows are updated, the data from the old and new version of the row is logged.

Row-based logging has a number of advantages over statement-based logging, perhaps the most significant being the fact that it's absolutely deterministic, since the actual data from the actual rows is sent from machine to machine. There are interactions with no user-defined variables, no non-deterministic functions (e.g. UUID()), no issues with rows being accessed in any particular order (e.g. DELETE ... LIMIT) ... but since row-based logging only deals with the data in the individual rows, it's only applicable to DML.

DDL is still logged an SQL query -- a statement-based log event -- because that's how DDL is done -- you alter a table with ALTER TABLE... the row based log events are "another way" of changing row data, but there would be no reason for MySQL to implement "another way" of handling ALTER TABLE (or similar events). The row-based logging format is only interested in row images, not table objects.

DDL has none of the same problems with determinism that DML has, which is the primary problem that row-based replication solves -- there's more than one way to DELETE FROM t1 LIMIT 1 (which '1' row would get deleted is generally predictable but technically undefined) but there's already exactly and only one way to ALTER TABLE t1 DROP COLUMN c1.

How does this affect replication? It doesn't have any negative impact on replication, or any particular impact at all, since that's how row-based replication has always worked since it was introduced in MySQL.

It's something to keep in mind if you are filtering replication because of the potential impact on how filtering rules are interpreted... but if you are thinking about filtering replication (not replicating all of your tables), my advice is that it's usually best to just take those thoughts... and let them go. It's hard to think of any really good cases where filtering replication is worth the trouble when the default "replicate everything" is so much more straightforward and less subject to nuance, and if you have data that for some reason shouldn't be on the slave, there's always the BLACKHOLE storage engine.

  • In MySQL 5.6, the default binary logging format is STATEMENT. Changing the binary logging format on the master while replication is ongoing, or without also changing it on the slave can thus cause unexpected results, or even cause replication to fail altogether.

  • In addition to switching the logging format manually, a slave server may switch the format automatically. This happens when the server is running in either STATEMENT or MIXED format and encounters an event in the binary log that is written in ROW logging format. In that case, the slave switches to row-based replication temporarily for that event, and switches back to the previous format afterward.

  • With statement-based binary logging, the master writes the executed queries to the binary log. This is a very fast, compact, and efficient logging method that works perfectly in most cases. However, it is possible for the data on the master and slave to become different if a query is designed in such a way that the data modification is nondeterministic (generally not a recommended practice, even outside of replication) The easiest way to avoid this problem is to add an ORDER BY clause to the aforementioned nondeterministic queries to ensure that the rows are always stored or modified in the same order. Using row-based or mixed logging format also avoids the problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.