GTID复制模式切换到传统主从复制
主从复制环境：
主库：10.18.10.11
从库：10.18.10.12
MySQL5.7.22

切换之前查看下主从gitd_mode参数值
主服务器：gtid_mode值
root@sakila 11:45: [(none)]> show variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | ON |
+---------------+-------+
1 row in set (0.01 sec)

从服务器gitd_mode值：
root@sakila 11:44: [(none)]> show variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | ON |
+---------------+-------+
1 row in set (0.01 sec)

root@sakila 11:47:  [(none)]> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.18.10.11

                  Master_User: repluser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: on.000012

          Read_Master_Log_Pos: 346

               Relay_Log_File: slave-relay-bin.000024

                Relay_Log_Pos: 505

        Relay_Master_Log_File: on.000012

             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: 346

              Relay_Log_Space: 753

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 3306100

                  Master_UUID: b8439fb9-4f22-11e8-a24e-000c291a6b52

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:103

            Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-103

                Auto_Position: 0

         Replicate_Rewrite_db:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)

先在从库上关闭slave，停止主从复制，然后修改复制模式为传统模式，master_auto_position=0

root@sakila 11:48: [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
root@sakila 11:50: [(none)]> CHANGE MASTER TO master_auto_position=0,
Master_Host='10.18.10.11',
MASTER_USER='repluser',
MASTER_PASSWORD='rep123',
Master_Log_File='on.000012',
MASTER_LOG_POS=346;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
root@sakila 11:52: [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
root@sakila 11:53: [(none)]> set global gtid_mode=on_permissive;
Query OK, 0 rows affected (0.01 sec)
root@sakila 11:53: [(none)]> set global gtid_mode=off_permissive;
Query OK, 0 rows affected (0.01 sec)
root@sakila 11:54: [(none)]> set global gtid_mode=off;
Query OK, 0 rows affected (0.01 sec)
root@sakila 11:55: [(none)]> set global enforce_gtid_consistency=off;
Query OK, 0 rows affected (0.00 sec)
root@sakila 11:55: [(none)]> show variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | OFF |
+---------------+-------+
1 row in set (0.00 sec)

同样主库做如下操作：
root@sakila 11:45: [(none)]> show variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | ON |
+---------------+-------+
1 row in set (0.01 sec)
root@sakila 11:46: [(none)]> set global gtid_mode=on_permissive;
Query OK, 0 rows affected (0.02 sec)
root@sakila 11:53: [(none)]> set global gtid_mode=off_permissive;
Query OK, 0 rows affected (0.01 sec)
root@sakila 11:54: [(none)]> set global gtid_mode=off;
Query OK, 0 rows affected (0.02 sec)
root@sakila 11:55: [(none)]> set global enforce_gtid_consistency=off;
Query OK, 0 rows affected (0.00 sec)
root@sakila 11:55: [(none)]> show variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | OFF |
+---------------+-------+
1 row in set (0.00 sec)

两边gtid复制模式方式现已关闭，同时开始传统模式，开始验证传统复制模式是否生效：

验证前查记录下当前Excuted_Gtid_Set值，方便后面做对比。

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-103

Auto_Position: 0

Replicate_Rewrite_db:

在主库插入数据：

root@sakila 12:11:  [test]> select * from tt;

+----+-------+-------+

| id | name  | score |

+----+-------+-------+

|  1 | Tome  |    80 |

|  2 | Janne |    90 |

|  3 | Kases |    84 |

|  4 | kids  |    99 |

+----+-------+-------+

4 rows in set (0.00 sec)

root@sakila 12:12:  [test]> insert into tt (name,score) values('MySQL',82);

Query OK, 1 row affected (0.01 sec)

root@sakila 12:13:  [test]> select * from tt;

+----+-------+-------+

| id | name  | score |

+----+-------+-------+

|  1 | Tome  |    80 |

|  2 | Janne |    90 |

|  3 | Kases |    84 |

|  4 | kids  |    99 |

|  5 | MySQL |    82 |

+----+-------+-------+

5 rows in set (0.00 sec)

从库查看插入数据：

root@sakila 12:06:  [(none)]> use test

Database changed

root@sakila 12:14:  [test]> select * from tt;

+----+-------+-------+

| id | name  | score |

+----+-------+-------+

|  1 | Tome  |    80 |

|  2 | Janne |    90 |

|  3 | Kases |    84 |

|  4 | kids  |    99 |

|  5 | MySQL |    82 |

+----+-------+-------+

5 rows in set (0.00 sec)

发现数据已经过来了 ，再查看Excuted_Gtid_Set的值

root@sakila 12:14: [test]> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.18.10.11

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: on.000015

Read_Master_Log_Pos: 461

Relay_Log_File: slave-relay-bin.000008

Relay_Log_Pos: 620

Relay_Master_Log_File: on.000015

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

--------------------省略--------------

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-103

Auto_Position: 0

Replicate_Rewrite_db:

Channel_Name:

Master_TLS_Version:

通过对比发现GTID的值没有增加，证明切换成功。

通过传统主从模式切换到GTID主从复制模式：

在主从库上修改enforce_gtid_consistency=warn,确保在error.log中不出现警告信息，如果有需要先修复。
主库：
root@sakila 12:13: [test]> set global enforce_gtid_consistency=warn;
Query OK, 0 rows affected (0.00 sec)
root@sakila 12:19: [test]> show variables like '%enforce_gtid_consistency%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| enforce_gtid_consistency | WARN |
+--------------------------+-------+
1 row in set (0.01 sec)
从库：
root@sakila 12:18: [test]> set global enforce_gtid_consistency=warn;
Query OK, 0 rows affected (0.00 sec)
root@sakila 12:19: [test]> show variables like '%enforce_gtid_consistency%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| enforce_gtid_consistency | WARN |
+--------------------------+-------+
1 row in set (0.01 sec)

在主从库上调整enforce_gtid_consistency=on,保证GTID的一致性
主库：
root@sakila 12:20: [test]> set global enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.00 sec)

root@sakila 12:21: [test]> show variables like '%enforce_gtid_consistency%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON |
+--------------------------+-------+
1 row in set (0.01 sec)
从库：
root@sakila 12:20: [test]> set global enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.00 sec)

