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

Zabbix监控MySQL的MGR群集状态操作教程~

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

1. 给MGR群集内的数据库监控账号赋权(这里用的zbxuser)

grant select on performance_schema.replication_group_members to 'zbxuser'@'%';

刷新mysql配置

flush privileges;

2. 在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