MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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 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.
If this option is enabled,
gtid_executed
and
gtid_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 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)
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.
Command-Line Format | --gtid-mode=MODE |
---|---|
System Variable | gtid_mode |
Scope | Global |
Dynamic | No |
Type | Enumeration |
Default Value | OFF |
Valid Values |
|
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.
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.
System Variable | gtid_next |
---|---|
Scope | Session |
Dynamic | Yes |
Type | Enumeration |
Default Value | AUTOMATIC |
Valid Values |
|
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)
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 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.