mysql mpm
参考文章
http://www.myexception.cn/mysql/1968274.html
http://www.linuxidc.com/Linux/2013-07/86965.htm
http://bbs.csdn.net/topics/390831931
http://634871.blog.51cto.com/624871/1382835/
mpm官网：http://www.fromdual.com/
1、安装部署Zabbix
2、MPM(PERFORMANCE MONITOR FOR MYSQL)依赖的相关Perl模块安装:
# yum install -y perl-File-Which perl-libwww-perl perl-Digest-SHA1 perl-DBD-MySQL perl-Time-HiRes perl-Crypt-SSLeay zabbix-sender
下载MPM并解压
解压缩软件包后，会发现有两个文件mysql_performance_monitor_agent-0.9.3.tar.gz和mysql_performance_monitor_templates-0.9.3.tar.gz。
其中mysql_performance_monitor_agent-0.9.3.tar.gz是放在监控机器上的配置信息
mysql_performance_monitor_templates-0.9.3.tar.gz则是要让我们导入到zabbix server的模板数据。
wget http://www.shinguz.ch/download/mysql_performance_monitor-latest.tar.gz
tar zxvf mysql_performance_monitor-latest.tar.gz
server端配置
3、Zabbix Web UI导入MPM模板
MPM模板:
# tar zxvf mysql_performance_monitor_templates-0.9.1.tar.gz
# sz mysql_performance_monitor_templates-0.9.1.tar.gz
# cd mysql_performance_monitor_templates-0.9.1/xml
在Zabbix Web UI导入监控所需的模板(“Configuration”->”Templates”->”Import”):
Template_FromDual.MySQL.mpm.xml (监控mpm agent本身,这个必须导入)
Template_FromDual.MySQL.server.xml (监控Linux系统跟数据库使用相关的附加项)
Template_FromDual.MySQL.process.xml (监控各种Linux进程[比如:mysqld,ndbd])
Template_FromDual.MySQL.mysql.xml (监控MySQL常用状态变量)
Template_FromDual.MySQL.innodb.xml (监控InnoDB存储引擎状态变量)
Template_FromDual.MySQL.myisam.xml (监控MyISAM存储引擎状态变量)
Template_FromDual.MySQL.master.xml (监控MySQL主从复制的Master状态)
Template_FromDual.MySQL.slave.xml (监控MySQL主从复制的Slave状态)
MPM其它用途的模板:
Template_FromDual.MySQL.ndb.xml (监控MySQL Cluster)
Template_FromDual.MySQL.galera.xml (监控MySQL Galera Cluster)
Template_FromDual.MySQL.pbxt.xml (监控PBXT存储引擎状态变量)
Template_FromDual.MySQL.aria.xml (监控Aria存储引擎的状态变量)
Template_FromDual.MySQL.drbd.xml (监控DRBD设备状态信息)
创建 Host groups，例如随便一个名字:GZNWMySQL
将已有的mysql机器（GZNWX-CASH-DB01）加入到GZNWMySQL组
值得注意的是:Hostname必须与MPM agent配置中的所使用的一致
Template_FromDual.MySQL.mpm.xml (监控mpm agent本身,这个必须导入)
Template_FromDual.MySQL.server.xml (监控Linux系统跟数据库使用相关的附加项)
Template_FromDual.MySQL.mysql.xml (监控MySQL常用状态变量)
Template_FromDual.MySQL.innodb.xml (监控InnoDB存储引擎状态变量)
Template_FromDual.MySQL.process.xml (监控各种Linux进程[比如:mysqld,ndbd])
将上面的这个5个模板关联到GZNWX-CASH-DB01
agent端配置
安装MPM和配置MPM Agent的Zabbix Keys
安装MPM:
yum install -y perl-File-Which perl-libwww-perl perl-Digest-SHA1 perl-DBD-MySQL perl-Time-HiRes perl-Crypt-SSLeay zabbix-sender
wget http://www.shinguz.ch/download/mysql_performance_monitor-latest.tar.gz
tar zxvf mysql_performance_monitor-latest.tar.gz
# tar zxvf mysql_performance_monitor_agent-0.9.1.tar.gz
# mv mysql_performance_monitor_agent-0.9.1 /usr/local/mysql_performance_monitor-agent
将MPM Agent的Keys配置添加到Zabbix Agentd配置文件中
cat >> /etc/zabbix/zabbix_agentd.conf << EOF
UnsafeUserParameters=1
UserParameter=FromDual.MySQL.check,/usr/local/mysql_performance_monitor-agent/FromDualMySQLagent.pl /etc/zabbix/FromDualMySQLagent.conf
EOF
配置MPM Agent:
cd /usr/local/mysql_performance_monitor-agent/etc
cp FromDualMySQLagent.conf.template /etc/zabbix/FromDualMySQLagent.conf
mkdir -p /var/log/zabbix /var/log/zabbix/cache
chown -R zabbix:zabbix /var/log/zabbix /var/log/zabbix/cache
给予zabbix对mysql的pid文件的读权限和MPM日志的权限
chmod o+r /data/mysql/mysql3306/tmp/mysql.pid
touch /var/log/zabbix/FromDualMySQLagent.log
chown -R zabbix:zabbix /var/log/zabbix
修改MPM配置文件
[root@oneproxy-vm zabbix]# cat /etc/zabbix/FromDualMySQLagent.conf
cat /etc/zabbix/FromDualMySQLagent.conf
[default]
# Type of section:
# mysqld for a normal MySQL database
# ndbd for a MySQL cluster
Type = mysqld
# Debug levels are:
# 1 is logging only errors
# 2 is logging errors and warnings (this is the default)
# 3 logs some informations messages as well
# 4 logs everything (for debugging purposes only)
Debug = 2
LogFile = /var/log/zabbix/FromDualMySQLagent.log
# Directory where the Cache files should be written to:
CacheFileBase = /var/log/zabbix/cache/FromDualAgentCache
# If you agent is not located in the same time zone as your server
# TimeShift = +0.0
# Information for MySQL connections:
Username = zabbix
Password = zabbix
MysqlHost = 127.0.0.1
MysqlPort = 3306
# Zabbix Server IP address
ZabbixServer = 192.168.0.22
# If section is disabled
Disabled = false
# Possible modules for databases are:
# process mysql myisam aria innodb pbxt ndb master slave drbd galera memcache
# Possible modules for servers are:
# mpm server
# Usual modules are
Modules = server mysql innodb process
# Special parameter which is used for module ndb and process
ClusterLog = /var/lib/mysql-cluster/ndb_1_cluster.log
PidFile = /ngbs/data/GZNWX-CASH-DB01.pid
# If you want to use Monitoring as a Service:
# MaaS = on
# Hash = <enter hash value we provide you>
# Methodes are: direct, http, email. Currently only http works
# Methode = http
# Url = http://support.fromdual.com/maas/receiver.php
#[db_server]
#Type = mysqld
#Modules = server mysql innodb process
# All MySQL databases here
# Try to avoid section names with spaces!
[GZNWX-CASH-DB01] # This MUST match Hostname in Zabbix!
Type = mysqld
MysqlPort = 3306
Modules = server mysql innodb process
PidFile = /ngbs/data/GZNWX-CASH-DB01.pid
# [mysql-5.1.41-ndb-7.0.13] # This MUST match Hostname in Zabbix!
#
# Type = mysqld
#
# MysqlPort = 3306
# Modules = slave
# PidFile = /var/lib/mysql/db_server.pid
# Debug = 0
# Disabled = false
# [mariadb-5.2.0] # This MUST match Hostname in Zabbix!
#
# Type = mysqld
#
# MysqlPort = 3306
# Modules = pbxt
# PidFile = /var/lib/mysql/db_server.pid
# All MySQL clusters here
# [cl_2_3] # This MUST match Hostname in Zabbix!
#
# Disabled = false
#
# Type = ndbd
# ClusterLog = /usr/local/mysql-cluster/ndb_1_cluster.log
#
# Modules = ndb process
#
# MysqlHost = 127.0.0.1
# MysqlPort = 3306
重启zabbix-agent即可
/etc/init.d/zabbix-agent restart
MPM agent 文件的配置要对应Zabbix agent文件的配置
sender
修改FromDualMySQLagent.pl中的zabbix_sender的路径:
# cd /usr/local/mysql_performance_monitor-agent/
# sed -i 's#/usr/local/bin#/usr/local/zabbix/bin/#g' FromDualMySQLagent.pl
查看日志FromDualMySQLagent.log
问题一 ：[Z3001] connection to database 'zabbix' failed:
在被监控机器加用户
grant all privileges on *.* to zabbix@'%' identified by 'zabbix'
问题二： 要写被监控主机名
cat /etc/zabbix/FromDualMySQLagent.conf
[GZNWX-CASH-DB01] # This MUST match Hostname in Zabbix! 被监控主机的名字，跟zabbix里的host的名字要一致
Type = mysqld
MysqlPort = 3306
Modules = server mysql innodb process
PidFile = /ngbs/data/GZNWX-CASH-DB01.pid 被监控主机的mysql的pid文件
问题三：测试
测试，返回1则正常
在被监控机器上执行一下
/usr/local/mysql_performance_monitor-agent/FromDualMySQLagent.pl /etc/zabbix/FromDualMySQLagent.conf
在zabbix服务器上执行一下
zabbix_get -s 192.168.0.21 -p10050 -k "FromDual.MySQL.check"
问题四：Load of cache file failed. rc=1301
1、删除cache目录下所有文件并重启zabbix-agentd
rm -f /var/log/zabbix/cache/*
2、注释源码
vi /usr/local/mysql_performance_monitor-agent/lib/sendData.pm
$rc = &FromDualMySQLagent::checkConnectionToZabbixServer($main::gParameter{'ZabbixServer'}, $main::gParameter{'ZabbixServerPort'}, $main::gParameter{'Hostname'});
3、cat /etc/zabbix/FromDualMySQLagent.conf 里面Modules不能写mpm
Modules = server mysql innodb process