Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.6Mb
PDF (A4) - 35.6Mb
PDF (RPM) - 34.7Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.5Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.4Mb
Eclipse Doc Plugin (Zip) - 11.5Mb
Man Pages (TGZ) - 203.5Kb
Man Pages (Zip) - 310.2Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Global Transaction ID Options and Variables

18.1.6.5 Global Transaction ID Options and Variables

Startup Options Used with GTID Replication

System Variables Used with GTID Replication

The MySQL Server options and system variables described in this section are used to monitor and control Global Transaction Identifiers (GTIDs).

For additional information, see Section 18.1.3, “Replication with Global Transaction Identifiers”.

Startup Options Used with GTID Replication

The following server startup options are used with GTID-based replication:

  • --enforce-gtid-consistency

    Command-Line Format--enforce-gtid-consistency[=value]
    System Variable (<= 5.7.5)Nameenforce_gtid_consistency
    Variable ScopeGlobal
    Dynamic VariableNo
    System Variable (>= 5.7.6)Nameenforce_gtid_consistency
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (<= 5.7.5)Typeboolean
    Defaultfalse
    Permitted Values (>= 5.7.6)Typeenumeration
    DefaultOFF
    Valid ValuesOFF
    ON
    WARN

    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:

    • 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. Added in MySQL 5.7.6.

    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:

    • CREATE TABLE ... SELECT statements

    • 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.

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

  • --executed-gtids-compression-period

    Introduced5.7.5
    Deprecated5.7.6
    Command-Line Format--executed-gtids-compression-period=#
    Permitted ValuesTypeinteger
    Default1000
    Min Value0
    Max Value4294967295

    This option is deprecated and will be removed in a future MySQL release. Use the renamed gtid_executed_compression_period to control how the gtid_executed table is compressed.

  • --gtid-mode

    Command-Line Format--gtid-mode=MODE
    System Variable (<= 5.7.5)Namegtid_mode
    Variable ScopeGlobal
    Dynamic VariableNo
    System Variable (>= 5.7.6)Namegtid_mode
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (<= 5.7.5)Typeenumeration
    DefaultOFF
    Valid ValuesOFF
    UPGRADE_STEP_1
    UPGRADE_STEP_2
    ON
    Permitted Values (>= 5.7.6)Typeenumeration
    DefaultOFF
    Valid ValuesOFF
    OFF_PERMISSIVE
    ON_PERMISSIVE
    ON

    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. Prior to MySQL 5.7.6 the gtid_mode variable which this option controls could only be set at server startup. In MySQL 5.7.6 and later the gtid_mode variable is dynamic and enables GTID based replication to be configured online. Before using this feature, see Section 18.1.5, “Changing Replication Modes on Online Servers”.

    Prior to MySQL 5.7.5, starting the server with --gtid-mode=ON required that the server also be started with the --log-bin, --log-slave-updates, options. In versions of MySQL 5.7.5 and later this is not a requirement. See mysql.gtid_executed Table.

  • --gtid-executed-compression-period

    Introduced5.7.6
    Command-Line Format--gtid-executed-compression-period=#
    Permitted ValuesTypeinteger
    Default1000
    Min Value0
    Max Value4294967295

    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.

    In MySQL version 5.7.5, this variable was added as executed_gtids_compression_period and in MySQL version 5.7.6 it was renamed to gtid_executed_compression_period.

System Variables Used with GTID Replication

