zabbix3.0 监控mysql服务器性能实战，精华篇
监控思路先在zabbix主机上面配置好MySQL服务器的信息，添加好zabbix自带的Template App MySQL模版，然后去mysql服务器上安装agent，再添加mysql监控脚本。
1，建立mysql host groups组模板是 zabbix系统提供的，进入 zabbix web 后台，Configuration-->Hosts groups-->点击“Create host group”-->选择template选项卡，选择模板“TemplateApp MySQL，Templdate OS Linux”，最后点击update 即可（032.png）
2，建立hosts模板是 zabbix系统提供的，进入zabbix web 后台，configuration-->hosts-->点击你的主机 name-->选择template选项卡，选择模板“Template App MySQL”，最后点击左边的“Add”按钮，最后点击“update”按钮即可(033.png)
3，rpm部署agent客户端rpm方式安装：#添加zabbix帐号
groupadd zabbix
useradd zabbix -g zabbix -s /sbin/nologin
# 下载安装包
wget http://repo.zabbix.com/zabbix/3. ... .0-2.el6.x86_64.rpm
rpm -ivh zabbix-agent-3.0.0-2.el6.x86_64.rpm
# 报错信息如下：
# rpm -ivh zabbix-agent-3.0.0-2.el6.x86_64.rpm
warning: zabbix-agent-3.0.0-2.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 79ea5ed4: NOKEY
error: Failed dependencies:
libodbc.so.2()(64bit) is needed by zabbix-agent-3.0.0-2.el6.x86_64
#
# 解决方法：
yum -y install unixODBC
启动agent：# 启动：
/usr/sbin/zabbix_agentd -c /etc/zabbix/zabbix_agentd.conf
# 添加开机自启动：
chkconfig --add zabbix_agentd
4，源码方式agent客户端
tar zxvf zabbix-3.0.2.tar.gz
cd zabbix-3.0.2
./configure --enable-agent --with-mysql
make
make install
# 其它zabbix_agentd.conf配置信息和rpm方式一样
5，开始mysql性能监控这里可以采用zabbix自带的mysql模版，但是也需要在mysql服务器上准备获取mysql status的脚本chk_mysql.sh，zabbix通过调用这个脚本来获取mysql的运行信息。
在配置文件zabbix_agentd.conf里面添加mysql监控信息：# vim /etc/zabbix/zabbix_agentd.conf
UserParameter=mysql.version,mysql -V
UserParameter=mysql.status,/usr/local/zabbix/scripts/chk_mysql.sh $1
UserParameter=mysql.ping,mysqladmin -uzabbix -pys_ipowerlong0418 -P3306 -h127.0.0.1 ping | grep -c alive
添加mysql帐号：GRANT PROCESS,SUPER,REPLICATION CLIENT ON *.* TO zabbix@'127.0.0.1' IDENTIFIED BY 'ys_ipowerlong0418';
添加check脚本权限：chmod u+x /usr/local/zabbix/chk_mysql.shchown -R zabbix.zabbix /usr/local/zabbix/chk_mysql.sh
去zabbix服务器上面检查基本mysql服务器信息是否能正常获取到，zabbix_get -s 192.168.1..62 -p10050 -k"system.cpu.load";：# zabbix_get -s 192.168.1..62 -p10050 -k "system.cpu.load";
0.050000
#
接下来准备mysql的运行信息监控，需要准备新的mysql监控脚本chk_mysql.sh,/usr/local/zabbix/chk_mysql.sh：#!/bin/sh
# -------------------------------------------------------------------------------
# FileName: check_mysql.sh
# Revision: 1.0
# Date: 2017/03/24
# Author: 定位
# Email: admin@cnblackhat.com
MYSQL_SOCK="/usr/local/mysql/mysql.sock"
MYSQL_USER='zabbix'
MYSQL_PWD='ys_ipowerlong0418'
MYSQL_HOST='127.0.0.1'
MYSQL_PORT='3306'
ARGS=1
if [ $# -ne "$ARGS" ];then
echo "Please input one arguement:"
fi
case $1 in
Uptime)
result=`/usr/local/mysql/bin/mysqladmin -u$MYSQL_USER -h$MYSQL_HOST -p${MYSQL_PWD} -S $MYSQL_SOCK status|cut -f2 -d":"|cut -f1 -d"T"`
echo $result
;;
Com_update)
result=`/usr/local/mysql/bin/mysqladmin -u$MYSQL_USER -h$MYSQL_HOST -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status |grep -w "Com_update"|cut -d"|" -f3`
echo $result
;;
Slow_queries)
result=`/usr/local/mysql/bin/mysqladmin -u$MYSQL_USER -h$MYSQL_HOST -p${MYSQL_PWD} -S $MYSQL_SOCK status |cut -f5 -d":"|cut -f1 -d"O"`
echo $result
;;
Com_select)
result=`/usr/local/mysql/bin/mysqladmin -u$MYSQL_USER -h$MYSQL_HOST -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status |grep -w "Com_select"|cut -d"|" -f3`
echo $result
;;
Com_rollback)
result=`/usr/local/mysql/bin/mysqladmin -u$MYSQL_USER -h$MYSQL_HOST -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status |grep -w "Com_rollback"|cut -d"|" -f3`
echo $result
;;
Questions)
result=`/usr/local/mysql/bin/mysqladmin -u$MYSQL_USER -h$MYSQL_HOST -p${MYSQL_PWD} -S $MYSQL_SOCK status|cut -f4 -d":"|cut -f1 -d"S"`
echo $result
;;
Com_insert)
result=`/usr/local/mysql/bin/mysqladmin -u$MYSQL_USER -h$MYSQL_HOST -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status |grep -w "Com_insert"|cut -d"|" -f3`
echo $result
;;
Com_delete)
result=`/usr/local/mysql/bin/mysqladmin -u$MYSQL_USER -h$MYSQL_HOST -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status |grep -w "Com_delete"|cut -d"|" -f3`
echo $result
;;
Com_commit)
result=`/usr/local/mysql/bin/mysqladmin -u$MYSQL_USER -h$MYSQL_HOST -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status |grep -w "Com_commit"|cut -d"|" -f3`
echo $result
;;
Bytes_sent)
result=`/usr/local/mysql/bin/mysqladmin -u$MYSQL_USER -h$MYSQL_HOST -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status |grep -w "Bytes_sent" |cut -d"|" -f3`
echo $result
;;
Bytes_received)
result=`/usr/local/mysql/bin/mysqladmin -u$MYSQL_USER -h$MYSQL_HOST -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status |grep -w "Bytes_received" |cut -d"|" -f3`
echo $result
;;
Com_begin)
result=`/usr/local/mysql/bin/mysqladmin -u$MYSQL_USER -h$MYSQL_HOST -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status |grep -w "Com_begin"|cut -d"|" -f3`
echo $result
;;
*)
echo "Usage:$0(Uptime|Com_update|Slow_queries|Com_select|Com_rollback|Questions)"
;;
esac
脚本准备完后，重启agent，然后在zabbix-server服务器上测试是否可以获取到mysql的status信息：# zabbix_get -s 192.168.1..71 -p10050 -k mysql.status
Warning: Using a password on the command line interface can be insecure.
78055
#
#
# zabbix_get -s 192.168.1..71 -p10050 -k mysql.status
Warning: Using a password on the command line interface can be insecure.
51332
#
6，监控性能视图监控脚本运行正常后，就会在zabbix-server的host下面的graph里面看到mysql的监控性能视图，monitoringààGraphsààHost(选择mysql服务器)ààGraph(选择mysql选项)，可以看到性能监控视图，zabbix自带模版默认有2个性能图，这些图要等运行一段时间后才能画出来：
041.png，MySQL operations：
042.png，MySQL bandwidth：
7，mysqladmin的缺陷当mysql down了的时候，zabbix监控不到。因为默认的MySQL is down 的触发器的触发条件是mysql.ping.last(0)，mysql.ping的实现方式是UserParameter=mysql.ping,mysqladmin -uzabbix -pys_ipowerlong0418-P3306 -h127.0.0.1 ping | grep -c alive，当mysql服务停止了后，mysqladmin执行报错是一推错误信息返回不了数字0，所以zabbix触发不了，所以zabbix界面会看到problems但是不会触发报警动作的，也就意味着你不会第一时间接到email、短信、电话报警了。
所以必须改正不能用mysqladmin这种方式，想到了用telnet mysql端口的方式来判断，如下所示：UserParameter=mysql.ping,netstat -ntpl |grep 3317 |grep mysql |wc|awk '{print $1}'3317就是mysql运行的端口号，这样当mysqlis alive那么返回为1，当mysql is down返回为0，就满足了zabbix的mysql.ping.last(0)=0的触发条件了。
修改zabbix_agentd.conf配置信息：#先移除默认的配置
mv /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf /usr/local/zabbix/
# 添加新的配置
vim /etc/zabbix/zabbix_agentd.conf
UserParameter=mysql.version,mysql -V
UserParameter=mysql.status,/usr/local/zabbix/chk_mysql.sh $1
UserParameter=mysql.ping,netstat -ntpl |grep 3317 |grep mysql |wc |awk '{print $1}'
实战模拟：
在zabbix客户端把mysql服务给关闭了，执行service mysqld stop
等待报警.........
收到报警了，如下图：
mysql服务重新启动,会收到mysql恢复的提醒,如下图:
最后就是mysql性能的监控,关于增删改查,慢查询,回滚,提交等等的监控，这些就是通过脚本来实现的，如下图：
8，报错汇总记录
8.1报错信息# tail -f/var/log/zabbix/zabbix_agentd.log……17336:20160422:163813.319 no active checkson server : host not found 17336:20160422:164013.503 no active checks onserver : host not found
解决办法：出现该错误的原因是一般是zabbix_agentd.conf里面的Hostname和前端zabbix web里面的配置不一样所造成的，即在zabbix web页面Monitoring->Configuration->Hosts 页面的Host Name和zabbix_agentd.conf里面的Hostname要一样。
再看后台日志信息，正常如下：# tail -f/var/log/zabbix/zabbix_agentd.log 17468:20160422:164350.284 IPv6 support: YES 17468:20160422:164350.284 TLS support: YES 17468:20160422:164350.284************************** 17468:20160422:164350.284 using configurationfile: /etc/zabbix/zabbix_agentd.conf 17468:20160422:164350.293 agent #0 started 17469:20160422:164350.294 agent #1 started 17470:20160422:164350.294 agent #2 started 17471:20160422:164350.294 agent #3 started 17472:20160422:164350.295 agent #4 started 17473:20160422:164350.295 agent #5 started
8.2问题is not suitable for value type and datatype
12394:20160422:200640.676 error reason for "db_master_1:mysql.status" changed: Received value is not suitable for value type
12393:20160422:200642.678 error reason for "db_master_1:mysql.status" changed: Received value is not suitable for value type and data type
后台zabbix服务器日志：
# /usr/local/zabbix/bin/zabbix_get -s 192.168.1..71 -p10050 -k mysql.status
Warning: Using a password on the command line interface can be insecure.
92315
#
解决办法是，在zabbix客户端，添加用户名密码配置，这样避免shell密码外泄也不会报错，（这里报这种错误是因为返回的结果集中有字符串提醒信息，而后台zabbix的item的是decimal所以存储不进去报错）：
vim /usr/local/mysql/my.cnf
user=zabbix
password=ys_ipowerlong0418
然后重启zabbix_agent
然后在后台zabbix测试，没有报多余的信息：
# /usr/local/zabbix/bin/zabbix_get -s 192.168.1..71 -p10050 -k mysql.status
58272
#
之后再看zabbix_server后台日志，正常了不会报错了，如下所示：
12855:20160422:233520.636 item "db_master_2:mysql.status" became supported
12857:20160422:233521.636 item "db_master_2:mysql.status" became supported
12854:20160422:233522.637 item "db_master_2:mysql.status" became supported
12856:20160422:233523.638 item "db_master_2:mysql.status" became supported
12857:20160422:233524.641 item "db_master_2:mysql.status" became supported
12855:20160422:233525.642 item "db_master_2:mysql.status" became supported
12856:20160422:233526.642 item "db_master_2:mysql.status" became supported
12854:20160422:233527.643 item "db_master_2:mysql.status" became supported
12855:20160422:233528.645 item "db_master_2:mysql.status" became supported
12857:20160422:233529.646 item "db_master_2:mysql.status" became supported
12856:20160422:233530.646 item "db_master_2:mysql.status" became supported
8.3报错Check access restrictions in Zabbix agent configuration：#zabbix_get -s 192.168.1..72 -p10050 -k "system.hostname";zabbix_get : Check accessrestrictions in Zabbix agent configuration
#解决：配置文件坏了，copy一个备份的过来，改下，重启agent。
在zabbix服务器上测试：#/usr/local/zabbix/bin/zabbix_get -s 192.168.1.63 -p10050 -k mysql.ping(Not all processes could be identified,non-owned process info willnot be shown, you would have to be root to see it all.)0#
去mysql服务器上赋予s权限# chmod +s /bin/netstatYou have new mail in /var/spool/mail/root#
8.4、场景：因为使用了netstat -p参数。权限问题，zabbix_agentd是zabbix用户启动的，默认不能执行netstat -p等命令，导致从服务器取到的自动发现脚本为空
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
解决方法 ：
chmod +s /bin/netstat
chmod +s 是什么意思
为了方便普通用户执行一些特权命令，SUID/SGID程序允许普通用户以root身份暂时执行该程序，并在执行结束后再恢复身份。
页:
[1]