将 MariaDB 基于位置的复制转换为 GTID 复制

转换步骤

1.停止所有从服务器上的复制

MariaDB [test_db] STOP SLAVE;
Query OK, 0 rows affected (0.007 sec)
MariaDB [test_db] show slave statusG
*************************** 1. row ***************************
            Slave_IO_State:
               Master_Host: 192.168.56.190
            Master_User: repl
                Master_Port: 3306
               Connect_Retry: 10
             Master_Log_File: bin_log.000002
        Read_Master_Log_Pos: 1206
               Relay_Log_File: mysqld-relay-bin.000007
              Relay_Log_Pos: 553
   Relay_Master_Log_File: bin_log.000002
             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: 1206
           Relay_Log_Space: 863
    ……..
Slave_Non_Transactional_Groups: 0
   Slave_Transactional_Groups: 0
   
1 row in set (0.000 sec)

在我们的例子中，Relay_Master_Log_File 的值为 bin_log.000002，Exec_Master_Log_Pos 为 1206

2.在主库上获取其对应的 GTID 位置（MySQL 没有此函数）

MariaDB[test_db] SELECT BINLOG_GTID_POS('bin_log.000002', 1206),@@hostname;
  +-----------------------------------------+------------+
| BINLOG_GTID_POS('bin_log.000002', 1206)   | @@hostname |
+-------------------------------------------+------------+
| 1-1-1,0-1-4                               |
+-------------------------------------------+------------+
1 row in set (0.000 sec)

3.回到从库上执行

MariaDB [test_db] SET GLOBAL gtid_slave_pos = '1-1-1,0-1-4';
MariaDB [test_db] CHANGE MASTER TO master_use_gtid=slave_pos;
MariaDB [test_db] START SLAVE;
MariaDB [test_db] show slave status\G
    *************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                     Master_Host: 192.168.56.190
                    Master_User: repl
                      Master_Port: 3306
                 Connect_Retry: 10
              Master_Log_File: bin_log.000002
          Read_Master_Log_Pos: 1391
                 Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 910
         Relay_Master_Log_File: bin_log.000002
 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: 1391
                Relay_Log_Space: 1220
      …….
              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: 1
                  Master_SSL_Crl:
              Master_SSL_Crlpath:
                      Using_Gtid: Slave_Pos
                      Gtid_IO_Pos: 1-1-2,0-1-4
           Replicate_Do_Domain_Ids:
       Replicate_Ignore_Domain_Ids:
                    Parallel_Mode: optimistic
                         SQL_Delay: 0
               SQL_Remaining_Delay: NULL
     Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
                Slave_DDL_Groups: 0
   Slave_Non_Transactional_Groups: 0
        Slave_Transactional_Groups: 1
   1 row in set (0.000 sec)

当看到Using_Gtid: Slave_Pos 代表已经转换成功！