root@sakila 12:22: [test]> show variables like '%enforce_gtid_consistency%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON |
+--------------------------+-------+
1 row in set (0.01 sec)

然后在主从库安装前面的顺序再一次修改回去，直至gtid_mode=on为止
主库操作：
root@sakila 12:21: [test]> set global gtid_mode=off_permissive;
Query OK, 0 rows affected (0.02 sec)
root@sakila 12:24: [test]> set global gtid_mode=on_permissive;
Query OK, 0 rows affected (0.01 sec)
root@sakila 12:24: [test]> set global gtid_mode=on;
Query OK, 0 rows affected (0.01 sec)
root@sakila 12:24: [test]> show variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | ON |
+---------------+-------+
1 row in set (0.00 sec)

从库操作：
root@sakila 12:22: [test]> set global gtid_mode=off_permissive;
Query OK, 0 rows affected (0.01 sec)
root@sakila 12:25: [test]> set global gtid_mode=on_permissive;
Query OK, 0 rows affected (0.01 sec)
root@sakila 12:25: [test]> set global gtid_mode=on;
Query OK, 0 rows affected (0.01 sec)
root@sakila 12:25: [test]> show variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | ON |
+---------------+-------+
1 row in set (0.01 sec)

提示：上述gtid_mode参数值修改必须严格安装顺序依次修改，否则会报错。

查看从库的参数Ongoing_anonymous_transaction_count参数值是否为0，如果为0，意味着没有等待的事务，可以直接进行后面的操作
root@sakila 12:29: [test]> show global status like '%ongoing_anonymous%';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0 |
+-------------------------------------+-------+
1 row in set (0.01 sec)

说明没有等待提交的事务，可以进入后面的操作。

再次核对GTID相关参数状态：
主服务器：
root@sakila 12:24: [test]> 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 | b8439fb9-4f22-11e8-a24e-000c291a6b52:1-92 |
| session_track_gtids | OFF |
+----------------------------------+-------------------------------------------+
8 rows in set (0.06 sec)

从服务器：
root@sakila 12:29: [test]> 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 | b8439fb9-4f22-11e8-a24e-000c291a6b52:1-95 |
| session_track_gtids | OFF |
+----------------------------------+-------------------------------------------+
8 rows in set (0.01 sec)

把传统的复制模式改为GTID模式要把传统的复制停掉，然后执行stop slave，然后执行change master to master_auto_position=1

停止slave，查看当前主从状态：

root@sakila 12:35:  [test]> stop slave;

Query OK, 0 rows affected (0.01 sec)

root@sakila 12:35:  [test]> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State:

                  Master_Host: 10.18.10.11

                  Master_User: repluser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: on.000018

          Read_Master_Log_Pos: 194

               Relay_Log_File: slave-relay-bin.000014

                Relay_Log_Pos: 353

        Relay_Master_Log_File: on.000018

             Slave_IO_Running: No

            Slave_SQL_Running: No

              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: 194

              Relay_Log_Space: 601

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 3306100

                  Master_UUID: b8439fb9-4f22-11e8-a24e-000c291a6b52

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State:

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-103

                Auto_Position: 0

         Replicate_Rewrite_db:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)

