Zabbix monitoring MySQL under Windows

1. Create monitoring account in MySQL

GRANT PROCESS,REPLICATION CLIENT ON *.* TO mysql_monitor@'localhost' IDENTIFIED BY 'Nt8eWv';

# process with this permission, users can executeSHOW PROCESSLIST and KILL Command.
# The replication client has this permission to query the status of master server and slave server.

2. Create three new script files in C: \ infinite server \ ZABBIX \ script directory of MySQL server

mysql_ping.vbs

Set objFS =CreateObject("Scripting.FileSystemObject")
Set objArgs = WScript.Arguments
str1 = getCommandOutput("C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqladmin.exe -umysql_monitor -pNt8eWv 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


mysql_status.vbs

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objArgs = WScript.Arguments
str1 = getCommandOutput("C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqladmin.exe -umysql_monitor -pNt8eWv 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
' Wscript.Echo getCommandOutput       //This sentence can print out all contents. Execute mysql_status.vbs $1
end Function


mysql_version.vbs

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objArgs = WScript.Arguments
str1 = getCommandOutput("C:\Program Files\MySQL\MySQL Server 5.5\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

3. Modify the ZABBIX? Agent.win.conf file on windows on MySQL server

Add at the end of zabbix_agent.win.conf

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

4. Test and add template in zabbix host

Test whether data can be obtained from script

/usr/bin/zabbix_get -s 192.168.10.61 -k "mysql.ping"
/usr/bin/zabbix_get -s 192.168.10.61 -k "mysql.version"
/usr/bin/zabbix_get -s 192.168.10.61 -k "mysql.status[Bytes_received]"

Restart ZABBIX [agentd], add Template App MySQL template to the host, and view the status of items

Monitoring items:

Com_update: number of updates executed by mysql
 Com_select: number of queries executed by mysql
 Com_insert: number of mysql inserts
 Com_delete: number of deletes executed
 Com? Rollback: number of operations to perform rollback
 Bytes_received: number of bytes accepted
 Bytes_sent: number of bytes sent
 Slow query: number of slow query statements
 Com_commit: number of confirmed things
 Com_begin: number of things to start
 Uptime: number of seconds the server has started
 Questions: number of statements sent by the client to the server


Tags: MySQL Zabbix shell mysqladmin

Posted on Tue, 05 May 2020 02:43:11 -0400 by b0gner17