Shulou(Shulou.com)06/01 Report--

Mysql Custom Monitoring

Requirements: the company needs to do mysql data monitoring to monitor the status and traffic of mysql.

Analysis: zabbix automatically has Template App MySQL template, we only need to add mysql monitoring script to achieve custom monitoring.

The configuration is as follows:

The server of zabbix is built, and it will not be demonstrated here. My zabbixserver here is already configured, so go directly to the mysql monitoring build.

1. Establish mysql host groups group

The mysql template is provided by the zabbix system. Enter the zabbix web backend, configure-- > Host Group-- > Click "create Host Group"-- > Select the template group and select the template "TemplateApp MySQL,Templdate OS Linux". TemplateApp Zabbix Agen;TemplateApp MySQL depends on the template TemplateApp Zabbix Agent, so add it. As shown in figures 1 and 2:

Figure 1

Figure 2

2. Deploy the agent client and install it using ansible. For more information, please refer to my installation process https://blog.51cto.com/xiaozhagn/1975084.

3. Set up the host, add the zabbix client host, and add the template.

Configure-- > Host-- > Click create Host-> create zabbix client (I am node2.1 here)-> Select template options, select template "TemplateApp MySQL", "Templdate OS Linux", "TemplateApp Zabbix Agent", and finally click the "add" button on the left, and finally click "Update" to complete the creation. As shown in figures 3 and 4:

Figure 3

Figure 4

4. Enable mysql performance monitoring.

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

Add mysql monitoring information to the configuration zabbix client file zabbix_agentd.conf:

[root@node2 ~] # vim / usr/local/zabbix/etc/zabbix_agentd.conf

# add the following

UserParameter=mysql.version,mysql-VUserParameter=mysql.status [*], / usr/local/zabbix/scripts/chk_mysql.sh $1 # this is the path where the script is placed UserParameter=mysql.ping,netstat-ntpl | grep 3306 | grep mysql | wc | awk'{print $1}'

5. Go to the database and add a mysql account (my database here is installed with yum)

MariaDB [(none)] > GRANT PROCESS,SUPER,REPLICATION CLIENT ON *. * TO zabbix@'127.0.0.1' IDENTIFIED BY 'xiaozhang'

6. Next, to prepare the running information monitoring of mysql, you need to prepare a new mysql monitoring script chk_mysql.sh. (the name should be the same as the name of the script path added on the zabbix client)

[root@node2 scripts] # mkdir / usr/local/zabbix/scripts/

[root@node2 scripts] # cat / usr/local/zabbix/scripts/chk_mysql.sh

#! / bin/sh#-# FileName: check_mysql.sh# Date: 2018 UniUniverse 1 Author: xiaozhang# Email: 847536944roomqq.comMYSQLroomSOCK = "/ 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:

[root@node2 ~] # chmod uplix / usr/local/zabbix/scripts/chk_mysql.sh [root@node2 ~] # chown-R zabbix.zabbix / usr/local/zabbix/scripts/chk_mysql.sh

8. Restart agent and check whether the basic mysql server information can be obtained normally on the zabbix server.

Restart the client

[root@node2 ~] # / etc/init.d/zabbix_agentd restart

Service testing to obtain mysql monitoring data

[root@node1 ~] # zabbix_get-s 172.25.0.30-p10050-k "system.cpu.load [all,avg15]"; 0.050000 [root@node1 ~] # zabbix_get-s 172.25.0.30-p10050-k mysql.status [Com _ update] 0 [root@node1 ~] # zabbix_get-s 172.25.0.30-p10050-k mysql.status [Com _ insert] 0

9. Monitor the addition of mysql performance graphs

After the monitoring script runs normally, you will see the monitoring performance view of mysql in the "Graph" below "Monitoring" in zabbix-server. Select the host node2.1 (zabbix_agent), and select MySQL operations to see the performance monitoring view, as shown in figure 5:

Figure 5

10. Zabbix comes with two performance charts by default. Of course, you can also create other diagrams. You only need to find the location of the host and create a graph, as shown in figure 6 and figure 7:

Figure 6

Figure 7

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.