主从复制环境：
主库：192.168.0.100
从库：192.168.0.101
MySQL5.7.22
当前主从状态展示如下：
1 root@db 06:32: [(none)]> show slave status\G 2 *************************** 1. row *************************** 3Slave_IO_State: Waiting for master to send event 4 Master_Host: 192.168.0.100 5 Master_User: repluser 6 Master_Port: 3306 7 Connect_Retry: 60 8Master_Log_File: on.000001 9 Read_Master_Log_Pos: 148010Relay_Log_File: node02-relay-bin.00000211 Relay_Log_Pos: 109812 Relay_Master_Log_File: on.00000113 Slave_IO_Running: Yes14 Slave_SQL_Running: Yes15Replicate_Do_DB: 16 Replicate_Ignore_DB: 17 Replicate_Do_Table: 18 Replicate_Ignore_Table: 19 Replicate_Wild_Do_Table: 20 Replicate_Wild_Ignore_Table: 21Last_Errno: 022Last_Error: 23 Skip_Counter: 024 Exec_Master_Log_Pos: 148025Relay_Log_Space: 130626Until_Condition: None27Until_Log_File: 28 Until_Log_Pos: 029 Master_SSL_Allowed: No30 Master_SSL_CA_File: 31 Master_SSL_CA_Path: 32Master_SSL_Cert: 33 Master_SSL_Cipher: 34Master_SSL_Key: 35 Seconds_Behind_Master: 036 Master_SSL_Verify_Server_Cert: No37 Last_IO_Errno: 038 Last_IO_Error: 39Last_SQL_Errno: 040Last_SQL_Error: 41 Replicate_Ignore_Server_Ids: 42 Master_Server_Id: 330610043 Master_UUID: b8439fb9-4f22-11e8-a24e-000c291a6b5244 Master_Info_File: mysql.slave_master_info45 SQL_Delay: 046 SQL_Remaining_Delay: NULL47 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates48 Master_Retry_Count: 8640049 Master_Bind: 50 Last_IO_Error_Timestamp: 51Last_SQL_Error_Timestamp: 52Master_SSL_Crl: 53 Master_SSL_Crlpath: 54 Retrieved_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:96-9855 Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-9856 Auto_Position: 157Replicate_Rewrite_DB: 58 Channel_Name: 59 Master_TLS_Version: 60 1 row in set (0.00 sec)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.01 sec)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.03 sec)8 9 root@db 10:42: [test]>
然后开启slave复制功能
1 root@db 10:46: [test]> start slave; 2 Query OK, 0 rows affected (0.01 sec) 3 4 root@db 10:47: [test]> show slave status\G 5 *************************** 1. row *************************** 6Slave_IO_State: Waiting for master to send event 7 Master_Host: 192.168.0.100 8 Master_User: repluser 9 Master_Port: 330610 Connect_Retry: 6011Master_Log_File: on.00000312 Read_Master_Log_Pos: 19413Relay_Log_File: node02-relay-bin.00000614 Relay_Log_Pos: 35315 Relay_Master_Log_File: on.00000316 Slave_IO_Running: Yes17 Slave_SQL_Running: Yes18Replicate_Do_DB: 19 Replicate_Ignore_DB: 20 Replicate_Do_Table: 21 Replicate_Ignore_Table: 22 Replicate_Wild_Do_Table: 23 Replicate_Wild_Ignore_Table: 24Last_Errno: 025Last_Error: 26 Skip_Counter: 027 Exec_Master_Log_Pos: 19428Relay_Log_Space: 60129Until_Condition: None30Until_Log_File: 31 Until_Log_Pos: 032 Master_SSL_Allowed: No33 Master_SSL_CA_File: 34 Master_SSL_CA_Path: 35Master_SSL_Cert: 36 Master_SSL_Cipher: 37Master_SSL_Key: 38 Seconds_Behind_Master: 039 Master_SSL_Verify_Server_Cert: No40 Last_IO_Errno: 041 Last_IO_Error: 42Last_SQL_Errno: 043Last_SQL_Error: 44 Replicate_Ignore_Server_Ids: 45 Master_Server_Id: 330610046 Master_UUID: b8439fb9-4f22-11e8-a24e-000c291a6b5247 Master_Info_File: mysql.slave_master_info48 SQL_Delay: 049 SQL_Remaining_Delay: NULL50 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates51 Master_Retry_Count: 8640052 Master_Bind: 53 Last_IO_Error_Timestamp: 54Last_SQL_Error_Timestamp: 55Master_SSL_Crl: 56 Master_SSL_Crlpath: 57 Retrieved_Gtid_Set: 58 Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-10159 Auto_Position: 060Replicate_Rewrite_DB: 61 Channel_Name: 62 Master_TLS_Version: 63 1 row in set (0.00 sec)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.00 sec)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.02 sec)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.00 sec)root@db 11:18: [test]> set global gtid_mode=off;ERROR 1788 (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 for instructions.root@db 11:19: [test]>
从服务器：
root@db 11:19: [test]> set global enforce_gtid_consistency=off;Query OK, 0 rows affected (0.00 sec)root@db 11:21: [test]> set global gtid_mode=off;ERROR 1788 (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 for instructions.root@db 11: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.00 sec)10 11 root@db 11:24: [test]> insert into tt (name,score) values ('kids',99);12 Query OK, 1 row affected (0.01 sec)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.00 sec)24 25 root@db 11:25: [test]>
从库查看：
1 root@db 11:17: [test]> show slave status\G 2 ERROR (HY000): MySQL server has gone away 3 No connection. Trying to reconnect... 4 Connection id: 14 5 Current database: test 6 7 *************************** 1. row *************************** 8Slave_IO_State: Waiting for master to send event 9 Master_Host: 192.168.0.100 10 Master_User: repluser 11 Master_Port: 3306 12 Connect_Retry: 60 13Master_Log_File: on.000003 14 Read_Master_Log_Pos: 194 15Relay_Log_File: node02-relay-bin.000006 16 Relay_Log_Pos: 353 17 Relay_Master_Log_File: on.000003 18 Slave_IO_Running: Yes 19 Slave_SQL_Running: Yes 20Replicate_Do_DB: 21 Replicate_Ignore_DB: 22 Replicate_Do_Table: 23 Replicate_Ignore_Table: 24 Replicate_Wild_Do_Table: 25 Replicate_Wild_Ignore_Table: 26Last_Errno: 0 27Last_Error: 28 Skip_Counter: 0 29 Exec_Master_Log_Pos: 194 30Relay_Log_Space: 601 31Until_Condition: None 32Until_Log_File: 33 Until_Log_Pos: 0 34 Master_SSL_Allowed: No 35 Master_SSL_CA_File: 36 Master_SSL_CA_Path: 37Master_SSL_Cert: 38 Master_SSL_Cipher: 39Master_SSL_Key: 40 Seconds_Behind_Master: 0 41 Master_SSL_Verify_Server_Cert: No 42 Last_IO_Errno: 0 43 Last_IO_Error: 44Last_SQL_Errno: 0 45Last_SQL_Error: 46 Replicate_Ignore_Server_Ids: 47 Master_Server_Id: 3306100 48 Master_UUID: b8439fb9-4f22-11e8-a24e-000c291a6b52 49 Master_Info_File: mysql.slave_master_info 50 SQL_Delay: 0 51 SQL_Remaining_Delay: NULL 52 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 53 Master_Retry_Count: 86400 54 Master_Bind: 55 Last_IO_Error_Timestamp: 56Last_SQL_Error_Timestamp: 57Master_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 62Replicate_Rewrite_DB: 63 Channel_Name: 64 Master_TLS_Version: 65 1 row in set (0.00 sec) 66 67 root@db 11:17: [test]> set global enforce_gtid_consistency=off; 68 Query OK, 0 rows affected (0.00 sec) 69 70 root@db 11:19: [test]> 71 72 root@db 11:25: [test]> show slave status\G 73 *************************** 1. row *************************** 74Slave_IO_State: Waiting for master to send event 75 Master_Host: 192.168.0.100 76 Master_User: repluser 77 Master_Port: 3306 78 Connect_Retry: 60 79Master_Log_File: on.000003 80 Read_Master_Log_Pos: 460 81Relay_Log_File: node02-relay-bin.000006 82 Relay_Log_Pos: 619 83 Relay_Master_Log_File: on.000003 84 Slave_IO_Running: Yes 85 Slave_SQL_Running: Yes 86Replicate_Do_DB: 87 Replicate_Ignore_DB: 88 Replicate_Do_Table: 89 Replicate_Ignore_Table: 90 Replicate_Wild_Do_Table: 91 Replicate_Wild_Ignore_Table: 92Last_Errno: 0 93Last_Error: 94 Skip_Counter: 0 95 Exec_Master_Log_Pos: 460 96Relay_Log_Space: 867 97Until_Condition: None 98Until_Log_File: 99 Until_Log_Pos: 0100 Master_SSL_Allowed: No101 Master_SSL_CA_File: 102 Master_SSL_CA_Path: 103Master_SSL_Cert: 104 Master_SSL_Cipher: 105Master_SSL_Key: 106 Seconds_Behind_Master: 0107 Master_SSL_Verify_Server_Cert: No108 Last_IO_Errno: 0109 Last_IO_Error: 110Last_SQL_Errno: 0111Last_SQL_Error: 112 Replicate_Ignore_Server_Ids: 113 Master_Server_Id: 3306100114 Master_UUID: b8439fb9-4f22-11e8-a24e-000c291a6b52115 Master_Info_File: mysql.slave_master_info116 SQL_Delay: 0117 SQL_Remaining_Delay: NULL118 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates119 Master_Retry_Count: 86400120 Master_Bind: 121 Last_IO_Error_Timestamp: 122Last_SQL_Error_Timestamp: 123Master_SSL_Crl: 124 Master_SSL_Crlpath: 125 Retrieved_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:102126 Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-102127 Auto_Position: 0128Replicate_Rewrite_DB: 129 Channel_Name: 130 Master_TLS_Version: 131 1 row in set (0.00 sec)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.01 sec)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.01 sec)8 9 root@db 11:47: [(none)]>
1 root@db 11:47: [(none)]> show slave status\G 2 *************************** 1. row *************************** 3Slave_IO_State: Waiting for master to send event 4 Master_Host: 192.168.0.100 5 Master_User: repluser 6 Master_Port: 3306 7 Connect_Retry: 60 8Master_Log_File: on.000012 9 Read_Master_Log_Pos: 34610Relay_Log_File: node02-relay-bin.00002411 Relay_Log_Pos: 50512 Relay_Master_Log_File: on.00001213 Slave_IO_Running: Yes14 Slave_SQL_Running: Yes15Replicate_Do_DB: 16 Replicate_Ignore_DB: 17 Replicate_Do_Table: 18 Replicate_Ignore_Table: 19 Replicate_Wild_Do_Table: 20 Replicate_Wild_Ignore_Table: 21Last_Errno: 022Last_Error: 23 Skip_Counter: 024 Exec_Master_Log_Pos: 34625Relay_Log_Space: 75326Until_Condition: None27Until_Log_File: 28 Until_Log_Pos: 029 Master_SSL_Allowed: No30 Master_SSL_CA_File: 31 Master_SSL_CA_Path: 32Master_SSL_Cert: 33 Master_SSL_Cipher: 34Master_SSL_Key: 35 Seconds_Behind_Master: 036 Master_SSL_Verify_Server_Cert: No37 Last_IO_Errno: 038 Last_IO_Error: 39Last_SQL_Errno: 040Last_SQL_Error: 41 Replicate_Ignore_Server_Ids: 42 Master_Server_Id: 330610043 Master_UUID: b8439fb9-4f22-11e8-a24e-000c291a6b5244 Master_Info_File: mysql.slave_master_info45 SQL_Delay: 046 SQL_Remaining_Delay: NULL47 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates48 Master_Retry_Count: 8640049 Master_Bind: 50 Last_IO_Error_Timestamp: 51Last_SQL_Error_Timestamp: 52Master_SSL_Crl: 53 Master_SSL_Crlpath: 54 Retrieved_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:10355 Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-10356 Auto_Position: 057Replicate_Rewrite_DB: 58 Channel_Name: 59 Master_TLS_Version: 60 1 row in set (0.00 sec)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.00 sec) 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.03 sec) 6 7 root@db 11:52: [(none)]> start slave; 8 Query OK, 0 rows affected (0.01 sec) 9 10 root@db 11:53: [(none)]> set global gtid_mode=on_permissive;11 Query OK, 0 rows affected (0.01 sec)12 13 root@db 11:53: [(none)]> set global gtid_mode=off_permissive;14 Query OK, 0 rows affected (0.01 sec)15 16 root@db 11:54: [(none)]> set global gtid_mode=off;17 Query OK, 0 rows affected (0.01 sec)18 19 root@db 11:55: [(none)]> set global enforce_gtid_consistency=off;20 Query OK, 0 rows affected (0.00 sec)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.01 sec) 8 9 root@db 11:46: [(none)]> set global gtid_mode=on_permissive;10 Query OK, 0 rows affected (0.02 sec)11 12 root@db 11:53: [(none)]> set global gtid_mode=off_permissive;13 Query OK, 0 rows affected (0.01 sec)14 15 root@db 11:54: [(none)]> set global gtid_mode=off;16 Query OK, 0 rows affected (0.02 sec)17 18 root@db 11:55: [(none)]> set global enforce_gtid_consistency=off;19 Query OK, 0 rows affected (0.00 sec)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.00 sec)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.00 sec)11 root@db 12:12: [test]> insert into tt (name,score) values('MySQL',82);12 Query OK, 1 row affected (0.01 sec)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.00 sec)25 26 root@db 12:13: [test]>
从库查看插入数据：
1 root@db 12:06: [(none)]> use test 2 Database changed 3 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.00 sec)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.00 sec) 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.01 sec)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.00 sec) 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.01 sec)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.00 sec) 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.01 sec)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.00 sec) 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.01 sec)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.02 sec) 3 4 root@db 12:24: [test]> set global gtid_mode=on_permissive; 5 Query OK, 0 rows affected (0.01 sec) 6 7 root@db 12:24: [test]> set global gtid_mode=on; 8 Query OK, 0 rows affected (0.01 sec) 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.00 sec)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.01 sec) 3 4 root@db 12:25: [test]> set global gtid_mode=on_permissive; 5 Query OK, 0 rows affected (0.01 sec) 6 7 root@db 12:25: [test]> set global gtid_mode=on; 8 Query OK, 0 rows affected (0.01 sec) 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.01 sec)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.01 sec)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.06 sec)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.01 sec)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.01 sec) 3 4 root@db 12:35: [test]> show slave status\G 5 *************************** 1. row *************************** 6Slave_IO_State: 7 Master_Host: 192.168.0.100 8 Master_User: repluser 9 Master_Port: 330610 Connect_Retry: 6011Master_Log_File: on.00001812 Read_Master_Log_Pos: 19413Relay_Log_File: node02-relay-bin.00001414 Relay_Log_Pos: 35315 Relay_Master_Log_File: on.00001816 Slave_IO_Running: No17 Slave_SQL_Running: No18Replicate_Do_DB: 19 Replicate_Ignore_DB: 20 Replicate_Do_Table: 21 Replicate_Ignore_Table: 22 Replicate_Wild_Do_Table: 23 Replicate_Wild_Ignore_Table: 24Last_Errno: 025Last_Error: 26 Skip_Counter: 027 Exec_Master_Log_Pos: 19428Relay_Log_Space: 60129Until_Condition: None30Until_Log_File: 31 Until_Log_Pos: 032 Master_SSL_Allowed: No33 Master_SSL_CA_File: 34 Master_SSL_CA_Path: 35Master_SSL_Cert: 36 Master_SSL_Cipher: 37Master_SSL_Key: 38 Seconds_Behind_Master: NULL39 Master_SSL_Verify_Server_Cert: No40 Last_IO_Errno: 041 Last_IO_Error: 42Last_SQL_Errno: 043Last_SQL_Error: 44 Replicate_Ignore_Server_Ids: 45 Master_Server_Id: 330610046 Master_UUID: b8439fb9-4f22-11e8-a24e-000c291a6b5247 Master_Info_File: mysql.slave_master_info48 SQL_Delay: 049 SQL_Remaining_Delay: NULL50 Slave_SQL_Running_State: 51 Master_Retry_Count: 8640052 Master_Bind: 53 Last_IO_Error_Timestamp: 54Last_SQL_Error_Timestamp: 55Master_SSL_Crl: 56 Master_SSL_Crlpath: 57 Retrieved_Gtid_Set: 58 Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-10359 Auto_Position: 060Replicate_Rewrite_DB: 61 Channel_Name: 62 Master_TLS_Version: 63 1 row in set (0.00 sec)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.02 sec) 3 4 root@db 12:36: [test]> start slave; 5 Query OK, 0 rows affected (0.00 sec) 6 7 root@db 12:37: [test]> show slave status\G 8 *************************** 1. row *************************** 9Slave_IO_State: Waiting for master to send event10 Master_Host: 192.168.0.10011 Master_User: repluser12 Master_Port: 330613 Connect_Retry: 6014Master_Log_File: on.00001815 Read_Master_Log_Pos: 19416Relay_Log_File: node02-relay-bin.00000217 Relay_Log_Pos: 35318 Relay_Master_Log_File: on.00001819 Slave_IO_Running: Yes20 Slave_SQL_Running: Yes21Replicate_Do_DB: 22 Replicate_Ignore_DB: 23 Replicate_Do_Table: 24 Replicate_Ignore_Table: 25 Replicate_Wild_Do_Table: 26 Replicate_Wild_Ignore_Table: 27Last_Errno: 028Last_Error: 29 Skip_Counter: 030 Exec_Master_Log_Pos: 19431Relay_Log_Space: 56132Until_Condition: None33Until_Log_File: 34 Until_Log_Pos: 035 Master_SSL_Allowed: No36 Master_SSL_CA_File: 37 Master_SSL_CA_Path: 38Master_SSL_Cert: 39 Master_SSL_Cipher: 40Master_SSL_Key: 41 Seconds_Behind_Master: 042 Master_SSL_Verify_Server_Cert: No43 Last_IO_Errno: 044 Last_IO_Error: 45Last_SQL_Errno: 046Last_SQL_Error: 47 Replicate_Ignore_Server_Ids: 48 Master_Server_Id: 330610049 Master_UUID: b8439fb9-4f22-11e8-a24e-000c291a6b5250 Master_Info_File: mysql.slave_master_info51 SQL_Delay: 052 SQL_Remaining_Delay: NULL53 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates54 Master_Retry_Count: 8640055 Master_Bind: 56 Last_IO_Error_Timestamp: 57Last_SQL_Error_Timestamp: 58Master_SSL_Crl: 59 Master_SSL_Crlpath: 60 Retrieved_Gtid_Set: 61 Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-10362 Auto_Position: 163Replicate_Rewrite_DB: 64 Channel_Name: 65 Master_TLS_Version: 66 1 row in set (0.00 sec)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.00 sec) 3 4 root@db 12:38: [test]> insert into tt (name,score) values('gtid_on',85); 5 Query OK, 1 row affected (0.00 sec) 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.01 sec)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.00 sec)14 15 root@db 12:40: [test]> show slave status\G16 *************************** 1. row ***************************17Slave_IO_State: Waiting for master to send event18 Master_Host: 192.168.0.10019 Master_User: repluser20 Master_Port: 330621 Connect_Retry: 6022Master_Log_File: on.00001823 Read_Master_Log_Pos: 72924Relay_Log_File: node02-relay-bin.00000225 Relay_Log_Pos: 88826 Relay_Master_Log_File: on.00001827 Slave_IO_Running: Yes28 Slave_SQL_Running: Yes29Replicate_Do_DB: 30 Replicate_Ignore_DB: 31 --------省略部分---------------------32 Last_IO_Error_Timestamp: 33Last_SQL_Error_Timestamp: 34Master_SSL_Crl: 35 Master_SSL_Crlpath: 36 Retrieved_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:104-10537 Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-10538 Auto_Position: 139Replicate_Rewrite_DB: 40 Channel_Name: 41 Master_TLS_Version: 42 1 row in set (0.00 sec)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的值虽然支持动态修改，但是在修改时不能跳跃式的修改，必须得严格按照顺序修改。
