Zabbix (4): Monitors multiple MySQL instances on Microsoft cloud Azure by modifying the official apsaradb for redis template of Zabbix.

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.