Enabling or disabling GTID mode in MySQL online

Basic Overview

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

1. Specifying the binlog file name and location
   - Anonymous transactions (Anonymous_gtid_log_event)
2. GTID (global transaction ID) based specification
   - GTID transaction (Gtid_log_event)

The GTID-based method has obvious advantages in master-slave switching under a one-master-multiple-slave architecture, and is also more convenient for troubleshooting daily replication anomalies. Since MySQL 5.7.6, it has supported dynamic opening and closing of the GTID mode. The parameter GTID_MODE has the following values:

• OFF - Only allow anonymous transactions to be replicated
• OFF_PERMISSIVE - Newly generated transactions are anonymous transactions, but GTID transactions are also allowed to be replicated and synchronized
• ON_PERMISSIVE - Newly generated transactions are GTID transactions, but anonymous transactions are also allowed to be replicated and synchronized
• ON - only allow GTID transactions to be replicated

Enable GTID online

1. Set GTID verification ENFORCE_GTID_CONSISTENCY to WARN

The purpose of this operation is to allow SQL statements executed in the master database to violate the GTID consistency check, and only output warning-level logs in the error log of the master database as a reminder. The purpose is to consider that if the replication mode is changed to GTID mode, the application will not cause abnormal errors due to some limitations of GTID.

For example, the CREATE TABLE AS SELECT syntax is not supported in GTID mode, and when ENFORCE_GTID_CONSISTENCY is set to WARN, only an error message will be displayed in the error log, not a direct error message.

## This operation is executed in both the master and slave databases. SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;

2. Set GTID verification ENFORCE_GTID_CONSISTENCY to ON

After observing for a period of time (more than one day in a rigorous situation), confirm that no relevant Warning information appears in the error log in the previous step, and then officially enable GTID consistency check. When it is set to ON, if you execute the CREATE TABLE AS SELECT statement again, an error will be reported directly.

## This operation is executed in both the master and slave databases SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;

3. Set GTID_MODE to OFF_PERMISSIVE

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

## This operation is executed in both the master and slave databases. SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

4. Set GTID_MODE to ON_PERMISSIVE

This operation is still a transitional attribute, and it means that all newly generated transactions are GTID transactions, but anonymous transactions are also allowed to be replicated.

## This operation is executed in both the master and slave databases SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

5. (Key point) Ensure that anonymous transactions are played back

The purpose of this step is to ensure that all old anonymous transactions are played back before the formal conversion to full GTID mode, and to ensure that when GTID_MODE is set to ON, no replication synchronization errors are caused by residual anonymous transactions. There are two ways to verify this:

## This operation can only be performed in the slave database## Method 1: Make sure that the number of anonymous transactions output by the status value is 0 (Note: as long as 0 appears, it means that the conversion has been completed, even if the status value changes from 0 to a value greater than 0 later)
SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';

## Execute this statement multiple times on the slave database## Method 2: Query the view LAST_SEEN_TRANSACTION to observe whether the currently synchronized transaction still has ANONYMOUS transactions select * from performance_schema.replication_applier_status_by_worker;

Make sure the number of anonymous transactions is 0

Ensure that all transactions replayed by the replay thread are GTID transactions

6. Trigger a round of log switching FLUSH LOGS

The purpose of this operation is to trigger the rotation of binlog in the master database so that the newly generated binlogs are all transactions containing GTID (to prevent a binlog from containing two types of transaction logs).

## This operation can be performed only in the main database FLUSH LOGS;

7. Officially turn on GTID_MODE

Officially launch GTID

## This operation is executed in both the master and slave databases SET @@GLOBAL.GTID_MODE = ON;
SELECT @@GTID_MODE,@@ENFORCE_GTID_CONSISTENCY;

8. Modify the configuration file to ensure the persistence of GTID parameters

Add the GTID parameter in the my.cnf configuration file to ensure that the restart will not fail. This operation can also be performed in the first step.

## This operation is performed on both the master and slave databases with gtid-mode = ON
enforce-gtid-consistency = 1

9. Change the replication mode to GTID mode

Change the replication mode from POS-based to GTID-based

## Stop replication STOP SLAVE;

## Change to GTID mode CHANGE MASTER TO MASTER_AUTO_POSITION = 1;

## Start replication START SLAVE;

## Observe the replication synchronization status SHOW SLAVE STATUS\G

Disable GTID online

The method is similar to the reverse operation of enabling GTID online. The following only describes the steps and specific commands without detailed explanation.

1. First change the GTID mode replication to POS-based replication
2. Set GTID_MODE to ON_PERMISSIVE
3. Set GTID_MODE to OFF_PERMISSIVE
4. Observe that the GTID_OWNED status variable becomes null and that all transactions in the replication_applier_status_by_worker table are converted to anonymous transactions.
5. Triggering FLUSH LOGS
6. Set GTID_MODE to OFF
7. Set ENFORCE_GTID_CONSISTENCY to OFF
8. Modify the GTID related parameters in the my.cnf configuration file to OFF

1. Change the replication to POS point-based

stop slave;
show slave status\G

## Take Master_Log_File and Exec_Master_Log_Pos from show slave status\G and fill them into CHANGE MASTER TO
  MASTER_AUTO_POSITION = 0,
  MASTER_LOG_FILE='mysql-bin.000017',
  MASTER_LOG_POS=224126137;
start slave;
show slave status\G

2. Set GTID_MODE to ON_PERMISSIVE

## This operation is executed in both the master and slave databases SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

3. Set GTID_MODE to OFF_PERMISSIVE

## This operation is executed in both the master and slave databases. SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

4. (Key point) Ensure that GTID transaction playback is complete

Observe that the GTID_OWNED status variable becomes null and that all transactions in the replication_applier_status_by_worker table are converted to anonymous transactions.

## This operation can be executed in the slave database SELECT @@GLOBAL.GTID_OWNED;
select * from performance_schema.replication_applier_status_by_worker;

5. Triggering FLUSH LOGS

## This operation can be performed in the main database FLUSH LOGS;

6. Set GTID_MODE to OFF

## This operation is executed in both the master and slave databases. SET @@GLOBAL.GTID_MODE = OFF;

7. Set ENFORCE_GTID_CONSISTENCY to OFF

## This operation is executed in both the master and slave databases. SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = OFF;

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

## This operation is performed on both the master and slave databases. gtid-mode = OFF
enforce-gtid-consistency = 1

Command Simplified

1. Enable GTID online

Determine whether the command is executed in the master or slave database

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;

## Configuration file modification gtid-mode = ON
enforce-gtid-consistency = 1

## Change the replication mode from POS-based to GTID-based
STOP SLAVE;
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
START SLAVE;
SHOW SLAVE STATUS\G

2. Disable GTID online

Determine whether the command is executed in the master or slave database

stop slave;
show slave status\G

## Take Master_Log_File and Exec_Master_Log_Pos from show slave status\G and fill them into CHANGE MASTER TO
  MASTER_AUTO_POSITION = 0,
  MASTER_LOG_FILE='mysql-bin.000017',
  MASTER_LOG_POS=224126137;
start slave;
show slave status\G

SET @@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 GTID related parameters in the my.cnf configuration file to OFF
gtid-mode = OFF
enforce-gtid-consistency = 1

Reference Links

https://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online.html

https://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-enable-gtids.html

https://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-disable-gtids.html

The above is the editor's introduction to enabling or disabling GTID mode in MySQL online. I hope it will be helpful to everyone. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!