Mysql 8.0 主从同步 传统模式和GTID模式相互切换

在前期实施案例中，我们在搭建主从时候，可能会选择模式的传统模式，如果后期客户需要调整为GTID模式，需要如果修改呢？是否需要停机呢？

本文，测试一下基于mysql 8.0 版本，主从同步中，传统模式和GTID模式之间相互切换。

一、检查当前主从环境

--1)查看主从版本都是8.0
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.22    |
+-----------+
1 row in set (0.00 sec)

mysql> show master status;
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB                   | Executed_Gtid_Set |
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
| master-bin.000001 |     1816 |       | mysql,information_schema,performance_schema,sys |                |
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)


--2)slave同步情况：
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.72.0.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 1816
               Relay_Log_File: slave1-relay-bin.000002
                Relay_Log_Pos: 615
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: information_schema,performance_schema,mysql,sys
           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: 1816
              Relay_Log_Space: 825
              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: 0
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: 573033261
                  Master_UUID: 081712e5-f45a-11ed-9af2-0242ac48000b
             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:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified
复制

可以看到当前同步状态为传统模式，未启用GTID
 

二、传统模式转GTID模式

1. 主从修改参数enforce_gtid_consistency

-- 主从都执行，先后顺序不要求
mysql> set @@global.enforce_gtid_consistency=warn;
Query OK, 0 rows affected (0.00 sec)
复制

• 如果出现GTID不兼容的语句用法，在错误日志会记录相关信息，需要先修复，调整应该程序避免不兼容的写法，直到完全没有产生不兼容的语句，可以设置后观察错误日志一段时间，防止出现问题，这一步非常重要。

2. 主从修改参数 enforce_gtid_consistency

​ 确定没有不兼容语法后，修改为ON

-- 主从都执行，先后顺序不要求
mysql> set @@global.enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.01 sec)
复制

3. 主从修改参数gtid_mode

​ 先设置gtid_mode为off_permissiv=off_permissiv，再改成on_permissiv

-- 建议先修改从库的gtid_mode为on_permissive，之后再修改主库的
mysql> SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
Query OK, 0 rows affected (0.00 sec)
复制

gitd_mode解释

• OFF不产生GTID，Slave只接收不带GTID的事务

• OFF_PERMISSIVE不产生GTID，Slave接收不带GTID的事务也接收带GTID的事务

• ON_PERMISSIVE产生GTID，Slave接收不带GTID的事务也接收带GTID的事务

• ON产生GTID，Slave只接收带GTID的事务

注意：需要严格按照以下顺序，不可跳跃，否则抛出如下错误：

OFF <= => OFF_PERMISSIVE <= => ON_PERMISSIVE <= => ON

-- 比如，直接从off改成on是不行的
mysql> show variables like 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SET @@GLOBAL.GTID_MODE = on;
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.
复制

4. 检查从库是否已经同步到当前主库位置

--1)master: 取主库的file跟pos到从库去执行查看
mysql> show master status;
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
| master-bin.000002 |      635 |              | mysql,information_schema,performance_schema,sys |                   |
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)


--2)slave
mysql> SELECT MASTER_POS_WAIT('master-bin.000002', 635);
+-------------------------------------------+
| MASTER_POS_WAIT('master-bin.000002', 635) |
+-------------------------------------------+
|                            0 |     -- 返回值为0，代表从库已经应用了指定的主库上binlog position位置数据
+-------------------------------------------+
复制

5. 确认从库没等待的事务

-- 检查全部实例 正在进行的匿名交易数目，也就是非GTID事务有没有都传送到从库上了，需要等到这个变量为 0 
mysql>  show status like 'ongoing_anonymous_transaction_count';
+-------------------------------------+-------+
| Variable_name                | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0     |
+-------------------------------------+-------+
1 row in set (0.01 sec)
复制

注意：如果是一主多从，那么需要在所有从库上查看，等待所有从库都变为0.

6. 启动GTID

-- 主从都执行，先后顺序不要求
mysql> SET @@GLOBAL.GTID_MODE = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+
1 row in set (0.00 sec)
复制

7. 检查同步状态

--1)master
mysql> insert into mytb1 values (4,'d');
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+-------------------+----------+--------------+-------------------------------------------------+----------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set                      |
+-------------------+----------+--------------+-------------------------------------------------+----------------------------------------+
| master-bin.000005 |      446 |              | mysql,information_schema,performance_schema,sys | 081712e5-f45a-11ed-9af2-0242ac48000b:1 |
+-------------------+----------+--------------+-------------------------------------------------+----------------------------------------+
1 row in set (0.00 sec)


--2)slave
mysql> select * from mytb1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.72.0.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000005
          Read_Master_Log_Pos: 446
               Relay_Log_File: slave1-relay-bin.000010
                Relay_Log_Pos: 663
        Relay_Master_Log_File: master-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: information_schema,performance_schema,mysql,sys
           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: 446
              Relay_Log_Space: 921
              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: 0
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: 573033261
                  Master_UUID: 081712e5-f45a-11ed-9af2-0242ac48000b
             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: 081712e5-f45a-11ed-9af2-0242ac48000b:1
            Executed_Gtid_Set: 081712e5-f45a-11ed-9af2-0242ac48000b:1
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified
复制

可以看到：

• 主库新插入的数据，可以正常同步到slave。
• Retrieved_Gtid_Set、Executed_Gtid_Set 都识别到了事务编号。
• Auto_Position 仍然是0，说明还是传统模式。

8. 将传统复制转为GTID模式

