Technology sharing | What to do in each stage of MySQL online GTID opening

Author: Yu Zhenxing

A member of the DBA team of Akson, keen on technology sharing and writing technical documents.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

Basic overview

We know that MySQL has 2 ways to specify the way of replication synchronization, namely:

1. Specifying method based on binlog file name and location

   • Anonymous Transaction (Anonymous_gtid_log_event)

2. Designation based on GTID (Global Transaction ID)

   • GTID transaction (Gtid_log_event)

The GTID-based method has obvious advantages in master-slave switching under the one-master-multiple-slave architecture. It is also more convenient for fault diagnosis of daily replication exceptions. In the process of daily operation and maintenance or MySQL upgrade, we inevitably need to enable or disable GTID. Close, from a personal point of view, I also prefer to open or close GTID online. On the one hand, this operation can affect the database downtime as little as possible, and on the other hand, it can be opened or closed by the way. Verify whether the adjustment of this parameter will affect the application. Since MySQL 5.7.6, the GTID mode has been dynamically enabled and disabled. 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 also allow GTID transactions 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 synchronized by replication

In fact, from the several values of this parameter, we can see that online modification is a step-by-step process of converting anonymous transactions into GTID transactions (and vice versa). Let's first look at what to do when opening GTID online

Open GTID online

1. Set the GTID check ENFORCE_GTID_CONSISTENCY to WARN

The purpose of this operation is to allow SQL statements executed in the main library to violate the GTID consistency check, and only output warning-level logs in the error log of the main library as a reminder. We know that GTID replication still has some restrictions. In fact, here Just to consider that if the replication mode is directly changed to GTID mode, the application will report abnormal errors due to some limitations of GTID. The advantage of this is that when I need to enable GTID, I can set the ENFORCE_GTID_CONSISTENCY parameter to WARN for observation for a period of time, such as one day, If no relevant Warning information is found in the error log during the observation period, then we will consider officially opening the GTID operation.

• Example: Using CREATE TABLE AS SELECT syntax is not supported in GTID mode ( topic: CTAS syntax is also supported in GTID mode after 8.0.21, the syntax was changed to a special atomic DDL operation ), and ENFORCE_GTID_CONSISTENCEY is set When it is WARN, it will only prompt in the error log, and will not report an error directly.

<br/>

## 该操作在主从库均执行
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;

2. Set the GTID check ENFORCE_GTID_CONSISTENCEY to ON

After confirming that there is no relevant Warning information in the error log in the previous step, the GTID consistency check is officially enabled. When it is set to ON, if the CREATE TABLE AS SELECT statement is executed again, an error will be reported directly.

<br/>

## 该操作在主从库均执行
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;

3. Set GTID_MODE to OFF_PERMISSIVE

As described in the previous description of the possible values of GTID_MODE, this operation indicates that the newly generated transaction is still an anonymous transaction, but also allows GTID transactions to be replicated and synchronized. For online GTID mode, this step is a simple transition attribute ( Note that it is prepared for online closing of GTID), you can quickly go to the next stage after execution

## 该操作在主从库均执行
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

4. Set GTID_MODE to ON_PERMISSIVE

This operation is still a transition attribute, which means that all new GTID transactions are generated, but anonymous transactions are also allowed to be copied. From this stage, it has been a formal conversion process, but it is still a process of two transactions. do compatible.

## 该操作在主从库均执行
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

5. (Key point) Ensure that anonymous transaction playback is completed

The purpose of this step is to ensure that the old anonymous transactions have been played back before the official conversion to the complete GTID mode, and to ensure that when GTID_MODE is set to ON, replication synchronization errors will not be reported due to the residual anonymous transactions. There are two methods as follows: check

## 该操作仅在从库执行即可
## 方式1:确保该状态值输出的匿名事务数显示为0(注意:只要出现过0即可表示已经转换完成,即使后续该状态值从0变为了大于0的值也不影响)
SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';

## 在从库上多次执行该语句
## 方式2: 查询该视图中LAST_SEEN_TRANSACTION可以观测当前同步的事务是否还存在ANONYMOUS事务
select * from performance_schema.replication_applier_status_by_worker;

Make sure the anonymous transaction count is 0

Ensure that the transactions played back by the playback thread are all 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 binlogs are all transactions containing GTID (to prevent one binlog from containing two types of transaction logs)

## 该操作仅在主库执行即可
FLUSH LOGS;

7. Officially open GTID_MODE to ON

Officially open GTID

## 该操作在主从库均执行
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 to the my.cnf configuration file to ensure that the restart will not fail. This operation can also be performed in the first step

## 该操作在主从库均执行
gtid-mode                 = ON
enforce-gtid-consistency  = 1

9. Modify the copy mode to GTID mode

After turning on the GTID mode, we also have to change the replication mode from POS-based to GTID-based, the operation is relatively simple

## 停止复制
STOP SLAVE;

## 修改为GTID模式
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;

## 开启复制
START SLAVE;

## 观测复制同步状态
SHOW SLAVE STATUS\G

Turn off GTID online

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

1. First, change the replication of GTID mode to POS point-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 the transactions in the replication_applier_status_by_worker table are turned into anonymous transactions
5. Trigger 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 replication to POS point-based mode

stop slave;
show slave status\G

## 取show slave status\G中的Master_Log_File和Exec_Master_Log_Pos填入
## 这里一定不要漏掉MASTER_AUTO_POSITION = 0这个配置
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

## 该操作在主从库均执行
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

3. Set GTID_MODE to OFF_PERMISSIVE

## 该操作在主从库均执行
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

4. (Key point) Make sure the GTID transaction is replayed

Observe that the GTID_OWNED status variable becomes null and the transactions in the replication_applier_status_by_worker table are turned into anonymous transactions

## 该操作在从库执行即可
SELECT @@GLOBAL.GTID_OWNED;
select * from performance_schema.replication_applier_status_by_worker;

5. Trigger FLUSH LOGS

## 该操作在主库执行即可
FLUSH LOGS;

6. Set GTID_MODE to OFF

## 该操作在主从库均执行
SET @@GLOBAL.GTID_MODE = OFF;

7. Set ENFORCE_GTID_CONSISTENCY to OFF

## 该操作在主从库均执行
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = OFF;

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

## 该操作在主从库均执行
gtid-mode                 = OFF
enforce-gtid-consistency  = 1

Short version of the command

1. Enable GTID online

Determine by yourself whether the command is executed in the main library or from the 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;

## 配置文件修改
gtid-mode                 = ON
enforce-gtid-consistency  = 1

## 将复制模式从基于POS点改为基于GTID
STOP SLAVE;
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
START SLAVE;
SHOW SLAVE STATUS\G

2. Turn off GTID online

Determine by yourself whether the command is executed in the main library or from the library

stop slave;
show slave status\G

## 取show slave status\G中的Master_Log_File和Exec_Master_Log_Pos填入
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;

## 修改my.cnf配置文件中GTID相关参数为OFF
gtid-mode                 = OFF
enforce-gtid-consistency  = 1

Technical summary

In fact, it seems that there are many commands to open and close GTID online, but in fact, it can be completed at a very fast speed and has little impact on the business. The more important thing is a verification process before the official opening.

Reference link

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