How To: Monitoring a MySQL instanced on a Windows box

[syntax53]

I searched a ton of different sites / forums for information on this and through bits and pieces I found here and there I got this working. Sharing an up to date how-to...

The basis that I started with was from this post, but is a bit inaccurate with today's stuff: https://eric256-sysadmin.blogspot.co...ws-server.html

First thing is to create a user on the mysql instance for the monitoring--

CREATE USER 'zabbix'@'localhost' IDENTIFIED WITH mysql_native_password;
GRANT PROCESS ON *.* TO 'zabbix'@'localhost' IDENTIFIED BY 'somepassword';

Next, create a conf file somewhere with the logon information. This is to avoid the 'insecure' warning when running mysql commands from the CLI and specifying the password.

Contents of my "D:\DBs\zabbix_monitor.conf"--

[client]
user = zabbix
password = somepassword
host = localhost

Next, you need some unix commands available on the windows box: grep, cut, wc. This could probably be avoided by re-writing the commands using similar windows utils. This is how it is because this has evolved (I'm assuming here) from how the commands were on a unix box and no one has felt like re-writing it because you can just download the tools. Anyway, with that said, I downloaded these: https://sourceforge.net/projects/unxutils/. Unzip them somewhere.

Now we need to put the path to those binaries into your PATH environment variable as well as the binaries to your mysql / mysqladmin executables. This will vary depending on where you unzipped your files and with what version of mysql you are running. For me, I put my unix commands into "D:\UnxUtils\usr\local\wbin" and my mysql binaries are in "D:\DBs\mySQL\MySQL Server 5.6\bin". So, to change the environment vars, go into system properties -> advanced system settings / advanced tab -> environment variables...

MAKE SURE YOU EDIT THE 'Path' SETTING UNDER BOTH THE "User variables" AND "System variables" (top and bottom boxes). The zabbix agent runs under the 'system account' so only changing the user environ won't help. Before you put the values at the end of the line, make sure the line ends with a semicolon ( and put semicolons at the end of each path you are adding, e.g. "EXISTING PATHS;D:\UnxUtils\usr\local\wbin;D:\DBs\mySQL\MySQL Server 5.6\bin;"

At this point, open a NEW command prompt window (if you already had one open before making the path environ changes, it won't be reflected). You should be able to run the following test commands and get just a single return value:

Should return a single number representing uptime:
mysql --defaults-extra-file=D:\path\to\your\zabbix_monitor.conf -N -e "show global status like 'Uptime'" -s | cut -f2

Should return a float value for queries per second:
mysqladmin --defaults-extra-file=D:\path\to\your\zabbix_monitor.conf status | cut -d: -f9 | cut -d " " -f2

Now, you may need to reboot so that your "system account" gets the updated path variables. I had to reboot, but I also edited the path var in the registry under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Contro l\Session Manager\Environment before realizing I could do it the easy way that I mentioned earlier. I assume that's irrelevant though, because the system account is already loaded and that's why it won't get the updated path without rebooting. Long story short, reboot.

Now lets modify the zabbix configuration for the windows agent. Open "C:\Program Files\Zabbix Agent\zabbix_agentd.conf" and add the following lines at the end:

UserParameter=mysql.status[*],mysql --defaults-extra-file=D:\path\to\your\zabbix_monitor.conf -N -e "show global status like '$1'" -s | cut -f2
UserParameter=mysql.ping,mysqladmin --defaults-extra-file=D:\path\to\your\zabbix_monitor.conf ping | grep alive | wc -l | cut -d" " -f 7
UserParameter=mysql.threads,mysqladmin --defaults-extra-file=D:\path\to\your\zabbix_monitor.conf status | cut -d: -f3 | cut -d " " -f2
UserParameter=mysql.qps,mysqladmin --defaults-extra-file=D:\path\to\your\zabbix_monitor.conf status | cut -d: -f9 | cut -d " " -f2
UserParameter=mysql.version,mysql -V

Save the file and restart the zabbix service (either through services.msc or net stop/start "zabbix agent"). After doing that you can test that the zabbix server can remotely query the values (this is from the CLI of your zabbix server):

zabbix_get -s WindowsServerIp_or_HostName -k "mysql.qps"

If that works, then we are almost done!

Before we assign the template I made some changes to the one that came pre-installed for me on zabbix 3.4. On the zabbix web GUI (logged in), go to Configuration -> Templates -> Template DB MySQL -> Items.

Add a new item--

Name: MySQL queries per second avg since uptime
Key: mysql.qps
Type: Numeric (float)
Units: qps
Update: 1m
History: 30d
Application: MySQL

Add one more new item--

Name: MySQL threads
Key: mysql.threads
Type: Numeric (unsigned)
Update: 1m
History: 30d
Application: MySQL

As for the update interval and history, I changed them all from 7 days to 30 days. So put a check box next to everything except "MySQL version" and then click "Mass update" at the bottom. On the next screen put checkboxes next to Interval and History and make Interval "1m" and History "30d".

Now finally all we need to do is assign the template. You can do this by clicking on the template name where it says "All templates/Template DB MySQL" and then adding it under "Hosts / templates" or you can go to the host itself and link it.

Once added, go to Monitoring -> Latest Data. On the filter choose the host and the "MySQL" application and watch for data.