Distributed monitoring System Zabbix using Percona to monitor MySQL

System Maintenance engineer Li Chao
We have introduced the Distributed Monitoring System zabbix-automatic discovery-add MySQL multi-instance monitoring, but did not provide the key that can be used directly, too shabby, the monitoring effect is poor. To monitor MySQL more carefully, the industry will choose to use Percona monitoring plugins to monitor MySQL. Percona has more detailed templates and scripts for monitoring MySQL, which improves the performance of innodb under high load conditions, provides some very useful performance diagnostic tools for OPS personnel, and has more parameters and commands to control server behavior. Needless to say, the following record uses Percona monitoring plugins to monitor MySQL's operation process:
The deployment path for the Zabbix of the monitored host is/data/zabbix.

Database directory:

Environment path We've all got it. Deploy our plays in the next step:

The following is the operation on the Zabbix monitored machine:
1) need to install PHP script running environment
Percona monitoring Zabbix script is written in PHP, so you need to prepare PHP runtime environment, here directly choose to install with Yum to meet the requirements:
CentOS System Installation:
#yum install-y PHP Php-mysql
Ubuntu System Installation:
#sudo apt-get Install php-* php-mysql

Ensure that the monitored host does not have Percona related software installed, if any, uninstall it!
#rpm-qa|grep Percona
2) Download the official Percona template (https://www.percona.com/downloads/percona-monitoring-plugins/)
Direct download: Https://pan.baidu.com/s/1c1IE7zq Extract Password: TRP5
(Package download not down the message I give you bag)
#wget https://www.percona.com/downloads/percona-monitoring-plugins/1.1.6/percona-zabbix-templates-1.1.6-1.noarch.rpm
Centos:
#rpm-IVH percona-zabbix-templates-1.1.6-1.noarch.rpm
Ubuntu:
sudo apt-get install Alien
sudo alien percona-zabbix-templates-1.1.6-1.noarch.rpm
sudo dpkg-i percona-zabbix-templates-1.1.6-2.deb
This is done after the decompression:

Go to two directories and see what's inside.
$ ll
Total 136
Drwxr-xr-x 2 root root 4096 Jul 19 14:50.
Drwxr-xr-x 4 root root 4096 Jul 18 16:51.. /
-rwxr-xr-x 1 Keytech Keytech 1385 Jul 14:50 get_mysql_stats_wrapper.sh
-rwxr-xr-x 1 root root 1254 Jul 18:29 Get_mysql_stats_wrapper.sh.bak
-rwxr-xr-x 1 Keytech keytech 59637 Jul 14:25 ss_get_mysql_stats.php
-rwxr-xr-x 1 root root 59637 Jul 16:58 ss_get_mysql_stats.php.bak
$ CD. /
scripts/templates/
$ CD. /templates/
$ ll
Total 272
Drwxr-xr-x 2 root root 4096 Jul 19 14:31.
Drwxr-xr-x 4 root root 4096 Jul 18 16:51.. /
-rw-r--r--1 root root 269258 Jan zabbix_agent_template_percona_mysql_server_ht_2.0.9-sver1.1.6.xml
-rw-r--r--1 root root 18866 Jan userparameter_percona_mysql.conf

As above, installing Percona will generate 2 directories in/var/lib/zabbix/percona, where:
/var/lib/zabbix/percona/scripts is the Monitoring script directory
/var/lib/zabbix/percona/templates is the XML template catalog that monitors MySQL
Then the template file under the/var/lib/zabbix/percona/templates/directory is zabbix_agent_template_percona_mysql_server_ht_2.0.9-sver1.1.6_1. The XML is copied out and imported into the Zabbix Admin interface import.
( NO NO NO 这个模板是2.0版本的zabbix可直接用的，目前大部分都是3.0版本以上的我用的3.4.5版本的zabbix。这个模板在最后边我会加上 You can also directly download the modified new template file is: Https://pan.baidu.com/s/1i5j8IJ7 extract password: ab8a).

3) Add parameter support
Copy the userparameter_percona_mysql.conf to the zabbix_agent's corresponding configuration directory on the monitored machine
#rsync-avpgolr/var/lib/ zabbix/percona/templates/userparameter_percona_mysql.conf/data/zabbix/etc/zabbix_agentd.conf.d/
#sed-i ' s#/ var/lib/zabbix/percona/scripts/#/data/zabbix/monitor_scripts/#g '/data/zabbix/etc/zabbix_agentd.conf.d/ userparameter_percona_mysql.conf

Note To modify the client machine's zabbix_agentd.conf file, add include to add the directory where the userparameter_percona_mysql.conf is located:
#cat/data/zabbix/etc/zabbix_agentd.conf|grep Include
# # #Option: Include
include=/data/zabbix/etc/zabbix_agentd.conf.d/
#Include =/usr/local/etc/zabbix_agentd.userparams.conf
#Include =/usr/local/etc/zabbix_agentd.conf.d/
#Include =/usr/local/etc/zabbix_agentd.conf.d/*.conf
4) Modify the monitoring script
Modify the/var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh script
-----------------------------------Note----------------------------------
If MySQL does not use the default port 3006, for example, MySQL uses 3317. You also need to modify the following script file, remember not to be in the pit here! Change port number file:
1-> here to build this file first, or will report RM permissions error

