Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to enable or disable GTID mode in MySQL". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to enable or disable GTID mode in MySQL".

Basic overview

We know that MySQL has two ways to specify replication synchronization, which are:

How to specify based on binlog file name and site

-Anonymous transaction (Anonymous_gtid_log_event)

Designation method based on GTID (Global transaction ID)

-GTID transaction (Gtid_log_event)

On the one hand, the GTID-based mode has obvious advantages in master-slave switching under the one-master and multi-slave architecture, but it is also more convenient for fault diagnosis of daily replication anomalies. Since MySQL 5.7.6, the GTID mode has been dynamically turned on and off. The parameter GTID_MODE has the following values.

OFF-only anonymous transactions are allowed to be replicated synchronized

OFF_PERMISSIVE-newly generated transactions are anonymous, but GTID transactions are also allowed to be replicated and synchronized

ON_PERMISSIVE-all new GTID transactions are generated, but anonymous transactions are also allowed to be replicated and synchronized

ON-only GTID transactions are allowed to be replicated and synchronized

Open GTID1 online. Set GTID check ENFORCE_GTID_CONSISTENCY to WARN

The purpose of this operation is to allow the SQL statement executed in the main library to violate the GTID consistency check, and only output the warning level log in the error log of the main library as a reminder, in order to consider that if the replication mode is changed to GTID mode, the application will not cause an exception error due to some restrictions of GTID

For example, using CREATE TABLE AS SELECT syntax is not supported in GTID mode, but when ENFORCE_GTID_CONSISTENCY is set to WARN, it will only be prompted in the error log and will not report an error directly.

# # this operation executes SET @ @ GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;2 in both master and slave libraries. Set GTID check ENFORCE_GTID_CONSISTENCY to ON

Observe for a period of time (in rigorous cases, you may observe for more than one day). After confirming that the relevant Warning information does not appear in the error log in the previous step, officially enable GTID consistency check. When set to ON, if you execute the CREATE TABLE AS SELECT statement, the error will be reported directly.

# # this operation executes SET @ @ GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;3 in both master and slave libraries. Set GTID_MODE to OFF_PERMISSIVE

This operation is a transitional property, indicating that the newly generated transaction is still an anonymous transaction, but also allows GTID transactions to be replicated and synchronized

# # this operation executes SET @ @ GLOBAL.GTID_MODE = OFF_PERMISSIVE;4 in both master and slave libraries. Set GTID_MODE to ON_PERMISSIVE

This operation is still a transitional property, and it indicates that all new GTID transactions are generated, but anonymous transactions are also allowed to be replicated.

# # this operation executes SET @ @ GLOBAL.GTID_MODE = ON_PERMISSIVE;5 in both master and slave libraries. (key point) ensure that anonymous transaction playback is complete

The purpose of this step is to ensure that the old anonymous transactions are played back before the formal conversion to the full GTID mode, and that when GTID_MODE is set to ON, replication synchronization errors will not be reported because of the remaining anonymous transactions. There are two ways to verify.

# # this operation can only be performed from the library # # method 1: make sure that the number of anonymous transactions output from this status value is displayed as 0 (Note: as long as the value exceeds 0, the transition has been completed, even if the status value changes from 0 to a value greater than 0) SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT' # # execute this statement multiple times on the slave library # # method 2: query LAST_SEEN_TRANSACTION in this view to see whether there is still an ANONYMOUS transaction select * from performance_schema.replication_applier_status_by_worker in the currently synchronized transaction

Ensure that the number of anonymous transactions is 0

Ensure that the transactions played back by the playback thread are already GTID transactions

6. Trigger a round of log switching FLUSH LOGS

The purpose of this operation is to trigger the rotation of the binlog in the main library so that the newly generated binlog is a transaction containing GTID (to prevent two types of transaction logs in a binlog)

# # this operation can only be performed in the main database for FLUSH LOGS;7. Officially enable GTID_MODE to ON

Officially open GTID

# # this operation executes SET @ @ GLOBAL.GTID_MODE = ON;SELECT @ @ GTID_MODE,@@ENFORCE_GTID_CONSISTENCY;8 in both master and slave libraries Modify the configuration file to ensure that GTID parameters are persisted

Add the GTID parameter to the my.cnf configuration file to ensure that the restart will not fail, which can also be done in the first step

# # this operation executes gtid-mode = ONenforce-gtid-consistency = 19 in both master and slave libraries. Change the replication mode to GTID mode

