GTID和binlog模式切换
前提:
```
基于binlog日志已搭建好主从关系,29:3306和29:3307互为主从关系
```
1、从传统复制模式切换为GTID复制模式
```bash
# 在主从库上同时修改参数enforce_gtid_consistency=warn,确保error log中不会出现警告信息,如# # 果有,需要先修复,再继续
mysql> set global enforce_gtid_consistency=warn;
# 在主从服务器上把enforce_gtid_consistency修改为on,保障GTID的一致性
mysql> set global enforce_gtid_consistency=on;
# 在主从服务器上同时调整GTID模式为off_permissive
mysql> set global gtid_mode=off_permissive;
# 在主从服务器上同时调整GTID模式为on_permissive
mysql> set global gtid_mode=on_permissive;
# 确认冲库的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.00 sec)
# 在主从库上同时设置gtid_mode为开启状态
mysql> set global gtid_mode=on;
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
# 把传统复制模式改为GTID复制,先把原有的传统复制停掉stop slave,在执行change master to master_auto_position=1,再启动slave
# 更改29:3307的配置
mysql> stop slave;
mysql> change master to master_auto_position=1;
mysql> start slave;
# 测试在主库29:3306添加一条数据
mysql> insert into test04.t4 values(1,'tt','cd');
# 在从库29:3307验证
mysql> show slave status\G;
Retrieved_Gtid_Set: 22346d12-9a6a-11ea-bd3c-fa163e25e077:1
Executed_Gtid_Set: 22346d12-9a6a-11ea-bd3c-fa163e25e077:1
Auto_Position: 1
# 从传统的binlog日志复制转变为gtid复制模式成功
# 更改29:3306的配置
mysql> stop slave;
mysql> change master to master_auto_position=1;
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
# 报错原因启动slave时,使用repository中信息初始化relay log结构失败了,解决方法如下:
mysql> reset slave;
mysql> start slave;
# 测试在29:3307添加一条数据
mysql> insert into test04.t4 values(5,'uu','ff');
# 在29:3306上验证
mysql> show slave status\G;
Retrieved_Gtid_Set: 5fcfeb62-9b29-11ea-8018-fa163e25e077:1
Executed_Gtid_Set: 22346d12-9a6a-11ea-bd3c-fa163e25e077:1,
5fcfeb62-9b29-11ea-8018-fa163e25e077:1
```
2、从GTID复制模式切换为传统复制模式的过程
```bash
# 思路:先在从库中执行stop slave,停掉主从复制,然后调整为传统复制模式,让master_auto_position=0
# 在29:3307和29:3306同时执行
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
# 29:3306上执行
mysql> change master to master_auto_position=0,master_host="192.168.1.29", master_port=3307,master_user="yaya",master_password="123456",\
-> master_log_file="mysql-bin.000037", master_log_pos=680;
Query OK, 0 rows affected, 2 warnings (0.07 sec)
# 29:3307上执行
mysql> change master to master_auto_position=0,master_host="192.168.1.29", master_port=3306, master_user="yaya",master_password="123456", \
-> master_log_file="mysql-bin.000051", master_log_pos=680;
# 3306和3307开启复制功能
mysql> start slave;
Query OK, 0 rows affected (0.08 sec)
# 3306和3307服务器上同时调整GTID模式为
mysql> set global gtid_mode=on_permissive;
# 3306和3307服务器上同时调整GTID模式
mysql> set global gtid_mode=off_permissive;
# 3306和3307服务器上同时关闭GTID功能
mysql> set global enforce_gtid_consistency=off;
mysql> set global gtid_mode=off;
注意: 如果在配置文件中配置过gtid_mode和enforce_gtid_consistency两个参数,记得更改配置文件,下次重启服务后直接生效
# 测试,分别在3306和3307上分别插入数据,查看是否同步成功,并用show slave status\G;查看以下参数是否发生改变
Retrieved_Gtid_Set:
Executed_Gtid_Set: 22346d12-9a6a-11ea-bd3c-fa163e25e077:1-2,
5fcfeb62-9b29-11ea-8018-fa163e25e077:1-2
Auto_Position: 0
# gtid的值未增加,并且Auto_Position为0,说明切换成功
```