Monitoring MySQL with Zabbix

I ran into quite a few issues with setting up a Zabbix agent for Linux to monitor the server and the MySQL database on the machine. It seems that MySQL monitoring in Zabbix has been slightly modified over the years and little change documentation was made to keep up with it. I’m going to go over every step involved with getting MySQL monitoring setup on Zabbix 4.4 since I wasted so much time getting it to work. Hopefully this saves someone a few hours or days of frustration.

My Setup:

• Zabbix 4.4.1 running in VMWare Workstation
• DigitalOcean Droplet running Ubuntu 18.04.3 (LTS) x64 on their lowest tier plan (1 CPU, 1GB RAM, 25GB SSD disk, 1000GB transfer).
• MySQL version is 14.14 Distrib 5.7.29, it was installed on the Droplet using this guide.

Zabbix Agent Setup

Installing the Zabbix Agent on Linux is very easy. Follow the instructions here.

The first problem you will most likely run into is the userparameter_mysql.conf file is missing from /etc/zabbix/agentd.d/.

Create your userparameter_mysql.conf file in /etc/zabbix/zabbix_agentd.conf.d/ and paste this in:

UserParameter=mysql.ping, mysqladmin --defaults-extra-file='/var/lib/zabbix/.my.cnf' ping | grep -c alive
UserParameter=mysql.get_status_variables, mysql --defaults-extra-file='/var/lib/zabbix/.my.cnf' -sNX -e "show global status"
UserParameter=mysql.version, mysql -V
UserParameter=mysql.db.discovery, mysql --defaults-extra-file='/var/lib/zabbix/.my.cnf' -sN -e "show databases"
UserParameter=mysql.dbsize[*], mysql --defaults-extra-file='/var/lib/zabbix/.my.cnf' -sN -e "SELECT SUM(DATA_LENGTH + INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$1'"
UserParameter=mysql.replication.discovery[*], mysql --defaults-extra-file='/var/lib/zabbix/.my.cnf' -sNX -e "show slave status"
UserParameter=mysql.slave_status[*], mysql --defaults-extra-file='/var/lib/zabbix/.my.cnf' -sN -e "show slave status"

If you look in zabbix_agentd.conf located at /etc/zabbix/ you will see that /etc/zabbix/agentd.d/*.conf is used by default, so you can actually create any filename you want in /etc/zabbix/agentd.d/ to do custom agent configuration. Do not create more than one file though or the Zabbix Agent will fail to start.

This file is defining what each Zabbix item will do to return a value. Notice that “var/lib/zabbix/.my.cnf” is referred to on every line. This is defining what file will provide credentials to Zabbix for MySQL. The next step is to create this file and decide what your username and password will be.

Create your .my.cnf file in /var/lib/zabbix/ and paste this in:

[client]
user=<user>
password=<password>

Enter the user and password that you will be using to login to MySQL. Let’s create the user in MySQL with this command:

CREATE USER 'user'@'%' IDENTIFIED BY '<password>';
GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'user'@'%';
FLUSH PRIVILEGES;

Now we need to address some permissions issues that you will most likely run into. In /etc/ update files passwd to include these directories (the two 000s will have values already):

zabbix:x:000:000::/nonexistent:/etc/zabbix:/var/lib/zabbix:/usr/sbin/nologin:/bin/bash:/home/zabbix:/var/run/zabbix/

Next, update line 95 “Server=” in /etc/zabbix/zabbix_agentd.conf to have the IP address of your Zabbix server.

Restart your Zabbix agent now to make changes effective:

systemctl restart zabbix-agent

Zabbix Server Configuration

To test that the configuration is working we can do a very basic check. From a command line run:

zabbix_get -s <ip_of_agent> -k mysql.ping

The return should be “1” which means MySQL is responding to your ping request. There are a few other responses you might experience if something is wrong:

error: 'Access denied for user 'zabbix'@'localhost' (using password: YES)'
0

If “zabbix” is not the user you assigned but it is trying to use it anyway, it is because the Zabbix Agent or MySQL cannot access .my.cnf due to a permission issue.

mysqladmin: [Warning] World-writable config file '/var/lib/zabbix/.my.cnf' is ignored.
mysqladmin: connect to server at 'localhost' failed

Another case of a permission issue. To fix, simply run:

chmod 440 /var/lib/zabbix/.my.cnf

Once you have any issues resolved and you can setup the host in your Zabbix server. Let’s address some issues that exist out of the box for Zabbix.

Navigate to “Configuration”, “Templates” and search for ‘mysql’:

Open “Template DB MySQL” and navigate to “Items”:

Open “MySQL: Version” and remove “[“{$MYSQL.HOST}”,”{$MYSQL.PORT}”]” from the Key:

Click Update at the bottom and open “MySQL: Status”. Remove “[“{$MYSQL.HOST}”,”{$MYSQL.PORT}”]” again:

Within MySQL:Status, open “Preprocessing”:

Remove both of these Preprocessing steps. They cause issues when doing Zabbix attempts the MySQL ping check:

Click Update. For the third item, click MySQL: Get Status Variables and delete remove “[“{$MYSQL.HOST}”,”{$MYSQL.PORT}”]”:

Click Update. Now the template should be ready for use.

Navigate to Configuration > Hosts and create a host. Name it whatever you want, assign it any group you want, make sure the IP is the IP of the Zabbix Agent / MySQL server. Click on Templates and assign it to Template DB MySQL:

Add the Host and navigate to Monitoring > Latest Data. Select your Host and see if the data starts to populate. It can take a few minutes, based on your settings and the current load on your Zabbix server.

Now you can setup triggers, dashboards, or whatever you want with the data.