Change replication mode from POS-based to GTID-based

# # stop copying STOP SLAVE;## and change it to GTID mode CHANGE MASTER TO MASTER_AUTO_POSITION = 1 alternate replication # enable replication START SLAVE;## observation replication synchronization status SHOW SLAVE STATUS\ G disable GTID online

The way is similar to the reverse operation of opening GTID online. Only the steps and specific commands are written below, without detailed explanation.

First change the replication of GTID mode to the replication based on POS points

Set GTID_MODE to ON_PERMISSIVE

Set GTID_MODE to OFF_PERMISSIVE

Observe that the GTID_OWNED state variable becomes null and the transaction in the replication_applier_status_by_ worker table becomes anonymous.

Trigger FLUSH LOGS

Set GTID_MODE to OFF

Set ENFORCE_GTID_CONSISTENCY to OFF

Modify the parameter related to GTID in my.cnf configuration file to OFF

1. Change the replication to be based on POS point mode stop slave;show slave status\ Exec_Master_Log_Pos # take the Master_Log_File and Exec_Master_Log_Pos in show slave status\ G and fill in CHANGE MASTER TO MASTER_AUTO_POSITION = 0, MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=224126137;start slave;show slave status\ G2. Set GTID_MODE to ON_PERMISSIVE##. This operation executes SET @ @ GLOBAL.GTID_MODE = ON_PERMISSIVE;3 in both master and slave libraries. Set GTID_MODE to OFF_PERMISSIVE##. This operation executes SET @ @ GLOBAL.GTID_MODE = OFF_PERMISSIVE;4 in both master and slave libraries. (key points) ensure that the playback of GTID transactions is complete

Observe that the GTID_OWNED state variable becomes null and the transaction in the replication_applier_status_by_ worker table becomes anonymous.

# # this operation can be performed by SELECT @ @ GLOBAL.GTID_OWNED;select * from performance_schema.replication_applier_status_by_worker;5. Trigger FLUSH LOGS## and the operation can be executed in the main library to FLUSH LOGS;6. Set GTID_MODE to OFF##. This operation executes SET @ @ GLOBAL.GTID_MODE = OFF;7 in both master and slave libraries. Set ENFORCE_GTID_CONSISTENCY to OFF##. This operation executes SET @ @ GLOBAL.ENFORCE_GTID_CONSISTENCY = OFF;8 in both master and slave libraries. Modify the GTID-related parameter in the my.cnf configuration file to OFF##. This operation executes the command gtid-mode = OFFenforce-gtid-consistency = 1 in both master and slave libraries. Open GTID online

Determine whether the command is executed in the master or slave library.

SET @ @ GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;SET @ @ GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;SET @ @ GLOBAL.GTID_MODE = OFF_PERMISSIVE;SET @ @ GLOBAL.GTID_MODE = ON_PERMISSIVE;SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';select * from performance_schema.replication_applier_status_by_worker;FLUSH LOGS;SET @ @ GLOBAL.GTID_MODE = ON # # modify the configuration file gtid-mode = ONenforce-gtid-consistency = 1 destroy # change the replication mode from POS-based to GTIDSTOP SLAVE;CHANGE MASTER TO MASTER_AUTO_POSITION = 1% start SLAVE;SHOW SLAVE STATUS\ G2. Shut down GTID online

Determine whether the command is executed in the master or slave library.

Stop slave;show slave status\ GSET # take the Master_Log_File and Exec_Master_Log_Pos in show slave status\ G and fill in CHANGE MASTER TO MASTER_AUTO_POSITION = 0, MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=224126137;start slave;show slave status\ GSET @ @ GLOBAL.GTID_MODE = ON_PERMISSIVE;SET @ @ GLOBAL.GTID_MODE = OFF_PERMISSIVE;SELECT @ @ GLOBAL.GTID_OWNED;select * from performance_schema.replication_applier_status_by_worker;FLUSH LOGS SET @ @ GLOBAL.GTID_MODE = OFF;SET @ @ GLOBAL.ENFORCE_GTID_CONSISTENCY = OFF # # modify the parameter related to GTID in my.cnf configuration file as OFFgtid-mode = OFFenforce-gtid-consistency = 1 Thank you for your reading. The above is the content of "how to enable or disable GTID mode in MySQL". After the study of this article, I believe you have a deeper understanding of how to enable or disable GTID mode in MySQL, and the specific usage needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.