Ubuntu下Zabbix结合percona监控mysql数据

按道理来说zabbix就自带的mysql插件来监控mysql数据库，但是你会发现，自带的mysql监控项是很少的，根本满足不了公司的需求。
由于它本身自带的模板太过简单了，所以需要做更详细的监控，而percona就提供了这个详细监控的模版以及脚本，解决了监控不全面的问题。
percona插件是安装在zabbix-agent端的

1、percona插件安装：

首先需要下载php依赖：

ubuntu@ubuntu:~$ apt-get install php

1、官网下载percona包

version: percona的版本
software：linux server的版本
download all packages together： 一起下载所有包
download packages separately： 单独下载某个包
这里我们选择下载最后一个包-percona-zabbix-templates-1.1.7-1.xenial_all.ded

2、命令下载

ubuntu@ubuntu:~$ wget https://www.percona.com/downloads/percona-monitoring-plugins/1.1.7/binary/debian/xenial/x86_64/percona-zabbix-templates_1.1.7-1.xenial_all.deb

3、安装percona包

ubuntu@ubuntu:~$ sudo dpkg -i percona-zabbix-templates_1.1.7-1.xenial_all.deb 
ubuntu@ubuntu:~$ cd /var/lib/zabbix/percona/templates && ll
total 292
drwxr-xr-x 2 root root   4096 mar 25 14:19 ./
drwxr-xr-x 4 root root   4096 mar 15 11:39 ../
-rw-r--r-- 1 root root  18866 dec  8  2016 userparameter_percona_mysql.conf
-rw-r--r-- 1 root root 269258 dec  8  2016 zabbix_agent_template_percona_mysql_server_ht_2.0.9-sver1.1.7.xml

2、编辑配置文件

1、添加模板文件路径

ubuntu@ubuntu:~$ sudo mv /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix_agentd.conf.d/

2、修改mysql的执行命令文件，更改mysql的用户与密码：

 ubuntu@ubuntu:~$ sudo vim /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh

res=`home=~ /usr/bin/mysql -uroot -pzabbix  -e 'show slave status\g' | egrep '(slave_io_running|slave_sql_running):' | awk -f: '{print $2}' | tr '\n' ','`

mysql -u用户名 -p密码，这里根据自己实际情况修改

3、修改 ss_get_mysql_stats.php文件

ubuntu@ubuntu:~$ sudo vim /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php

$mysql_user = 'root';
$mysql_pass = 'zabbix';

4、重启zabbix客户端

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

5、导入mysql的监控模板

模板需要先导入桌面

ubuntu@ubuntu:~$ sudo sz /var/lib/zabbix/percona/templates/zabbix_agent_template_percona_mysql_server_ht_2.0.9-sver1.1.7.xml 

                                                                   图1

6、导入出错

出现“标签无效 "/zabbix_export/date": "yyyy-mm-ddthh:mm:ssz" 预计”，如图2所示：
 
                                                                   图2
解决方法：百度了一下说将zabbix_agent_template_percona_mysql_server_ht_2.0.9-sver1.1.7.xml导入zabbix2.4版本中再导出。之后将新的导出xml导入到3.2中问题解决。

7、关联模板template percona mysql server

可以发现自带的监控项就一百多了，图形四十多个。

                                                                 图3

8、查看监控图像，出数据如图4、图5所示：

 
                                                                 图4
 

9、碰到问题：

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)"

执行测试发现发现是文件权限不对：

[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

[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

修改权限后测试正常：

[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

查看日志也正常：

[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