如何把一个现有的传统主从架构转换为MGR架构
随着MySQL版本的迭代,功能的完善,Group replication必将是未来的主流。在不远的将来,必将有许多的传统主从架构改为MGR复制架构。然而这个转换过程并不复杂!
一、完善配置
group replication 插件要求如下配置。如果你的my.cnf缺少这些配置需要加上。
1 | gtid_mode=ON |
二、重启mysqld
这里需要一次性完成my.cnf的配置,重启一次就行了。
三、配置Group replication
配置
group_replication_recovery
线程,在三个节点执行:1
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
在主节点启动group replication
1
2
3SET 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后就会创建这个账号。
- 对于原来的从节点,则需要停掉从节点原来的异步复制线程,然后直接启动 group_replication
1
2
3stop slave;
reset slave all;
start group_replication;
如果没有配置report_host=192.168.216.200
,group replication 默认使用主机名来联系各个程序,而非IP1
2
3
4
5
6
7
8mysql> 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
8mysql> 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呢? 现在终于搞清楚了。
解决办法有两个:
- 需要在/etc/hosts中配置三台主机的IP与主机名的对应关系。
- 在my.cnf中配置report_host为本机IP(推荐)
我们前面已经在my.cnf中配置了report_host,这个问题不会再出现。
配置report_host后,可以看到MEMBER_HOST
是IP了:1
2
3
4
5
6
7mysql> 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
9mysql> 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 |
|
这里需要给这个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.