MySQL Shell如何接管手动搭建(含仲裁节点)MGR集群
本文源自GreatSQL社区用户的一次提问:
Q:一个包含仲裁节点(ARBITRATOR)的GreatSQL MGR集群,一开始是用手动方式构建,后来想用MySQL Shell接管,可以吗?
A:是可以的,不过也有一定局限性
具体的操作如下
检查当前MGR集群情况
greatsql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 04b57be0-73a0-11ee-a450-00155d064000 | 192.168.5.170 | 3307 | ONLINE | SECONDARY | 8.0.32 | XCom |
| group_replication_applier | 0b157081-73a7-11ee-899b-00155d064000 | 192.168.5.170 | 3308 | ONLINE | ARBITRATOR | 8.0.32 | XCom |
| group_replication_applier | d4b877cf-16f0-11ee-9e98-00155d064000 | 192.168.5.170 | 3306 | ONLINE | PRIMARY | 8.0.32 | XCom |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)
可以看到三个节点都是ONLINE
状态
专属账户增加相应授权
连接 Primary 节点,查看下原来的账户权限情况,对MGR专属账户增加相应授权
greatsql> show grants for GreatSQL;
+--------------------------------------------------+
| Grants for GreatSQL@% |
+--------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `GreatSQL`@`%` |
| GRANT BACKUP_ADMIN ON *.* TO `GreatSQL`@`%` |
+--------------------------------------------------+
可以看到该权限并不能足以让 Shell 使用,需要增加授权才可以
以下是用 Shell 接管的 MGR 集群专属账户授权,手动添加到权限一致即可
greatsql> show grants for GreatSQL;
# 只展示关键权限部分
| GRANT SELECT, RELOAD, SHUTDOWN, PROCESS, FILE, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO `GreatSQL`@`%` WITH GRANT OPTION|
| GRANT BACKUP_ADMIN ON *.* TO `GreatSQL`@`%`|
| GRANT CLONE_ADMIN,CONNECTION_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,ROLE_ADMIN,SYSTEM_VARIABLES_ADMIN ON *.* TO `GreatSQL`@`%` WITH GRANT OPTION|
| GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO `GreatSQL`@`%` WITH GRANT OPTION|
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata`.* TO `GreatSQL`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata_bkp`.* TO `GreatSQL`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata_previous`.* TO `GreatSQL`@`%` WITH GRANT OPTION |
上述授权工作在 Primary 节点执行完后,Secondary节点会自动跟随。ARBITRATOR节点需要手动处理。
ARBITRATOR节点手动增加授权
修改 **ARBITRATOR **节点的my.cnf,关闭 ARBITRATOR 角色
(设置 group_replication_arbitrator = 0
),并记得确保MGR不会自动启动
(设置 group_replication_start_on_boot = OFF
),然后重启该实例。
重启完成后,此时尚未启动MGR进程,因此 ARBITRATOR 节点会变成一个普通实例,可以对其进行读写操作。
-- 手动增加相应授权
greatsql> set sql_log_bin = 0;
-- 参考第2步,手动增加相应授权
greatsql> GRANT ....
确认授权完成后,即可关闭该实例,重新启用 ARBITRATOR 角色(设置 group_replication_arbitrator = 1
),重启实例,但先不启动 MGR进程,后面再说。
用MySQL Shell接管MGR
利用Shell接管现有MGR:
mysqlsh> c=dba.create_cluster("mgr",{"adoptFromGR": "true"})
参数{"adoptFromGR": "true"}
的作用就是告诉Shell,接管现有MGR集群,而不是全新创建一个。
之后会很顺利地完成接管,此时只有 Primary 和 Secondary 两个节点:
shell> c=dba.create_cluster("mgr", {"adoptFromGR":"true"})
A new InnoDB cluster will be created based on the existing replication group on instance '127.0.0.1:3306'.
Creating InnoDB cluster 'mgr' on '192.168.5.170:3306'...
Adding Seed Instance...
Adding Instance '192.168.5.170:3307'...
Adding Instance '192.168.5.170:3306'...
Resetting distributed recovery credentials across the cluster...
NOTE: User 'mysql_innodb_cluster_3307'@'%' already existed at instance '192.168.5.170:3306'. It will be deleted and created again with a new password.
Cluster successfully created based on existing replication group.
查看下状态
shell> c.status()
{
"clusterName": "mgr",
"defaultReplicaSet": {
"name": "default",
"primary": "192.168.5.170:3306",
"ssl": "DISABLED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"192.168.5.170:3306": {
"address": "192.168.5.170:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.32"
},
"192.168.5.170:3307": {
"address": "192.168.5.170:3307",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.32"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "192.168.5.170:3306"
}
连接ARBITRATOR节点,启动MGR进程
连接 ARBITRATOR 节点,并执行 start group_replication
启动MGR进程,此时能看到各节点状态工作正常:
greatsql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 04b57be0-73a0-11ee-a450-00155d064000 | 192.168.5.170 | 3307 | ONLINE | SECONDARY | 8.0.32 | XCom |
| group_replication_applier | 0b157081-73a7-11ee-899b-00155d064000 | 192.168.5.170 | 3308 | ONLINE | ARBITRATOR | 8.0.32 | XCom |
| group_replication_applier | d4b877cf-16f0-11ee-9e98-00155d064000 | 192.168.5.170 | 3306 | ONLINE | PRIMARY | 8.0.32 | XCom |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)
切换到 MySQL Shell 查看
shell> c.status()
"clusterName": "mgr",
"defaultReplicaSet": {
"name": "default",
"primary": "192.168.5.170:3306",
"ssl": "DISABLED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"192.168.5.170:3306": {
"address": "192.168.5.170:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.32"
},
"192.168.5.170:3307": {
"address": "192.168.5.170:3307",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.32"
},
"192.168.5.170:3308": {
"address": "192.168.5.170:3308",
"instanceErrors": [
"WARNING: Instance is not managed by InnoDB cluster. Use cluster.rescan() to repair."
],
"memberRole": "ARBITRATOR",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.32"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "192.168.5.170:3306"
}
可以看到已经能看到所有节点,包括 ARBITRATOR 节点,但是因为该节点无法对其进行读写,所以实际上 Shell 接入时的一些初始化工作还是没完全执行,所以才有上面的提示:
"instanceErrors": [
"WARNING: Instance is not managed by InnoDB cluster. Use cluster.rescan() to repair."
],
不过并不影响,因为该节点只需参与MGR投票即可,可以忽略这个错误。
不知道注意到了没有,在这个过程中,并不需要像添加常规 Secondary 节点那样要 CLONE 全量数据。
**提醒:**后续如果要通过 Shell 对 MGR 做些操作,可能 ARBITRATOR 节点会提示不支持,此时只需临时把 ARBITRATOR 的MGR进程关闭,必要的操作执行完毕后再次启动MGR进程即可。
至此,就完成了 Shell 接管 MGR 集群的过程。
这里附带几个FAQ:
Q:在GreatSQL MGR集群中,新增 ARBITRATOR 节点时,是否一定要 CLONE 数据?
因为如果当前 Primary 节点上数据量巨大时,每次都 CLONE 代价太高了,那么第一次加入 ARBITRATOR 节点的成本有点难以接受。
A:当MGR中Primary节点已有用户数据时,无论是用 Shell 还是手动加入一个新的仲裁节点(ARBITRATOR),首次加入都需要经过 CLONE 的过程(即便是在启动前已经设置group_replication_arbitrator = 1)
变通的办法有几个:
- 第一个加入的ARBITRATOR节点,可以在加入成功后,关闭ARBITRATOR角色,然后删除所有用户数据,这时候就变成一个空实例了,再次重启后,再开启ARBITRATOR角色,不会再次 CLONE 数据。
- 在上述第一个ARBITRATOR节点的基础上,在其关闭期间,做一次物理全备,然后这个备份就可以作为未来新的ARBITRATOR节点的datadir,再次加入MGR集群也不会再次 CLONE 数据。
实际上,在加入 MGR 时,判断是否需要 CLONE 数据的依据是看 gtid_purged ,因此还有第三个办法:
- 在完成实例初始化后,手动修改 gtid_purged,例如
set global gtid_purged = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:1-1449587416';
也可以跳过数据 CLONE。
Enjoy GreatSQL :)
关于 GreatSQL
GreatSQL是适用于金融级应用的国内自主开源数据库,具备高性能、高可靠、高易用性、高安全等多个核心特性,可以作为MySQL或Percona Server的可选替换,用于线上生产环境,且完全免费并兼容MySQL或Percona Server。
相关链接: GreatSQL社区 Gitee GitHub Bilibili
GreatSQL社区:
社区有奖建议反馈: https://greatsql.cn/thread-54-1-1.html
社区博客有奖征稿详情: https://greatsql.cn/thread-100-1-1.html
(对文章有疑问或者有独到见解都可以去社区官网提出或分享哦~)
技术交流群:
微信&QQ群:
QQ群:533341697
微信群:添加GreatSQL社区助手(微信号:wanlidbc
)好友,待社区助手拉您进群。