### 环境准备 系统: `CentOS7` 数据库: `MySQL5.7` Master节点: `172.16.0.100(node1)` Slave节点: `172.16.0.101(node2)` #### GTID复制切换成传统复制 查看复制信息 `Slave` ```sql mysql> show slave status\G ``` ``` Slave_IO_State: Waiting for master to send event Master_Host: 172.16.0.100 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: on.000001 Read_Master_Log_Pos: 7227 Relay_Log_File: db3-relay-bin.000002 Relay_Log_Pos: 2374 Relay_Master_Log_File: on.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 7227 ... ``` 停止Slave,配置MASTER_AUTO_POSITION=0 `Slave` ```sql mysql> stop slave; mysql> CHANGE MASTER TO MASTER_HOST='172.16.0.100', MASTER_PORT=3306,MASTER_USER='replica', MASTER_PASSWORD='xxxxx',MASTER_AUTO_POSITION=0,MASTER_LOG_FILE='on.000001',MASTER_LOG_POS=7227; mysql> start slave; ``` 主从同时配置GTID模式为on_permissive `Master/Slave` ```sql mysql> set global gtid_mode=on_permissive ``` 主从同时配置GTID模式为off_permissive `Master/Slave` ```sql mysql> set global gtid_mode=off_permissive ``` 主从同时配置关闭GTID功能 `Master/Slave` ```sql mysql> set global enforce_gtid_consistency=off; mysql> set global gtid_mode=off; ``` 把gtid_mode=off和enforce_gtid_consistency=off写入my.cnf `Master/Slave` ```bash cat /etc/my.cnf ``` ```ini [mysqld] gtid_mode=on enforce_gtid_consistency=on ``` 验证传统复制是否成功 _1.插入数据到Master_ _2.Slave查看Executed_Gtid_Set值是否增加，如果值没变化，数据同步成功，说明传统复制配置成功_   #### 传统复制切换GTID复制 主从同时修改参数enforce_gtid_consistency=warn `Master/Slave` ```sql mysql> set global enforce_gtid_consistency=warn; ``` 主从同时修改参数enforce_gtid_consistency=on `Master/Slave` ```sql mysql> set global enforce_gtid_consistency=on; ``` 主从同时配置GTID模式为off_permissive `Master/Slave` ```sql mysql> set global gtid_mode=off_permissive ``` 主从同时配置GTID模式为on_permissive `Master/Slave` ```sql mysql> set global gtid_mode=on_permissive ``` 确认从库的Ongoing_anonymous_transaction_count参数是否为0(为0，意味着没有等待的事务，可以直接进行下一步操作) `Slave` ```sql mysql> show global status like 'Ongoing_anonymous_transaction_count'; ``` ``` +-------------------------------------+-------+ | Variable_name | Value | +-------------------------------------+-------+ | Ongoing_anonymous_transaction_count | 0 | +-------------------------------------+-------+ ``` 主从同时配置gtid_mode=on `Master/Slave` ```sql mysql> set global gtid_mode=on; ``` 把传统模式改为GTID复制 `Slave` ```sql mysql> stop slave; mysql> change master to master_auto_position=1; mysql> start slave; ``` 验证GTID复制是否成功 _1.插入数据到Master_ _2.Slave查看Executed_Gtid_Set值是否增加，如果是则说明切换GTID复制成功_