As part of the release of MySQL-5.7.7, based on community feedback, improvements have been made with respect to replication defaults, which will make MySQL replication safer and easier to use.
This blog is intended to provide information about these default changes, and it briefly explains the advantages of having them.
1) Enable Simplified GTID recovery by default:
binlog_gtid_simple_recovery=TRUE by default.
https://dev.mysql.com/doc/refman/5.7/en/replication-options-gtids.html#sysvar_binlog_gtid_simple_recovery
This variable controls how binary log files are iterated during the search for GTIDs when MySQL starts or restarts.
Setting this option to TRUE gives improved recovery performance. Because of this option server startup and binary log purge are fast. When this parameter is set the server opens only the oldest and the newest binary log files and the values of gtid_purged and gtid_executed are computed based on only Previous_gtids_log_event or Gtid_log_event found in these files. This ensures only two binary log files are opened during server restart or when binary logs are being purged.
Setting this option to TRUE in MySQL-5.6 gives good performance but in some corner cases, the computation of the set of gtids in the binary log might be inaccurate. Setting this option to FALSE always computed correct results.
In MySQL-5.7.7 there is almost no trade-off between speed and safety. Setting this option to TRUE always computes the correct results except in some corner cases.
Corner cases include:
- The newest binary log was generated by MySQL 5.7.5 or older, and gtid_mode was ON for some binary logs but OFF for the newest binary log.
- A SET GTID_PURGED statement was issued on a MySQL version prior to 5.7.7, and the binary log that was active at the time of the SET GTID_PURGED has not yet been purged.
Hence the faster option is almost always better and it has been made the default.
If this feature is off, during recovery in order to initialize gtid_executed all binary logs starting with the newest file are examined and to initialize gtid_purged all binary logs starting from the oldest to newest are examined. This can potentially take a long time.
2) Set ROW based binary log format by default:
binlog-format=ROW by default.
http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_format
When operating with the binary log ON and the binary log format is set to ROW mode, then changes to individual table rows are written to the binary log file. Then they will be installed on the slave side. This is different from when using STATEMENT format which instead makes the server write STATEMENTs in the binary log that will be re-executed on the slave.
All sorts of changes can be replicated and this is the safest form of replication. It requires fewer row locks when compared to statement based replication for certain SQL statements. With the previous default STATEMENT option nondeterministic statements could make the slave diverge from the master.
3) Make binlog_error_action=ABORT_SERVER the default:
‘binlog_error_action’ controls what action should be taken when the server cannot write to the binary log.
http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_error_action
Setting binlog_error_action=ABORT_SERVER will make the server abort on fatal errors such as disk full, readonly file system, etc. With this ABORT_SERVER option the binary log and slaves are safe, and this is the reason we have changed the default.
With previous default binlog_error_action=IGNORE_ERROR, if an error occurs that prevents mysqld from writing to the binary log the server writes an appropriate error message into the error log and disables the binary log. Note that the server will continue to operate with the binary log disabled, thus causing the slave to miss the changes that happened after the error.
4) Enable crash safe binary logs by default:
Crash safe binary logs are controlled by the sync_binlog variable. This variable identifies the number of binary log commit groups to collect before synchronizing the binary log to disk.
http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_sync_binlog
With sync_binlog=1, all transactions are synchronized to the binary log before they are committed. Therefore, even in the event of an unexpected restart, any transactions that are missing from the binary log are only in prepared state. This causes the server’s automatic recovery routine to rollback those transactions. This guarantees that no transaction is lost from the binary log, and is the safest option. Indeed, it increases the total number of fsyncs called, but since MySQL 5.6, the server groups transactions and fsync’s them together, which minimizes greatly a potential performance hit.
When sync_binlog=0, the binary log is never synchronized to disk by the MySQL server, in this case the server relies on the operating system to flush the binary log’s contents from time to time as for any other file. Therefore, in the event of a power failure or operating system crash, it is possible that the server has committed some transactions that have not been synchronized to the binary log. Therefore it is impossible for the recovery routine to recover these transactions and they will be lost from the binary log.
Hence the new default sync_binlog=1 is safer.
5) Lower default slave_net_timeout:
‘slave_net_timeout’ specifies the number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. This variable also has an impact on the frequency of heartbeats between master and slave, since the hearbeat period is
slave_net_timeout divided by 2 by default.
http://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#option_mysqld_slave-net-timeout
The new default value is slave_net_timeout=60 while the previous default value was 3600 seconds. With the old value, long replication delays were possible due to transient network failures.
6) Deprecate @@session.gtid_executed:
‘@@global.gtid_executed’ variable contains a representation of the set of all transactions that are logged in the binary log.
http://dev.mysql.com/doc/refman/5.7/en/replication-options-gtids.html#sysvar_gtid_executed
When used in session scope this variable contains the set of transactions that are written to the transaction cache. More specifically, SESSION.GTID_EXECUTED is equal to UUID:NUMBER only when GTID_NEXT has the form UUID:NUMBER and at least one DML statement has been executed and not committed. When GTID_NEXT is set to other values, SESSION.GTID_EXECUTED contains an empty string. In MySQL-5.7.7 if this session scope variable is used a deprecation warning is generated. There is no change with respect to @@global.gtid_executed. Since @@global.gtid_executed is used most frequently and if a user forgets to mention the ‘global’ key word they will get this session variable which will give them incorrect data. To avoid this confusion the session variable is being deprecated. Also this session variable is not known to be useful for any practical purpose, hence it is being deprecated.