--slave执行
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_auto_position=1;  -- 设置为1，自动查找位置复制
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
复制

9. 测试同步

--1)master
mysql> insert into mytb1 values (5,'e');
Query OK, 1 row affected (0.00 sec)

--2)slave: 同步正常
mysql> select * from testdb.mytb1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
+------+------+
5 rows in set (0.00 sec)
复制

10. 主从修改my.cnf文件

cat >> /etc/my.cnf <<EOF
gtid-mode=ON
enforce-gtid-consistency=on
EOF
复制

或者利用MySQL 8.0新特性，持久化修改参数：

mysql> set persist gtid_mode = on;
Query OK, 0 rows affected (0.00 sec)

mysql> set persist enforce_gtid_consistency = on;
Query OK, 0 rows affected (0.00 sec)


-- 这种方式会把参数设置写入到mysqld-auto.cnf文件中
root@slave1:/var/lib/mysql# more mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "mysql_server_static_options" : { "enforce_gtid_consistency" : { "Value" : "ON" , "Metadata" : { "Timestamp" : 1684293203484259 , "User"
: "root" , "Host" : "localhost" } } , "gtid_mode" : { "Value" : "ON" , "Metadata" : { "Timestamp" : 1684293146585382 , "User" : "root" , "Host" : "localhost" } } } } }
复制

此时，我们已经将原来的传统复制模式，修改为GTID复制模式。

三、GTID模式转传统模式

1. 停止从库

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
复制

2. 修改复制模式为传统模式

--1)master:取主库file跟pos
mysql> show master status;
+-------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set                        |
+-------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
| master-bin.000005 |      736 |              | mysql,information_schema,performance_schema,sys | 081712e5-f45a-11ed-9af2-0242ac48000b:1-2 |
+-------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
1 row in set (0.00 sec)

--2)slave:
mysql> change master to master_log_file='master-bin.000005',master_log_pos=736,master_auto_position=0;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
复制

3. 测试同步

--1)master
mysql> insert into mytb1 values (6,'f');
Query OK, 1 row affected (0.00 sec)

--2)slave
mysql> select * from mytb1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
+------+------+
6 rows in set (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.72.0.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000005
          Read_Master_Log_Pos: 1026
               Relay_Log_File: slave1-relay-bin.000002
                Relay_Log_Pos: 615
        Relay_Master_Log_File: master-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: information_schema,performance_schema,mysql,sys
           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: 1026
              Relay_Log_Space: 825
              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: 0
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: 573033261
                  Master_UUID: 081712e5-f45a-11ed-9af2-0242ac48000b
             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: 081712e5-f45a-11ed-9af2-0242ac48000b:3
            Executed_Gtid_Set: 081712e5-f45a-11ed-9af2-0242ac48000b:1-3
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified
复制

可以看到：

• 主库新插入的数据，可以正常同步到slave。
• Read_Master_Log_Pos、Exec_Master_Log_Pos 都产生了变化。
• Auto_Position 变为0，已经修改为传统模式。

4. 主从修改参数gtid_mode

先设置gtid_mode为off_permissiv=on_permissiv，再改成off_permissiv

-- 主从都执行，先后顺序不要求
mysql> SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
Query OK, 0 rows affected (0.00 sec)
复制

5. 禁用GTID

-- 主从都执行，先后顺序不要求
mysql> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@GLOBAL.GTID_MODE = OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | OFF    |
+---------------+-------+
1 row in set (0.00 sec)
复制

7. 检查同步状态

--1)master
mysql> insert into mytb1 values (7,'g');
Query OK, 1 row affected (0.00 sec)

--2)slave: 同步正常
mysql> select * from mytb1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
|    7 | g    |
+------+------+
7 rows in set (0.00 sec)
复制

9. 主从修改my.cnf文件

-- 注释
#gtid-mode=ON
#enforce-gtid-consistency=on
复制

如果使用MySQL 8.0新特性，持久化修改参数的，需要再重置回去：

mysql> reset persist gtid_mode ;
Query OK, 0 rows affected (0.00 sec)

mysql> reset persist enforce_gtid_consistency ;
Query OK, 0 rows affected (0.00 sec)


-- 可以看到mysqld-auto.cnf文件，之前设置的参数已被删除
root@slave1:/var/lib/mysql# more mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : {  } }
复制

此时，我们已经将原来的GTID复制模式，修改为传统复制模式。

10.思考？

在第一步，停止从库复制(stop slave），到再次启动slave 复制，期间是有时间差的，如果从库停止复制期间，主库有新的事务产生，那么从库在启动复制后，会丢失这部分数据，因此，从GTID模式修改为传统复制模式，需要将主库停止，防止丢失数据。但是从传统模式修改为GTID模式，不需要停止主库，可以在线操作。

 
–实验：

--1) slave停止同步
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

--2)slave停止同步后，主库插入一条数据
mysql> insert into mytb1 values (8,'h');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mytb1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
|    7 | g    |
|    8 | h   |
+------+------+

-- 查看当前主库file和pos
mysql> show master status;
+-------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set                        |
+-------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
| master-bin.000008 |     1648 |        | mysql,information_schema,performance_schema,sys | 081712e5-f45a-11ed-9af2-0242ac48000b:1-3 |
+-------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
1 row in set (0.00 sec)


--3)从库重新配置同步，并启动
mysql> change master to master_log_file='master-bin.000008',master_log_pos=1648,master_auto_position=0;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

-- slave查看数据
mysql> select * from mytb1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
|    7 | g    |
+------+------+
复制

 slave 重新启动同步后，丢失了主库插入的（8,h）这一行数据。