mysql pmp

xywuyiba6

　　mysql  pmp
　　网址：https://www.percona.com/downloads/percona-monitoring-plugins/
　　Percona Monitoring Plugins (pmp)是一个高质量的组件为 MySQL 数据库添加企业级的监控和图表功能，另外该插件可以和 Nagios 或者是 Cacti 等监控系统的集成，从 1.1 版本开始，支持 zabbix 的监控。
其脚本是使用 php，故需要安装 php 环境。
　　2017-3-30
版本：percona-monitoring-plugins-1.1.7
　　1、安装php环境
yum -y install  php php-gd php-xml php-mbstring php-ldap php-pear php-xmlrpc  php-mysql php-pdo  php-cli  php-common
　　2、下载 Percona Monitoring Plugins
wget -c https://www.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.7/binary/redhat/6/x86_64/percona-monitoring-plugins-1.1.7-r9e56fa7-el6-x86_64-bundle.tar
　　3、解压
tar xvf percona-monitoring-plugins-1.1.7-r9e56fa7-el6-x86_64-bundle.tar
　　
4、安装插件：
rpm -ivh percona-zabbix-templates-1.1.7-2.noarch.rpm
　　5、拷贝userparameter文件到zabbix_agentd.conf.d
mkdir -p /etc/zabbix/zabbix_agentd.conf.d/
cp /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agentd.conf.d/
　　在zabbix_agentd.conf 行尾添加
cat >> /etc/zabbix/zabbix_agentd.conf << EOF
Include=/etc/zabbix/zabbix_agentd.conf.d/
UnsafeUserParameters=1
EOF
　　6、修改ss_get_mysql_stats.php脚本中的mysql用户和密码为被监控机器的mysql用户和密码
　　被监控机上mysql新建一个监控用户
grant all privileges on *.* to monitor@'%' identified by '123456';
flush privileges;
　　
vi /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php
$mysql_user = 'monitor';
$mysql_pass = '123456';
$mysql_port = 3306;
$mysql_socket = '/data/mysql/mysql3306/tmp/mysql.sock';  或 $mysql_socket = NULL;
　　
7、重启 zabbix_agentd
/etc/init.d/zabbix-agent stop
/etc/init.d/zabbix-agent start
　　8、测试是否能获取到值
zabbix_get -s 127.0.0.1 -p10050 -k "MySQL.Opened-tables"
　　9、导入模板并关联主机
　　
目录结构
/var/lib/zabbix/percona目录为插件新建目录
rpm -ql percona-zabbix-templates
/var/lib/zabbix/percona
/var/lib/zabbix/percona/scripts
/var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh
/var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php
/var/lib/zabbix/percona/templates
/var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf
/var/lib/zabbix/percona/templates/zabbix_agent_template_percona_mysql_server_ht_2.0.9-sver1.1.7.xml
　　cat /var/log/zabbix/zabbix_server.log
　　cat /var/log/zabbix/zabbix_agentd.log
　　这些报错不用理，误导
　　item "GZNWX-CASH-DB01:MySQL.Qcache-lowmem-prunes" became not supported: Received value [] is not suitable for value type [Numeric (float)]
item "GZNWX-CASH-DB01:MySQL.Query-time-count-03" became not supported: Received value [] is not suitable for value type [Numeric (float)]
25653:20170331:105531.835 item "GZNWX-CASH-DB01:MySQL.running-slave" became not supported: Received value [ERROR 1045 (28000): Access denied for user 'yonghualin'@'localhost' (using password: NO)0] is not suitable for value type [Numeric (unsigned)] and data type [Decimal]
　　zabbix-server日志收到下面信息才正常
　　2440:20170331:120518.804 item "GZNWX-CASH-DB01:MySQL.Binlog-cache-use" became supported
  2440:20170331:120518.805 item "GZNWX-CASH-DB01:MySQL.Bytes-received" became supported
  2440:20170331:120518.805 item "GZNWX-CASH-DB01:MySQL.Bytes-sent" became supported
  2440:20170331:120518.805 item "GZNWX-CASH-DB01:MySQL.Com-delete" became supported
  2440:20170331:120518.805 item "GZNWX-CASH-DB01:MySQL.Com-delete-multi" became supported

　　监控指标
innodb buffer pool efficiency
innodb checkpoint age
innodb I/O
innodb  row lock time
innodb row lock waits
innodb transactions
mysql command counters
mysql connections
mysql file and tables
mysql temporary objects
　　

调用流程
zabbix agent调用
/etc/zabbix/zabbix_agentd.conf.d/userparameter_percona_mysql.conf
调用
/var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh
调用
/usr/bin/php -q /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php --host GZNWX-CASH-DB01 --items xx
生成
/tmp/localhost-mysql_cacti_stats.txt

cat localhost-mysql_cacti_stats.txt | sed 's/ /\n/g; s/-1/0/g'| grep nr


/usr/bin/php -q /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php --host GZNWX-VG-JMOPDB01 --items ix
/usr/bin/php -q /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php --host GZNWX-CASH-DB01 --items ix
zabbix_get -s 127.0.0.1 -p10050 -k "MySQL.Aborted-clients"
zabbix_get -s 127.0.0.1 -p10050 -k  "system.hostname"
zabbix_get -s 127.0.0.1 -p10050 -k "MySQL.adaptive-hash-memory"

　　ss_get_mysql_stats.php文件里有使用方法
　　Usage: php ss_get_mysql_stats.php --host <host> --items <item,...> [OPTION]
   --host               MySQL host
   --items              Comma-separated list of the items whose data you want
   --user               MySQL username
   --pass               MySQL password
   --port               MySQL port
   --socket             MySQL socket
   --flags              MySQL flags
   --connection-timeout MySQL connection timeout
   --server-id          Server id to associate with a heartbeat if heartbeat usage is enabled
   --nocache            Do not cache results in a file
   --help               Show usage
　　注意改了my.cnf之后，要修改一下socket文件

注意：做一个软链接
mkdir -p /var/lib/mysql
ln -s /ngbs/data/tmp/mysql.sock   /var/lib/mysql/mysql.sock

否则/tmp/localhost-mysql_cacti_stats.txt 文件无数据