Author: Yu Zhenxing
As a member of aikesheng DBA team, he is keen on technology sharing and writing technical documents.
Source: original contribution
*It is produced by aikesheng open source community. The original content cannot be used without authorization. For reprint, please contact Xiaobian and indicate the source.
Basic overview
We know that MySQL has two ways to specify replication and synchronization:
- Designation method based on binlog file name and location
- Anonymous_gtid_log_event
- Specifying method based on GTID (global transaction ID)
- GTID transaction (Gtid_log_event)
In addition to the obvious advantages of master-slave switching based on GTID under the structure of one master and multiple slaves, it is also more convenient for fault diagnosis of daily replication abnormalities. In the process of daily operation and maintenance or MySQL upgrade, we have to turn on or off GTID. Personally, I prefer you to turn on or off GTID online, On the one hand, this operation can affect the database downtime as little as possible. On the other hand, it can also verify whether the adjustment of this parameter will affect the application during the process of startup or shutdown. Since MySQL 5.7.6, it has supported the dynamic startup and shutdown of GTID mode, and its parameter GTID_MODE has the following values
- OFF - only anonymous transactions are allowed to be replicated and synchronized
- OFF_PERMISSIVE - all newly generated transactions are anonymous transactions, but GTID transactions are also allowed to be replicated and synchronized
- ON_PERMISSIVE - all newly generated transactions are GTID transactions, but anonymous transactions are also allowed to be replicated and synchronized
- ON - only GTID transactions are allowed to be replicated and synchronized
In fact, we can see from several values of this parameter that online modification is a step-by-step process of transforming anonymous transactions into GTID transactions (the same is true on the contrary). Let's first see what we need to do to open GTID online
Open GTID Online
1. Set gtid verification force_ GTID_ Consistency is WARN
The purpose of this operation is to allow the SQL statements executed in the main database to violate the gtid consistency check, and only output the warning level log in the error log of the main database as a reminder. We know that there are still some restrictions on gtid replication. In fact, this is to consider that if the replication method is directly changed to gtid mode, the application will cause abnormal error due to some restrictions of gtid, The advantage of this is that I can turn on force before I need to turn on gtid_ GTID_ The consistency parameter is set 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 cycle, we will consider officially starting gtid
- Example: using CREATE TABLE AS SELECT syntax is not supported in gtid mode (aside: CTAS syntax is also supported in gtid mode after 8.0.21, and the syntax is changed to a special atomic DDL operation), while force_ GTID_ When consistency is set to WARN, it will only prompt in the error log and will not directly report an error,
## This operation is performed in both master and slave libraries SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;
2. Set gtid verification force_ GTID_ Consistency is ON
After confirming that there is no relevant Warning information in the error log in the previous step, the GTID consistency verification is officially started. When it is set to ON, if the CREATE TABLE AS SELECT statement is executed again, an error will be reported directly
## This operation is performed in both master and slave libraries SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
3. Set GTID_MODE is OFF_PERMISSIVE
If facing GTID_ Description of the value of mode. This operation indicates that the newly generated transaction is still anonymous, but GTID transactions are also allowed to be copied and synchronized. For online GTID mode, this step is a simple transition attribute (note that it is prepared for online GTID shutdown). After execution, it can quickly move to the next stage
## This operation is performed in both master and slave libraries SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
4. Set GTID_MODE is ON_PERMISSIVE
This operation is still a transition attribute, which indicates that all newly generated transactions are GTID transactions, but anonymous transactions are also allowed to be copied. It has been a formal transformation process since this stage, but it is still compatible with the two transactions.
## This operation is performed in both the master and slave libraries SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
5. (key points) ensure that the playback of anonymous transactions is completed
The purpose of this step is to ensure that the old anonymous transactions have been played back before the formal conversion to the complete GTID mode_ When mode is set to ON, replication synchronization errors will not be reported due to residual anonymous transactions. There are two ways to verify
## This operation can only be performed from the library ## Method 1: ensure that the number of anonymous transactions output by the status value is displayed as 0 (Note: as long as 0 appears, 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 the statement multiple times on the slave library ## Method 2: query last in this view_ SEEN_ Transaction can observe whether the currently synchronized transaction still has an ANONYMOUS transaction select * from performance_schema.replication_applier_status_by_worker;
Ensure that the number of anonymous transactions is 0
Ensure that all transactions played back by the playback 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 main database so that the newly generated binlog is a transaction containing GTID (to prevent a binlog from containing two types of transaction logs)
## This operation can be performed only in the main library FLUSH LOGS;
7. Officially open GTID_MODE is ON
Officially open GTID
## This operation is performed in both master and slave libraries SET @@GLOBAL.GTID_MODE = ON; SELECT @@GTID_MODE,@@ENFORCE_GTID_CONSISTENCY;
8. Modify the configuration file to ensure the persistence of GTID parameters
In my The GTID parameter is added to the CNF configuration file to ensure that the restart will not fail. This operation can also be carried out in the first step
## This operation is performed in both master and slave libraries gtid-mode = ON enforce-gtid-consistency = 1
9. Modify the copy mode to GTID mode
After the GTID mode is enabled, we also need to change the copy mode from POS based to GTID based. The operation is relatively simple
## Stop replication STOP SLAVE; ## Change to GTID mode CHANGE MASTER TO MASTER_AUTO_POSITION = 1; ## Turn on replication START SLAVE; ## Observe replication synchronization status SHOW SLAVE STATUS\G
Online shutdown GTID
The way of online closing is basically similar to the reverse operation of online opening GTID. Only the steps and specific commands are written below without detailed explanation
- First, change the replication of GTID mode to POS point based replication
- Set GTID_MODE is ON_PERMISSIVE
- Set GTID_MODE is OFF_PERMISSIVE
- Observation GTID_OWNED state variable becomes null and replication_ applier_ status_ by_ All transactions in the worker table are converted to anonymous transactions
- Trigger FLUSH LOGS
- Set GTID_MODE is OFF
- Set ENFORCE_GTID_CONSISTENCY is OFF
- Modify my The parameter CNF in the configuration file is OFF
1. Change the copy mode to POS point based mode
stop slave; show slave status\G ## Take the master in show slave status\G_ Log_ File and Exec_Master_Log_Pos filling ## Master must not be omitted here_ AUTO_ Position = 0 this configuration 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 is ON_PERMISSIVE
## This operation is performed in both master and slave libraries SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
3. Set GTID_MODE is OFF_PERMISSIVE
## This operation is performed in both master and slave libraries SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
4. (key points) ensure that GTID transaction playback is completed
Observation GTID_OWNED state variable becomes null and replication_ applier_ status_ by_ All transactions in the worker table are converted to anonymous transactions
## This operation can be performed from the library SELECT @@GLOBAL.GTID_OWNED; select * from performance_schema.replication_applier_status_by_worker;
5. Trigger FLUSH LOGS
## This operation can be performed in the main library FLUSH LOGS;
6. Set GTID_MODE is OFF
## This operation is performed in both master and slave libraries SET @@GLOBAL.GTID_MODE = OFF;
7. Set force_ GTID_ Consistency is OFF
## This operation is performed in both master and slave libraries SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = OFF;
8. Modify my The parameter CNF in the configuration file is OFF
## This operation is performed in both master and slave libraries gtid-mode = OFF enforce-gtid-consistency = 1
Command Brief
1. Open GTID Online
Judge whether the command is executed in the master library 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; ## Profile 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. Turn off GTID Online
Judge whether the command is executed in the master library or slave library
stop slave; show slave status\G ## Take the master in show slave status\G_ Log_ File and Exec_Master_Log_Pos filling 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 my The GTID related parameter in CNF configuration file is OFF gtid-mode = OFF enforce-gtid-consistency = 1
Technical summary
In fact, there seem to be many commands for online opening and closing GTID, but in fact, they can be completed very quickly and have little impact on the business. The more important thing is a verification process before formal 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