MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
The MySQL Server system variables described in this section are used to monitor and control Global Transaction Identifiers (GTIDs). For additional information, see Section 19.1.3, “Replication with Global Transaction Identifiers”.
Command-Line Format | --binlog-gtid-simple-recovery[={OFF|ON}] |
---|---|
System Variable | binlog_gtid_simple_recovery |
Scope | Global |
Dynamic | No |
SET_VAR Hint Applies |
No |
Type | Boolean |
Default Value | ON |
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:
The newest binary log was generated by MySQL 5.7.5 or
earlier, and gtid_mode
was ON
for some binary logs but
OFF
for the newest binary log.
A SET @@GLOBAL.gtid_purged
statement
was issued on MySQL 5.7.7 or earlier, and the binary log
that was active at the time of the SET
@@GLOBAL.gtid_purged
statement 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 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:
Instead of using the value of
Previous_gtids_log_event
and GTID log
events from the newest binary log file, the computation
for gtid_executed
iterates from the newest binary log file, and uses the
value of Previous_gtids_log_event
and
any GTID log events from the first binary log file where
it finds a Previous_gtids_log_event
value. If the server's most recent binary log files do not
have GTID log events, for example if
gtid_mode=ON
was 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_event
from the
oldest binary log file, the computation for
gtid_purged
iterates from
the oldest binary log file, and uses the value of
Previous_gtids_log_event
from the first
binary log file where it finds either a nonempty
Previous_gtids_log_event
value, 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=ON
was only set
recently on the server, this process can take a long time.
Command-Line Format | --enforce-gtid-consistency[=value] |
---|---|
System Variable | enforce_gtid_consistency |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Enumeration |
Default Value | OFF |
Valid Values |
|
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:
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.
--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.
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:
CREATE
TEMPORARY TABLE
or
DROP TEMPORARY
TABLE
statements 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.
CREATE
TABLE ... SELECT
statements, prior to MySQL
8.0.21. From MySQL 8.0.21,
CREATE
TABLE ... SELECT
statements are allowed for
storage engines that support atomic DDL.
For more information, see Section 19.1.3.7, “Restrictions on Replication with GTIDs”.
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.
System Variable | gtid_executed |
---|---|
Scope | Global |
Dynamic | No |
SET_VAR Hint Applies |
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
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 REPLICA 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
.
gtid_executed_compression_period
Command-Line Format | --gtid-executed-compression-period=# |
---|---|
System Variable | gtid_executed_compression_period |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value (≥ 8.0.23) | 0 |
Default Value (≤ 8.0.22) | 1000 |
Minimum Value | 0 |
Maximum Value | 4294967295 |
Compress the mysql.gtid_executed
table 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_executed
table 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_executed
table. 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.
From MySQL 8.0.17, InnoDB
transactions are
written to the mysql.gtid_executed
table by
a separate process to non-InnoDB
transactions. If the server has a mix of
InnoDB
transactions and
non-InnoDB
transactions, the compression
controlled by this system variable interferes with the work of
this process and can slow it significantly. For this reason,
from that release it is recommended that you set
gtid_executed_compression_period
to 0.
From MySQL 8.0.23, InnoDB
and
non-InnoDB
transactions are written to the
mysql.gtid_executed
table by the same
process, and the
gtid_executed_compression_period
default value is 0.
See mysql.gtid_executed Table Compression for more information.
Command-Line Format | --gtid-mode=MODE |
---|---|
System Variable | gtid_mode |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Enumeration |
Default Value | OFF |
Valid Values |
|
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 7.1.9.1, “System Variable Privileges”.
enforce_gtid_consistency
must
be set to ON
before you can set
gtid_mode=ON
. Before
modifying this variable, see
Section 19.1.4, “Changing GTID Mode 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:
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_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.
System Variable | gtid_next |
---|---|
Scope | Session |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
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 either the
REPLICATION_APPLIER
privilege
(see Section 19.3.3, “Replication Privilege Checks”), or
privileges sufficient to set restricted session variables (see
Section 7.1.9.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.
Exactly which of the above options are valid depends on the
setting of gtid_mode
, see
Section 19.1.4.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.
System Variable | gtid_owned |
---|---|
Scope | Global, Session |
Dynamic | No |
SET_VAR Hint Applies |
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_owned
holds 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_owned
shows 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_owned
holds 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_owned
displays 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=AUTOMATIC
is
used for the session,
gtid_owned
is populated
only 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_owned
is 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
session_track_gtids
system variable.
System Variable | gtid_purged |
---|---|
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | String |
Unit | set of GTIDs |
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
:
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 SET @@GLOBAL.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.
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_purged
to a number
that approaches this limit, subsequent commits can cause the
server to run out of GTIDs and take the action specified by
binlog_error_action
. From
MySQL 8.0.23, a warning message is issued when the server
instance is approaching the limit.
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 source 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 replica using dumps from two
different servers, use the second method of adding to the
value of gtid_purged
.
To replace the value of
gtid_purged
with your
specified GTID set, use the following statement:
SET @@GLOBAL.gtid_purged = 'gtid_set'
gtid_set
must be a superset of the
current value of
gtid_purged
, and must not
intersect with
gtid_subtract(gtid_executed,gtid_purged)
.
In other words, the new GTID set
must include any GTIDs
that were already in
gtid_purged
, and
must not include any
GTIDs in gtid_executed
that have not yet been purged. gtid_set
also cannot include any GTIDs that are in
@@global.gtid_owned
, that is, the GTIDs
for transactions that are currently being processed on the
server.
The result is that the global value of
gtid_purged
is set equal
to gtid_set
, and the value of
gtid_executed
becomes the
union of gtid_set
and the previous
value of gtid_executed
.
To append your specified GTID set to
gtid_purged
, use the
following statement with a plus sign (+) before the GTID
set:
SET @@GLOBAL.gtid_purged = '+gtid_set'
gtid_set
must
not intersect with the current value of
gtid_executed
. In other
words, the new GTID set must not include any GTIDs in
gtid_executed
, including
transactions that are already also in
gtid_purged
.
gtid_set
also cannot include any GTIDs
that are in @@global.gtid_owned
, that
is, the GTIDs for transactions that are currently being
processed on the server.
The result is that gtid_set
is added to
both gtid_executed
and
gtid_purged
.
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), after issuing a SET
@@GLOBAL.gtid_purged
statement, you might need to set
binlog_gtid_simple_recovery=FALSE
in the server's configuration file before restarting the server,
otherwise gtid_purged
can be
computed incorrectly. See the description for
binlog_gtid_simple_recovery
for
details of the situations in which this setting is needed.