MySQL主从复制之传统复制与GTID模式之间切换

MySQL主从复制之传统复制与GTID模式之间切换

主从复制环境：

主库：192.168.0.100

从库：192.168.0.101

MySQL5.7.22

当前主从状态展示如下：

 1 root@db 06:32:  [(none)]> show slave status\G 2 *************************** 1. row *************************** 3                Slave_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 8               Master_Log_File: on.000001 9           Read_Master_Log_Pos: 148010                Relay_Log_File: node02-relay-bin.00000211                 Relay_Log_Pos: 109812         Relay_Master_Log_File: on.00000113              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: 022                    Last_Error: 
23                  Skip_Counter: 024           Exec_Master_Log_Pos: 148025               Relay_Log_Space: 130626               Until_Condition: None27                Until_Log_File: 
28                 Until_Log_Pos: 029            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: 036 Master_SSL_Verify_Server_Cert: No37                 Last_IO_Errno: 038                 Last_IO_Error: 
39                Last_SQL_Errno: 040                Last_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: 
51      Last_SQL_Error_Timestamp: 
52                Master_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: 157          Replicate_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 *************************** 6                Slave_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: 6011               Master_Log_File: on.00000312           Read_Master_Log_Pos: 19413                Relay_Log_File: node02-relay-bin.00000614                 Relay_Log_Pos: 35315         Relay_Master_Log_File: on.00000316              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: 025                    Last_Error: 
26                  Skip_Counter: 027           Exec_Master_Log_Pos: 19428               Relay_Log_Space: 60129               Until_Condition: None30                Until_Log_File: 
31                 Until_Log_Pos: 032            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: 039 Master_SSL_Verify_Server_Cert: No40                 Last_IO_Errno: 041                 Last_IO_Error: 
42                Last_SQL_Errno: 043                Last_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: 
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-10159                 Auto_Position: 060          Replicate_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 2006 (HY000): MySQL server has gone away  3 No connection. Trying to reconnect...  4 Connection id:    14  5 Current database: test  6   7 *************************** 1. row ***************************  8                Slave_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 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: Yes 19             Slave_SQL_Running: Yes 20               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: None 32                Until_Log_File: 
 33                 Until_Log_Pos: 0 34            Master_SSL_Allowed: No 35            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: No 42                 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-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: 
 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.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 *************************** 74                Slave_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 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: Yes 85             Slave_SQL_Running: Yes 86               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: None 98                Until_Log_File: 
 99                 Until_Log_Pos: 0100            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: 0107 Master_SSL_Verify_Server_Cert: No108                 Last_IO_Errno: 0109                 Last_IO_Error: 
110                Last_SQL_Errno: 0111                Last_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: 
122      Last_SQL_Error_Timestamp: 
123                Master_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: 0128          Replicate_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 *************************** 3                Slave_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 8               Master_Log_File: on.000012 9           Read_Master_Log_Pos: 34610                Relay_Log_File: node02-relay-bin.00002411                 Relay_Log_Pos: 50512         Relay_Master_Log_File: on.00001213              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: 022                    Last_Error: 
23                  Skip_Counter: 024           Exec_Master_Log_Pos: 34625               Relay_Log_Space: 75326               Until_Condition: None27                Until_Log_File: 
28                 Until_Log_Pos: 029            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: 036 Master_SSL_Verify_Server_Cert: No37                 Last_IO_Errno: 038                 Last_IO_Error: 
39                Last_SQL_Errno: 040                Last_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: 
51      Last_SQL_Error_Timestamp: 
52                Master_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: 057          Replicate_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 *************************** 6                Slave_IO_State: 
 7                   Master_Host: 192.168.0.100 8                   Master_User: repluser 9                   Master_Port: 330610                 Connect_Retry: 6011               Master_Log_File: on.00001812           Read_Master_Log_Pos: 19413                Relay_Log_File: node02-relay-bin.00001414                 Relay_Log_Pos: 35315         Relay_Master_Log_File: on.00001816              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: 025                    Last_Error: 
26                  Skip_Counter: 027           Exec_Master_Log_Pos: 19428               Relay_Log_Space: 60129               Until_Condition: None30                Until_Log_File: 
31                 Until_Log_Pos: 032            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: 041                 Last_IO_Error: 
42                Last_SQL_Errno: 043                Last_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: 
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-10359                 Auto_Position: 060          Replicate_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 *************************** 9                Slave_IO_State: Waiting for master to send event10                   Master_Host: 192.168.0.10011                   Master_User: repluser12                   Master_Port: 330613                 Connect_Retry: 6014               Master_Log_File: on.00001815           Read_Master_Log_Pos: 19416                Relay_Log_File: node02-relay-bin.00000217                 Relay_Log_Pos: 35318         Relay_Master_Log_File: on.00001819              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: 028                    Last_Error: 
29                  Skip_Counter: 030           Exec_Master_Log_Pos: 19431               Relay_Log_Space: 56132               Until_Condition: None33                Until_Log_File: 
34                 Until_Log_Pos: 035            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: 042 Master_SSL_Verify_Server_Cert: No43                 Last_IO_Errno: 044                 Last_IO_Error: 
45                Last_SQL_Errno: 046                Last_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: 
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-10362                 Auto_Position: 163          Replicate_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 ***************************17                Slave_IO_State: Waiting for master to send event18                   Master_Host: 192.168.0.10019                   Master_User: repluser20                   Master_Port: 330621                 Connect_Retry: 6022               Master_Log_File: on.00001823           Read_Master_Log_Pos: 72924                Relay_Log_File: node02-relay-bin.00000225                 Relay_Log_Pos: 88826         Relay_Master_Log_File: on.00001827              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-10537             Executed_Gtid_Set: b8439fb9-4f22-11e8-a24e-000c291a6b52:1-10538                 Auto_Position: 139          Replicate_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的值虽然支持动态修改，但是在修改时不能跳跃式的修改，必须得严格按照顺序修改。

转载于:https://blog.51cto.com/11759015/2164575