The following system variables are used with GTID-based replication:

  • binlog_gtid_simple_recovery

    Introduced5.7.6
    Command-Line Format--binlog-gtid-simple-recovery
    System VariableNamebinlog_gtid_simple_recovery
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultFALSE
    Permitted Values (>= 5.7.7)Typeboolean
    DefaultTRUE

    This variable controls how binary log files are iterated during the search for GTIDs when MySQL starts or restarts. In MySQL version 5.7.5, this variable was added as simplified_binlog_gtid_recovery and in MySQL version 5.7.6 it was renamed to binlog_gtid_simple_recovery.

    When binlog_gtid_simple_recovery=FALSE, the method of iterating the binary log files is:

    • To initialize gtid_executed, binary log files are iterated from the newest file, stopping at the first binary log that has any Previous_gtids_log_event. All GTIDs from Previous_gtids_log_event and Gtid_log_events are read from this binary log file. This GTID set is stored internally and called gtids_in_binlog. The value of gtid_executed is computed as the union of this set and the GTIDs stored in the mysql.gtid_executed table.

      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.

    • To initialize gtid_purged, binary log files are iterated from the oldest to the newest, stopping at the first binary log that contains either a Previous_gtids_log_event that is non-empty (that has at least one GTID) or that has at least one Gtid_log_event. From this binary log it reads Previous_gtids_log_event. This GTID set is subtracted from gtids_in_binlog and the result stored in the internal variable gtids_in_binlog_not_purged. The value of gtid_purged is initialized to the value of gtid_executed, minus gtids_in_binlog_not_purged.

    When binlog_gtid_simple_recovery=TRUE, which is the default in MySQL 5.7.7 and later, the server iterates only the oldest and the newest binary log files and the values of gtid_purged and gtid_executed are computed based only on Previous_gtids_log_event or Gtid_log_event found in these files. This ensures only two binary log files are iterated during server restart or when binary logs are being purged.

    Note

    If this option is enabled, gtid_executed and gtid_purged may be initialized incorrectly in the following situations:

    • The newest binary log was generated by MySQL 5.7.5 or older, and gtid_mode was ON for some binary logs but OFF for the newest binary log.

    • A SET GTID_PURGED statement was issued on a MySQL version prior to 5.7.7, and the binary log that was active at the time of the SET GTID_PURGED has not yet been purged.

    If an incorrect GTID set is computed in either situation, it will remain incorrect even if the server is later restarted, regardless of the value of this option.

  • enforce_gtid_consistency

    Command-Line Format--enforce-gtid-consistency[=value]
    System Variable (<= 5.7.5)Nameenforce_gtid_consistency
    Variable ScopeGlobal
    Dynamic VariableNo
    System Variable (>= 5.7.6)Nameenforce_gtid_consistency
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (<= 5.7.5)Typeboolean
    Defaultfalse
    Permitted Values (>= 5.7.6)Typeenumeration
    DefaultOFF
    Valid ValuesOFF
    ON
    WARN

    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. Added in MySQL 5.7.6.

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

    Prior to MySQL 5.7.6, the boolean enforce-gtid-consistency defaulted to OFF. To maintain compatibility with previous versions, in MySQL 5.7.6 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 versions. 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.

  • executed_gtids_compression_period

    Introduced5.7.5
    Deprecated5.7.6
    System Variable (>= 5.7.5)Nameexecuted_gtids_compression_period
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default1000
    Min Value0
    Max Value4294967295

    This option is deprecated and will be removed in a future MySQL release. Use the renamed gtid_executed_compression_period to control how the gtid_executed table is compressed.

  • gtid_executed

    System VariableNamegtid_executed
    Variable ScopeGlobal, Session
    Dynamic VariableNo
    System Variable (>= 5.7.7)Namegtid_executed
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring

    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.

    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.

  • gtid_executed_compression_period

    Introduced5.7.6
    System Variable (>= 5.7.6)Namegtid_executed_compression_period
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default1000
    Min Value0
    Max Value4294967295

    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.

    In MySQL version 5.7.5, this variable was added as executed_gtids_compression_period and in MySQL version 5.7.6 it was renamed to gtid_executed_compression_period.

  • gtid_mode

    System Variable (<= 5.7.5)Namegtid_mode
    Variable ScopeGlobal
    Dynamic VariableNo
    System Variable (>= 5.7.6)Namegtid_mode
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (<= 5.7.5)Typeenumeration
    DefaultOFF
    Valid ValuesOFF
    UPGRADE_STEP_1
    UPGRADE_STEP_2
    ON
    Permitted Values (>= 5.7.6)Typeenumeration
    DefaultOFF
    Valid ValuesOFF
    OFF_PERMISSIVE
    ON_PERMISSIVE
    ON

    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 the --gtid-mode option only. MySQL 5.7.6 enables this variable to be set dynamically. You must have the SUPER privilege to set this variable. enforce_gtid_consistency must be true before you can set gtid_mode=ON. Before modifying this variable, see Section 18.1.5, “Changing Replication Modes on Online Servers”.

    Transactions logged in MySQL 5.7.6 and later 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 OFF_PERMISSIVE and ON_PERMISSIVE modes 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_mode is currently set to OFF_PERMISSIVE, it is possible to change to OFF or ON_PERMISSIVE but not to ON.

    In MySQL 5.7.6 and later, 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. In MySQL 5.7.5 and earlier, 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.

  • gtid_next

    System VariableNamegtid_next
    Variable ScopeSession
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultAUTOMATIC
    Valid ValuesAUTOMATIC
    ANONYMOUS
    UUID:NUMBER

    This variable is used to specify whether and how the next GTID is obtained. 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 18.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.

    In MySQL 5.7.5 and later, 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. Prior to MySQL 5.7.5, when GTIDs were enabled but gtid_next was not AUTOMATIC, DROP TABLE did 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, 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.7.2 and later. (Bug #16062608, Bug #16715809, Bug #69045) (Bug #16062608)

  • gtid_owned

    System VariableNamegtid_owned
    Variable ScopeGlobal, Session
    Dynamic VariableNo
    Permitted ValuesTypestring

    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.

  • gtid_purged

    System VariableNamegtid_purged
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypestring

    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. The value of this variable is a GTID set, see GTID Sets for more information.

    When the server starts, the global value of gtid_purged is initialized to a set of GTIDs. See binlog_gtid_simple_recovery for more information on how binary logs are iterated to populate gtid_purged. Issuing RESET MASTER causes the value of this variable to be reset to an empty string.

    It is possible to update the value of this variable, but only when gtid_executed is the empty string, and therefore gtid_purged is the empty string. This can occur either when replication has not been started previously, or when replication was not previously using GTIDs. Prior to MySQL 5.7.6, this variable was settable only when gtid_mode=ON. In MySQL 5.7.6 and later, this variable is settable regardless of the value of gtid_mode.

    If all existing binary logs were generated using MySQL 5.7.6 or later, after issuing a SET gtid_purged statement, binlog_gtid_simple_recovery=TRUE (the default setting in MySQL 5.7.7 and later) can safely be used. If binary logs from MySQL 5.7.7 or earlier exist, there is a chance that gtid_purged may be computed incorrectly. See binlog_gtid_simple_recovery for more information. If you are using MySQL 5.7.7 or earlier, after issuing a SET gtid_purged statement 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=FALSE to avoid gtid_purged or gtid_executed being computed incorrectly.

  • simplified_binlog_gtid_recovery

    Introduced5.7.5
    Deprecated5.7.6
    Command-Line Format--simplified-binlog-gtid-recovery
    System VariableNamesimplified_binlog_gtid_recovery
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultFALSE

    This option is deprecated and will be removed in a future MySQL release. Use the renamed binlog_gtid_simple_recovery to control how MySQL iterates through binary log files after a crash.


User Comments
Sign Up Login You must be logged in to post a comment.