MySQL-5.7.7 default settings improvements for the copy feature

• 1. Simplified Gtid recovery with default enabled

binlog_gtid_simple_recovery=ture (default value)
This parameter controls how MySQL iterates over the Binlog file when it searches for Gtids when MySQL starts or restarts.

This option is set to true and will improve the performance of MySQL performing recovery. Because of this mysql-server start and binlog log cleanup faster. When this parameter is true, Mysql-server only needs to open the oldest and newest of these 2 binlog files, the value of gtid_purged parameter and the value of the gtid_executed parameter can be based on the previous_gtids_log_event in these files or gtid_log_event calculated. This ensures that when Mysql-server restarts or cleans up binlog, only 2 binlog files are opened.
In MySQL-5.6, adjusting this option setting also improves performance, but in some special scenarios, calculating the Gtids value can be an error. Keeping the value of this option false will ensure that the calculation is always correct.
In MySQL-5.7.7, there is little need for permission between speed and security. Setting this option to true always gets the correct results, except for the following 2 extreme scenarios:
A. The latest binlog is MySQL-5.7.5 (or older), and Gtid_mode is set to on some binlog, but the latest Binlog setting is off.
B. The status of Gtid_purged was released prior to MySQL-5.7.7, and at the time the Binlog active cleanup was still not cleaned up at the moment.
Therefore, it is almost always better to turn on this option, so this option is turned on by default.
If this parameter is set to OFF, all binlog starting with the latest file will be checked during MySQL recovery in order to initialize the gtid_executed. And in order to initialize the gtid_purged, all Binlog are checked. This can take a very long time.

• 2. Default Binlog format adjusted to row format

Binlog-format=row (default value)
When Binlog is turned on and the Binlog format is set to row mode, the row changes for each table are written to the Binlog file, and these changes are applied from the library side. This is different from using the statement format Binlog. In the statement format, Binlog is re-executed from the library side.
All database changes can be replicated and this is the safest way to replicate. Row-based replication requires fewer rows-level locks than statement-based replication. In the previous default statement format, an indeterminate state could cause an issue where the principal was never consistent.

• 3. The default Binlog error after the operation is adjusted to Abort_server

Binlog_error_action=abort_server
The Binlog_error_action parameter controls what action Mysql-server will take when the Binlog cannot be written.
Setting Binlog_error_action=abort_server causes Mysql-server to exit when write Binlog encounters a critical error, such as a full disk, a file system that is not writable, and so on. Under the Abort_server option, both Binlog and slave libraries are safe, which is why the official modification of this default value.
Under Previous options (Binlog_error_action=ignore_error), if an error occurs, the inability to write to Binlog,mysql-server logs an error in the error log and forces the Binlog feature to be turned off. This causes the Mysql-server to continue running in a mode that does not log binlog, resulting in binlog from the library that cannot continue to be fetched to the main library.

• 4. Binlog security When MySQL crashes is turned on by default

MySQL crash binlog security is the Sync_binlog parameter control, the value of this parameter represents the number of groups committed before the Binlog is flushed to disk.
When sync_binlog=1, all transactions are written to Binlog before committing. Therefore, even if the Binlog event encounters an unexpected restart, some binlog in the prepared state are lost. This causes the server to automatically roll back these transactions when it recovers data. This ensures that the transaction is not lost from Binlog and is therefore the safest option. In fact, this increases the total number of synchronizations to disk. However, starting with MySQL5.6, the group commit and merge synchronization has been supported, which minimizes the likelihood of performance problems.
When sync_binlog=0, Mysql-server does not synchronize Binlog to disk, but relies on the operating system to synchronize the contents of the Binlog to disk. Therefore, when a power-down or operating system crash occurs, it is likely that a transaction that has been committed has not been synchronized to disk. As a result, MySQL cannot recover these transactions during automatic recovery, and they are missing from the Binlog.

• 5. Default Low Slave_net_timeout

Slave_net_timeout defines the number of seconds to get data from the library from the main library, over which time the library proactively exits the read, interrupts the connection, and attempts to reconnect. This parameter also affects the frequency of the heartbeat test between the master and slave libraries, and the default value of this frequency is slave_net_timeout divided by 2.
Slave_net_timeout the new default value is 60, and the previous default value is 3,600 seconds. Under the old value, long replication latencies are likely to be caused by network transients.

• 6. Cancel the session-level gtid_executed parameter (@ @session. gtid_executed)

The ' @ @global. gtid_executed ' variable includes a collection of all transactions that are recorded in Binlog.
When the session-level value of this variable is used, he represents the value written to the transaction cache. Session.gtid_executed is equal to the value of Uuid:number only if Gtid_next is uuid:number and at least one DML statement has been executed but not committed. When Gtid_next is set to another value is, session. Gtid_executed contains an empty string. In MySQL5.7.7, if you use this session-level variable, a warning is triggered. There is no change to the global level of this variable (@ @global. gtid_executed) Because this parameter is used more frequently and when the user forgets to mention the Global keyword, they get the session-level value of the variable, which is probably incorrect. To avoid this problem, we removed the session-level setting for this variable, and also because this parameter has no known value in the application. So we canceled him off.

MySQL-5.7.7 default settings improvements for the copy feature