如何从传统主从复制切换MGR复制

现有业务是传统的主从复制集群，无法直接切换为MGR复制

如果需要将传统集群切换为MGR复制，需要先搭建MGR复制组，然后同步复制的方式将现有业务数据同步到MGR，然后再进行业务切换。 (类似级联复制)

下面我们来演示下如何将主从复制集群转换为MGR复制组

业务DB拓扑如下：

# 首先要确保业务DB开启gtid模式

10.x.x.44:3306

10.x.x.45:3306

MGR复制组拓扑如下

10.x.x.6:3308

10.x.x.170:3308

10.x.x.171:3308

 # 首先进行数据导出

mysqldump -h 10.x.x.44 -P 3306 --single-transaction --master-data=2 -udbmha -p  -A  >  mgr_test.sql

记得注释掉语句 SET @@GLOBAL.GTID_PURGED=

另外将 SET @@SESSION.SQL_LOG_BIN= 0 改为 SET @@SESSION.SQL_LOG_BIN= 1

将备份数据mgr_test.sql导入MGR复制集群

ERROR 3098 (HY000) at line 27931: The table does not comply with the requirements by an external plugin.

在进行导入时报错原因如下：

MGR要求表的引擎是innodb，必须包含主键， mysql库的权限表不符合要求，所以在进行同步时避免导出mysql权限库

重新导出数据，这次只导出业务库

mysqldump -h 10.x.x.44 -P 3306 --single-transaction --master-data=2 -udbmha -p  -B db_x1  db_x2  db_x3  >  mgr_test.sql

可以单独将业务库账号导出再导入MGR集群

 # 导出账号权限

pt-show-grants -h127.0.0.1 -P3306 -uxxx -p'xxx'  > pri_log_3306.log

重新导入数据mgr_test.sql       

建立复制通道

change master to master_host='10.x.x.44',

    master_user='xxx',

    master_password='xxx',

    master_port=3306,

    master_connect_retry=60,

    master_log_file='binlog.000182',

    master_log_pos=2229081

    for channel 'mgr_test_38_9'  ;

这里虽然是基于gtid模式，但是MGR在搭建（级联）复制时，

无法执行 SET @@GLOBAL.GTID_PURGED，所以gtid模式下，我们使用基于pos的复制搭建

 # 查看复制链路show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.x.x.44

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: binlog.000182

          Read_Master_Log_Pos: 2231951

               Relay_Log_File: relaylog-mgr_test_38_9.000002

                Relay_Log_Pos: 3179

        Relay_Master_Log_File: binlog.000182

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

.....................................................................................................

             Master_Server_Id: 338170844

                  Master_UUID: b221eadb-b5de-11ea-9626-6805caa7f9a8

             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:b221eadb-b5de-11ea-9626-6805caa7f9a8:319767-319771

            Executed_Gtid_Set: 88aede81-a935-11ea-858b-6805caa8069c:5-294,

aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-7128:1000018-1000020:2000018,

b221eadb-b5de-11ea-9626-6805caa7f9a8:319767-319771

                Auto_Position: 0

         Replicate_Rewrite_DB:

                 Channel_Name: mgr_test_38_9

           Master_TLS_Version:

1 row in set (0.00 sec)

# 查看channel

group_replication_applier和group_replication_recovery是组复制的channel

mgr_test_38_9是我们搭建的复制链接channel

select * from performance_schema.replication_connection_status\G

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

             CHANNEL_NAME: group_replication_applier

               GROUP_NAME: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa

              SOURCE_UUID: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa

                THREAD_ID: NULL

            SERVICE_STATE: ON

COUNT_RECEIVED_HEARTBEATS: 0

 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00

 RECEIVED_TRANSACTION_SET: 88aede81-a935-11ea-858b-6805caa8069c:5-294,

aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-7128:1000018-1000020:2000018,

b221eadb-b5de-11ea-9626-6805caa7f9a8:319767-319772

        LAST_ERROR_NUMBER: 0

       LAST_ERROR_MESSAGE:

     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

*************************** 2. row ***************************

             CHANNEL_NAME: group_replication_recovery

               GROUP_NAME:

              SOURCE_UUID:

                THREAD_ID: NULL

            SERVICE_STATE: OFF

COUNT_RECEIVED_HEARTBEATS: 0

 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00

 RECEIVED_TRANSACTION_SET:

        LAST_ERROR_NUMBER: 0

       LAST_ERROR_MESSAGE:

     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

*************************** 3. row ***************************

             CHANNEL_NAME: mgr_test_38_9

               GROUP_NAME:

              SOURCE_UUID: b221eadb-b5de-11ea-9626-6805caa7f9a8

                THREAD_ID: 183

            SERVICE_STATE: ON

COUNT_RECEIVED_HEARTBEATS: 7

 LAST_HEARTBEAT_TIMESTAMP: 2020-07-09 14:50:09

 RECEIVED_TRANSACTION_SET:b221eadb-b5de-11ea-9626-6805caa7f9a8:319767-319772

        LAST_ERROR_NUMBER: 0

       LAST_ERROR_MESSAGE:

     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

3 rows in set (0.00 sec)

验证数据同步是否正常

 # 在业务库上创建一个账号   create user  userxxx@'%' identified by 'passxxx';

# 在MGR集群进行查询

select user,host,authentication_string from mysql.user;

可发现，数据同步正常