MySQL GTID (ii)

MySQL GTID Series two or three. Convert GTID to traditional mode online

Environment See previous article series

Close the Gtid without stopping the service and not affecting the online business

3.1 Turn off Gtid replication, adjust to traditional replication

#SLVAE实例上停止复制 STOP SLAVE#SLVAE实例上查看复制的位置SHOW SLAVE STATUS \G# 查看 Master_Log_File 和 Read_Master_Log_Pos对应的值binlog.000012:1821#重新 CHANGE MASTERCHANGE MASTER TO MASTER_AUTO_POSITION=0,                 MASTER_LOG_FILE='binlog.000012',                 MASTER_LOG_POS=1821;#开启SLAVESTART SLAVE;

3.2 Set Gtid mode to on_permissive on all servers

SET @@GLOBAL.GTID_MODE=ON_PERMISSIVE;

3.3 Set Gtid mode to off_permissive on all servers

SET @@GLOBAL.GTID_MODE=OFF_PERMISSIVE;

3.4 Wait for the variable @ @GLOBAL on all servers. Gtid_owned is empty, which represents the global GTID collection being executed by the thread

mysql> SELECT @@GLOBAL.GTID_OWNED;+---------------------+| @@GLOBAL.GTID_OWNED |+---------------------+|                     |+---------------------+1 row in set (0.00 sec)

3.5 Wait for all slave to replicate to complete the anonymous transaction. 3.6 Shut down Gtid on all servers.

SET @@GLOBAL.GTID_MODE=OFF;

3.7 Modify the configuration file for the next reboot, the configuration is also in effect.

gtid_mode=OFFenforce_gtid_consistency=OFF

Four. Traditional mode conversion to Gtid mode

Support for online modification of Gtid mode after MySQL5.7.6 version

4.1 Set Enforce_gtid_consistency=warn on all servers so that all transactions allow violation of GTID consistency

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY=WARN;

4.2 Set Enforce_gtid_consistency=on on all servers. Ensure that all transactions do not violate GTID consistency

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY=ON;

4.3 Set gtid_mode=off_permissive on all servers

SET @@GLOBAL.GTID_MODE=OFF_PERMISSIVE;

4.4 Set gtid_mode=on_permissive on all servers

SET @@GLOBAL.GTID_MODE=ON_PERMISSIVE;

4.5 Waiting for all Ongoing_anonymous_transaction_count from library to 0

SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';

4.6 Set Gtid_mode=on on all servers, turn on GTID

4.7 Modify the configuration file for the next reboot, the configuration is also in effect.

gtid_mode=ONenforce_gtid_consistency=1

MySQL GTID (ii)