Zabbix (4): Monitors multiple MySQL instances on Microsoft cloud Azure by modifying the official apsaradb for redis template of Zabbix.
Since MySQL instances on Microsoft cloud cannot install plug-ins to export data, refer to the previous article to monitor multiple Redis instances on Microsoft cloud Azure using the official Zabbix template. We can modify the official redis script of zabbix, use commands such as show status to obtain mysql status indicators and send them to zabbix in trap-messages mode to monitor MySQL instances on Microsoft cloud.
1. Running environment:
OS: CentOS 7.3/Python: 2.7.3/Pip: 9.0.1/Zabbix: 3.2.8/MySQL: Microsoft cloud MySQL 5.7
The apsaradb for redis monitoring template officially provided by Zabbix. we modified the official python script to monitor MySQL.
Project address: https://github.com/blacked/zbx_redis_template
All official Zabbix templates: http://www.zabbix.org/wiki/Zabbix_Templates
2. Deployment
For more information about the trap-messages method, see the previous article Monitoring Multiple Redis instances on Microsoft cloud Azure using the official Zabbix template.
1> install Python Dependencies
# yum -y install python-pip# pip install argparse# pip install pymysql
2> place zbx_redis_stats.py in a path on the zabbix server and rename it, for example,/etc/zabbix/script/mysql/zbx_mysql_stats.py.
3> modify the script. The following is my modified script. It monitors the information obtained by show status and the space occupied by database tables. You can directly copy it, or add other information you want to monitor.
1 # !/usr/bin/python 2 3 import sys, pymysql, json, re, struct, time, socket, argparse 4 5 parser = argparse.ArgumentParser(description='Zabbix MySQL status script') 6 parser.add_argument('mysql_hostname', nargs='?') 7 parser.add_argument('metric', nargs='?') 8 parser.add_argument('db', default='none', nargs='?') 9 parser.add_argument('-p', '--port', dest='mysql_port', action='store', help='MySQL server port', default=3306, type=int) 10 parser.add_argument('-a', '--auth', dest='mysql_pass', action='store', help='MySQL server pass', default=None) 11 parser.add_argument('-u', '--user', dest='mysql_user', action='store', help='MySQL server user', default=None) 12 args = parser.parse_args() 13 14 zabbix_host = '127.0.0.1' # Zabbix Server IP 15 zabbix_port = 10051 # Zabbix Server Port 16 17 # Name of monitored server like it shows in zabbix web ui display 18 mysql_hostname = args.mysql_hostname if args.mysql_hostname else socket.gethostname() 19 20 21 class Metric(object): 22 def __init__(self, host, key, value, clock=None): 23 self.host = host 24 self.key = key 25 self.value = value 26 self.clock = clock 27 28 def __repr__(self): 29 result = None 30 if self.clock is None: 31 result = 'Metric(%r, %r, %r)' % (self.host, self.key, self.value) 32 else: 33 result = 'Metric(%r, %r, %r, %r)' % (self.host, self.key, self.value, self.clock) 34 return result 35 36 37 def value_format(value): 38 if value.isdigit() is True: 39 value = int(value) 40 return value 41 42 43 def send_to_zabbix(metrics, zabbix_host='127.0.0.1', zabbix_port=10051): 44 result = None 45 j = json.dumps 46 metrics_data = [] 47 for m in metrics: 48 clock = m.clock or ('%d' % time.time()) 49 metrics_data.append( 50 ('{"host":%s,"key":%s,"value":%s,"clock":%s}') % (j(m.host), j(m.key), j(m.value), j(clock))) 51 json_data = ('{"request":"sender data","data":[%s]}') % (','.join(metrics_data)) 52 data_len = struct.pack('<Q', len(json_data)) 53 packet = 'ZBXD\x01' + data_len + json_data 54 55 # For debug: 56 # print(packet) 57 # print(':'.join(x.encode('hex') for x in packet)) 58 59 try: 60 zabbix = socket.socket() 61 zabbix.connect((zabbix_host, zabbix_port)) 62 zabbix.sendall(packet) 63 resp_hdr = _recv_all(zabbix, 13) 64 if not resp_hdr.startswith('ZBXD\x01') or len(resp_hdr) != 13: 65 print('Wrong zabbix response') 66 result = False 67 else: 68 resp_body_len = struct.unpack('<Q', resp_hdr[5:])[0] 69 resp_body = zabbix.recv(resp_body_len) 70 zabbix.close() 71 72 resp = json.loads(resp_body) 73 # For debug 74 # print(resp) 75 if resp.get('response') == 'success': 76 result = True 77 else: 78 print('Got error from Zabbix: %s' % resp) 79 result = False 80 except: 81 print('Error while sending data to Zabbix') 82 result = False 83 finally: 84 return result 85 86 87 def _recv_all(sock, count): 88 buf = '' 89 while len(buf) < count: 90 chunk = sock.recv(count - len(buf)) 91 if not chunk: 92 return buf 93 buf += chunk 94 return buf 95 96 97 def main(): 98 if mysql_hostname and args.metric: 99 100 client = pymysql.connect(host=mysql_hostname, user=args.mysql_user, password=args.mysql_pass)101 cursor = client.cursor()102 cursor.execute("show status")103 data = cursor.fetchall()104 dict_local = {}105 for k, v in data:106 dict_local[k] = v107 108 cursor.execute("select SCHEMA_NAME from information_schema.schemata ")109 db = cursor.fetchall()110 dblist = []111 for i in db:112 dblist.append(i[0])113 114 if args.metric:115 def list_db():116 if args.db in dblist:117 print(dblist)118 else:119 print('database_detect')120 121 def default():122 if args.metric in dict_local.keys():123 print(dict_local[args.metric])124 125 {126 'list_db': list_db127 }.get(args.metric, default)()128 129 else:130 print('Not selected metric')131 else:132 client = pymysql.connect(host=mysql_hostname, user=args.mysql_user, password=args.mysql_pass)133 cursor = client.cursor()134 cursor.execute("show status")135 data = cursor.fetchall()136 137 a = []138 for k, v in data:139 k = k.lower()140 v = value_format(v)141 a.append(Metric(mysql_hostname, ('azuremysql[%s]' % k), v))142 143 # cursor.execute("show processlist;")144 cursor.execute("select count(*) from information_schema.processlist;")145 processdata = cursor.fetchall()146 # process_count = len(processdata)147 process_count = processdata[0][0]148 a.append(Metric(mysql_hostname, 'azuremysql[process_count]', process_count))149 150 cursor.execute(151 "select table_schema,round(sum(data_length+index_length)/1024/1024,4) from information_schema.tables group by table_schema;")152 spacedata = cursor.fetchall()153 totalspace = 0154 for k, v in spacedata:155 a.append(Metric(mysql_hostname, ('azuremysql[%s]' % k), float(v)))156 totalspace += float(v)157 a.append(Metric(mysql_hostname, 'azuremysql[totalspace]', totalspace))158 159 # Send packet to zabbix160 send_to_zabbix(a, zabbix_host, zabbix_port)161 # for i in a:162 # print(i)163 cursor.close()164 client.close()165 166 167 if __name__ == '__main__':168 main()
Zbx_mysql_stats.py
Zbx_mysql_stats.py
4> test whether the script can connect to MySQL on Microsoft cloud and obtain the parameters:
/etc/zabbix/script/mysql/zbx_mysql_status.py -u mysqluser -a mysqlpasswd test.mysqldb.chinacloudapi.cn list_db
5> Add crontab and periodically execute the script to send data to zabbix. Note that if the password contains %, escape it with \ (\ % ).
To monitor multiple MySQL instances, add multiple crontabs:
*/1 * * * * /etc/zabbix/script/mysql/zbx_mysql_status.py -u mysqluser -a mysqlpasswd test.mysqldb.chinacloudapi.cn
6> Create a MySQL monitoring template. Select "zabbix Collector" for the metric item type. If the script is used, the key value is "azuremysql [xxxxxxx]". otherwise, use the key value defined by your script.
The attachment is a template created by me. Some metric items are not complete. You can add the required metric items on this basis.
Zbx_Azure_MySQL_template.xml
7> use MySQL hostname to create a host and link the template to implement zabbix monitoring.