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”.
The following system variables are used with GTID-based replication:
-
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 tobinlog_gtid_simple_recovery
.When
binlog_gtid_simple_recovery=FALSE
, the iteration starts from the newest file to initializegtid_executed
, and starts from the oldest file to initializegtid_purged
. This process could take a long time if you had a large number of binary log files without GTID events, for example created whengtid_mode=OFF
.When
binlog_gtid_simple_recovery=TRUE
, the server does not open more than two binary logs when iterating to populategtid_purged
andgtid_executed
, either during server restart or when binary logs are being purged.NoteIf this option is enabled,
gtid_executed
andgtid_purged
may be initialized incorrectly in the following situations: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.
-
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 withgtid_mode=ON
; otherwise, enabling GTID mode fails with an error. You can (and should) enable GTID consistency prior to usinggtid_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
statementsCREATE TEMPORARY TABLE
statements inside transactionsTransactions 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 theMyISAM
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)
-
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 ofSHOW MASTER STATUS
andSHOW 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 logThe 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 toRESET MASTER
. The set is also cleared if the server is shut down and all binary logs are removed. -
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 toON
when there remain anonymous transactions to be executed, causes an error.ImportantThis 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
andUPGRADE_STEP_2
are reserved for future use, but currently are not supported in production; if you setgtid_mode
to either of these two values, the server refuses to start.The values of
gtid_purged
andgtid_executed
are not persistent whilegtid_mode=off
. Therefore, after changinggtid_mode
toOFF
, once all binary logs containing GTIDs are purged, the values of these variables are lost. -
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
isOFF
.Prior to MySQL 5.6.20, when GTIDs were enabled but
gtid_next
was notAUTOMATIC
,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
orDROP 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
, orRESET
whengtid_next
is set to any value other thanAUTOMATIC
; 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) -
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.
-
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 thePrevious_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 beON
,gtid_executed
must be the empty string, and thereforegtid_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 usingSHOW MASTER STATUS
. If the server is restarted before this file has been purged, then you should usebinlog_gtid_simple_recovery=0
(the default in 5.6) to avoidgtid_purged
orgtid_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.