mysql传统复制环境切换成gtid复制

1.目前的环境：

master 192.168.26.233 3306 

slave 192.168.26.108 3306 

复制：binlog+position 传统复制

2.在线切换

1.master和slave执行

root@localhost [zw3306]>set @@global.enforce_gtid_consistency = warn;

Query OK, 0 rows affected (0.00 sec)

root@localhost [zw3306]>show global variables like '%enforce_gtid_consistency%';

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

| Variable_name            | Value |

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

| enforce_gtid_consistency | WARN  |

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

  

2.在master和slave都要执行

root@localhost [zw3306]>set @@global.enforce_gtid_consistency = on; 

Query OK, 0 rows affected (0.00 sec)

root@localhost [zw3306]>show global variables like '%enforce_gtid_consistency%';

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

| Variable_name            | Value |

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

| enforce_gtid_consistency | ON    |

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

1 row in set (0.00 sec)

3. 在master和slave都要执行

root@localhost [zw3306]>set @@global.gtid_mode = off_permissive; 

Query OK, 0 rows affected (0.16 sec)

root@localhost [zw3306]> show global variables like '%gtid_mode%';

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

| Variable_name | Value          |

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

| gtid_mode     | OFF_PERMISSIVE |

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

1 row in set (0.00 sec)

 

4. 在master和slave都要执行

mysql> set @@global.gtid_mode=on_permissive;

Query OK, 0 rows affected (0.21 sec)

mysql>  show global variables like '%gtid_mode%';

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

| Variable_name | Value         |

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

| gtid_mode     | ON_PERMISSIVE |

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

1 row in set (0.00 sec)

在这个骤开始生的日志都带有gtid信息,执行步骤一般推荐在先在slave上执行，然后再去master上执行。 

mysql> show status like 'ongoing_anonymous_transaction_count';

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

| Variable_name                       | Value |

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

| Ongoing_anonymous_transaction_count | 0     |

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

1 row in set (0.00 sec)

所有的节点都确认为0. 查看是不是0，如果是0就都是gtid复制

5.所有的节点也可以执行一下: flush logs; 用于切换一下日志。

mysql> flush logs;

Query OK, 0 rows affected (0.10 sec)

6. master和slave都要执行

root@localhost [zw3306]> set @@global.gtid_mode=on;

Query OK, 0 rows affected (0.16 sec)

root@localhost [zw3306]> show global variables like '%gtid_mode%';

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

| Variable_name | Value |

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

| gtid_mode     | ON    |

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

1 row in set (0.01 sec)

7.停止slave端

root@localhost [zw3306]>stop slave;

Query OK, 0 rows affected (0.00 sec)

root@localhost [zw3306]>change master to master_auto_position=1;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    22

Current database: zw3306

Query OK, 0 rows affected (0.19 sec)

root@localhost [zw3306]>start slave;

Query OK, 0 rows affected (0.00 sec)

root@localhost [zw3306]>show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.26.233

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000010

          Read_Master_Log_Pos: 194

               Relay_Log_File: relay-bin.000002

                Relay_Log_Pos: 367

        Relay_Master_Log_File: mysql-bin.000010

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

......

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 194   

              Relay_Log_Space: 608

......  

           Retrieved_Gtid_Set: 

            Executed_Gtid_Set: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-36,

ba0d5587-74d6-11e6-ab5c-005056a3f46e:1-2

                Auto_Position: 1

         Replicate_Rewrite_DB: 

                 Channel_Name: 

           Master_TLS_Version: 

1 row in set (0.00 sec)

可以看到 Auto_Position的值为1了

8.测试

8.1 master插入3条数据

mysql> insert into t1 values(1);

Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(2);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(3);

Query OK, 1 row affected (0.00 sec)

8.2. 查看slave端

root@localhost [zw3306]>select * from t1;

+------+

| id   |

+------+

|   14 |

|   15 |

|   16 |

|   17 |

|   18 |

|   19 |

|   19 |

|   20 |

|    1 |

|    2 |

|    3 |

+------+

11 rows in set (0.00 sec)

首先数据过来了

root@localhost [zw3306]>show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.26.233

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000010

          Read_Master_Log_Pos: 965

               Relay_Log_File: relay-bin.000002

                Relay_Log_Pos: 1138

        Relay_Master_Log_File: mysql-bin.000010

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

......

          Exec_Master_Log_Pos: 965

              Relay_Log_Space: 1379

              Until_Condition: None

               Until_Log_File: 

......

           Retrieved_Gtid_Set: 7e354a2c-6f5f-11e6-997d-005056a36f08:37-39

            Executed_Gtid_Set: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-39,

ba0d5587-74d6-11e6-ab5c-005056a3f46e:1-2

                Auto_Position: 1

         Replicate_Rewrite_DB: 

                 Channel_Name: 

           Master_TLS_Version: 

1 row in set (0.00 sec)

原来的：

Exec_Master_Log_Pos: 194   

Executed_Gtid_Set: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-36,

                   ba0d5587-74d6-11e6-ab5c-005056a3f46e:1-2

   

现在的：

Exec_Master_Log_Pos: 965

            Executed_Gtid_Set: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-39,

                               ba0d5587-74d6-11e6-ab5c-005056a3f46e:1-2

可以看到pos和gtid都发生了变化，说明gtid切换已经成功。

8.最后一步记住把修改的参数写入配置文件my.cnf

gtid_mode=on

enforce_gtid_consistency=on