从GTID复制模式在线切换到传统模式

从GTID复制模式切换到传统模式

准备工作
1.拓扑中的所有服务器都必须使用MySQL 5.7.6或更高版本. 除非拓扑中的所有服务器都使用此版本,否则无法在任何单个服务器上启用GTID事务.
2.所有服务器都将gtid_mode设置为默认值ON.

1.在每个slave执行以下操作,如果使用多源复制,请为每个通道执行以下操作,并包括FOR CHANNEL通道子句：

STOP SLAVE [FOR CHANNEL 'channel'];
CHANGE MASTER TO MASTER_AUTO_POSITION = 0, MASTER_LOG_FILE = file, \
MASTER_LOG_POS = position [FOR CHANNEL 'channel'];
START SLAVE [FOR CHANNEL 'channel'];

示例(非多源复制)

mysql> stop slave;
Query OK, 0 rows affected (0.10 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.16.65.35
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000032
          Read_Master_Log_Pos: 79678258
               Relay_Log_File: uz6536-relay-bin.000002
                Relay_Log_Pos: 2060459
        Relay_Master_Log_File: mysql-bin.000032
             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: 79678258
              Relay_Log_Space: 2060667
              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: NULL
Master_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: 353306
                  Master_UUID: 77827a78-4a5b-11e7-bd10-005056811999
             Master_Info_File: /data/mysql55/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: 77827a78-4a5b-11e7-bd10-005056811999:65590-66792
            Executed_Gtid_Set: 77827a78-4a5b-11e7-bd10-005056811999:1-66792
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)


CHANGE MASTER TO MASTER_AUTO_POSITION = 0, --关闭auto_position,改用手动指定位置
MASTER_LOG_FILE = 'mysql-bin.000032',  --取Master_Log_File值
MASTER_LOG_POS = 79678258; --取Exec_Master_Log_Pos值

mysql> start slave;
Query OK, 0 rows affected (0.10 sec)

2.在每个sever执行:

SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

3.在每个sever执行:

SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

4.在每个sever,等待@@GLOBAL.GTID_OWNED变量值为空,可以通过如下命令查看

SELECT @@GLOBAL.GTID_OWNED;

在从库上,理论上这个值可能显示为零,之后又显示为非零值.这并不是问题,只要显示为零一次即可

5.等待任何二进制日志中当前存在的所有事务复制到所有从库. 有关检查所有匿名事务已复制到所有服务器的一种方法,请参见第16.1.5.4节“验证匿名事务的复制”.

6.If you use binary logs for anything other than replication, for example point in time backup and restore, wait until you do not need the old binary logs having transactions without GTIDs.

For instance, after step 6 has completed, you can execute FLUSH LOGS on the server where you are taking backups. Then either explicitly take a backup or wait for the next iteration of any periodic backup routine you may have set up.

Ideally, wait for the server to purge all binary logs that existed when step 6 was completed. Also wait for any backup taken before step 6 to expire.

Important
This is the second important point. It is vital to understand that binary logs containing anonymous transactions, without GTIDs cannot be used after the next step. After this step, you must be sure that transactions without GTIDs do not exist anywhere in the topology.

7.在每个sever执行:

SET @@GLOBAL.GTID_MODE = OFF;

8.在每个服务器上,在my.cnf中设置gtid-mode = OFF.

如果要设置enforce_gtid_consistency = OFF,可以现在就这样做. 设置完成后,您应该在配置文件中添加enforce_gtid_consistency = OFF.