zabbix配置mysql监控

1. 编辑脚本文件/usr/local/zabbix/etc/zabbix_agentd.conf.d/chk_mysql.sh

# vi /usr/local/zabbix/etc/zabbix_agentd.conf.d/chk_mysql.sh

#!/bin/bash

# 用户名
MYSQL_USER='root'

# 密码
MYSQL_PWD='asd123'

# 主机地址/IP
MYSQL_HOST='localhost'

# 端口
MYSQL_PORT='3306'

# 数据连接
MYSQL_CONN="/usr/bin/mysqladmin -u${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT}"

# 参数是否正确
if [ $# -ne "1" ];then
    echo "arg error!"
fi

# 获取数据
case $1 in
    Uptime)
        result=`${MYSQL_CONN} status|cut -f2 -d":"|cut -f1 -d"T"`
        echo $result
        ;;
    Com_update)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_update"|cut -d"|" -f3`
        echo $result
        ;;
    Slow_queries)
        result=`${MYSQL_CONN} status |cut -f5 -d":"|cut -f1 -d"O"`
        echo $result
        ;;
    Com_select)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_select"|cut -d"|" -f3`
        echo $result
                ;;
    Com_rollback)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_rollback"|cut -d"|" -f3`
    Questions)
        result=`${MYSQL_CONN} status|cut -f4 -d":"|cut -f1 -d"S"`
                echo $result
                ;;
    Com_insert)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_insert"|cut -d"|" -f3`
                echo $result
                ;;
    Com_delete)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_delete"|cut -d"|" -f3`
                echo $result
                ;;
    Com_commit)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_commit"|cut -d"|" -f3`
                echo $result
                ;;
    Bytes_sent)
        result=`${MYSQL_CONN} extended-status |grep -w "Bytes_sent" |cut -d"|" -f3`
                echo $result
                ;;
    Bytes_received)
        result=`${MYSQL_CONN} extended-status |grep -w "Bytes_received" |cut -d"|" -f3`
                echo $result
                ;;
    Com_begin)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_begin"|cut -d"|" -f3`
                echo $result
                ;;
        *)
        result=`${MYSQL_CONN} extended-status |grep -w "$1" |cut -d"|" -f3`
                echo $result
                ;;
esac

2. 编辑配置文件/usr/local/zabbix/etc/zabbix_agentd.conf.d/zabbix_mysql.conf

# vi /usr/local/zabbix/etc/zabbix_agentd.conf.d/zabbix_mysql.conf

#Mysql版本
UserParameter=mysql.version,mysql -V
# 获取mysql性能指标,这个是上面定义好的脚本
UserParameter=mysql.status[*],/usr/local/zabbix/etc/zabbix_agentd.conf.d/chk_mysql.sh $1
# 获取mysql运行状态
UserParameter=mysql.ping,mysqladmin -uroot -pqwe123 -P3306 -hlocalhost  ping | grep -c alive

3. 编辑zabbix_agentd的配置文件，加入zabbix_mysql.conf配置

# vi /usr/local/zabbix/etc/zabbix_agentd.conf

// 去掉#
Include=/usr/local/zabbix/etc/zabbix_agentd.conf.d/*.conf

4. 重启zabbix_agent
5. 测试是否成功

# /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -p 10050 -I 172.16.1.162 -k mysql.version

附录: windows下的脚本

mysql_ping.vbs

Set objFS =CreateObject("Scripting.FileSystemObject")
Set objArgs = WScript.Arguments
str1 = getCommandOutput("mysqladmin -uroot -pqwe123 ping")
  
If Instr(str1,"alive") > 0Then
WScript.Echo 1
Else
WScript.Echo 0
End If
  
Function getCommandOutput(theCommand)
  
Dim objShell, objCmdExec
Set objShell =CreateObject("WScript.Shell")
Set objCmdExec = objshell.exec(thecommand)
getCommandOutput =objCmdExec.StdOut.ReadAll
end Function

mysql_status.vbs

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objArgs = WScript.Arguments
str1 = getCommandOutput("mysqladmin -uroot -pqwe123 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 For
End If
next
  
  
Function getCommandOutput(theCommand)
  
Dim objShell, objCmdExec
Set objShell =CreateObject("WScript.Shell")
Set objCmdExec = objshell.exec(thecommand)
getCommandOutput =objCmdExec.StdOut.ReadAll
  
end Function