随着MySQL版本的迭代，功能的完善，Group replication必将是未来的主流。在不远的将来，必将有许多的传统主从架构改为MGR复制架构。然而这个转换过程并不复杂！

一、完善配置

group replication 插件要求如下配置。如果你的my.cnf缺少这些配置需要加上。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
# 这个变量确保使用IP而非主机名，避免了配置/etc/hosts的麻烦
report_host=192.168.216.200
report_port=3306

#
# Group Replication configuration
#
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaab"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.216.200:24901"
loose-group_replication_group_seeds= "192.168.216.200:24901,192.168.216.201:24901,192.168.216.202:24901"
loose-group_replication_bootstrap_group= off

二、重启mysqld

这里需要一次性完成my.cnf的配置，重启一次就行了。

三、配置Group replication

  1. 配置group_replication_recovery线程，在三个节点执行：

    1
    CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

  2. 在主节点启动group replication

    1
    2
    3
    SET GLOBAL group_replication_bootstrap_group=ON;
    START GROUP_REPLICATION;
    SET GLOBAL group_replication_bootstrap_group=OFF;

启动时必须存在用户mysql.session, mysql5.7都会有这个账号，对于从mysql5.6升级到5.7，运行mysql_upgrade后就会创建这个账号。

  1. 对于原来的从节点，则需要停掉从节点原来的异步复制线程，然后直接启动 group_replication
    1
    2
    3
    stop slave;
    reset slave all;
    start group_replication;

如果没有配置report_host=192.168.216.200,group replication 默认使用主机名来联系各个程序，而非IP

1
2
3
4
5
6
7
8
mysql> select * from replication_group_members;
+---------------------------+--------------------------------------+-----------------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                       | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------------------------+-------------+--------------+
| group_replication_applier | b8641538-caa9-11e8-a2d4-525400452be3 | mha-test-02.localhost.localdomain |        3306 | RECOVERING   |
| group_replication_applier | e78a55d7-cb68-11e8-a2ab-525400330a25 | mha-test-01.localhost.localdomain |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-----------------------------------+-------------+--------------+
2 rows in set (0.00 sec)

如果这个主机名相互直接不能解析会报错。

1
2
3
4
5
6
7
8
mysql> select * from performance_schema.replication_connection_status;
+----------------------------+--------------------------------------+--------------------------------------+-----------+---------------+---------------------------+--------------------------+------------------------------------------------+-------------------+-------------------------------------------------------------------------------------------------------+----------------------+
| CHANNEL_NAME               | GROUP_NAME                           | SOURCE_UUID                          | THREAD_ID | SERVICE_STATE | COUNT_RECEIVED_HEARTBEATS | LAST_HEARTBEAT_TIMESTAMP | RECEIVED_TRANSACTION_SET                       | LAST_ERROR_NUMBER | LAST_ERROR_MESSAGE                                                                                    | LAST_ERROR_TIMESTAMP |
+----------------------------+--------------------------------------+--------------------------------------+-----------+---------------+---------------------------+--------------------------+------------------------------------------------+-------------------+-------------------------------------------------------------------------------------------------------+----------------------+
| group_replication_applier  | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaab | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaab |      NULL | ON            |                         0 | 0000-00-00 00:00:00      | e78a55d7-cb68-11e8-a2ab-525400330a25:1-9999957 |                 0 |                                                                                                       | 0000-00-00 00:00:00  |
| group_replication_recovery |                                      |                                      |      NULL | OFF           |                         0 | 0000-00-00 00:00:00      |                                                |              2005 | error connecting to master 'repl@mha-test-01.localhost.localdomain:3306' - retry-time: 60  retries: 1 | 2019-07-09 09:02:31  |
+----------------------------+--------------------------------------+--------------------------------------+-----------+---------------+---------------------------+--------------------------+------------------------------------------------+-------------------+-------------------------------------------------------------------------------------------------------+----------------------+
2 rows in set (15.39 sec)

我曾经一直有一个困惑，使用mysql-shell搭建group replication，其MEMBER_HOST是IP，为啥手工搭建的就是主机的hostname呢? 现在终于搞清楚了。

