To install and deploy zabbix, see the previous article< ZABBIX Enterprise Monitoring Installation Deployment>
http://blog.csdn.net/crpp0902/article/details/77978405
After deploying zabbix, the monitoring script for mysql is far from meeting the needs of DBA's daily monitoring. percona has this detailed monitoring template and script, which can be used for DBA's daily monitoring.
1. The php script runtime environment needs to be installed
percona's scripts for monitoring zabbix are written in php, so you need to be ready for the PHP runtime environment:
yum install -yphp php-mysql
2. Install percona-zabbix-templates
yum install https://www.percona.com/downloads/percona-monitoring-plugins/1.1.6/percona-zabbix-templates-1.1.6-1.noarch.rpm
After installation, we introduce several important files:
[root@slave1 zabbix_agentd.d]# cd /var/lib/zabbix/percona/ [root@slave1 percona]# ls scripts templates [root@slave1 percona]# [root@slave1 scripts]# cd ../templates/ [root@slave1 templates]# ls -alt total 292 drwxr-xr-x. 2 root root 4096 Sep 14 01:33 . drwxr-xr-x. 4 root root 4096 Sep 13 21:52 .. -rw-r--r--. 1 root root 18866 Jan 11 2016 userparameter_percona_mysql.conf -rw-r--r--. 1 root root 269258 Jan 11 2016 zabbix_agent_template_percona_mysql_server_ht_2.0.9-sver1.1.6.xml [root@slave1 templates]# ls -alt ../scripts/ total 72 drwxr-xr-x. 2 root root 4096 Sep 14 01:40 . -rwxr-xr-x. 1 root root 1259 Sep 14 01:40 get_mysql_stats_wrapper.sh -rwxr-xr-x. 1 root root 59634 Sep 13 22:23 ss_get_mysql_stats.php
* The get_mysql_stats_wrapper.sh script in the scripts directory monitors the status of MySQL, and the ss_get_mysql_stats.php file configures the password of the user name connecting to the database. Call PHP with shell.
* The conf file in the templates directory is placed under the agent side/etc/zabbix/zabbix_agent d.d/ for defining monitored items. The XML file is a template file (this template does not support zabbix 3 yet and needs to be modified).
3. Import template xml file to zabbix server
Error reporting: Invalid tag"/zabbix_export/date": "YYYY-MM-DDThh:mm:ssZ" is expected.
Solution: This template does not support zabbix, need to modify the xml template file, the corresponding ZABBIX 3 template can be imported online.
4. Modify relevant scripts:
vim get_mysql_stats_wrapper.sh
//Amendment:
RES=`HOME=~zabbix mysql -e 'SHOW SLAVESTATUS\G' | egrep '(Slave_IO_Running|Slave_SQL_Running):' | awk -F: '{print$2}' | tr '\n
//For:
RES=`HOME=~/usr/bin/mysql -e 'SHOW SLAVE STATUS\G' | egrep'(Slave_IO_Running|Slave_SQL_Running):' | awk -F: '{print $2}' | tr '\n' ','`
//modify
HOST=localhost
//For:
HOST=192.168.174.133
vim ss_get_mysql_stats.php
$mysql_user = 'zabbix';
$mysql_pass = '123456';
$mysql_port = 3306;
cp/var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf/etc/zabbix/zabbix_agentd.d/userparameter_percona_mysql.conf
//Test monitoring items:
[root@slave1 templates]# /usr/bin/php -q /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php--host localhost --items gg
ERROR: Access denied for user'zabbix'@'localhost' (using password: YES)
[root@slave1templates]# /usr/bin/php -q/var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php --host 192.168.174.133--items gg
gg:285. Apply percona template to monitor:
View the corresponding monitoring data chart:
Problems encountered:
55392:20170914:014811.374 item "SLAVE1:MySQL.State-none" became not supported: Value "rm: cannot remove `/tmp/192.168.174.133-mysql_cacti_stats.txt': Operation not permitted 21" of type "string" is not suitable for value type "Numeric (float)" 55392:20170914:014813.403 item "SLAVE1:MySQL.State-other" became not supported: Value "rm: cannot remove `/tmp/192.168.174.133-mysql_cacti_stats.txt': Operation not permitted 0" of type "string" is not suitable for value type "Numeric (float)"
The execution test found that the file permissions were incorrect:
[root@slave1 zabbix]# zabbix_get -s 192.168.174.133 -p 10050 -k "MySQL.Threads-connected" rm: cannot remove `/tmp/192.168.174.133-mysql_cacti_stats.txt': Operation not permitted 22
[root@slave1 zabbix]# ls -alt /tmp/192.168.174.133-mysql_cacti_stats.txt -rw-r--r--. 1 root root 1422 Sep 14 01:41 /tmp/192.168.174.133-mysql_cacti_stats.txt
After modifying the permission, the test is normal:
[root@slave1 zabbix]# chown zabbix:zabbix /tmp/192.168.174.133-mysql_cacti_stats.txt [root@slave1 zabbix]# zabbix_get -s 192.168.174.133 -p 10050 -k "MySQL.Threads-connected" 22 [root@slave1 zabbix]# zabbix_get -s 192.168.174.133 -p 10050 -k "MySQL.Key-read-requests" 152 //It is also normal to view logs: [root@slave1 zabbix]# tail -f zabbix_server.log 55393:20170914:015126.082 item "SLAVE1:MySQL.innodb-transactions" became not supported: cannot convert value to numeric type 55394:20170914:015127.098 item "SLAVE1:MySQL.Key-buf-bytes-unflushed" became not supported: Value "rm: cannot remove `/tmp/192.168.174.133-mysql_cacti_stats.txt': Operation not permitted 0" of type "string" is not suitable for value type "Numeric (float)" 55392:20170914:015128.100 item "SLAVE1:MySQL.Key-buf-bytes-used" became not supported: Value "rm: cannot remove `/tmp/192.168.174.133-mysql_cacti_stats.txt': Operation not permitted 1530880" of type "string" is not suitable for value type "Numeric (float)" 55392:20170914:015129.120 item "SLAVE1:MySQL.key-buffer-size" became not supported: Value "rm: cannot remove `/tmp/192.168.174.133-mysql_cacti_stats.txt': Operation not permitted 8388608" of type "string" is not suitable for value type "Numeric (float)" 55395:20170914:015130.166 item "SLAVE1:MySQL.Key-read-requests" became not supported: cannot convert value to numeric type 55393:20170914:015131.169 item "SLAVE1:MySQL.Key-reads" became not supported: cannot convert value to numeric type 55393:20170914:015132.182 item "SLAVE1:MySQL.Key-write-requests" became not supported: cannot convert value to numeric type 55392:20170914:015252.792 item "SLAVE1:MySQL.slave-lag" became supported 55393:20170914:015253.795 item "SLAVE1:MySQL.Slave-open-temp-tables" became supported 55393:20170914:015255.830 item "SLAVE1:MySQL.slave-running" became supported 55395:20170914:015256.873 item "SLAVE1:MySQL.slave-stopped" became supported 55393:20170914:015304.906 item "SLAVE1:MySQL.State-closing-tables" became supported 55392:20170914:015305.913 item "SLAVE1:MySQL.State-copying-to-tmp-table" became supported 55395:20170914:015306.925 item "SLAVE1:MySQL.State-end" became supported 55392:20170914:015307.934 item "SLAVE1:MySQL.State-freeing-items" became supported