一、需求说明

  因GTID复制存在条件限制，有些时候我们需要将主从复制模式进行切换，下面将进行GTID复制和传统复制模式切换介绍，演示环境当前为GTID复制模式。演示环境配置信息如下,其中从库为多源复制共用的从库：

序号	主机名	IP地址	角色	server-id
1	test1	192.168.0.124	master	124
2	test2	192.168.0.126	slave	126

二、GTID复制切换为传统复制

1、检查确认当前模式及状态

mysql> show variables like ‘%gtid_mode%’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| gtid_mode | ON |
±--------------±------+
1 row in set (0.01 sec)

mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 4430
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: b9468eb8-74db-11eb-8e41-000c290b9fa0:1-18
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.124
Master_User: bak124
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4430
Relay_Log_File: test3-relay-bin-s124.000003
Relay_Log_Pos: 1273
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
…

2、在从库停止主从复制，调整为传统模式，设置参数master_auto_position=0

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to
-> master_auto_position=0,
-> MASTER_HOST=‘192.168.0.124’,
-> MASTER_USER=‘bak124’,
-> MASTER_PASSWORD=‘Test!123’,
-> MASTER_PORT=3306,
-> master_log_file=‘mysql-bin.000001’,
-> master_log_pos=4430 for CHANNEL ‘s124’;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

3、启动主从同步

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

4、主从服务器同时调整GTID模式为on_permissive

主库

mysql> set global gtid_mode=on_permissive;
Query OK, 0 rows affected (0.01 sec)

从库

mysql> set global gtid_mode=on_permissive;
Query OK, 0 rows affected (0.04 sec)

5、主从服务器同时调整GTID模式为off_permissive

主库

mysql> set global gtid_mode=off_permissive;
Query OK, 0 rows affected (0.00 sec)

从库

mysql> set global gtid_mode=off_permissive;
Query OK, 0 rows affected (0.00 sec)

6、主从服务器同时关闭GTID功能

主库

mysql> set global enforce_gtid_consistency=off;
Query OK, 0 rows affected (0.00 sec)

mysql> set global gtid_mode=off;
Query OK, 0 rows affected (0.00 sec)

从库

mysql> set global enforce_gtid_consistency=off;
Query OK, 0 rows affected (0.00 sec)

mysql> set global gtid_mode=off;
Query OK, 0 rows affected (0.01 sec)

7、如果需要永久切换需要，将第6步参数写入my.cnf配置文件

将如下参数配置写入配置文件/etc/my.cnf，以便数据库重启时以传统复制方式启动。
gtid_mode=off
enforce_gtid_consistency=off

8、测试是否切换成功

1）、往主库t2表插入数据

mysql> insert into t2 (s124_id,s124_company,s124_name) values (‘4’,‘yhxx’,‘yueyue’);
Query OK, 1 row affected (0.00 sec)

2）、在从库查看

mysql> select * from t2;
±--------±-------------±----------+
| s124_id | s124_company | s124_name |
±--------±-------------±----------+
| 1 | sac | sunru |
| 2 | yhxx | wuhs |
| 3 | yhxx | bluesky |
| 4 | yhxx | yueyue |
±--------±-------------±----------+
4 rows in set (0.00 sec)

3）、检查gtid值是否增加

查看binlog日志文件POS值已经变化，但是gtid值并没有增加

mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 468
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: b9468eb8-74db-11eb-8e41-000c290b9fa0:1-18
1 row in set (0.00 sec)

4）、检查从库状态

可以看到从库的binlog和POS值已经同步更新。

三、传统复制切换为GTID复制

1、主从库上同时修改enforce_gtid_consistency参数为warn

修改enforce_gtid_consistency参数为warn，确保在error log中不会出现告警信息。如果有告警信息，需要先修复后才能继续执行。

• 主库

mysql> set global enforce_gtid_consistency=warn;
Query OK, 0 rows affected (0.00 sec)

• 从库

mysql> set global enforce_gtid_consistency=warn;
Query OK, 0 rows affected (0.00 sec)

2、主从库上同时修改enforce_gtid_consistency参数为on

修改enforce_gtid_consistency参数为on，保证GTID的一致性。

