By default in MySQL 5.6 default binary logging format is "STATEMENT". (https://dev.mysql.com/doc/refman/5.6/en/replication-formats.html)

MySQL 5.6 running on RDS has by default format set to "MIXED" though (http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.MySQL.html). Seems like STATEMENT is not even available.

What is the background behind this discrepancy?


There's no discrepancy. RDS, being a managed service, does not enable every possible configurable value for every configuration parameter.

The architects of RDS chose not to make STATEMENT available as an option for BINLOG_FORMAT, presumably, because they recognize the fact that it has many limitations in a replication environment.

Not all statements which modify data (such as INSERT, DELETE, UPDATE, and REPLACE statements) can be replicated [correctly] using statement-based replication. Any nondeterministic behavior is difficult to replicate when using statement-based replication.


Even if you don't have RDS replicas, the binary log is still used for RDS point-in-time recovery. Without requiring at least MIXED logging, there would be no way for RDS to restore an instance to a point-in-time and have it guaranteed to actually be identical to the original instance at the same point in time. Using MIXED or ROW makes that possible.

Indeed, the more confusing question is why the default value in the official distributions wasn't changed to MIXED back in MySQL 5.1 or 5.5. Using STATEMENT mode is almost always the wrong choice.

| improve this answer | |

As of 2018, AWS RDS allows the following settings for the binlog_format: ROW, STATEMENT, MIXED, OFF as shown in the official documentation of AWS RDS

The default setting of a fresh Aurora instance is STATEMENT.

mysql>  show global variables like 'binlog_format';
| Variable_name | Value     |
| binlog_format | STATEMENT |
1 row in set (0,08 sec)
| improve this answer | |

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.