17.1.4.5 Global Transaction ID Options and Variables

• System Variables Used with GTID Replication

The MySQL Server system variables described in this section are used to monitor and control Global Transaction Identifiers (GTIDs). For additional information, see Section 17.1.3, “Replication with Global Transaction Identifiers”.

System Variables Used with GTID Replication

The following system variables are used with GTID-based replication:

• binlog_gtid_simple_recovery

  Command-Line Format	--binlog-gtid-simple-recovery[={OFF|ON}]
  Introduced	5.6.23
  System Variable	binlog_gtid_simple_recovery
  Scope	Global
  Dynamic	No
  Type	Boolean
  Default Value	OFF

  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.

  Note

  If this option is enabled, gtid_executed and gtid_purged may be initialized incorrectly in the following situations:

  • Some binary logs were generated when gtid_mode was ON, but gtid_mode was OFF for the newest binary log.

  • A SET GTID_PURGED statement was issued after the oldest existing binary log was generated.

  If an incorrect GTID set is computed in either situation, it remains incorrect even if the server is later restarted, regardless of the value of this option.

• enforce_gtid_consistency

  Command-Line Format	--enforce-gtid-consistency[=value]
  System Variable	enforce_gtid_consistency
  Scope	Global
  Dynamic	No
  Type	Boolean
  Default Value	OFF

  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:

  • CREATE TABLE ... SELECT statements

  • CREATE TEMPORARY TABLE statements inside transactions

  • Transactions or statements that update both transactional and nontransactional tables.

  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

  System Variable	gtid_executed
  Scope	Global, Session
  Dynamic	No
  Type	String
  Unit	set of GTIDs

  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:

  • The GTIDs listed in the Previous_gtids_log_event of the newest binary log

  • The GTIDs found in every Gtid_log_event in the newest binary log.

  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

  Command-Line Format	--gtid-mode=MODE
  System Variable	gtid_mode
  Scope	Global
  Dynamic	No
  Type	Enumeration
  Default Value	OFF
  Valid Values	OFF UPGRADE_STEP_1 UPGRADE_STEP_2 ON

  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.

  Important

  This option does not employ boolean values; its values are in fact enumerated. You should not attempt to use numeric values when setting this option, as these may lead to unexpected results. The values UPGRADE_STEP_1 and UPGRADE_STEP_2 are reserved for future use, but currently are not supported in production; if you set gtid_mode to either of these two values, the server refuses to start.

  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

  System Variable	gtid_next
  Scope	Session
  Dynamic	Yes
  Type	Enumeration
  Default Value	AUTOMATIC
  Valid Values	AUTOMATIC ANONYMOUS UUID:NUMBER

  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:

  • AUTOMATIC: Use the next automatically-generated global transaction ID.

  • ANONYMOUS: Transactions do not have global identifiers, and are identified by file and position only.

  • A global transaction ID in UUID:NUMBER format.

  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

  System Variable	gtid_owned
  Scope	Global, Session
  Dynamic	No
  Type	String
  Unit	set of GTIDs

  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

  System Variable	gtid_purged
  Scope	Global
  Dynamic	Yes
  Type	String
  Unit	set of GTIDs

  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

  Command-Line Format	--simplified-binlog-gtid-recovery[={OFF|ON}]
  Introduced	5.6.21
  Deprecated	5.6.23
  System Variable	simplified_binlog_gtid_recovery
  Scope	Global
  Dynamic	No
  Type	Boolean
  Default Value	OFF

  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.