#touch/tmp/localhost-mysql_cacti_stats.txt:3307
#chown-R zabbix.zabbix/tmp/localhost-mysql_cacti_stats.txt:3307
2-> Modify the port script (the script below has been modified and can be used directly).

#!/bin/sh
#The wrapper for Cacti PHP script.
#It runs the script every 5 min. and parses the cache file on each following run.
#Version: 1.1.6
#This program was part of Percona monitoring Plugins
#License: GPL License (see COPYING)
#Copyright: Percona
#Authors: Roman Vynar

Item=$1
Host=localhost
Dir=dirname $0
cmd= "/usr/bin/php-q $DIR/ss_get_mysql_stats.php--host $HOST--items gg"
cachefile= "/tmp/$HOST-mysql_cacti_stats.txt:3307"
User= ' Root '
pass= ' keytech2017 '
sock= '/data/mysql/mysql_3307/tmp/mysql.sock '
If ["$ITEM" = "running-slave"]; Then

Check for running slave

RES=`/data/mysql/mysql_3307/bin/mysql -u$USER -h$HOST -p$PASS -S$sock -e ‘SHOW SLAVE STATUS\G‘ | egrep ‘(Slave_IO_Running|Slave_SQL_Running):‘ | awk -F: ‘{print $2}‘ | tr ‘\n‘ ‘,‘`    if [ "$RES" = " Yes, Yes," ]; then    echo 1else    echo 0fiexit

elif [-e $CACHEFILE]; Then

Check and run the script

TIMEFLM=`stat -c %Y /tmp/$HOST-mysql_cacti_stats.txt:3307`TIMENOW=`date +%s`if [ `expr $TIMENOW - $TIMEFLM` -gt 300 ]; then    rm -f $CACHEFILE    $CMD 2>&1 > /dev/nullfi

Else
$CMD 2>&1 >/dev/null
Fi

#Parse Cache file
If [-e $CACHEFILE]; Then
Cat $CACHEFILE | Sed ' s//\n/g; S/-1/0/g ' | grep $ITEM | Awk-f: ' {print $} '
Else
echo "Error:run the command manually to investigate the problem: $CMD"
Fi

Log in to MySQL create a monitor to use the account (grant permissions, and finally manual login verification)

Mysql> GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT on . To ' zabbixmysql ' @ ' localhost ' identified by "[email protected]";
Query OK, 0 rows affected (0.03 sec)
mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)
5) Monitoring items under test
Under the client ZABBIX-AGENTD test:
#/usr/bin/php-q/var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php--host localhost--items gg
Gg:6
Under Server Zabbix-server Test (192.168.1.10 is the IP address of the monitored host):
#/data/zabbix/bin/zabbix_get-s 192.168.1.10-p 10050-k "mysql.threads-connected"
1
#/data/zabbix/bin/zabbix_get-s 192.168.1.10-p 10050-k "Mysql.handler-commit"
3
Then go to the web to view our surveillance,

And then you're done, and there's one more question.

You're red, and the solution is to go in.


The share is complete.
System Maintenance engineer Li Chao

Distributed monitoring System Zabbix using Percona to monitor MySQL