Continuing the series about zabbix, in this article I will show you how to monitor MySQL/MariaDB with Zabbix.
Before going into the MySQL/MariaDB monitoring setup, you need to make sure that the zabbix agent is installed on the server.
Configuration Guide
Step 1: Configure MySQL/MariaDB
Create a new user, here I create a user named zabbix, and grant permissions for it
1 2 3 4 5 6 | mysql -u root -p CREATE USER zabbix@localhost IDENTIFIED BY '<password>'; GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zabbix'@'localhost'; FLUSH PRIVILEGES; quit; |
Step 2: Create file .my.cnf
You need to create a .my.cnf file in the home path of zabbix /var/lib/zabbix, declare the following information:
1 2 3 | mkdir -p /var/lib/zabbix vi /var/lib/zabbix/.my.cnf |
then paste the following content into the file .my,cnd
1 2 3 4 | [client] user = zabbix password = <password> |
Step 3: Check the configuration in the file zabbix_agentd.conf
Now we will check if the following line is uncommented in the /etc/zabbix/zabbix_agentd.conf file (if not, add it)
1 2 | Include=/etc/zabbix/zabbix_agentd.d/*.conf |
Step 4: Create the file userparameter_mysql.conf Next, inside the directory /etc/zabbix/zabbix_agent.d/ there will be a file userparameter_mysql.conf. If not, recreate this file and add the following content:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | #template_db_mysql.conf created by Zabbix for "Template DB MySQL" and Zabbix 4.2 #For OS Linux: You need create .my.cnf in zabbix-agent home directory (/var/lib/zabbix by default) #For OS Windows: You need add PATH to mysql and mysqladmin and create my.cnf in %WINDIR%my.cnf,C:my.cnf,BASEDIRmy.cnf https://dev.mysql.com/doc/refman/5.7/en/option-files.html #The file must have three strings: #[client] #user='zbx_monitor' #password='<password>' # UserParameter=mysql.ping[*], mysqladmin -h"$1" -P"$2" ping UserParameter=mysql.get_status_variables[*], mysql -h"$1" -P"$2" -sNX -e "show global status" UserParameter=mysql.version[*], mysqladmin -s -h"$1" -P"$2" version UserParameter=mysql.db.discovery[*], mysql -h"$1" -P"$2" -sN -e "show databases" UserParameter=mysql.dbsize[*], mysql -h"$1" -P"$2" -sN -e "SELECT COALESCE(SUM(DATA_LENGTH + INDEX_LENGTH),0) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$3'" UserParameter=mysql.replication.discovery[*], mysql -h"$1" -P"$2" -sNX -e "show slave status" UserParameter=mysql.slave_status[*], mysql -h"$1" -P"$2" -sNX -e "show slave status" |
Note: Remember to pay attention to find the right monitor template according to the version of mysql and zabbix, the template above is testing for mysql version 5.6 and zabbix server 5.0
Step 5: Now restart zabbix agent
1 2 | systemctl restart zabbix-agent |
Step 6: Add Host and select MySQL/MariaDB template
Access the zabbix server interface, select Configuration >> Host >> Create Host
Select MySQL DB template
After filling in the necessary information as shown above, click Add to complete.