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 Function3. 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.vbs4. 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