从MGR集群到单机的GTID复制

最新推荐文章于 2022-05-26 17:19:17 发布

windyzhou

最新推荐文章于 2022-05-26 17:19:17 发布

阅读量583

点赞数

分类专栏： mysql 文章标签： mysql group replication GTID

本文链接：https://blog.csdn.net/windyzhou/article/details/80193780

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

为了方便描述，我们使用字母代码来标记实例：
群组有3个实例：A、B、C
单节点实例为D

当前主节点为A，

在群组集群主节点上配置普通复制slave专用用户：

CREATE USER repl@'%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';

在单节点上配置到主节点的复制，5.7上建议设置channel名称：

CHANGE MASTER TO
MASTER_HOST='127.0.0.1',    
MASTER_USER='repl',    
MASTER_PASSWORD='repl',    
MASTER_PORT=24801,    
MASTER_AUTO_POSITION = 1
for CHANNEL 'normal_replication';

测试案例：
本案例为了验证利用GTID复制的强大性，在原来主节点A出故障后，通过GTID复制从新主节点B上同步，验证从机D末出现数据丢失。
1、把D上的slave进程停掉，并做复制信息清理。
2、A上增加一条数据后，退出群组复制，然后重新加入。
3、B成为新主节点，在B上也增加一条数据。

4、配置D从B复制，确认有最新的两条数据。

测试步骤：

D上操作：

mysql> reset slave all for channel 'normal_replication';
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
Empty set (0.00 sec)

mysql> select * from test.t1;
+----+--------+
| c1 | c2     |
+----+--------+
|  1 | Luis   |
|  2 | from A |
+----+--------+
2 rows in set (0.00 sec)

A上操作：

mysql> show global status like 'group_replication%';
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 595937c0-4d9c-11e8-a819-00163e06ea60 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29d2ae7b-4de8-11e8-a27e-00163e06ea60 | myserver01  |       24802 | ONLINE       |
| group_replication_applier | 2fdfc55d-4de8-11e8-a3af-00163e06ea60 | myserver01  |       24803 | ONLINE       |
| group_replication_applier | 595937c0-4d9c-11e8-a819-00163e06ea60 | myserver01  |       24801 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

mysql> select * from test.t1;
+----+--------+
| c1 | c2     |
+----+--------+
|  1 | Luis   |
|  2 | from A |
+----+--------+
2 rows in set (0.00 sec)

mysql> insert into test.t1 values(3,'From A');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test.t1;
+----+--------+
| c1 | c2     |
+----+--------+
|  1 | Luis   |
|  2 | from A |
|  3 | From A |
+----+--------+
3 rows in set (0.00 sec)

