如何使用Zabbix监控MySQL的MGR群集状态

MySQL的MGR（MySQL Group Replication）是MySQL官方提供的一种高可用性和高可靠性的集群解决方案。MGR通过使用基于组复制的方式，实现了多个MySQL实例之间的数据同步和故障转移，从而提供了自动故障恢复和负载均衡的功能。本文将介绍如何通过Zabbix监控mysql的MGR群集状态。

给MGR群集内的数据库监控账号赋权(这里用的zbxuser)
grant select on performance_schema.replication_group_members to ‘zbxuser’@‘%’;

刷新mysql配置

flush privileges;

在pymys.py内添加MGR监控脚本
cd /itops/zabbix/share/zabbix/externalscripts #zabbix的外部检查目录

vim pymys.py 写入以下内容：

#!/usr/bin/env python3 #encoding=utf-8 #edit by yang hao #version 0.1

import argparse import pymysql import inspect import json import re

class Checks(object): def mysql_mgr_state(self): #print(‘11’) #print(self.args.text) #sql = ‘select * from performance_schema.replication_group_members;’ sqlhost = ‘select MEMBER_HOST from performance_schema.replication_group_members;’ self.cur.execute(sqlhost) res = self.cur.fetchall() key = [‘{#MEMBERHOST}’] lst = [] for i in res: d = dict(zip(key, i)) lst.append(d) print(json.dumps({‘data’: lst}))

def mysql_mgr_status(self): name = self.args.text #print(name) sql = “select MEMBER_STATE from performance_schema.replication_group_members where MEMBER_HOST=‘%s’” % (name)+ “;” if self.cur.execute(sql): res = self.cur.fetchall()[0][0] print(res) else: print(“Empty set”)

def mysql_mgr_id(self): name = self.args.text #print(name) sql = “select MEMBER_ID from performance_schema.replication_group_members where MEMBER_HOST=‘%s’” % (name)+ “;” if self.cur.execute(sql): res = self.cur.fetchall()[0][0] print(res) else: print(“Empty set”)

class Main(Checks): def init(self): parser = argparse.ArgumentParser() parser.add_argument(‘–ip’) parser.add_argument(‘–username’) parser.add_argument(‘–password’) parser.add_argument(‘–port’) parser.add_argument(‘–database’) parser.add_argument(‘–text’)

subparsers = parser.add_subparsers()

for name in dir(self): if not name.startswith(“_”): p = subparsers.add_parser(name) method = getattr(self, name) argnames = inspect.getargspec(method).args[1:] for argname in argnames: p.add_argument(argname) p.set_defaults(func=method, argnames=argnames) self.args = parser.parse_args()

def db_connect(self): a = self.args ip = a.ip username = a.username password = a.password port = int(a.port) database = a.database self.db = pymysql.connect(host = ip, port = port, user = username,passwd = password, db = database) self.cur = self.db.cursor()

def db_close(self): self.cur.close() self.db.close()

def call(self): try: a = self.args callargs = [getattr(a, name) for name in a.argnames] self.db_connect() try: return self.args.func(*callargs) finally: self.db_close() except Exception as err: print(“0”) print(str(err))

if name == “main”: main = Main() main()

vim pymys 写入以下内容：

#!/usr/bin/bash /usr/bin/python3 /itops/zabbix/share/zabbix/externalscripts/pymys.py --ip $1 --username $2 --password $3 --port $4 --database $5 $6 $7 $8

测试命令参考：
./pymys 数据库地址用户名密码端口号库名 mysql_mgr_state //返回数据类似下图即可

测试拿群集状态信息：./pymys 数据库地址用户名密码端口号库名 --text 节点名称 mysql_mgr_status
在mysql监控模板创建自动发现规则
名称：MGR监控

类型：外部检查

键值：pymys[{ $KaTeX parse error: Expected 'EOF', got '}' at position 8: ADDRESS}̲,{$ USERNAME},{ $KaTeX parse error: Expected 'EOF', got '}' at position 9: PASSWORD}̲,{$ PORT},{$DATABASE},mysql_mgr_state]
在这里插入图片描述
5. 创建监控项原型一
名称：{#MEMBERHOST}的节点ID

类型：外部检查

键值：pymys[{ $KaTeX parse error: Expected 'EOF', got '}' at position 8: ADDRESS}̲,{$ USERNAME},{ $KaTeX parse error: Expected 'EOF', got '}' at position 9: PASSWORD}̲,{$ PORT},{$DATABASE},–text,{#MEMBERHOST},mysql_mgr_id] 在这里插入图片描述
6. 创建监控项原型二
名称：{#MEMBERHOST}的状态