In the MySQL team @ Oracle, we’ve been discussing changing the default values for MySQL replication so that they are safer to use.
|Setting||Current Value||Proposed New Value|
I have written about all of these settings before in my post on deciding whether or not to make MySQL durable. The short version is that:
- By default, if MySQL crashes you could lose data.
- You don’t get to chose what you lose, it could be a $10 order, or a $10M order.
- Independent of losing orders, DBA time is expensive. Having to perform post-failure investigation to handle discrepancies has a high cost.
- The cost of durability is much lower with SSDs. In a comment, Daniël van Eeden also correctly said that the cost is much lower in 5.6 with group commit (thanks Daniël!).
- I recommend most users should be running with MySQL as fully durable.
Now to explain each of these proposed changes:
sync_binlog=1 you are ensuring that any
changes written to the binary log will not be lost in the event
of a crash. This is a problem because with the current default of
sync_binlog=0, a replication master crashing may
lose events and require all slaves to be re-imaged with a fresh
copy of data to be consistent.
It’s important to note that changing this setting has a downside: syncing the binary log causes a performance impact. As I mentioned above, this was reduced significantly in MySQL 5.6 with group commit, but it will still be evident in some workloads. The most unfortunate may be in single-threaded performance on systems with hard-drives and no RAID controller with a battery backed write cache.
These two options were first introduced in MySQL 5.6 as part of a
feature called transactional replication. When both set
TABLE, they store the internal replication
coordinates in an InnoDB table, and
as part of the same transaction as the replication events being
What this means in practical terms, is that slaves are in a consistent state when they crash. Replication can just resume processing events from the point of failure, and data does not need to be re-imaged from the master or another slave.
As with previous proposals, we are seeking feedback on how these changes will impact you.
- Do you currently use
sync_binlogand transactional replication?
- Have you considered these features, but decided not to switch? (This is a case where we’d really love to hear from you).
- Do you agree that these changes will make MySQL safer, and easier to administer?
Please leave a comment, or get in touch!