mysql> stop group_replication;
Query OK, 0 rows affected (9.35 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 595937c0-4d9c-11e8-a819-00163e06ea60 | myserver01  |       24801 | OFFLINE      |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

mysql> start group_replication;
Query OK, 0 rows affected (4.29 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29d2ae7b-4de8-11e8-a27e-00163e06ea60 | myserver01  |       24802 | ONLINE       |
| group_replication_applier | 2fdfc55d-4de8-11e8-a3af-00163e06ea60 | myserver01  |       24803 | ONLINE       |
| group_replication_applier | 595937c0-4d9c-11e8-a819-00163e06ea60 | myserver01  |       24801 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

mysql> show global status like 'group_replication%';
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 29d2ae7b-4de8-11e8-a27e-00163e06ea60 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

在B上操作：

mysql> select * from test.t1;
+----+--------+
| c1 | c2     |
+----+--------+
|  1 | Luis   |
|  2 | from A |
|  3 | From A |
+----+--------+
3 rows in set (0.00 sec)

mysql> insert into test.t1 values(4,'From B');
2018-05-03T15:29:12.592666Z 23 [Note] Plugin group_replication reported: 'Primary had applied all relay logs, disabled conflict detection'
Query OK, 1 row affected (0.01 sec)

mysql> select * from test.t1;
+----+--------+
| c1 | c2     |
+----+--------+
|  1 | Luis   |
|  2 | from A |
|  3 | From A |
|  4 | From B |
+----+--------+
4 rows in set (0.00 sec)

在D上操作：

CHANGE MASTER TO
MASTER_HOST='127.0.0.1',    
MASTER_USER='repl',    
MASTER_PASSWORD='repl',    
MASTER_PORT=24802,    
MASTER_AUTO_POSITION = 1
for CHANNEL 'normal_replication';


mysql> select * from test.t1;
+----+--------+
| c1 | c2     |
+----+--------+
|  1 | Luis   |
|  2 | from A |
+----+--------+
2 rows in set (0.00 sec)

mysql> CHANGE MASTER TO
    -> MASTER_HOST='127.0.0.1',    
    -> MASTER_USER='repl',    
    -> MASTER_PASSWORD='repl',    
    -> MASTER_PORT=24802,    
    -> MASTER_AUTO_POSITION = 1
    -> for CHANNEL 'normal_replication';
Query OK, 0 rows affected, 2 warnings (0.04 sec)

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 24802
                Connect_Retry: 60
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 2203
               Relay_Log_File: myserver01-relay-bin-normal_replication.000002
                Relay_Log_Pos: 1191
        Relay_Master_Log_File: binlog.000004
             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: 2203
              Relay_Log_Space: 1414
              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: 2
                  Master_UUID: 29d2ae7b-4de8-11e8-a27e-00163e06ea60
             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: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:22-24
            Executed_Gtid_Set: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-24
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: normal_replication
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> select * from test.t1;
+----+--------+
| c1 | c2     |
+----+--------+
|  1 | Luis   |
|  2 | from A |
|  3 | From A |
|  4 | From B |
+----+--------+
4 rows in set (0.00 sec)

可以看到第3，4行的数据，数据验证是全的。
我们可以更进一步，把主库切换回A，然后在A上新增加数据，保持D从B复制，看看会不会有数据不复制过来。

在B上操作：

mysql> stop group_replication;
Query OK, 0 rows affected (9.54 sec)

mysql> start group_replication;
Query OK, 0 rows affected (3.08 sec)

在A上操作：

mysql> show global status like 'group_replication%';
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 595937c0-4d9c-11e8-a819-00163e06ea60 |
+----------------------------------+--------------------------------------+
1 row in set (0.01 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29d2ae7b-4de8-11e8-a27e-00163e06ea60 | myserver01  |       24802 | ONLINE       |
| group_replication_applier | 2fdfc55d-4de8-11e8-a3af-00163e06ea60 | myserver01  |       24803 | ONLINE       |
| group_replication_applier | 595937c0-4d9c-11e8-a819-00163e06ea60 | myserver01  |       24801 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

mysql> insert into test.t1 values(5,'From A');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test.t1;
+----+--------+
| c1 | c2     |
+----+--------+
|  1 | Luis   |
|  2 | from A |
|  3 | From A |
|  4 | From B |
|  5 | From A |
+----+--------+
5 rows in set (0.00 sec)

在D上操作：

mysql> select * from test.t1;
+----+--------+
| c1 | c2     |
+----+--------+
|  1 | Luis   |
|  2 | from A |
|  3 | From A |
|  4 | From B |
|  5 | From A |
+----+--------+
5 rows in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 24802
                Connect_Retry: 60
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 2766
               Relay_Log_File: myserver01-relay-bin-normal_replication.000002
                Relay_Log_Pos: 1754
        Relay_Master_Log_File: binlog.000004
             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: 2766
              Relay_Log_Space: 1977
              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: 2
                  Master_UUID: 29d2ae7b-4de8-11e8-a27e-00163e06ea60
             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: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:22-26
            Executed_Gtid_Set: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-26
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: normal_replication
           Master_TLS_Version: 
1 row in set (0.00 sec)

可以看到同步正常，数据也过来了。
测试案例说明：
1、MGR集群到单机的复制是可行的
2、GTID复制可以轻易解决从库在主机down掉指向新主机的问题，从新主库同步的配置变得如此轻松
3、当从库复制的主机在维护或者其他故障导致的变为MGR从库时，到单机的复制也不会中断，当然需要log_slave_updates=ON的支持。

windyzhou

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
从MGR集群到单机的GTID复制

为了方便描述，我们使用字母代码来标记实例：群组有3个实例：A、B、C单节点实例为D当前主节点为A，在群组集群主节点上配置普通复制slave专用用户：CREATE USER repl@'%' IDENTIFIED BY 'repl';GRANT REPLICATION SLAVE ON *.* TO repl@'%';在单节点上配置到主节点的复制，5.7上建议设置channel名称：CHANGE ...
复制链接

扫一扫