Basic overview
We know that MySQL has two ways to specify replication and synchronization:
- Specifying 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)
On the one hand, the GTID based method has obvious advantages in the master-slave switching under the one master-slave architecture, and it is also more convenient for the fault diagnosis of daily replication exceptions. Since MySQL 5.7.6, it has supported the dynamic opening and closing 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
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. The purpose is to consider that if the replication mode is changed to GTID mode, the application will not cause abnormal error due to some restrictions of GTID,
- For example, CREATE TABLE AS SELECT syntax is not supported in gtid mode, but enforce_ GTID_ When consistency is set to WARN, you will only be prompted in the error log and will not directly report an error,
## This operation is performed in both the master and slave libraries SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;
2. Set gtid verification force_ GTID_ Consistency is ON
Observe for a period of time (the rigorous situation may be observed for more than one day). After confirming that there is no relevant Warning information in the error log in the previous step, officially start the GTID consistency verification. 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 performed in both the master and slave libraries SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
3. Set GTID_MODE is OFF_PERMISSIVE
This operation is a transition attribute, indicating that the newly generated transaction is still anonymous, but GTID transactions are allowed to be replicated and synchronized
## This operation is performed in both the 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
## This operation is performed in both the master and slave libraries SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
5. (key point) ensure that the playback of anonymous transactions is completed
The purpose of this step is to ensure that the old anonymous transactions are played back before the formal conversion to the complete GTID mode, so as to ensure the GTID_ 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 0 (Note: as long as 0 occurs, 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 binlogs in the main database so that the newly generated binlogs are transactions containing gtids (to prevent a binlog from containing two types of transaction logs)
## This operation can only be performed in the main library FLUSH LOGS;
7. Officially open GTID_MODE is ON
Officially open GTID
## This operation is performed in both the 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
Add the GTID parameter in the my.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 the master and slave libraries gtid-mode = ON enforce-gtid-consistency = 1
9. Modify the copy 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; ## Turn on replication START SLAVE; ## Observe replication synchronization status SHOW SLAVE STATUS\G
Online shutdown GTID
The method is similar to the reverse operation of starting GTID online. 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 force_ GTID_ Consistency is OFF
- Modify the GTID related parameter in my.cnf configuration file to OFF
1. Change replication to POS point based mode
stop slave; show slave status\G ## Get the master in show slave status\G_ Log_ File and Exec_Master_Log_Pos fill 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 the master and slave libraries SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
3. Set GTID_MODE is OFF_PERMISSIVE
## This operation is performed in both the 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 the master and slave libraries SET @@GLOBAL.GTID_MODE = OFF;
7. Set force_ GTID_ Consistency is OFF
## This operation is performed in both the master and slave libraries SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = OFF;
8. Modify the GTID related parameter in my.cnf configuration file to OFF
## This operation is performed in both the master and slave libraries gtid-mode = OFF enforce-gtid-consistency = 1
Command Brief
1. Open GTID Online
Determine whether the command is executed in the master library or the 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
Determine whether the command is executed in the master library or the slave library
stop slave; show slave status\G ## Get the master in show slave status\G_ Log_ File and Exec_Master_Log_Pos fill 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 parameter in my.cnf configuration file to OFF gtid-mode = OFF enforce-gtid-consistency = 1