root@sakila 12:36: [test]> change master to master_auto_position=1;
Query OK, 0 rows affected (0.02 sec)
root@sakila 12:36: [test]> start slave;
Query OK, 0 rows affected (0.00 sec)

root@sakila 12:37:  [test]> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.18.10.11

                  Master_User: repluser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: on.000018

          Read_Master_Log_Pos: 194

               Relay_Log_File: slave-relay-bin.000002

                Relay_Log_Pos: 353

        Relay_Master_Log_File: on.000018

             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: 194

              Relay_Log_Space: 561

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 3306100

                  Master_UUID: b8439fb9-4f22-11e8-a24e-000c291a6b52

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-103

                Auto_Position: 1

         Replicate_Rewrite_db:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)

验证切换是否成功，主库插入数据:
root@sakila 12:30: [test]> insert into tt (name,score) values('gtid',82);
Query OK, 1 row affected (0.00 sec)
root@sakila 12:38: [test]> insert into tt (name,score) values('gtid_on',85);
Query OK, 1 row affected (0.00 sec)
root@sakila 12:38: [test]> select * from tt;
+----+---------+-------+
| id | name | score |
+----+---------+-------+
| 1 | Tome | 80 |
| 2 | Janne | 90 |
| 3 | Kases | 84 |
| 4 | kids | 99 |
| 5 | MySQL | 82 |
| 6 | gtid | 82 |
| 7 | gtid_on | 85 |
+----+---------+-------+
7 rows in set (0.01 sec)

从库查看数据和Excuted_Gtid_Set对应值是否发生变化：
root@sakila 12:37: [test]> select * from tt;
+----+---------+-------+
| id | name | score |
+----+---------+-------+
| 1 | Tome | 80 |
| 2 | Janne | 90 |
| 3 | Kases | 84 |
| 4 | kids | 99 |
| 5 | MySQL | 82 |
| 6 | gtid | 82 |
| 7 | gtid_on | 85 |
+----+---------+-------+
7 rows in set (0.00 sec)

root@sakila 12:40: [test]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.18.10.11
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: on.000018
Read_Master_Log_Pos: 729
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 888
Relay_Master_Log_File: on.000018
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_db:
Replicate_Ignore_db:
--------省略部分---------------------
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:104-105
Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-105
Auto_Position: 1
Replicate_Rewrite_db:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

通过上述查询，发现数据已经复制过来，说明数据同步成功，而Excuted_Gtid_Set的值由“ b8439fb9-4f22-11e8-a24e-000c291a6b52:1-103” 变换为：“b8439fb9-4f22-11e8-a24e-000c291a6b52:1-105”，说明切换成功，因为GTID的值增加了 ，证明开启了GTID的复制方式。

　　MySQL的传统复制方式和GTID方式互相切换演示完成，需要注意的是gtid_mode的值虽然支持动态修改，但是在修改时不能跳跃式的修改，必须得严格按照顺序修改。

参考：https://www.cnblogs.com/kindnull/p/9061968.html

最新文章

1. Netruon 理解（12）：使用 Linux bridge 将 Linux network namespace 连接外网
2. XproerIM V1,2,12,65376 发布。
3. Struts2笔记——struts常用标签
4. Curl 采集乱码 gzip 原因及解决方案 utf-8
5. POOL
6. Python 一路走来 DOM & Jquery
7. LEK-Introduction-Installation-Usage-new
8. Spring入门学习(一)
9. 学习笔记——Java类和对象
10. java中的==、equals()、hashCode()源码分析
11. HDU1027 Ignatius and the Princess II
12. QtWebEngine加载资料缓慢
13. iOS音频格式PCM转G711u(或G711a-law)
14. Docker从零构建php-nginx-alpine镜像
15. OpenFlow学习笔记
16. log4j日志输出到日志文件中和控制台中 +log4j配置详解
17. codeforces 161D Distance in Tree 树上点分治
18. qt 提高图片加载速度
19. 11.8 开课二个月零四天 （Jquery）
20. 【转】qt ，使用tcp/ip协议网络传输数据时，字节序转换方法

热门文章

1. windows上zeal安装和使用--离线API文档
2. 8. golang 基本类型转换
3. go & RabbitMQ
4. 锋利的JS解读——认识JQuery（一）
5. vue 使用element-ui中的Notification自定义按钮并实现关闭功能以及如何处理多个通知
6. element ui 选择期 传对象
7. 第七讲 自定义Realm实现授权
8. [转载]转一篇Systemverilog的一个牛人总结
9. 牛客OI周赛11-普及组 B Game with numbers (数学，预处理真因子)
10. Java 学习输入1234 求和