Zabbix监控Windows系统上的MySQL

Zabbix监控Windows系统上的MySQL

环境：
操作系统：windows server 2008
MySQL版本8.0.32
Zabbix agent版本5.0.41

一. MySQL下创建zabbix监控用户

CREATE USER 'zabbix'@'%' IDENTIFIED BY 'zabbix2024';
GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zabbix'@'%';
FLUSH PRIVILEGES;

由于从MySQL 8.0开始，默认的用户密码加密方式变成了caching_sha2_password，而老版本的客户端可能还不支持这种新的加密方式。
可以将MySQL用户的密码认证方式改为mysql_native_password。可以使用以下SQL命令：

ALTER USER 'zabbix'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'zabbix2024';
FLUSH PRIVILEGES;

否则从zabbix服务器测试连接mysql会报错：
Authentication plugin ‘caching_sha2_password’ cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

二.创建监控脚本

（网上找的😅）

(1)mysql_version.vbs

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objArgs = WScript.Arguments
str1 = getCommandOutput("E:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe -V")
 
WScript.Echo str1
 
Function getCommandOutput(theCommand)
Dim objShell, objCmdExec
Set objShell = CreateObject("WScript.Shell")
Set objCmdExec = objshell.exec(thecommand)
getCommandOutput = objCmdExec.StdOut.ReadAll
end Function

(2) mysql_ping.vbs

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objArgs = WScript.Arguments
str1 = getCommandOutput("E:\Program Files\MySQL\MySQL Server 8.0\bin\mysqladmin -uzabbix -pzabbix2024 ping")

If Instr(str1,"alive") > 0 Then
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

(3) mysql_status.vbs

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objArgs = WScript.Arguments
str1 = getCommandOutput("E:\Program Files\MySQL\MySQL Server 8.0\bin\mysqladmin -uzabbix -pzabbix2024 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

三.修改zabbix_agentd.conf配置文件

UnsafeUserParameters=1

UserParameter=mysql.status[*],cscript /nologo C:\zabbix\scripts\mysql_status.vbs $1
UserParameter=mysql.ping,cscript /nologo  C:\zabbix\scripts\mysql_ping.vbs  
UserParameter=mysql.version,cscript /nologo  C:\zabbix\scripts\mysql_version.vbs

重启zabbix agent服务

四.在zabbix服务端测试

zabbix_get命令安装：yum install zabbix-get -y

通过zabbix_get测试是否能从脚本获取到数据：
zabbix_get -s 192.168.130.224 -k “mysql.ping”
zabbix_get -s 192.168.130.224 -k “mysql.version”
zabbix_get -s 192.168.130.224 -k “mysql.status[Bytes_received]”

😥遇到的问题：
添加Zabbix自带的模板，监控平台无法获取到数据😥。（目前还没找到解决办法，希望有大佬指教）

我们创建自定义监控项
（1）mysql.ping

（2）mysql.status


创建触发器（直接克隆模板自带的触发器再修改）

修改如下：

手动停止MySQL服务后，该触发器能正常触发。