17.1.6.5 Global Transaction ID Options and Variables

--enforce-gtid-consistency

When enabled, the server enforces GTID consistency by allowing execution of only statements that can be safely logged using a GTID. You must set this option to ON before enabling GTID based replication.

The values that --enforce-gtid-consistency can be configured to are:

Setting --enforce-gtid-consistency without a value is an alias for --enforce-gtid-consistency=ON. This impacts on the behavior of the variable, see enforce_gtid_consistency.

Only statements that can be logged using GTID safe statements can be logged when enforce-gtid-consistency is set to ON, so the operations listed here cannot be used with this option:

--enforce-gtid-consistency only takes effect if binary logging takes place for a statement. If binary logging is disabled on the server, or if statements are not written to the binary log because they are removed by a filter, GTID consistency is not checked or enforced for the statements that are not logged.

For more information, see Section 17.1.3.6, “Restrictions on Replication with GTIDs”.

--gtid-mode

This option specifies whether global transaction identifiers (GTIDs) are used to identify transactions. Setting this option to --gtid-mode=ON requires that enforce-gtid-consistency be set to ON. The gtid_mode variable is dynamic and enables GTID based replication to be configured online. Before using this feature, see Section 17.1.5, “Changing Replication Modes on Online Servers”.

--gtid-executed-compression-period

Compress the mysql.gtid_executed table each time this many transactions have taken place. A setting of 0 means that this table is not compressed. No compression of the table occurs when binary logging is enabled, therefore the option has no effect unless log_bin is OFF.

See mysql.gtid_executed Table Compression, for more information.

binlog_gtid_simple_recovery

This variable controls how binary log files are iterated during the search for GTIDs when MySQL starts or restarts.

When binlog_gtid_simple_recovery=TRUE, which is the default in MySQL 8.0, the values of gtid_executed and gtid_purged are computed at startup based on the values of Previous_gtids_log_event in the most recent and oldest binary log files. For a description of the computation, see The gtid_purged System Variable. This setting accesses only two binary log files during server restart. If all binary logs on the server were generated using MySQL 5.7.8 or later, binlog_gtid_simple_recovery=TRUE can always safely be used.

If any binary logs from MySQL 5.7.7 or older are present on the server (for example, following an upgrade of an older server to MySQL 8.0), with binlog_gtid_simple_recovery=TRUE, gtid_executed and gtid_purged might be initialized incorrectly in the following two situations:

If an incorrect GTID set is computed in either situation, it will remain incorrect even if the server is later restarted with binlog_gtid_simple_recovery=FALSE. If either of these situations apply or might apply on the server, set binlog_gtid_simple_recovery=FALSE before starting or restarting the server.

When binlog_gtid_simple_recovery=FALSE is set, the method of computing gtid_executed and gtid_purged as described in The gtid_purged System Variable is changed to iterate the binary log files as follows:

enforce_gtid_consistency

Depending on the value of this variable, the server enforces GTID consistency by allowing execution of only statements that can be safely logged using a GTID. You must set this variable to ON before enabling GTID based replication.

The values that enforce_gtid_consistency can be configured to are:

enforce_gtid_consistency only takes effect if binary logging takes place for a statement. If binary logging is disabled on the server, or if statements are not written to the binary log because they are removed by a filter, GTID consistency is not checked or enforced for the statements that are not logged.

For more information on statements that can be logged using GTID based replication, see --enforce-gtid-consistency.

Prior to MySQL 5.7 and in early releases in that release series, the boolean enforce_gtid_consistency defaulted to OFF. To maintain compatibility with these earlier releases, the enumeration defaults to OFF, and setting --enforce-gtid-consistency without a value is interpreted as setting the value to ON. The variable also has multiple textual aliases for the values: 0=OFF=FALSE, 1=ON=TRUE,2=WARN. This differs from other enumeration types but maintains compatibility with the boolean type used in previous releases. These changes impact on what is returned by the variable. Using SELECT @@ENFORCE_GTID_CONSISTENCY, SHOW VARIABLES LIKE 'ENFORCE_GTID_CONSISTENCY', and SELECT * FROM INFORMATION_SCHEMA.VARIABLES WHERE 'VARIABLE_NAME' = 'ENFORCE_GTID_CONSISTENCY', all return the textual form, not the numeric form. This is an incompatible change, since @@ENFORCE_GTID_CONSISTENCY returns the numeric form for booleans but returns the textual form for SHOW and the Information Schema.

