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

原创

乐维_lwops 2024-03-29 15:00:36 博主文章分类：zabbix技术干货 ©著作权

文章标签 sql MySQL zabbix 文章分类 运维

©著作权归作者所有：来自51CTO博客作者乐维_lwops的原创作品，请联系作者获取转载授权，否则将追究法律责任

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 //返回数据类似下图

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