binlog_gtid_simple_recovery

This variable controls how binary log files are iterated during the search for GTIDs when MySQL starts or restarts. In MySQL version 5.6.21, this variable was added as simplified_binlog_gtid_recovery and in MySQL version 5.6.23 it was renamed to binlog_gtid_simple_recovery.

When binlog_gtid_simple_recovery=FALSE, the iteration starts from the newest file to initialize gtid_executed, and starts from the oldest file to initialize gtid_purged. This process could take a long time if you had a large number of binary log files without GTID events, for example created when gtid_mode=OFF.

When binlog_gtid_simple_recovery=TRUE, the server does not open more than two binary logs when iterating to populate gtid_purged and gtid_executed, either during server restart or when binary logs are being purged.

enforce_gtid_consistency

When this variable is true, the server enforces GTID consistency by allowing execution of only those statements that can be logged in a transactionally safe manner. You must enable GTID consistency (by enabling enforce_gtid_consistency) before you can start the server with gtid_mode=ON; otherwise, enabling GTID mode fails with an error. You can (and should) enable GTID consistency prior to using gtid_mode, in order to test whether the system is ready to use GTIDs.

Since only transactionally safe statements can be logged when enforce_gtid_consistency is true, it follows that the operations listed here cannot be used when this is the case:

This variable is read-only at runtime and must be set at server startup.

Nontransactional DML statements involving temporary tables are allowed when using binlog_format=ROW, as long as any nontransactional tables affected by the statements are temporary tables (Bug #14272672).

In MySQL 5.6, it is possible but not recommended to run mysql_upgrade on a server where gtid_mode=ON, since the MySQL system tables use the MyISAM storage engine, which is nontransactional.

This option allows single statements updating nontransactional tables, which is intended chiefly for use with programs such as mysql_install_db and mysql_upgrade. (Bug #14722659)

gtid_executed

When used with global scope, this variable contains a representation of the set of all transactions that are logged in the binary log. This is the same as the value of the Executed_Gtid_Set column in the output of SHOW MASTER STATUS and SHOW SLAVE STATUS.

When used with session scope, this variable contains a representation of the set of transactions that are written to the cache in the current session.

The set of transactions that can be found in the binary logs at any given time is equal to GTID_SUBTRACT(@@GLOBAL.gtid_executed, @@GLOBAL.gtid_purged); that is, to all transactions in the binary log that have not yet been purged.

When the server starts, @@GLOBAL.gtid_executed is initialized to the union of the following two sets:

Thereafter, GTIDs are added to the set as transactions are executed.

Issuing RESET MASTER causes the global value (but not the session value) of this variable to be reset to an empty string. GTIDs are not otherwise removed from this set other than when the set is cleared due to RESET MASTER. The set is also cleared if the server is shut down and all binary logs are removed.

gtid_mode

This variable specifies whether global transaction identifiers (GTIDs) are used to identify transactions. This variable is read-only at runtime and must be set at server startup.

Starting the server with gtid_mode=ON requires that the server also be started with the --log-bin, --log-slave-updates, and --enforce-gtid-consistency options.

Setting this option to OFF when there are GTIDs in the binary log or in the relay log, or to ON when there remain anonymous transactions to be executed, causes an error.

The values of gtid_purged and gtid_executed are not persistent while gtid_mode=off. Therefore, after changing gtid_mode to OFF, once all binary logs containing GTIDs are purged, the values of these variables are lost.

gtid_next

This variable is used to specify whether and how the next GTID is obtained.

Setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.8.1, “System Variable Privileges”.

gtid_next can take any of the following values:

Setting this variable has no effect if gtid_mode is OFF.

Prior to MySQL 5.6.20, when GTIDs were enabled but gtid_next was not AUTOMATIC, DROP TABLE did not work correctly when used on a combination of nontemporary tables with temporary tables, or of temporary tables using transactional storage engines with temporary tables using nontransactional storage engines. In MySQL 5.6.20 and higher, DROP TABLE or DROP TEMPORARY TABLE fails with an explicit error when used with either of these combinations of tables. (Bug #17620053)

In MySQL 5.6.11 only, you cannot execute any of the statements CHANGE MASTER TO, START SLAVE, STOP SLAVE, REPAIR TABLE, OPTIMIZE TABLE, ANALYZE TABLE, CHECK TABLE, CREATE SERVER, ALTER SERVER, DROP SERVER, CACHE INDEX, LOAD INDEX INTO CACHE, FLUSH, or RESET when gtid_next is set to any value other than AUTOMATIC; in such cases, the statement fails with an error. Such statements are not disallowed in MySQL 5.6.12 and later. (Bug #16062608, Bug #16715809, Bug #69045)

gtid_owned

This read-only variable holds a list whose contents depend on its scope. When used with session scope, the list holds all GTIDs that are owned by this client; when used with global scope, it holds a list of all GTIDs along with their owners.

gtid_purged

The set of all transactions that have been purged from the binary log. This is a subset of the set of transactions in gtid_executed.

When the server starts, the global value of gtid_purged is initialized to the set of GTIDs contained by the Previous_gtid_log_event of the oldest binary log. When a binary log is purged, gtid_purged is re-read from the binary log that has now become the oldest one.

To update the value of this variable, gtid_mode must be ON, gtid_executed must be the empty string, and therefore gtid_purged is also the empty string. This can occur either when replication has not been started previously, or when replication was not previously using GTIDs.

After executing SET gtid_purged, you should note down the current binary log file name, which can be checked using SHOW MASTER STATUS. If the server is restarted before this file has been purged, then you should use binlog_gtid_simple_recovery=0 (the default in 5.6) to avoid gtid_purged or gtid_executed being computed incorrectly.

Issuing RESET MASTER causes the value of this variable to be reset to an empty string.

simplified_binlog_gtid_recovery

This option is deprecated; expect it to be removed in a future MySQL release. Use the binlog_gtid_simple_recovery variable to control how MySQL iterates through binary log files after a crash.