主从复制环境：
主库：192.168.0.100
从库：192.168.0.101
MySQL5.7.22
当前主从状态展示如下：
1 root@db 06:32: [(none)]>show slave status\G2 *************************** 1. row ***************************
3 Slave_IO_State: Waiting formaster to send event4 Master_Host: 192.168.0.100
5 Master_User: repluser6 Master_Port: 3306
7 Connect_Retry: 60
8 Master_Log_File: on.000001
9 Read_Master_Log_Pos: 1480
10 Relay_Log_File: node02-relay-bin.000002
11 Relay_Log_Pos: 1098
12 Relay_Master_Log_File: on.000001
13 Slave_IO_Running: Yes14 Slave_SQL_Running: Yes15 Replicate_Do_DB:16 Replicate_Ignore_DB:17 Replicate_Do_Table:18 Replicate_Ignore_Table:19 Replicate_Wild_Do_Table:20 Replicate_Wild_Ignore_Table:21 Last_Errno: 0
22 Last_Error:23 Skip_Counter: 0
24 Exec_Master_Log_Pos: 1480
25 Relay_Log_Space: 1306
26 Until_Condition: None27 Until_Log_File:28 Until_Log_Pos: 0
29 Master_SSL_Allowed: No30 Master_SSL_CA_File:31 Master_SSL_CA_Path:32 Master_SSL_Cert:33 Master_SSL_Cipher:34 Master_SSL_Key:35 Seconds_Behind_Master: 0
36 Master_SSL_Verify_Server_Cert: No37 Last_IO_Errno: 0
38 Last_IO_Error:39 Last_SQL_Errno: 0
40 Last_SQL_Error:41 Replicate_Ignore_Server_Ids:42 Master_Server_Id: 3306100
43 Master_UUID: b8439fb9-4f22-11e8-a24e-000c291a6b5244 Master_Info_File: mysql.slave_master_info45 SQL_Delay: 0
46 SQL_Remaining_Delay: NULL47 Slave_SQL_Running_State: Slave has read all relay log; waiting for moreupdates48 Master_Retry_Count: 86400
49 Master_Bind:50 Last_IO_Error_Timestamp:51 Last_SQL_Error_Timestamp:52 Master_SSL_Crl:53 Master_SSL_Crlpath:54 Retrieved_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:96-98
55 Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-98
56 Auto_Position: 1
57 Replicate_Rewrite_DB:58 Channel_Name:59 Master_TLS_Version:60 1 row in set (0.00sec)61
62 root@db 06:32: [(none)]>
操作过程如下：
(1) 先在从库中执行 stop slave，停止主从复制，然后调整为传统复制模式，让master_auto_position=0
1 root@db 07:04: [test]>stop slave;2 Query OK, 0 rows affected (0.01sec)3
4 root@db 07:04: [test]>
5
6 root@db 10:39: [test]> CHANGE MASTER TO master_auto_position=0,Master_Host='192.168.0.100',MASTER_USER='repluser',MASTER_PASSWORD='rep123',Master_Log_File='on.000001',MASTER_LOG_POS=2280;7 Query OK, 0 rows affected, 2 warnings (0.03sec)8
9 root@db 10:42: [test]>
然后开启slave复制功能
1 root@db 10:46: [test]>start slave;2 Query OK, 0 rows affected (0.01sec)3
4 root@db 10:47: [test]>show slave status\G5 *************************** 1. row ***************************
6 Slave_IO_State: Waiting formaster to send event7 Master_Host: 192.168.0.100
8 Master_User: repluser9 Master_Port: 3306
10 Connect_Retry: 60
11 Master_Log_File: on.000003
12 Read_Master_Log_Pos: 194
13 Relay_Log_File: node02-relay-bin.000006
14 Relay_Log_Pos: 353
15 Relay_Master_Log_File: on.000003
16 Slave_IO_Running: Yes17 Slave_SQL_Running: Yes18 Replicate_Do_DB:19 Replicate_Ignore_DB:20 Replicate_Do_Table:21 Replicate_Ignore_Table:22 Replicate_Wild_Do_Table:23 Replicate_Wild_Ignore_Table:24 Last_Errno: 0
25 Last_Error:26 Skip_Counter: 0
27 Exec_Master_Log_Pos: 194
28 Relay_Log_Space: 601
29 Until_Condition: None30 Until_Log_File:31 Until_Log_Pos: 0
32 Master_SSL_Allowed: No33 Master_SSL_CA_File:34 Master_SSL_CA_Path:35 Master_SSL_Cert:36 Master_SSL_Cipher:37 Master_SSL_Key:38 Seconds_Behind_Master: 0
39 Master_SSL_Verify_Server_Cert: No40 Last_IO_Errno: 0
41 Last_IO_Error:42 Last_SQL_Errno: 0
43 Last_SQL_Error:44 Replicate_Ignore_Server_Ids:45 Master_Server_Id: 3306100
46 Master_UUID: b8439fb9-4f22-11e8-a24e-000c291a6b5247 Master_Info_File: mysql.slave_master_info48 SQL_Delay: 0
49 SQL_Remaining_Delay: NULL50 Slave_SQL_Running_State: Slave has read all relay log; waiting for moreupdates51 Master_Retry_Count: 86400
52 Master_Bind:53 Last_IO_Error_Timestamp:54 Last_SQL_Error_Timestamp:55 Master_SSL_Crl:56 Master_SSL_Crlpath:57 Retrieved_Gtid_Set:58 Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-101
59 Auto_Position: 0
60 Replicate_Rewrite_DB:61 Channel_Name:62 Master_TLS_Version:63 1 row in set (0.00sec)64
65 root@db 10:47: [test]>
主从服务器上同时调整GTID模式为on_permissive
主服务器上：
1 root@db 10:45: [test]> set global gtid_mode=on_permissive;2 Query OK, 0 rows affected (0.00sec)3
4 root@db 10:45: [test]>
从服务器上：
1 root@db 10:42: [test]> set global gtid_mode=on_permissive;2 Query OK, 0 rows affected (0.02sec)3
4 root@db 10:46: [test]>
需要在主从服务器上关闭GTID功能
主服务器：
root@db 11:18: [test]> set global enforce_gtid_consistency=off;
Query OK,0 rows affected (0.00sec)
root@db11:18: [test]> set global gtid_mode=off;
ERROR1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF OFF_PERMISSIVE ON_PERMISSIVE ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual forinstructions.
root@db11:19: [test]>
从服务器：
root@db 11:19: [test]> set global enforce_gtid_consistency=off;
Query OK,0 rows affected (0.00sec)
root@db11:21: [test]> set global gtid_mode=off;
ERROR1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF OFF_PERMISSIVE ON_PERMISSIVE ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual forinstructions.
root@db11:21: [test]>
发现主从服务器执行set global gitd_mode=off时报错，因为我在前面说过gtid_mode的值有有四种状态(off、off_permissive、on_permissive,on)，虽然四种状态支持动态修改，但是不能跳跃修改，即只能严格依次顺序修改，
测试切换是否成功，在主库中插入数据进行测试：
1 root@db 11:24: [test]>desc tt;2 +-------+-------------+------+-----+---------+----------------+
3 | Field | Type | Null | Key | Default | Extra |
4 +-------+-------------+------+-----+---------+----------------+
5 | id | int(11) | NO | PRI | NULL | auto_increment |
6 | name | varchar(20) | YES | MUL | NULL | |
7 | score | int(10) | YES | | NULL | |
8 +-------+-------------+------+-----+---------+----------------+
9 3 rows in set (0.00sec)10
11 root@db 11:24: [test]> insert into tt (name,score) values ('kids',99);12 Query OK, 1 row affected (0.01sec)13
14 root@db 11:25: [test]> select *from tt;15 +----+-------+-------+
16 | id | name | score |
17 +----+-------+-------+
18 | 1 | Tome | 80 |
19 | 2 | Janne | 90 |
20 | 3 | Kases | 84 |
21 | 4 | kids | 99 |
22 +----+-------+-------+
23 4 rows in set (0.00sec)24
25 root@db 11:25: [test]>
从库查看：
1 root@db 11:17: [test]>show slave status\G2 ERROR 2006(HY000): MySQL server has gone away3 No connection. Trying to reconnect...4 Connection id: 14
5 Current database: test6
7 *************************** 1. row ***************************
8 Slave_IO_State: Waiting formaster to send event9 Master_Host: 192.168.0.100
10 Master_User: repluser11 Master_Port: 3306
12 Connect_Retry: 60
13 Master_Log_File: on.000003
14 Read_Master_Log_Pos: 194
15 Relay_Log_File: node02-relay-bin.000006
16 Relay_Log_Pos: 353
17 Relay_Master_Log_File: on.000003
18 Slave_IO_Running: Yes19 Slave_SQL_Running: Yes20 Replicate_Do_DB:21 Replicate_Ignore_DB:22 Replicate_Do_Table:23 Replicate_Ignore_Table:24 Replicate_Wild_Do_Table:25 Replicate_Wild_Ignore_Table:26 Last_Errno: 0
27 Last_Error:28 Skip_Counter: 0
29 Exec_Master_Log_Pos: 194
30 Relay_Log_Space: 601
31 Until_Condition: None32 Until_Log_File:33 Until_Log_Pos: 0
34 Master_SSL_Allowed: No35 Master_SSL_CA_File:36 Master_SSL_CA_Path:37 Master_SSL_Cert:38 Master_SSL_Cipher:39 Master_SSL_Key:40 Seconds_Behind_Master: 0
41 Master_SSL_Verify_Server_Cert: No42 Last_IO_Errno: 0
43 Last_IO_Error:44 Last_SQL_Errno: 0
45 Last_SQL_Error:46 Replicate_Ignore_Server_Ids:47 Master_Server_Id: 3306100
48 Master_UUID: b8439fb9-4f22-11e8-a24e-000c291a6b5249 Master_Info_File: mysql.slave_master_info50 SQL_Delay: 0
51 SQL_Remaining_Delay: NULL52 Slave_SQL_Running_State: Slave has read all relay log; waiting for moreupdates53 Master_Retry_Count: 86400
54 Master_Bind:55 Last_IO_Error_Timestamp:56 Last_SQL_Error_Timestamp:57 Master_SSL_Crl:58 Master_SSL_Crlpath:59 Retrieved_Gtid_Set:60 Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-101
61 Auto_Position: 0
62 Replicate_Rewrite_DB:63 Channel_Name:64 Master_TLS_Version:65 1 row in set (0.00sec)66
67 root@db 11:17: [test]> set global enforce_gtid_consistency=off;68 Query OK, 0 rows affected (0.00sec)69
70 root@db 11:19: [test]>
71
72 root@db 11:25: [test]>show slave status\G73 *************************** 1. row ***************************
74 Slave_IO_State: Waiting formaster to send event75 Master_Host: 192.168.0.100
76 Master_User: repluser77 Master_Port: 3306
78 Connect_Retry: 60
79 Master_Log_File: on.000003
80 Read_Master_Log_Pos: 460
81 Relay_Log_File: node02-relay-bin.000006
82 Relay_Log_Pos: 619
83 Relay_Master_Log_File: on.000003
84 Slave_IO_Running: Yes85 Slave_SQL_Running: Yes86 Replicate_Do_DB:87 Replicate_Ignore_DB:88 Replicate_Do_Table:89 Replicate_Ignore_Table:90 Replicate_Wild_Do_Table:91 Replicate_Wild_Ignore_Table:92 Last_Errno: 0
93 Last_Error:94 Skip_Counter: 0
95 Exec_Master_Log_Pos: 460
96 Relay_Log_Space: 867
97 Until_Condition: None98 Until_Log_File:99 Until_Log_Pos: 0
100 Master_SSL_Allowed: No101 Master_SSL_CA_File:102 Master_SSL_CA_Path:103 Master_SSL_Cert:104 Master_SSL_Cipher:105 Master_SSL_Key:106 Seconds_Behind_Master: 0
107 Master_SSL_Verify_Server_Cert: No108 Last_IO_Errno: 0
109 Last_IO_Error:110 Last_SQL_Errno: 0
111 Last_SQL_Error:112 Replicate_Ignore_Server_Ids:113 Master_Server_Id: 3306100
114 Master_UUID: b8439fb9-4f22-11e8-a24e-000c291a6b52115 Master_Info_File: mysql.slave_master_info116 SQL_Delay: 0
117 SQL_Remaining_Delay: NULL118 Slave_SQL_Running_State: Slave has read all relay log; waiting for moreupdates119 Master_Retry_Count: 86400
120 Master_Bind:121 Last_IO_Error_Timestamp:122 Last_SQL_Error_Timestamp:123 Master_SSL_Crl:124 Master_SSL_Crlpath:125 Retrieved_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:102
126 Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-102
127 Auto_Position: 0
128 Replicate_Rewrite_DB:129 Channel_Name:130 Master_TLS_Version:131 1 row in set (0.00sec)132
133 root@db 11:25: [test]>
发现插入前后Excuted_Gtid_Set发生变化，说明我们这里切换没有成功，接下来我们把参数严格按照顺序修改，再进行测试：
切换之前我们查看下主从gitd_mode参数值：
主服务器：gtid_mode值
1 root@db 11:45: [(none)]> show variables like '%gtid_mode%';2 +---------------+-------+
3 | Variable_name | Value |
4 +---------------+-------+
5 | gtid_mode | ON |
6 +---------------+-------+
7 1 row in set (0.01sec)8
9 root@db 11:46: [(none)]>
从服务器gitd_mode值：
1 root@db 11:44: [(none)]> show variables like '%gtid_mode%';2 +---------------+-------+
3 | Variable_name | Value |
4 +---------------+-------+
5 | gtid_mode | ON |
6 +---------------+-------+
7 1 row in set (0.01sec)8
9 root@db 11:47: [(none)]>
1 root@db 11:47: [(none)]>show slave status\G2 *************************** 1. row ***************************
3 Slave_IO_State: Waiting formaster to send event4 Master_Host: 192.168.0.100
5 Master_User: repluser6 Master_Port: 3306
7 Connect_Retry: 60
8 Master_Log_File: on.000012
9 Read_Master_Log_Pos: 346
10 Relay_Log_File: node02-relay-bin.000024
11 Relay_Log_Pos: 505
12 Relay_Master_Log_File: on.000012
13 Slave_IO_Running: Yes14 Slave_SQL_Running: Yes15 Replicate_Do_DB:16 Replicate_Ignore_DB:17 Replicate_Do_Table:18 Replicate_Ignore_Table:19 Replicate_Wild_Do_Table:20 Replicate_Wild_Ignore_Table:21 Last_Errno: 0
22 Last_Error:23 Skip_Counter: 0
24 Exec_Master_Log_Pos: 346
25 Relay_Log_Space: 753
26 Until_Condition: None27 Until_Log_File:28 Until_Log_Pos: 0
29 Master_SSL_Allowed: No30 Master_SSL_CA_File:31 Master_SSL_CA_Path:32 Master_SSL_Cert:33 Master_SSL_Cipher:34 Master_SSL_Key:35 Seconds_Behind_Master: 0
36 Master_SSL_Verify_Server_Cert: No37 Last_IO_Errno: 0
38 Last_IO_Error:39 Last_SQL_Errno: 0
40 Last_SQL_Error:41 Replicate_Ignore_Server_Ids:42 Master_Server_Id: 3306100
43 Master_UUID: b8439fb9-4f22-11e8-a24e-000c291a6b5244 Master_Info_File: mysql.slave_master_info45 SQL_Delay: 0
46 SQL_Remaining_Delay: NULL47 Slave_SQL_Running_State: Slave has read all relay log; waiting for moreupdates48 Master_Retry_Count: 86400
49 Master_Bind:50 Last_IO_Error_Timestamp:51 Last_SQL_Error_Timestamp:52 Master_SSL_Crl:53 Master_SSL_Crlpath:54 Retrieved_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:103
55 Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-103
56 Auto_Position: 0
57 Replicate_Rewrite_DB:58 Channel_Name:59 Master_TLS_Version:60 1 row in set (0.00sec)61
62 root@db 11:48: [(none)]>
重复以前的步骤，先在从库上关闭slave，停止主从复制，然后修改复制模式为传统模式，master_auto_position=0
1 root@db 11:48: [(none)]>stop slave;2 Query OK, 0 rows affected (0.00sec)3
4 root@db 11:50: [(none)]> CHANGE MASTER TO master_auto_position=0,Master_Host='192.168.0.100',MASTER_USER='repluser',MASTER_PASSWORD='rep123',Master_Log_File='on.000012',MASTER_LOG_POS=346;5 Query OK, 0 rows affected, 2 warnings (0.03sec)6
7 root@db 11:52: [(none)]>start slave;8 Query OK, 0 rows affected (0.01sec)9
10 root@db 11:53: [(none)]> set global gtid_mode=on_permissive;11 Query OK, 0 rows affected (0.01sec)12
13 root@db 11:53: [(none)]> set global gtid_mode=off_permissive;14 Query OK, 0 rows affected (0.01sec)15
16 root@db 11:54: [(none)]> set global gtid_mode=off;17 Query OK, 0 rows affected (0.01sec)18
19 root@db 11:55: [(none)]> set global enforce_gtid_consistency=off;20 Query OK, 0 rows affected (0.00sec)21
22 root@db 11:55: [(none)]>
root@db 11:55: [(none)]> show variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | OFF |
+---------------+-------+
1 row in set (0.00 sec)
root@db 11:58: [(none)]>
同样主库做如下操作：
1 root@db 11:45: [(none)]> show variables like '%gtid_mode%';2 +---------------+-------+
3 | Variable_name | Value |
4 +---------------+-------+
5 | gtid_mode | ON |
6 +---------------+-------+
7 1 row in set (0.01sec)8
9 root@db 11:46: [(none)]> set global gtid_mode=on_permissive;10 Query OK, 0 rows affected (0.02sec)11
12 root@db 11:53: [(none)]> set global gtid_mode=off_permissive;13 Query OK, 0 rows affected (0.01sec)14
15 root@db 11:54: [(none)]> set global gtid_mode=off;16 Query OK, 0 rows affected (0.02sec)17
18 root@db 11:55: [(none)]> set global enforce_gtid_consistency=off;19 Query OK, 0 rows affected (0.00sec)20
21 root@db 11:55: [(none)]> show variables like '%gtid_mode%';22 +---------------+-------+
23 | Variable_name | Value |
24 +---------------+-------+
25 | gtid_mode | OFF |
26 +---------------+-------+
27 1 row in set (0.00sec)28
29 root@db 11:57: [(none)]>
两边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:
在主库插入数据：
1 root@db 12:11: [test]> select *from tt;2 +----+-------+-------+
3 | id | name | score |
4 +----+-------+-------+
5 | 1 | Tome | 80 |
6 | 2 | Janne | 90 |
7 | 3 | Kases | 84 |
8 | 4 | kids | 99 |
9 +----+-------+-------+
10 4 rows in set (0.00sec)11 root@db 12:12: [test]> insert into tt (name,score) values('MySQL',82);12 Query OK, 1 row affected (0.01sec)13
14 root@db 12:13: [test]> select *from tt;15 +----+-------+-------+
16 | id | name | score |
17 +----+-------+-------+
18 | 1 | Tome | 80 |
19 | 2 | Janne | 90 |
20 | 3 | Kases | 84 |
21 | 4 | kids | 99 |
22 | 5 | MySQL | 82 |
23 +----+-------+-------+
24 5 rows in set (0.00sec)25
26 root@db 12:13: [test]>
从库查看插入数据：
1 root@db 12:06: [(none)]>use test2 Database changed3 root@db 12:14: [test]> select *from tt;4 +----+-------+-------+
5 | id | name | score |
6 +----+-------+-------+
7 | 1 | Tome | 80 |
8 | 2 | Janne | 90 |
9 | 3 | Kases | 84 |
10 | 4 | kids | 99 |
11 | 5 | MySQL | 82 |
12 +----+-------+-------+
13 5 rows in set (0.00sec)14
15 root@db 12:14: [test]>
发现数据已经过来了 ，再查看Excuted_Gtid_Set的值。
root@db 12:14: [test]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.100
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: on.000015
Read_Master_Log_Pos: 461
Relay_Log_File: node02-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中不出现警告信息，如果有需要先修复。
主库：
1 root@db 12:13: [test]> set global enforce_gtid_consistency=warn;2 Query OK, 0 rows affected (0.00sec)3
4 root@db 12:19: [test]> show variables like '%enforce_gtid_consistency%';5 +--------------------------+-------+
6 | Variable_name | Value |
7 +--------------------------+-------+
8 | enforce_gtid_consistency | WARN |
9 +--------------------------+-------+
10 1 row in set (0.01sec)11
12 root@db 12:20: [test]>
从库：
1 root@db 12:18: [test]> set global enforce_gtid_consistency=warn;2 Query OK, 0 rows affected (0.00sec)3
4 root@db 12:19: [test]> show variables like '%enforce_gtid_consistency%';5 +--------------------------+-------+
6 | Variable_name | Value |
7 +--------------------------+-------+
8 | enforce_gtid_consistency | WARN |
9 +--------------------------+-------+
10 1 row in set (0.01sec)11
12 root@db 12:20: [test]>
在主从库上调整enforce_gtid_consistency=on,保证GTID的一致性
主库：
1 root@db 12:20: [test]> set global enforce_gtid_consistency=on;2 Query OK, 0 rows affected (0.00sec)3
4 root@db 12:21: [test]> show variables like '%enforce_gtid_consistency%';5 +--------------------------+-------+
6 | Variable_name | Value |
7 +--------------------------+-------+
8 | enforce_gtid_consistency | ON |
9 +--------------------------+-------+
10 1 row in set (0.01sec)11
12 root@db 12:21: [test]>
从库：
1 root@db 12:20: [test]> set global enforce_gtid_consistency=on;2 Query OK, 0 rows affected (0.00sec)3
4 root@db 12:22: [test]> show variables like '%enforce_gtid_consistency%';5 +--------------------------+-------+
6 | Variable_name | Value |
7 +--------------------------+-------+
8 | enforce_gtid_consistency | ON |
9 +--------------------------+-------+
10 1 row in set (0.01sec)11
12 root@db 12:22: [test]>
然后在主从库安装前面的顺序再一次修改回去，直至gtid_mode=on为止
主库操作：
1 root@db 12:21: [test]> set global gtid_mode=off_permissive;2 Query OK, 0 rows affected (0.02sec)3
4 root@db 12:24: [test]> set global gtid_mode=on_permissive;5 Query OK, 0 rows affected (0.01sec)6
7 root@db 12:24: [test]> set global gtid_mode=on;8 Query OK, 0 rows affected (0.01sec)9
10 root@db 12:24: [test]> show variables like '%gtid_mode%';11 +---------------+-------+
12 | Variable_name | Value |
13 +---------------+-------+
14 | gtid_mode | ON |
15 +---------------+-------+
16 1 row in set (0.00sec)17
18 root@db 12:24: [test]>
从库操作：
1 root@db 12:22: [test]> set global gtid_mode=off_permissive;2 Query OK, 0 rows affected (0.01sec)3
4 root@db 12:25: [test]> set global gtid_mode=on_permissive;5 Query OK, 0 rows affected (0.01sec)6
7 root@db 12:25: [test]> set global gtid_mode=on;8 Query OK, 0 rows affected (0.01sec)9
10 root@db 12:25: [test]> show variables like '%gtid_mode%';11 +---------------+-------+
12 | Variable_name | Value |
13 +---------------+-------+
14 | gtid_mode | ON |
15 +---------------+-------+
16 1 row in set (0.01sec)17
18 root@db 12:26: [test]>
提示：上述gtid_mode参数值修改必须严格安装顺序依次修改，否则会报错。
查看从库的参数Ongoing_anonymous_transaction_count参数值是否为0，如果为0，意味着没有等待的事务，可以直接进行后面的操作
1 root@db 12:29: [test]> show global status like '%ongoing_anonymous%';2 +-------------------------------------+-------+
3 | Variable_name | Value |
4 +-------------------------------------+-------+
5 | Ongoing_anonymous_transaction_count | 0 |
6 +-------------------------------------+-------+
7 1 row in set (0.01sec)8
9 root@db 12:29: [test]>
说明没有等待提交的事务，可以进入后面的操作。
再次核对GTID相关参数状态：
主服务器：
1 root@db 12:24: [test]> show variables like '%gtid%';2 +----------------------------------+-------------------------------------------+
3 | Variable_name | Value |
4 +----------------------------------+-------------------------------------------+
5 | binlog_gtid_simple_recovery | ON |
6 | enforce_gtid_consistency | ON |
7 | gtid_executed_compression_period | 1000 |
8 | gtid_mode | ON |
9 | gtid_next | AUTOMATIC |
10 | gtid_owned | |
11 | gtid_purged | b8439fb9-4f22-11e8-a24e-000c291a6b52:1-92 |
12 | session_track_gtids | OFF |
13 +----------------------------------+-------------------------------------------+
14 8 rows in set (0.06sec)15
16 root@db 12:30: [test]>
从服务器：
1 root@db 12:29: [test]> show variables like '%gtid%';2 +----------------------------------+-------------------------------------------+
3 | Variable_name | Value |
4 +----------------------------------+-------------------------------------------+
5 | binlog_gtid_simple_recovery | ON |
6 | enforce_gtid_consistency | ON |
7 | gtid_executed_compression_period | 1000 |
8 | gtid_mode | ON |
9 | gtid_next | AUTOMATIC |
10 | gtid_owned | |
11 | gtid_purged | b8439fb9-4f22-11e8-a24e-000c291a6b52:1-95 |
12 | session_track_gtids | OFF |
13 +----------------------------------+-------------------------------------------+
14 8 rows in set (0.01sec)15
16 root@db 12:31: [test]>
我们把传统的复制模式改为GTID模式要把传统的复制停掉，然后执行stop slave，然后执行change master to master_auto_position=1
停止slave，查看当前主从状态：
1 root@db 12:35: [test]>stop slave;2 Query OK, 0 rows affected (0.01sec)3
4 root@db 12:35: [test]>show slave status\G5 *************************** 1. row ***************************
6 Slave_IO_State:7 Master_Host: 192.168.0.100
8 Master_User: repluser9 Master_Port: 3306
10 Connect_Retry: 60
11 Master_Log_File: on.000018
12 Read_Master_Log_Pos: 194
13 Relay_Log_File: node02-relay-bin.000014
14 Relay_Log_Pos: 353
15 Relay_Master_Log_File: on.000018
16 Slave_IO_Running: No17 Slave_SQL_Running: No18 Replicate_Do_DB:19 Replicate_Ignore_DB:20 Replicate_Do_Table:21 Replicate_Ignore_Table:22 Replicate_Wild_Do_Table:23 Replicate_Wild_Ignore_Table:24 Last_Errno: 0
25 Last_Error:26 Skip_Counter: 0
27 Exec_Master_Log_Pos: 194
28 Relay_Log_Space: 601
29 Until_Condition: None30 Until_Log_File:31 Until_Log_Pos: 0
32 Master_SSL_Allowed: No33 Master_SSL_CA_File:34 Master_SSL_CA_Path:35 Master_SSL_Cert:36 Master_SSL_Cipher:37 Master_SSL_Key:38 Seconds_Behind_Master: NULL39 Master_SSL_Verify_Server_Cert: No40 Last_IO_Errno: 0
41 Last_IO_Error:42 Last_SQL_Errno: 0
43 Last_SQL_Error:44 Replicate_Ignore_Server_Ids:45 Master_Server_Id: 3306100
46 Master_UUID: b8439fb9-4f22-11e8-a24e-000c291a6b5247 Master_Info_File: mysql.slave_master_info48 SQL_Delay: 0
49 SQL_Remaining_Delay: NULL50 Slave_SQL_Running_State:51 Master_Retry_Count: 86400
52 Master_Bind:53 Last_IO_Error_Timestamp:54 Last_SQL_Error_Timestamp:55 Master_SSL_Crl:56 Master_SSL_Crlpath:57 Retrieved_Gtid_Set:58 Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-103
59 Auto_Position: 0
60 Replicate_Rewrite_DB:61 Channel_Name:62 Master_TLS_Version:63 1 row in set (0.00sec)64
65 root@db 12:35: [test]>
执行change master to master_auto_position=1.开启复制状态，start slave
1 root@db 12:36: [test]> change master to master_auto_position=1;2 Query OK, 0 rows affected (0.02sec)3
4 root@db 12:36: [test]>start slave;5 Query OK, 0 rows affected (0.00sec)6
7 root@db 12:37: [test]>show slave status\G8 *************************** 1. row ***************************
9 Slave_IO_State: Waiting formaster to send event10 Master_Host: 192.168.0.100
11 Master_User: repluser12 Master_Port: 3306
13 Connect_Retry: 60
14 Master_Log_File: on.000018
15 Read_Master_Log_Pos: 194
16 Relay_Log_File: node02-relay-bin.000002
17 Relay_Log_Pos: 353
18 Relay_Master_Log_File: on.000018
19 Slave_IO_Running: Yes20 Slave_SQL_Running: Yes21 Replicate_Do_DB:22 Replicate_Ignore_DB:23 Replicate_Do_Table:24 Replicate_Ignore_Table:25 Replicate_Wild_Do_Table:26 Replicate_Wild_Ignore_Table:27 Last_Errno: 0
28 Last_Error:29 Skip_Counter: 0
30 Exec_Master_Log_Pos: 194
31 Relay_Log_Space: 561
32 Until_Condition: None33 Until_Log_File:34 Until_Log_Pos: 0
35 Master_SSL_Allowed: No36 Master_SSL_CA_File:37 Master_SSL_CA_Path:38 Master_SSL_Cert:39 Master_SSL_Cipher:40 Master_SSL_Key:41 Seconds_Behind_Master: 0
42 Master_SSL_Verify_Server_Cert: No43 Last_IO_Errno: 0
44 Last_IO_Error:45 Last_SQL_Errno: 0
46 Last_SQL_Error:47 Replicate_Ignore_Server_Ids:48 Master_Server_Id: 3306100
49 Master_UUID: b8439fb9-4f22-11e8-a24e-000c291a6b5250 Master_Info_File: mysql.slave_master_info51 SQL_Delay: 0
52 SQL_Remaining_Delay: NULL53 Slave_SQL_Running_State: Slave has read all relay log; waiting for moreupdates54 Master_Retry_Count: 86400
55 Master_Bind:56 Last_IO_Error_Timestamp:57 Last_SQL_Error_Timestamp:58 Master_SSL_Crl:59 Master_SSL_Crlpath:60 Retrieved_Gtid_Set:61 Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-103
62 Auto_Position: 1
63 Replicate_Rewrite_DB:64 Channel_Name:65 Master_TLS_Version:66 1 row in set (0.00sec)67
68 root@db 12:37: [test]>
验证切换是否成功，主库插入数据：
1 root@db 12:30: [test]> insert into tt (name,score) values('gtid',82);2 Query OK, 1 row affected (0.00sec)3
4 root@db 12:38: [test]> insert into tt (name,score) values('gtid_on',85);5 Query OK, 1 row affected (0.00sec)6
7 root@db 12:38: [test]> select *from tt;8 +----+---------+-------+
9 | id | name | score |
10 +----+---------+-------+
11 | 1 | Tome | 80 |
12 | 2 | Janne | 90 |
13 | 3 | Kases | 84 |
14 | 4 | kids | 99 |
15 | 5 | MySQL | 82 |
16 | 6 | gtid | 82 |
17 | 7 | gtid_on | 85 |
18 +----+---------+-------+
19 7 rows in set (0.01sec)20
21 root@db 12:38: [test]>
从库查看数据和Excuted_Gtid_Set对应值是否发生变化：
1 root@db 12:37: [test]> select *from tt;2 +----+---------+-------+
3 | id | name | score |
4 +----+---------+-------+
5 | 1 | Tome | 80 |
6 | 2 | Janne | 90 |
7 | 3 | Kases | 84 |
8 | 4 | kids | 99 |
9 | 5 | MySQL | 82 |
10 | 6 | gtid | 82 |
11 | 7 | gtid_on | 85 |
12 +----+---------+-------+
13 7 rows in set (0.00sec)14
15 root@db 12:40: [test]>show slave status\G16 *************************** 1. row ***************************
17 Slave_IO_State: Waiting formaster to send event18 Master_Host: 192.168.0.100
19 Master_User: repluser20 Master_Port: 3306
21 Connect_Retry: 60
22 Master_Log_File: on.000018
23 Read_Master_Log_Pos: 729
24 Relay_Log_File: node02-relay-bin.000002
25 Relay_Log_Pos: 888
26 Relay_Master_Log_File: on.000018
27 Slave_IO_Running: Yes28 Slave_SQL_Running: Yes29 Replicate_Do_DB:30 Replicate_Ignore_DB:31 --------省略部分---------------------
32 Last_IO_Error_Timestamp:33 Last_SQL_Error_Timestamp:34 Master_SSL_Crl:35 Master_SSL_Crlpath:36 Retrieved_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:104-105
37 Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-105
38 Auto_Position: 1
39 Replicate_Rewrite_DB:40 Channel_Name:41 Master_TLS_Version:42 1 row in set (0.00sec)43
44 root@db 12:40: [test]>
通过上述查询，发现数据已经复制过来，说明数据同步成功，而Excuted_Gtid_Set的值由“ b8439fb9-4f22-11e8-a24e-000c291a6b52:1-103” 变换为：“b8439fb9-4f22-11e8-a24e-000c291a6b52:1-105”，说明切换成功，因为GTID的值增加了 ，证明开启了GTID的复制方式。
至此MySQL的传统复制方式和GTID方式互相切换演示完成，需要注意的是gtid_mode的值虽然支持动态修改，但是在修改时不能跳跃式的修改，必须得严格按照顺序修改。
367
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