• 主库

mysql> set global enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.00 sec)

• 从库

mysql> set global enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.00 sec)

3、主从服务器同时调整GTID模式为off_permissive

• 主库

mysql> set global gtid_mode=off_permissive;
Query OK, 0 rows affected (0.01 sec)

• 从库

mysql> set global gtid_mode=off_permissive;
Query OK, 0 rows affected (0.01 sec)

4、主从服务器同时调整GTID模式为on_permissive

• 主库

mysql> set global gtid_mode=on_permissive;
Query OK, 0 rows affected (0.01 sec)

• 从库

mysql> set global gtid_mode=on_permissive;
Query OK, 0 rows affected (0.01 sec)

5、在从库上检查是否有等待的事务

查看ongoing_anonymous_transaction_count值是否为0，值为0表示当前没有等待的事务，可以继续下一步操作。

mysql> show global status like ‘%ongoing_anonymous_%’;
±------------------------------------±------+
| Variable_name | Value |
±------------------------------------±------+
| Ongoing_anonymous_transaction_count | 0 |
±------------------------------------±------+
1 row in set (0.01 sec)

6、在主从库上同时设置gtid_mode=on

• 主库

mysql> set global gtid_mode=on;
Query OK, 0 rows affected (0.00 sec)

• 从库

mysql> set global gtid_mode=on;
Query OK, 0 rows affected (0.00 sec)

7、把传统复制改为GTID模式

• 停止主从
  设置master_auto_position=1

mysql> change master to master_auto_position=1 for channel ‘s124’;
Query OK, 0 rows affected (0.01 sec)

• 启动主从

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

8、插入数据验证是否成功

1）、在主库上插入数据

mysql> insert into t2 (s124_id,s124_company,s124_name) values (‘5’,‘changsha’,‘baby’);
Query OK, 1 row affected (0.00 sec)

mysql> delete from t2 where s124_id=3;
Query OK, 1 row affected (0.00 sec)

mysql> update t2 set s124_company=‘BeiJing’ where s124_id=‘4’;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

2）、从库上查看数据

mysql> select * from t2;
±--------±-------------±----------+
| s124_id | s124_company | s124_name |
±--------±-------------±----------+
| 1 | sac | sunru |
| 2 | yhxx | wuhs |
| 4 | BeiJing | yueyue |
| 5 | changsha | baby |
±--------±-------------±----------+
4 rows in set (0.01 sec)

3）、主库查看GTID值是否增加

可以看到gtid值已增

mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000007
Position: 1041
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: b9468eb8-74db-11eb-8e41-000c290b9fa0:1-21
1 row in set (0.00 sec)

4）、主库上更新数据

mysql> CREATE TABLE IF NOT EXISTS `t3`(
-> `s125_id` INT UNSIGNED AUTO_INCREMENT,
-> `s125_company` VARCHAR(100) NOT NULL,
-> `s125_name` VARCHAR(40) NOT NULL,
-> PRIMARY KEY ( `s125_id` )
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> insert into t3 (s125_id,s125_company,s125_name) values (‘1’,‘sac’,‘sunru’);
Query OK, 1 row affected (0.00 sec)

5）、从库上查看GTID值的变化

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.124
Master_User: bak124
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 1943
Relay_Log_File: test3-relay-bin-s124.000003
Relay_Log_Pos: 454
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
…
Retrieved_Gtid_Set: b9468eb8-74db-11eb-8e41-000c290b9fa0:19-24
Executed_Gtid_Set: b799ae0c-74db-11eb-8404-000c2964c7fe:1-8,
b8253ff7-74db-11eb-9897-000c29043152:1-21,
b9468eb8-74db-11eb-8e41-000c290b9fa0:1-24
…

四、GTID使用的限制条件

GTID复制是针对事务的，一个事务只对应一个GTID。

1. 不能使用create table table_name select * from table_name;
2. 在一个事务中既包括事务的操作又包含非事务表；
3. 不支持create temporary table 或者 drop temporary table语句操作；
4. 使用GTID复制在从库跳过错误时，不支持执行sql_slave_skip_counter参数的语法。