zabbix监控mysql相关配置内容

zabbix自带监控mysql模板开启后不能直接监控到mysql的数据，需要agent端配合脚本获取相关值。

通过获取mysql状态值将这些状态值传递给服务器并绘制成图片，这样可以观察mysql的工作情况，通常需要获得状态变量有以下

• Com_update：mysql执行的更新个数
• Com_select：mysql执行的查询个数
• Com_insert：mysql执行插入的个数
• Com_delete：执行删除的个数
• Com_rollback：执行回滚的操作个数
• Bytes_received:接受的字节数
• Bytes_sent：发送的字节数
• Slow_queries：慢查询语句的个

vim /usr/local/zabbix/etc/mysql.sh

#!/bin/bash#Create by zhengdazhi 2014.09.22MYSQL_DIR=/usr/MYSQL=${MYSQL_DIR}/bin/mysqlMYSQLADMIN=${MYSQL_DIR}/bin/mysqladminMYSQL_SOCK="/var/lib/mysql/mysql.sock"MYSQL_USER=rootMYSQL_PWD=123456  ARGS=1 if [ $# -ne "$ARGS" ];then   echo "Please input one arguement:"ficase $1 in  Uptime)     result=`${MYSQLADMIN} -u${MYSQL_USER} -p${MYSQL_PWD} -S $MYSQL_SOCK status|cut -f2 -d":"|cut -f1 -d"T"`       echo $result       ;;     Com_update)       result=`${MYSQLADMIN} -u${MYSQL_USER} -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status |grep -w "Com_update"|cut -d"|" -f3`       echo $result       ;;     Slow_queries)     result=`${MYSQLADMIN} -u${MYSQL_USER} -p${MYSQL_PWD} -S $MYSQL_SOCK status |cut -f5 -d":"|cut -f1 -d"O"`         echo $result         ;;   Com_select)     result=`${MYSQLADMIN} -u${MYSQL_USER} -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status |grep -w "Com_select"|cut -d"|" -f3`         echo $result         ;;   Com_rollback)     result=`${MYSQLADMIN} -u${MYSQL_USER} -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status |grep -w "Com_rollback"|cut -d"|" -f3`         echo $result         ;;   Questions)     result=`${MYSQLADMIN} -u${MYSQL_USER} -p${MYSQL_PWD} -S $MYSQL_SOCK status|cut -f4 -d":"|cut -f1 -d"S"`         echo $result         ;;   Com_insert)     result=`${MYSQLADMIN} -u${MYSQL_USER} -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status |grep -w "Com_insert"|cut -d"|" -f3`         echo $result         ;;   Com_delete)     result=`${MYSQLADMIN} -u${MYSQL_USER} -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status |grep -w "Com_delete"|cut -d"|" -f3`         echo $result         ;;   Com_commit)     result=`${MYSQLADMIN} -u${MYSQL_USER} -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status |grep -w "Com_commit"|cut -d"|" -f3`         echo $result         ;;   Bytes_sent)     result=`${MYSQLADMIN} -u${MYSQL_USER} -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status |grep -w "Bytes_sent" |cut -d"|" -f3`         echo $result         ;;   Bytes_received)     result=`${MYSQLADMIN} -u${MYSQL_USER} -p${MYSQL_PWD} -S $MYSQL_SOCK extended-status |grep -w "Bytes_received" |cut -d"|" -f3`         echo $result         ;;   Com_begin)     result=`${MYSQLADMIN} -u${MYSQL_USER} -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

zabbix监控mysql是读取 mysql.status的键，因此在客户端配置文件中加入的自定义键名也应该是mysql.status

vim /usr/local/zabbix_agentd/etc/zabbix_agentd.conf

#开启用户自定义配置

UnsafeUserParameters=1

#添加mysql监控

UserParameter=mysql.status[*],/usr/local/zabbix/etc/mysql.sh $1 $2

配置完成后重启zabbix-agent服务。

二、windows服务器上配置

要想在Windows上取得MySQL的状态数据，可以用vbs脚本运行mysql命令

1）在d:\Zabbix\Scripts\目录下新建两个脚本文件内容如下：

Set objFS =CreateObject("Scripting.FileSystemObject")Set objArgs = WScript.Argumentsstr1 = getCommandOutput("D:\SOFT_PHP_PACKAGE\mysql\bin\mysqladmin -uroot -p123456 ping")     //修改对应数据库路径，用户名和密码，修改完成后删除本备注项If Instr(str1,"alive") > 0ThenWScript.Echo 1ElseWScript.Echo 0End IfFunction getCommandOutput(theCommand)Dim objShell, objCmdExecSet objShell =CreateObject("WScript.Shell")Set objCmdExec = objshell.exec(thecommand)getCommandOutput =objCmdExec.StdOut.ReadAllend Function

 mysql-status.vbs

Set objFS = CreateObject("Scripting.FileSystemObject")Set objArgs = WScript.Argumentsstr1 = getCommandOutput("D:\SOFT_PHP_PACKAGE\mysql\bin\mysqladmin -u root -p123456 extended-status")      //修改对应数据库路径，用户名和密码，修改后删除此备注Arg = objArgs(0)str2 = Split(str1,"|")For i = LBound(str2) to UBound(str2)If Trim(str2(i)) = Arg Then   WScript.Echo TRIM(str2(i+1))Exit ForEnd IfnextFunction getCommandOutput(theCommand)Dim objShell, objCmdExecSet objShell =CreateObject("WScript.Shell")Set objCmdExec = objshell.exec(thecommand)getCommandOutput =objCmdExec.StdOut.ReadAllend Function

2）修改windows上的zabbix_agentd.win.conf文件，设置key值。在UserParameter下面加两句；

UserParameter=mysql.status[*], cscript/nologo d:\Zabbix\Scripts\mysql-status.vbs $1 UserParameter=mysql.ping, cscript /nologo d:\Zabbix\Scripts\mysql-ping.vbs

3）重启zabbix_agentd，并给主机添加MySQL模版，查看items状态。