16.1.4.5. Global Transaction ID Options and Variables

The MySQL Server options and system variables described in this section are used to monitor and control Global Transaction Identifiers (GTIDs).

For additional information, see Section 16.1.3, "Replication with Global Transaction Identifiers".

Startup options used in GTID replication. The followup server startup options are used with GTID-based replication:

--enforce-gtid-consistency

Command-Line Format	`--enforce-gtid-consistency[=value]`
Option-File Format	`enforce-gtid-consistency`
System Variable Name	`enforce_gtid_consistency`
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
	Type	`boolean`
	Default	`false`

When set, this option allows execution of only those statements that can be logged in a transactionally safe manner. This means that the following operations cannot be used when this option is enabled:

CREATE TABLE ... SELECT statements
CREATE TEMPORARY TABLE statements inside transactions
Transactions or statements that update both transactional and nontransactional tables.

Nontransactional DML statements are allowed on temporary tables with --enforce-gtid-consistency as long as all affected tables are temporary tables.

This option is intended chiefly for use with programs such as mysql_install_db and mysql_upgrade.

--gtid-mode

Command-Line Format	`--gtid-mode=MODE`
Option-File Format	`gtid-mode`
System Variable Name	`gtid_mode`
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
	Type	`enumeration`
	Default	`OFF`
	Valid Values	`OFF`
		`UPGRADE_STEP_1`
		`UPGRADE_STEP_2`
		`ON`

This option specifies whether GTIDs are enabled. Starting the server with --gtid-mode=ON requires that the server also be started with the --log-bin and --log-slave-updates options as well. (In addition, you should also use --enforce-gtid-consistency.)

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 use one of these two values with --gtid-mode, the server refuses to start.

It is possible but not recommended to run mysql_upgrade on a server where --gtid-mode=ON, since it may make changes to MySQL system tables that use the MyISAM storage engine, which is nontransactional.

Prior to MySQL 5.7.1, setting the global value for the sql_slave_skip_counter variable to 1 had no effect --gtid-mode was set to ON.. (Bug #15833516) A workaround in in previous versions is to reset the slave's position using CHANGE MASTER TO ... MASTER_LOG_FILE = ... MASTER_LOG_POS = ..., including the MASTER_AUTO_POSITION = 0 option with this statement if needed.

System variables used on replication masters. The following system variables are used with GTID-based replication:

enforce_gtid_consistency

Command-Line Format	`--enforce-gtid-consistency[=value]`
Option-File Format	`enforce_gtid_consistency`
System Variable Name	`enforce_gtid_consistency`
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
	Type	`boolean`
	Default	`false`

When this variable is true, execution is allowed of only those statements that can be logged in a transactionally safe manner, which means that the following operations cannot be used:

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. To set it, use the --enforce-gtid-consistency option on the command line or in an option file when starting the MySQL Server.

gtid_executed

System Variable Name	`gtid_executed`
Variable Scope	Global, Session
Dynamic Variable	No
	Permitted Values
	Type	`string`

When used with global scope, this variable contains a representation of the set of all transactions that are logged in the binary log. When used with session scope, it contains a representation of the set of transactions that are written to the cache in the current session.

Issuing RESET MASTER causes the global value (but not the session value) of this variable to be reset to an empty string.

gtid_mode

System Variable Name	`gtid_mode`
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
	Type	`enumeration`
	Default	`OFF`
	Valid Values	`OFF`
		`UPGRADE_STEP_1`
		`UPGRADE_STEP_2`
		`ON`

Shows whether GTIDs are enabled. Read-only; set using --gtid-mode.

gtid_next

System Variable Name	`gtid_next`
Variable Scope	Session
Dynamic Variable	Yes
	Permitted Values
	Type	`enumeration`
	Default	`AUTOMATIC`
	Valid Values	`AUTOMATIC`
		`ANONYMOUS`
		`UUID:NUMBER`

This variable is used to specify whether and how the next GTID is obtained. 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.

You must have the SUPER privilege to set this variable. Setting this variable has no effect if gtid_mode is OFF.

In MySQL 5.7.1, 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.7.2 and later. (Bug #16062608, Bug #16715809, Bug #69045) (Bug #16062608)

gtid_owned

System Variable Name	`gtid_owned`
Variable Scope	Global, Session
Dynamic Variable	No
	Permitted Values
	Type	`string`

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 Name	`gtid_purged`
Variable Scope	Global
Dynamic Variable	Yes
	Permitted Values
	Type	`string`

The set of all transactions that have been purged from the binary log.

It is possible to update the value of this variable, but only by adding GTIDs to those already listed, and only when gtid_executed is unset—that is, on a new server.

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