This article is NOT about how to setup the application called MySQL Enterprise Monitor but rather how to setup MySQL monitoring in your Zabbix Enterprise Monitoring software.

Zabbix is fucking nice and gives a lot of functionality that you normally have to pay top DOLLAR for. Usually US dollars too.

Anyways you can use Zabbix to do some pretty nice MySQL monitoring, with the end result providing a lot of information you would expect to find in MySQL Enterprise Monitor.

Here’s how this works:

• – Network communication to the server provided by the Zabbix Agent.
• – Agent side UserParameter settings allow for needed commands to be run against MySQL
• – Server side MySQL Host Templates are used to apply the checks, create graphs etc.

Agent Side Configuration (the box you’re monitoring):

So yeah this is where we assume you have your Zabbix server and agent up and running properly.  As such, go figure that part out first and perhaps I’ll write about it one day.

On the Scientific Linux/CentOS repository packages provided by Zabbix, a default configuration file is included with everything you need.

[root@vmhacks.com ~]#  cd /etc/zabbix/zabbix_agentd.d/

[root@vmhacks.com zabbix_agentd.d]# ls
userparameter_mysql.conf

Taking a look at that file, which by the way is enabled by having this line in your agent config file (should be already enabled)

Include=/etc/zabbix/zabbix_agentd.d/

Right so taking a look at the userparameter_mysql.conf file, these are the lines which perform the actual MySQL checks:

UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';" | HOME=/var/lib/zabbix mysql -N | awk '{print $$2}'

UserParameter=mysql.size[*],echo “select sum($(case “$3″ in both|””) echo “data_length+index_length”;; data|index) echo “$3_length”;; free) echo “data_free”;; esac)) from information_schema.tables$([[ “$1” = “all” || ! “$1″ ]] || echo ” where table_schema=’$1′”)$([[ “$2” = “all” || ! “$2” ]] || echo “and table_name=’$2′”);” | HOME=/var/lib/zabbix mysql -N

UserParameter=mysql.ping,HOME=/var/lib/zabbix mysqladmin ping | grep -c alive

UserParameter=mysql.version,mysql -V

Alright so you don’t need to change anything in the above file at all, just notice that the HOME variable is set to “/var/lib/zabbix”.

NOTE: If you’re using RHEL 5 instead of 6, see my notes at the bottom about it.

Now, this next step should be the first change you’ve actually made thus far:

[root@vmhacks.com ~]# mkdir /var/lib/zabbix

Right, now you can place your MySQL client connection information into this directory.  When the Zabbix agent runs the UserParameter commands, it will check the /var/lib/zabbix/.my.cnf  for a username and password.  So that means you need something like the following in your /var/lib/zabbix/.my.cnf file:

[client]
user=zabbix_boy
password=boyimtired

 Grant Access In MySQL Server for the zabbix_boy user account (or whatever you call you account, probably something like “moniton_the_unstoppable”): 

So login as root and run these in MySQL:


mysql> create user 'zabbix_boy'@'localhost' IDENTIFIED BY 'boyimtired';
mysql> SET PASSWORD FOR 'zabbix_boy'@'localhost' = PASSWORD('boyimtired');
mysql> GRANT USAGE ON *.* TO 'zabbix_boy'@'localhost' IDENTIFIED BY 'boyimtired';
mysql> flush privileges;

THE FINISHING TOUCH:

Login to Zabbix and go to Configuration > Hosts > Add the App MySQL template to your host and BOOM.

You’ll start getting a status and uptime monitor plus metrics and graphs on the following info:

• bytes received/sent per second
• deletes per second
• inserts per second
• queries per second
• selects per second
• slow queries
• status, uptime
• update ops per second

Pretty fucking awesome!

CENTOS 5: I’ve had no issues with the above steps on CentOS/RHEL/Scientific Linux 6 but it seems to just read from your standard /etc/my.cnf for client connection info in CentOS 5.  So just throw the [Client] lines into your /etc/my.cnf and it should be fine.  If not, you’ll probably get access denied errors in your agent log because it will just try to connect to MySQL via localhost using “zabbix” username and no password.