随着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.