MySQL custom monitoring based on Zabbix

MySQL Custom monitoring

Requirements: The company needs to do MySQL data monitoring, the status of MySQL, traffic monitoring.

Analysis: Zabbix automatically has template App MySQL template, we can only add MySQL monitoring script to achieve custom monitoring.

The configuration is as follows:

Zabbix service-side build, here no longer demo, I here Zabbixserver has been configured, directly into the MySQL monitoring build.

1. Build MySQL host groups group

MySQL The template is provided by the Zabbix system, enters the Zabbix Web background, configures the host group--click on "Create Host group"--Select template Group, select Templates "Templateapp MySQL, Templdate OS Linux ", Templateapp Zabbix agen;templateapp MySQL relies on template Templateapp Zabbix agent so also to be added. 1, Figure 2 shows:

Figure 1

Figure 2

2, deploy agent client, use Ansible a Jian installation, detailed can refer to my installation process http://blog.51cto.com/xiaozhagn/1975084

3, set up the host, add Zabbix host, add templates.

Configuration - --Host--Click Create Host--Create Zabbix client (I'm node2.1 here)--Select template option, select template "Templates App MySQL", "Templdate OS Linux", "Templateapp Zabbix Agent" , then click on the "Add" button on the left, and finally click "Update" to complete the creation. 3, Figure 4 shows:

Figure 3

Figure 4

4, turn on MySQL performance monitoring.

You can use the MySQL template that comes with Zabbix, but you also need to prepare a script for MySQL status on the MySQL server Chk_mysql.sh,zabbix call this script to get MySQL running information.

Add the MySQL monitoring information in the configuration Zabbix client file zabbix_agentd.conf:

[Email protected] ~]# vim/usr/local/zabbix/etc/zabbix_agentd.conf

#添加以下内容

USERPARAMETER=MYSQL.VERSION,MYSQL-VUSERPARAMETER=MYSQL.STATUS[*],/USR/LOCAL/ZABBIX/SCRIPTS/CHK_MYSQL.SH # This is the path to put the script userparameter=mysql.ping,netstat-ntpl |grep 3306 |grep mysql |wc |awk ' {print $} '

5, enter the database to add MySQL account, (my database here is installed with Yum)

MariaDB [(None)]> GRANT process,super,replication CLIENT on * * to [e-mail protected] ' 127.0.0.1 ' identified by ' Xiaozhan G ';

6. Next, prepare MySQL monitor for running information, and need to prepare new MySQL monitoring script chk_mysql.sh. (name to match the name of the script path added to the Zabbix client)

[Email protected] scripts]# mkdir/usr/local/zabbix/scripts/

[Email protected] scripts]# cat/usr/local/zabbix/scripts/chk_mysql.sh

