Movatterモバイル変換

binlog_gtid_simple_recovery

This variable controls how binary log files are iterated during the search for GTIDs when MySQL starts or restarts.

Whenbinlog_gtid_simple_recovery=TRUE, which is the default, the values ofgtid_executed andgtid_purged are computed at startup based on the values ofPrevious_gtids_log_event in the most recent and oldest binary log files. For a description of the computation, seeThegtid_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 and you are using MySQL 5.7.8 or later,binlog_gtid_simple_recovery=TRUE can always safely be used.

Withbinlog_gtid_simple_recovery=TRUE,gtid_executed andgtid_purged might be initialized incorrectly in the following two situations:

If an incorrect GTID set is computed in either situation, it remains incorrect even if the server is later restarted withbinlog_gtid_simple_recovery=FALSE. If either of these situations applies on the server, setbinlog_gtid_simple_recovery=FALSE before starting or restarting the server. To check for the second situation, if you are using MySQL 5.7.7 or earlier, after issuing aSET @@GLOBAL.gtid_purged statement 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 setbinlog_gtid_simple_recovery=FALSE.

Whenbinlog_gtid_simple_recovery=FALSE is set, the method of computinggtid_executed andgtid_purged as described inThegtid_purged System Variable is changed to iterate the binary log files as follows:

In MySQL version 5.7.5, this variable was added assimplified_binlog_gtid_recovery and in MySQL version 5.7.6 it was renamed tobinlog_gtid_simple_recovery.

enforce_gtid_consistency

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. Youmust set this variable toON before enabling GTID based replication.

The values thatenforce_gtid_consistency can be configured to are:

Only statements that can be logged using GTID safe statements can be logged whenenforce_gtid_consistency is set toON, so the operations listed here cannot be used with this option:

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

For more information, seeSection 16.1.3.6, “Restrictions on Replication with GTIDs”.

Prior to MySQL 5.7.6, the booleanenforce_gtid_consistency defaulted toOFF. To maintain compatibility with previous releases, in MySQL 5.7.6 the enumeration defaults toOFF, and setting--enforce-gtid-consistency without a value is interpreted as setting the value toON. 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. UsingSELECT @@ENFORCE_GTID_CONSISTENCY,SHOW VARIABLES LIKE 'ENFORCE_GTID_CONSISTENCY', andSELECT * 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 forSHOW and the Information Schema.

gtid_executed

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 aSETgtid_purged statement. This is the same as the value of theExecuted_Gtid_Set column in the output ofSHOW MASTER STATUS andSHOW SLAVE STATUS. The value of this variable is a GTID set, seeGTID Sets for more information.

When the server starts,@@GLOBAL.gtid_executed is initialized. Seebinlog_gtid_simple_recovery for more information on how binary logs are iterated to populategtid_executed. GTIDs are then added to the set as transactions are executed, or if anySETgtid_purged statement is executed.

The set of transactions that can be found in the binary logs at any given time is equal toGTID_SUBTRACT(@@GLOBAL.gtid_executed, @@GLOBAL.gtid_purged); that is, to all transactions in the binary log that have not yet been purged.

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

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

Compress themysql.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 themysql.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 themysql.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.

Seemysql.gtid_executed Table Compression for more information.

This variable was added in MySQL version 5.7.5 asexecuted_gtids_compression_period and renamed in MySQL version 5.7.6 togtid_executed_compression_period.

gtid_mode

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-mode at server startup only. Prior to MySQL 5.7.5, starting the server with--gtid-mode=ON required that the server also be started with the--log-bin and--log-slave-updates options. As of MySQL 5.7.5, this is no longer a requirement. Seemysql.gtid_executed Table.

MySQL 5.7.6 enables this variable to be set dynamically. You must have privileges sufficient to set global system variables. SeeSection 5.1.8.1, “System Variable Privileges”.enforce_gtid_consistency must be set toON before you can setgtid_mode=ON. Before modifying this variable, seeSection 16.1.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. TheOFF_PERMISSIVE andON_PERMISSIVE modes added in MySQL 5.7.6 permit a mix of these transaction types in the topology. The different modes are now:

Changes from one value to another can only be one step at a time. For example, ifgtid_mode is currently set toOFF_PERMISSIVE, it is possible to change toOFF orON_PERMISSIVE but not toON.

In MySQL 5.7.6 and higher, the values ofgtid_purged andgtid_executed are persistent regardless of the value ofgtid_mode. Therefore even after changing the value ofgtid_mode, these variables contain the correct values. In MySQL 5.7.5 and earlier, the values ofgtid_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.

gtid_next

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. SeeSection 5.1.8.1, “System Variable Privileges”.

gtid_next can take any of the following values:

Exactly which of the above options are valid depends on the setting ofgtid_mode, seeSection 16.1.4.1, “Replication Mode Concepts” for more information. Setting this variable has no effect ifgtid_mode isOFF.

After this variable has been set toUUID:NUMBER, and a transaction has been committed or rolled back, an explicitSET GTID_NEXT statement must again be issued before any other statement.

In MySQL 5.7.5 and higher,DROP TABLE orDROP 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 butgtid_next was notAUTOMATIC,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 statementsCHANGE 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 arenot disallowed in MySQL 5.7.2 and later. (Bug #16062608, Bug #16715809, Bug #69045) (Bug #16062608)

gtid_owned

This read-only variable is primarily for internal use. Its contents depend on its scope.

gtid_purged

The global value of thegtid_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 ofgtid_executed. The following categories of GTIDs are ingtid_purged:

When the server starts or restarts, the global value ofgtid_purged is initialized to a set of GTIDs. For information on how this GTID set is computed, seeThegtid_purged System Variable. If binary logs from MySQL 5.7.7 or older are present on the server, you might need to setbinlog_gtid_simple_recovery=FALSE in the server's configuration file to produce the correct computation. See the description forbinlog_gtid_simple_recovery for details of the situations in which this setting is needed.

IssuingRESET MASTER causes the value ofgtid_purged to be reset to an empty string.

You can set the value ofgtid_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.

In MySQL 5.7, it is possible to update the value ofgtid_purged only whengtid_executed is the empty string, and thereforegtid_purged is 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_purged was also settable only whengtid_mode=ON. In MySQL 5.7.6 and higher,gtid_purged is settable regardless of the value ofgtid_mode.

To replace the value ofgtid_purged with your specified GTID set, use the following statement:

SET @@GLOBAL.gtid_purged = 'gtid_set'