本文共 31501 字，大约阅读时间需要 105 分钟。
root@db 06:32: [(none)]> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.101 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1480 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 1098 Relay_Master_Log_File: mysql-bin.000001 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: 1480 Relay_Log_Space: 1306 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: 0Master_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: 7d66c13f-41f0-11e7-aff4-f44d306bf92c 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: 7d66c13f-41f0-11e7-aff4-f44d306bf92c:96-98 Executed_Gtid_Set: 7d66c13f-41f0-11e7-aff4-f44d306bf92c:1-98 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)root@db 06:32: [(none)]>
操作过程如下：
root@db 07:04: [test]> stop slave;Query OK, 0 rows affected (0.01 sec)root@db 10:39: [test]> CHANGE MASTER TO master_auto_position=0,Master_Host='192.168.1.101',MASTER_USER='repluser',MASTER_PASSWORD='reppassword',Master_Log_File='mysql-bin.000001',MASTER_LOG_POS=2280;Query OK, 0 rows affected, 2 warnings (0.03 sec)root@db 10:42: [test]>
root@db 10:46: [test]> start slave;Query OK, 0 rows affected (0.01 sec)root@db 10:47: [test]> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.101 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 194 Relay_Log_File: relay-bin.000006 Relay_Log_Pos: 353 Relay_Master_Log_File: mysql-bin.000003 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: 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: 0Master_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: 7d66c13f-41f0-11e7-aff4-f44d306bf92c 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: 7d66c13f-41f0-11e7-aff4-f44d306bf92c:1-101 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)root@db 10:47: [test]>
root@db 10:45: [test]> set global gtid_mode=on_permissive;Query OK, 0 rows affected (0.00 sec)root@db 10:45: [test]>
root@db 10:42: [test]> set global gtid_mode=on_permissive;Query OK, 0 rows affected (0.02 sec)root@db 10:46: [test]>
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），虽然四种状态支持动态修改，但是不能跳跃修改，即只能严格依次顺序修改，
root@db 11:24: [test]> desc tt;+-------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(20) | YES | MUL | NULL | || score | int(10) | YES | | NULL | |+-------+-------------+------+-----+---------+----------------+3 rows in set (0.00 sec)root@db 11:24: [test]> insert into tt (name,score) values ('kids',99);Query OK, 1 row affected (0.01 sec)root@db 11:25: [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@db 11:25: [test]> root@db 11:17: [test]> show slave status\GERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 14Current database: test*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.101 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 194 Relay_Log_File: relay-bin.000006 Relay_Log_Pos: 353 Relay_Master_Log_File: mysql-bin.000003 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: 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: 0Master_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: 7d66c13f-41f0-11e7-aff4-f44d306bf92c 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: 7d66c13f-41f0-11e7-aff4-f44d306bf92c:1-101 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)root@db 11:17: [test]> set global enforce_gtid_consistency=off;Query OK, 0 rows affected (0.00 sec)root@db 11:25: [test]> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.101 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 460 Relay_Log_File: relay-bin.000006 Relay_Log_Pos: 619 Relay_Master_Log_File: mysql-bin.000003 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: 460 Relay_Log_Space: 867 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: 0Master_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: 7d66c13f-41f0-11e7-aff4-f44d306bf92c 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: 7d66c13f-41f0-11e7-aff4-f44d306bf92c:102 Executed_Gtid_Set: 7d66c13f-41f0-11e7-aff4-f44d306bf92c:1-102 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)root@db 11:25: [test]>
发现插入前后Excuted_Gtid_Set发生变化，说明我们这里切换没有成功，接下来我们把参数严格按照顺序修改，再进行测试：
切换之前我们查看下主从gitd_mode参数值：
主服务器：gtid_mode值
root@db 11:45: [(none)]> show variables like '%gtid_mode%';+---------------+-------+| Variable_name | Value |+---------------+-------+| gtid_mode | ON |+---------------+-------+1 row in set (0.01 sec)root@db 11:46: [(none)]>
从服务器gitd_mode值：
root@db 11:44: [(none)]> show variables like '%gtid_mode%';+---------------+-------+| Variable_name | Value |+---------------+-------+| gtid_mode | ON |+---------------+-------+1 row in set (0.01 sec)root@db 11:47: [(none)]>
从服务器执行
root@db 11:47: [(none)]> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.101 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000012 Read_Master_Log_Pos: 346 Relay_Log_File: relay-bin.000024 Relay_Log_Pos: 505 Relay_Master_Log_File: mysql-bin.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: 0Master_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: 7d66c13f-41f0-11e7-aff4-f44d306bf92c 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: 7d66c13f-41f0-11e7-aff4-f44d306bf92c:103 Executed_Gtid_Set: 7d66c13f-41f0-11e7-aff4-f44d306bf92c:1-103 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)root@db 11:48: [(none)]>
root@db 11:48: [(none)]> stop slave;Query OK, 0 rows affected (0.00 sec)root@db 11:50: [(none)]> CHANGE MASTER TO master_auto_position=0,Master_Host='192.168.1.101',MASTER_USER='repluser',MASTER_PASSWORD='reppassword',Master_Log_File='mysql-bin.000012',MASTER_LOG_POS=346; Query OK, 0 rows affected, 2 warnings (0.03 sec)root@db 11:52: [(none)]> start slave;Query OK, 0 rows affected (0.01 sec)root@db 11:53: [(none)]> set global gtid_mode=on_permissive;Query OK, 0 rows affected (0.01 sec)root@db 11:53: [(none)]> set global gtid_mode=off_permissive;Query OK, 0 rows affected (0.01 sec)root@db 11:54: [(none)]> set global gtid_mode=off;Query OK, 0 rows affected (0.01 sec)root@db 11:55: [(none)]> set global enforce_gtid_consistency=off;Query OK, 0 rows affected (0.00 sec)root@db 11:55: [(none)]> root@db 11:55: [(none)]> show variables like '%gtid_mode%';+---------------+-------+| Variable_name | Value |+---------------+-------+| gtid_mode | OFF |+---------------+-------+row in set (0.00 sec)root@db 11:58: [(none)]>
root@db 11:45: [(none)]> show variables like '%gtid_mode%';+---------------+-------+| Variable_name | Value |+---------------+-------+| gtid_mode | ON |+---------------+-------+1 row in set (0.01 sec)root@db 11:46: [(none)]> set global gtid_mode=on_permissive;Query OK, 0 rows affected (0.02 sec)root@db 11:53: [(none)]> set global gtid_mode=off_permissive;Query OK, 0 rows affected (0.01 sec)root@db 11:54: [(none)]> set global gtid_mode=off;Query OK, 0 rows affected (0.02 sec)root@db 11:55: [(none)]> set global enforce_gtid_consistency=off;Query OK, 0 rows affected (0.00 sec)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:57: [(none)]>
两边gtid复制模式方式现已关闭，同时开始传统模式，开始验证传统复制模式是否生效：
验证前查记录下当前Excuted_Gtid_Set值，方便后面做对比。
Master_SSL_Crlpath:
Retrieved_Gtid_Set: Executed_Gtid_Set: 7d66c13f-41f0-11e7-aff4-f44d306bf92c:1-103Auto_Position: 0Replicate_Rewrite_DB:在主库插入数据：
root@db 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@db 12:12: [test]> insert into tt (name,score) values('MySQL',82);Query OK, 1 row affected (0.01 sec)root@db 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@db 12:13: [test]> 从库查看插入数据：
root@db 12:06: [(none)]> use testDatabase changedroot@db 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)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 eventMaster_Host: 192.168.1.101Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000015Read_Master_Log_Pos: 461Relay_Log_File: relay-bin.000008Relay_Log_Pos: 620Relay_Master_Log_File: mysql-bin.000015Slave_IO_Running: YesSlave_SQL_Running: Yes--------------------省略--------------Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 7d66c13f-41f0-11e7-aff4-f44d306bf92c:1-103Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
通过对比发现GTID的值没有增加，证明切换成功。
接下来做相反操作，通过传统模式切换到GTID模式
在主从库上修修改enforce_gtid_consistency=warn,确保在error.log中不出现警告信息，如果有需要先修复。
主库：
root@db 12:13: [test]> set global enforce_gtid_consistency=warn;Query OK, 0 rows affected (0.00 sec)root@db 12:19: [test]> show variables like '%enforce_gtid_consistency%';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| enforce_gtid_consistency | WARN |+--------------------------+-------+1 row in set (0.01 sec)root@db 12:20: [test]>
从库：
root@db 12:18: [test]> set global enforce_gtid_consistency=warn;Query OK, 0 rows affected (0.00 sec)root@db 12:19: [test]> show variables like '%enforce_gtid_consistency%';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| enforce_gtid_consistency | WARN |+--------------------------+-------+1 row in set (0.01 sec)root@db 12:20: [test]>
在主从库上调整enforce_gtid_consistency=on,保证GTID的一致性
主库：
root@db 12:20: [test]> set global enforce_gtid_consistency=on;Query OK, 0 rows affected (0.00 sec)root@db 12:21: [test]> show variables like '%enforce_gtid_consistency%';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| enforce_gtid_consistency | ON |+--------------------------+-------+1 row in set (0.01 sec)root@db 12:21: [test]>
从库：
root@db 12:20: [test]> set global enforce_gtid_consistency=on;Query OK, 0 rows affected (0.00 sec)root@db 12:22: [test]> show variables like '%enforce_gtid_consistency%';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| enforce_gtid_consistency | ON |+--------------------------+-------+1 row in set (0.01 sec)root@db 12:22: [test]>
然后在主从库安装前面的顺序再一次修改回去，直至gtid_mode=on为止
主库操作：
root@db 12:21: [test]> set global gtid_mode=off_permissive;Query OK, 0 rows affected (0.02 sec)root@db 12:24: [test]> set global gtid_mode=on_permissive;Query OK, 0 rows affected (0.01 sec)root@db 12:24: [test]> set global gtid_mode=on;Query OK, 0 rows affected (0.01 sec)root@db 12:24: [test]> show variables like '%gtid_mode%';+---------------+-------+| Variable_name | Value |+---------------+-------+| gtid_mode | ON |+---------------+-------+1 row in set (0.00 sec)root@db 12:24: [test]>
从库操作：
root@db 12:22: [test]> set global gtid_mode=off_permissive;Query OK, 0 rows affected (0.01 sec)root@db 12:25: [test]> set global gtid_mode=on_permissive;Query OK, 0 rows affected (0.01 sec)root@db 12:25: [test]> set global gtid_mode=on;Query OK, 0 rows affected (0.01 sec)root@db 12:25: [test]> show variables like '%gtid_mode%';+---------------+-------+| Variable_name | Value |+---------------+-------+| gtid_mode | ON |+---------------+-------+1 row in set (0.01 sec)root@db 12:26: [test]>
提示：上述gtid_mode参数值修改必须严格安装顺序依次修改，否则会报错。
查看从库的参数Ongoing_anonymous_transaction_count参数值是否为0，如果为0，意味着没有等待的事务，可以直接进行后面的操作
查看从库的参数Ongoing_anonymous_transaction_count参数值是否为0，如果为0，意味着没有等待的事务，可以直接进行后面的操作root@db 12:29: [test]> show global status like '%ongoing_anonymous%';+-------------------------------------+-------+| Variable_name | Value |+-------------------------------------+-------+| Ongoing_anonymous_transaction_count | 0 |+-------------------------------------+-------+1 row in set (0.01 sec)root@db 12:29: [test]>
说明没有等待提交的事务，可以进入后面的操作。
再次核对GTID相关参数状态：
主服务器：
root@db 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 | 7d66c13f-41f0-11e7-aff4-f44d306bf92c:1-92 || session_track_gtids | OFF |+----------------------------------+-------------------------------------------+8 rows in set (0.06 sec)root@db 12:30: [test]>
从服务器：
root@db 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 | 7d66c13f-41f0-11e7-aff4-f44d306bf92c:1-95 || session_track_gtids | OFF |+----------------------------------+-------------------------------------------+8 rows in set (0.01 sec)root@db 12:31: [test]>
我们把传统的复制模式改为GTID模式要把传统的复制停掉，然后执行stop slave，然后执行change master to master_auto_position=1
停止slave，查看当前主从状态：
root@db 12:35: [test]> stop slave;Query OK, 0 rows affected (0.01 sec)root@db 12:35: [test]> show slave status\G*************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.1.101 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000018 Read_Master_Log_Pos: 194 Relay_Log_File: relay-bin.000014 Relay_Log_Pos: 353 Relay_Master_Log_File: mysql-bin.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: NULLMaster_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: 7d66c13f-41f0-11e7-aff4-f44d306bf92c 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: 7d66c13f-41f0-11e7-aff4-f44d306bf92c:1-103 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)root@db 12:35: [test]>
执行change master to master_auto_position=1.开启复制状态，start slave
root@db 12:36: [test]> change master to master_auto_position=1;Query OK, 0 rows affected (0.02 sec)root@db 12:36: [test]> start slave;Query OK, 0 rows affected (0.00 sec)root@db 12:37: [test]> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.101 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000018 Read_Master_Log_Pos: 194 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 353 Relay_Master_Log_File: mysql-bin.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: 0Master_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: 7d66c13f-41f0-11e7-aff4-f44d306bf92c 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: 7d66c13f-41f0-11e7-aff4-f44d306bf92c:1-103 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)root@db 12:37: [test]>
验证切换是否成功，主库插入数据：
root@db 12:30: [test]> insert into tt (name,score) values('gtid',82);Query OK, 1 row affected (0.00 sec)root@db 12:38: [test]> insert into tt (name,score) values('gtid_on',85);Query OK, 1 row affected (0.00 sec)root@db 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)root@db 12:38: [test]> 从库查看数据和Excuted_Gtid_Set对应值是否发生变化：
root@db 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@db 12:40: [test]> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.101 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000018 Read_Master_Log_Pos: 729 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 888 Relay_Master_Log_File: mysql-bin.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: 7d66c13f-41f0-11e7-aff4-f44d306bf92c:104-105 Executed_Gtid_Set: 7d66c13f-41f0-11e7-aff4-f44d306bf92c:1-105 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)root@db 12:40: [test]>
通过上述查询，发现数据已经复制过来，说明数据同步成功，而Excuted_Gtid_Set的值由“ 7d66c13f-41f0-11e7-aff4-f44d306bf92c:1-103” 变换为：“7d66c13f-41f0-11e7-aff4-f44d306bf92c:1-105”，说明切换成功，因为GTID的值增加了 ，证明开启了GTID的复制方式。
至此MySQL的传统复制方式和GTID方式互相切换演示完成，需要注意的是gtid_mode的值虽然支持动态修改，但是在修改时不能跳跃式的修改，必须得严格按照顺序修改。
转载于:https://blog.51cto.com/moerjinrong/2160355