解决办法有两个：

  1. 需要在/etc/hosts中配置三台主机的IP与主机名的对应关系。
  2. 在my.cnf中配置report_host为本机IP（推荐）
    我们前面已经在my.cnf中配置了report_host,这个问题不会再出现。

配置report_host后，可以看到MEMBER_HOST是IP了：

1
2
3
4
5
6
7
mysql> select * from replication_group_members;
+---------------------------+--------------------------------------+-----------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST     | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+
| group_replication_applier | b8641538-caa9-11e8-a2d4-525400452be3 | 192.168.216.201 |        3306 | ONLINE       |
| group_replication_applier | e78a55d7-cb68-11e8-a2ab-525400330a25 | 192.168.216.200 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+

依次配置其它从库，这样一个三个节点的group replication就搭建好了！在performance_schema库中的表replication_group_members中可以查看到各节点的状态：

1
2
3
4
5
6
7
8
9
mysql> select * from replication_group_members;
+---------------------------+--------------------------------------+-----------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST     | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+
| group_replication_applier | b8641538-caa9-11e8-a2d4-525400452be3 | 192.168.216.201 |        3306 | ONLINE       |
| group_replication_applier | c2414bf1-caa9-11e8-a41c-5254000b8959 | 192.168.216.202 |        3306 | ONLINE       |
| group_replication_applier | e78a55d7-cb68-11e8-a2ab-525400330a25 | 192.168.216.200 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+
3 rows in set (0.00 sec)

四、创建InnoDB Cluster,使用mysql-shell来管理MGR

mysql-shell是mysql官方推出的命令行管理工具，目前已随mysql8一起发布，功能越来越艳，很值得尝试！
登陆到MGR的primary节点：

1
mysqlsh --uri mysqlsh@192.168.216.200:3306

这条命令会提示你输入密码，然后可以选择save,以后就不用再输入了。

根据一个现有的group replication初始化一个InnoDB Cluster:

1
2
3
4
5
6
7
8
9
10

 MySQL  192.168.216.200:3306 ssl  JS > var c = dba.createCluster('test_mgr',{adoptFromGR:true})
A new InnoDB cluster will be created based on the existing replication group on instance 'mysqlsh@192.168.216.200:3306'.

Creating InnoDB cluster 'test_mgr' on 'mysqlsh@192.168.216.200:3306'...
Adding Seed Instance...
Adding Instance '192.168.216.201:3306'...
Adding Instance '192.168.216.202:3306'...

Cluster successfully created based on existing replication group.

这里需要给这个cluster去一个名字：test_mgr. 创建成功后，会在primary节点上创建数据库mysql_innodb_cluster_metadata， 里面保存了这个cluster的元数据信息！

查看cluster的状态：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 MySQL  192.168.216.200:3306 ssl  JS > c.status()
{
    "clusterName": "test_mgr",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "192.168.216.200:3306",
        "ssl": "DISABLED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "192.168.216.200:3306": {
                "address": "192.168.216.200:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "192.168.216.201:3306": {
                "address": "192.168.216.201:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "192.168.216.202:3306": {
                "address": "192.168.216.202:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "192.168.216.200:3306"
}

 MySQL  192.168.216.200:3306 ssl  JS > c.describe()
{
    "clusterName": "test_mgr",
    "defaultReplicaSet": {
        "name": "default",
        "topology": [
            {
                "address": "192.168.216.201:3306",
                "label": "192.168.216.201:3306",
                "role": "HA"
            },
            {
                "address": "192.168.216.202:3306",
                "label": "192.168.216.202:3306",
                "role": "HA"
            },
            {
                "address": "192.168.216.200:3306",
                "label": "192.168.216.200:3306",
                "role": "HA"
            }
        ],
        "topologyMode": "Single-Primary"
    }
}

五、总结

从步骤来看，把生产上一个传统复制架构改成一个MGR架构并不复杂，基本上就是一次性修改好my.cnf配置，然后重启一下mysql就可以了。如果是新搭建一套MGR架构，建议使用mysql-shell， 步骤更加简单。以后对MGR的管理也可以都是用mysql-shell.