#!/bin/sh# -------------------------------------------------------------------------------# filename :     check_mysql.sh# date:        2018/1/10#  author:     xiaozhang# email:       [ email protected]mysql_sock= "/var/lib/mysql/mysql.sock" mysql_user= ' Zabbix ' mysql_pwd= ' Xiaozhang ' MYSQL_HOST= ' 127.0.0.1 ' mysql_port= ' 3306 ' args=1if [ $# -ne  "$ARGS"  ];then     echo  "please input one arguement:" Ficase $1 in    uptime)         result= '/usr/bin/mysqladmin -u$mysql_user -h$mysql_ host -p${mysql_pwd} -s  $MYSQL _sock status|cut -f2 -d ":" |cut -f1 -d "T" '             echo  $result              ;;         com_update)              result= '/usr/bin/mysqladmin -u$mysql_user -h$mysql_host -p${mysql_pwd}  -S  $MYSQL _sock extended-status |grep -w  "com_update" |cut -d "|"  -f3 '             echo  $result              ;;         slow_queries)          result= '/usr/bin/mysqladmin -u$mysql_user -h$mysql_host -p${mysql_pwd} -s  $MYSQL _sock  status |cut -f5 -d ":" |cut -f1 -d "O"                  echo  $result                  ;;     com_select)         result= '/usr/bin/mysqladmin  -u$MYSQL_USER -h$MYSQL_HOST -p${MYSQL_PWD} -S  $MYSQL _sock extended-status  |grep -w  "Com_select" |cut -d "|"  -f3 '                 echo   $result                 ;;     com_rollback)         result= '/usr/bin/ mysqladmin -u$mysql_user -h$mysql_host -p${mysql_pwd} -s  $MYSQL _sock  extended-status |grep -w  "Com_rollback" |cut -d "|"  -f3 '                 echo   $result                 ;;     questions)         result= '/usr/bin/mysqladmin  -u$MYSQL_USER -h$MYSQL_HOST -p${MYSQL_PWD} -S  $MYSQL _sock status|cut -f4  -d ":" |cut -f1 -d "S" '                  echo  $result                  ;;     com_insert)         result= '/usr/bin/mysqladmin  -u$MYSQL_USER -h$MYSQL_HOST -p${MYSQL_PWD} -S  $MYSQL _sock extended-status  |grep -w  "Com_insert" |cut -d "|"  -f3 '                 echo   $result                 ;;     com_delete)          result= '/usr/bin/mysqladmin -u$mysql_user -h$mysql_host -p${ mysql_pwd} -s  $MYSQL _sock extended-status |grep -w  "Com_delete" |cut -d "|"  -f3 '                 echo   $result                 ;;     com_commit)         result= '/usr/bin/mysqladmin  -u$MYSQL_USER -h$MYSQL_HOST -p${MYSQL_PWD} -S  $MYSQL _sock extended-status  |grep -w  "Com_commit" |cut -d "|"  -f3 '                 echo   $result                 ;;     bytes_sent)         result= '/usr/bin/mysqladmin -u$mysql_user -h$mysql_host -p${mysql_pwd} -s  $MYSQL _sock extended-status |grep -w  "bytes_sent"  |cut -d "|"  -f3 '                 echo   $result                 ;;     bytes_received)         result= '/usr/bin/ mysqladmin -u$mysql_user -h$mysql_host -p${mysql_pwd} -s  $MYSQL _sock  extended-status |grep -w  "bytes_received"  |cut -d "|"  -f3 '                 echo   $result                 ;;     com_begin)         result= '/usr/bin/mysqladmin -u$mysql_user -h$mysql_host -p${mysql_pwd} -s  $MYSQL _sock  extended-status |grep -w  "Com_begin" |cut -d "|"  -f3 '                 echo   $result                 ;;                                 *)          echo  "Usage:$0 (uptime| com_update| slow_queries| Com_select| com_rollback| Questions) "        ;; Esac

7 , add chec_mysql script permissions:

[[Email protected] ~] #chmod u+x/usr/local/zabbix/scripts/chk_mysql.sh [[email protected] ~] #chown-R zabbix.zabbix/usr /local/zabbix/scripts/chk_mysql.sh

8 , restart the agent, check the basic MySQL server information on the Zabbix server to obtain the normal.

Restarting the client

[Email protected] ~]#/etc/init.d/zabbix_agentd restart

Service test to get MySQL monitoring data

[Email protected] ~]# zabbix_get-s 172.25.0.30-p10050-k "system.cpu.load[all,avg15]"; 0.050000 [[email protected] ~]# zabbix_get-s 172.25.0.30-p10050-k mysql.status[com_update] 0[[email protected] ~]# za Bbix_get-s 172.25.0.30-p10050-k mysql.status[com_insert]0

9. Monitor MySQL Performance graph add

After the monitoring script is running, you will see the MySQL monitoring performance view in the "graphics" under "Monitoring" in Zabbix-server, select Host node2.1 (zabbix_agent), and choose MySQL Operations can see the performance monitoring view, as shown in 5:

Figure 5

10, Zabbix self-contained template by default there are 2 performance graphs, of course, you can also create additional diagrams, only need to find the host, create graphics can be, 6, Figure 7 shows:

Figure 6

Figure 7

MySQL custom monitoring based on Zabbix