gtid_executed

When used with global scope, this variable contains a representation of the set of all transactions executed on the server and GTIDs that have been set by a SET gtid_purged statement. This is the same as the value of the Executed_Gtid_Set column in the output of SHOW MASTER STATUS and SHOW SLAVE STATUS. The value of this variable is a GTID set, see GTID Sets for more information.

When the server starts, @@GLOBAL.gtid_executed is initialized. See binlog_gtid_simple_recovery for more information on how binary logs are iterated to populate gtid_executed. GTIDs are then added to the set as transactions are executed, or if any SET gtid_purged statement is executed.

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.

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.

In some older releases, this variable could also be used with session scope, where it contained a representation of the set of transactions that are written to the cache in the current session. The session scope is now deprecated.

gtid_executed_compression_period

Compress the mysql.gtid_executed table each time this many transactions have been processed. A setting of 0 means that this table is not compressed. Since no compression of the table occurs when using the binary log, setting the value of the variable has no effect unless binary logging is disabled.

See mysql.gtid_executed Table Compression, for more information.

gtid_mode

Controls whether GTID based logging is enabled and what type of transactions the logs can contain. You must have privileges sufficient to set global system variables. See Section 5.1.9.1, “System Variable Privileges”. enforce_gtid_consistency must be true before you can set gtid_mode=ON. Before modifying this variable, see Section 17.1.5, “Changing Replication Modes on Online Servers”.

Logged transactions can be either anonymous or use GTIDs. Anonymous transactions rely on binary log file and position to identify specific transactions. GTID transactions have a unique identifier that is used to refer to transactions. The different modes are:

Changes from one value to another can only be one step at a time. For example, if gtid_mode is currently set to OFF_PERMISSIVE, it is possible to change to OFF or ON_PERMISSIVE but not to ON.

The values of gtid_purged and gtid_executed are persistent regardless of the value of gtid_mode. Therefore even after changing the value of gtid_mode, these variables contain the correct values.

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.9.1, “System Variable Privileges”.

gtid_next can take any of the following values:

Exactly which of the above options are valid depends on the setting of gtid_mode, see Section 17.1.5.1, “Replication Mode Concepts” for more information. Setting this variable has no effect if gtid_mode is OFF.

After this variable has been set to UUID:NUMBER, and a transaction has been committed or rolled back, an explicit SET GTID_NEXT statement must again be issued before any other statement.

DROP TABLE or DROP TEMPORARY TABLE fails with an explicit error 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.

gtid_owned

This read-only variable is primarily for internal use. Its contents depend on its scope.

gtid_purged

The global value of the gtid_purged system variable (@@GLOBAL.gtid_purged) is a GTID set consisting of the GTIDs of all the transactions that have been committed on the server, but do not exist in any binary log file on the server. gtid_purged is a subset of gtid_executed. The following categories of GTIDs are in gtid_purged:

When the server starts, the global value of gtid_purged is initialized to a set of GTIDs. For information on how this GTID set is computed, see The gtid_purged System Variable. If binary logs from MySQL 5.7.7 or older are present on the server, you might need to set binlog_gtid_simple_recovery=FALSE in the server's configuration file to produce the correct computation. See the description for binlog_gtid_simple_recovery for details of the situations in which this setting is needed.

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

You can set the value of gtid_purged in order to record on the server that the transactions in a certain GTID set have been applied, although they do not exist in any binary log on the server. An example use case for this action is when you are restoring a backup of one or more databases on a server, but you do not have the relevant binary logs containing the transactions on the server.

From MySQL 8.0, there are two ways to set the value of gtid_purged. You can either replace the value of gtid_purged with your specified GTID set, or you can append your specified GTID set to the GTID set that is already held by gtid_purged. If the server has no existing GTIDs, for example an empty server that you are provisioning with a backup of an existing database, both methods have the same result. If you are restoring a backup that overlaps the transactions that are already on the server, for example replacing a corrupted table with a partial dump from the master made using mysqldump (which includes the GTIDs of all the transactions on the server, even though the dump is partial), use the first method of replacing the value of gtid_purged. If you are restoring a backup that is disjoint from the transactions that are already on the server, for example provisioning a multi-source replication slave using dumps from two different servers, use the second method of adding to the value of gtid_purged.