The MySQL Server system variables described in this section are used to monitor and control Global Transaction Identifiers (GTIDs). For additional information, see Section 2.3, “Replication with Global Transaction Identifiers”.
Scope Global Dynamic No Type Boolean Default Value
This variable controls how binary log files are iterated during the search for GTIDs when MySQL starts or restarts.
binlog_gtid_simple_recovery=TRUE, which is the default, the values of
gtid_purgedare computed at startup based on the values of
Previous_gtids_log_eventin the most recent and oldest binary log files. For a description of the computation, see The
gtid_purgedSystem 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 and you are using MySQL 5.7.8 or later,
binlog_gtid_simple_recovery=TRUEcan always safely be used.
gtid_purgedmight be initialized incorrectly in the following two situations:
The newest binary log was generated by MySQL 5.7.5 or earlier, and
ONfor some binary logs but
OFFfor the newest binary log.
SET @@GLOBAL.gtid_purgedstatement was issued on MySQL 5.7.7 or earlier, and the binary log that was active at the time of the
SET @@GLOBAL.gtid_purgedstatement has not yet been purged.
If an incorrect GTID set is computed in either situation, it remains incorrect even if the server is later restarted with
binlog_gtid_simple_recovery=FALSE. If either of these situations applies on the server, set
binlog_gtid_simple_recovery=FALSEbefore starting or restarting the server. To check for the second situation, if you are using MySQL 5.7.7 or earlier, after issuing a
SET @@GLOBAL.gtid_purgedstatement 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 set
binlog_gtid_simple_recovery=FALSEis set, the method of computing
gtid_purgedas described in The
gtid_purgedSystem Variable is changed to iterate the binary log files as follows:
Instead of using the value of
Previous_gtids_log_eventand GTID log events from the newest binary log file, the computation for
gtid_executediterates from the newest binary log file, and uses the value of
Previous_gtids_log_eventand any GTID log events from the first binary log file where it finds a
Previous_gtids_log_eventvalue. If the server's most recent binary log files do not have GTID log events, for example if
gtid_mode=ONwas used but the server was later changed to
gtid_mode=OFF, this process can take a long time.
Instead of using the value of
Previous_gtids_log_eventfrom the oldest binary log file, the computation for
gtid_purgediterates from the oldest binary log file, and uses the value of
Previous_gtids_log_eventfrom the first binary log file where it finds either a nonempty
Previous_gtids_log_eventvalue, or at least one GTID log event (indicating that the use of GTIDs starts at that point). If the server's older binary log files do not have GTID log events, for example if
gtid_mode=ONwas only set recently on the server, this process can take a long time.
In MySQL version 5.7.5, this variable was added as
simplified_binlog_gtid_recoveryand in MySQL version 5.7.6 it was renamed to
Scope Global Dynamic Yes Type Enumeration Default Value
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
ONbefore enabling GTID based replication.
The values that
enforce_gtid_consistencycan be configured to are:
OFF: all transactions are allowed to violate GTID consistency.
ON: no transaction is allowed to violate GTID consistency.
WARN: all transactions are allowed to violate GTID consistency, but a warning is generated in this case.
WARNwas added in MySQL 5.7.6.
Only statements that can be logged using GTID safe statements can be logged when
enforce_gtid_consistencyis set to
ON, so the operations listed here cannot be used with this option:
CREATE TABLE ... SELECTstatements
CREATE TEMPORARY TABLEor
DROP TEMPORARY TABLEstatements inside transactions
Transactions or statements that update both transactional and nontransactional tables. There is an exception that nontransactional DML is allowed in the same transaction or in the same statement as transactional DML, if all nontransactional tables are temporary.
--enforce-gtid-consistencyonly 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 2.3.6, “Restrictions on Replication with GTIDs”.
Prior to MySQL 5.7.6, the boolean
OFF. To maintain compatibility with previous releases, in MySQL 5.7.6 the enumeration defaults to
OFF, and setting
--enforce-gtid-consistencywithout a value is interpreted as setting the value to
ON. The variable also has multiple textual aliases for the values:
2=WARN. This differs from other enumeration types but maintains compatibility with the boolean type used in previous versions. These changes impact on what is returned by the variable. Using
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_CONSISTENCYreturns the numeric form for booleans but returns the textual form for
SHOWand the Information Schema.
Scope Global Dynamic No Type String Unit set of GTIDs
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
gtid_purgedstatement. This is the same as the value of the
Executed_Gtid_Setcolumn in the output of
SHOW MASTER STATUSand
SHOW SLAVE STATUS. The value of this variable is a GTID set, see GTID Sets for more information.
When the server starts,
@@GLOBAL.gtid_executedis initialized. See
binlog_gtid_simple_recoveryfor 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
gtid_purgedstatement 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.
RESET MASTERcauses 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
Prior to MySQL 5.7.7, 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 was deprecated in MySQL 5.7.7.
Scope Global Dynamic Yes Type Integer Default Value
mysql.gtid_executedtable each time this many transactions have been processed. When binary logging is enabled on the server, this compression method is not used, and instead the
mysql.gtid_executedtable is compressed on each binary log rotation. When binary logging is disabled on the server, the compression thread sleeps until the specified number of transactions have been executed, then wakes up to perform compression of the
mysql.gtid_executedtable. Setting the value of this system variable to 0 means that the thread never wakes up, so this explicit compression method is not used. Instead, compression occurs implicitly as required.
See mysql.gtid_executed Table Compression for more information.
This variable was added in MySQL version 5.7.5 as
executed_gtids_compression_periodand renamed in MySQL version 5.7.6 to
Scope Global Dynamic Yes Type Enumeration Default Value
Controls whether GTID based logging is enabled and what type of transactions the logs can contain. Prior to MySQL 5.7.6, this variable was read-only and was set using
--gtid-modeat server startup only. Prior to MySQL 5.7.5, starting the server with
--gtid-mode=ONrequired that the server also be started with the
--log-slave-updatesoptions. As of MySQL 5.7.5, this is no longer a requirement. See mysql.gtid_executed Table.
MySQL 5.7.6 enables this variable to be set dynamically. You must have privileges sufficient to set global system variables. See System Variable Privileges.
enforce_gtid_consistencymust be set to
ONbefore you can set
gtid_mode=ON. Before modifying this variable, see Section 2.4, “Changing Replication Modes on Online Servers”.
Transactions logged in MySQL 5.7.6 and higher 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
ON_PERMISSIVEmodes added in MySQL 5.7.6 permit a mix of these transaction types in the topology. The different modes are now:
OFF: Both new and replicated transactions must be anonymous.
OFF_PERMISSIVE: New transactions are anonymous. Replicated transactions can be either anonymous or GTID transactions.
ON_PERMISSIVE: New transactions are GTID transactions. Replicated transactions can be either anonymous or GTID transactions.
ON: Both new and replicated transactions must be GTID transactions.
Changes from one value to another can only be one step at a time. For example, if
gtid_modeis currently set to
OFF_PERMISSIVE, it is possible to change to
ON_PERMISSIVEbut not to
In MySQL 5.7.6 and higher, the values of
gtid_executedare persistent regardless of the value of
gtid_mode. Therefore even after changing the value of
gtid_mode, these variables contain the correct values. In MySQL 5.7.5 and earlier, the values of
gtid_executedare not persistent while
gtid_mode=OFF. Therefore, after changing
OFF, once all binary logs containing GTIDs are purged, the values of these variables are lost.
Scope Session Dynamic Yes Type Enumeration Default Value
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 System Variable Privileges.
gtid_nextcan 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
Exactly which of the above options are valid depends on the setting of
gtid_mode, see Section 2.4.1, “Replication Mode Concepts” for more information. Setting this variable has no effect if
After this variable has been set to
NUMBER, and a transaction has been committed or rolled back, an explicit
SET GTID_NEXTstatement must again be issued before any other statement.
In MySQL 5.7.5 and higher,
DROP TEMPORARY TABLEfails 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. Prior to MySQL 5.7.5, when GTIDs were enabled but
DROP TABLEdid not work correctly when used with either of these combinations of tables. (Bug #17620053)
In MySQL 5.7.1, you cannot execute any of the statements
CHANGE MASTER TO,
LOAD INDEX INTO CACHE,
gtid_nextis 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)
Scope Global, Session Dynamic No Type String Unit set of GTIDs
This read-only variable is primarily for internal use. Its contents depend on its scope.
When used with global scope,
gtid_ownedholds a list of all the GTIDs that are currently in use on the server, with the IDs of the threads that own them. This variable is mainly useful for a multi-threaded replica to check whether a transaction is already being applied on another thread. An applier thread takes ownership of a transaction's GTID all the time it is processing the transaction, so
@@global.gtid_ownedshows the GTID and owner for the duration of processing. When a transaction has been committed (or rolled back), the applier thread releases ownership of the GTID.
When used with session scope,
gtid_ownedholds a single GTID that is currently in use by and owned by this session. This variable is mainly useful for testing and debugging the use of GTIDs when the client has explicitly assigned a GTID for the transaction by setting
gtid_next. In this case,
@@session.gtid_owneddisplays the GTID all the time the client is processing the transaction, until the transaction has been committed (or rolled back). When the client has finished processing the transaction, the variable is cleared. If
gtid_next=AUTOMATICis used for the session,
gtid_ownedis only populated briefly during the execution of the commit statement for the transaction, so it cannot be observed from the session concerned, although it is listed if
@@global.gtid_ownedis read at the right point. If you have a requirement to track the GTIDs that are handled by a client in a session, you can enable the session state tracker controlled by the
Scope Global Dynamic Yes Type String Unit set of GTIDs
The global value of the
gtid_purgedsystem 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_purgedis a subset of
gtid_executed. The following categories of GTIDs are in
GTIDs of replicated transactions that were committed with binary logging disabled on the replica.
GTIDs of transactions that were written to a binary log file that has now been purged.
GTIDs that were added explicitly to the set by the statement
When the server starts or restarts, the global value of
gtid_purgedis initialized to a set of GTIDs. For information on how this GTID set is computed, see The
gtid_purgedSystem 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=FALSEin the server's configuration file to produce the correct computation. See the description for
binlog_gtid_simple_recoveryfor details of the situations in which this setting is needed.
RESET MASTERcauses the value of
gtid_purgedto be reset to an empty string.
You can set the value of
gtid_purgedin 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.Important
GTIDs are only available on a server instance up to the number of non-negative values for a signed 64-bit integer (2 to the power of 63, minus 1). If you set the value of
gtid_purgedto a number that approaches this limit, subsequent commits can cause the server to run out of GTIDs and take the action specified by
In MySQL 5.7, it is possible to update the value of
gtid_executedis the empty string, and therefore
gtid_purgedis the empty string. This is the case either when replication has not been started previously, or when replication did not previously use GTIDs. Prior to MySQL 5.7.6,
gtid_purgedwas also settable only when
gtid_mode=ON. In MySQL 5.7.6 and higher,
gtid_purgedis settable regardless of the value of
To replace the value of
gtid_purgedwith your specified GTID set, use the following statement:
SET @@GLOBAL.gtid_purged = 'gtid_set'Note
If you are using MySQL 5.7.7 or earlier, after issuing a
SET @@GLOBAL.gtid_purgedstatement, you might need to set
binlog_gtid_simple_recovery=FALSEin the server's configuration file before restarting the server, otherwise
gtid_purgedcan be computed incorrectly. See the description for
binlog_gtid_simple_recoveryfor details of the situations in which this setting is needed. If all binary logs on the server were generated using MySQL 5.7.8 or later and you are using MySQL 5.7.8 or later,
binlog_gtid_simple_recovery=TRUE(which is the default setting from MySQL 5.7.7) can always safely be used.