How to Add MySQL/MariaDB to zabbix monitoring?

How to Add MySQL/MariaDB to zabbix monitoring?

Its important to monitor your Database. Be it availability, DB size etc. We can monitor mysql Database in various Monitoring tools. Here in this article we'll see how to do it in Zabbix.

1. Create monitoring User.

mysql> create user 'zabbix'@'localhost' IDENTIFIED BY 'Password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT USAGE ON *.* TO 'zabbix'@'localhost' IDENTIFIED BY 'Password';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit

2. In the Database Server add this File with these parameters.

[root@ZABBIXDB1 ~]# cat /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf
# For all the following commands HOME should be set to the directory that has .my.cnf file with password information.

# Flexible parameter to grab global variables. On the frontend side, use keys like mysql.status[Com_insert].
# Key syntax is mysql.status[variable].
UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';" | HOME=/var/lib/zabbix mysql -N | awk '{print $$2}'

# Flexible parameter to determine database or table size. On the frontend side, use keys like mysql.size[zabbix,history,data].
# Key syntax is mysql.size[<database>,<table>,<type>].
# Database may be a database name or "all". Default is "all".
# Table may be a table name or "all". Default is "all".
# Type may be "data", "index", "free" or "both". Both is a sum of data and index. Default is "both".
# Database is mandatory if a table is specified. Type may be specified always.
# Returns value in bytes.
# 'sum' on data_length or index_length alone needed when we are getting this information for whole database instead of a single table
UserParameter=mysql.size[*],bash -c '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
UserParameter=mysql.uptime,HOME=/var/lib/zabbix mysqladmin status | cut -f2 -d ":" | cut -f1 -d "T" | tr -d " "
UserParameter=mysql.threads,HOME=/var/lib/zabbix mysqladmin status | cut -f3 -d ":" | cut -f1 -d "Q" | tr -d " "
UserParameter=mysql.questions,HOME=/var/lib/zabbix mysqladmin status | cut -f4 -d ":"|cut -f1 -d "S" | tr -d " "
UserParameter=mysql.slowqueries,HOME=/var/lib/zabbix mysqladmin status | cut -f5 -d ":" | cut -f1 -d "O" | tr -d " "
UserParameter=mysql.qps,HOME=/var/lib/zabbix mysqladmin status | cut -f9 -d ":" | tr -d " "
[root@ZABBIXDB1 ~]#
3. Create the file /var/lib/zabbix/.my.cnf

[root@ZABBIXDB1 ~]# cat /var/lib/zabbix/.my.cnf
[mysql]
user=zabbix
password=password
[mysqladmin]
user=zabbix
password=password

[root@ZABBIXDB1 ~]#

4. Now restart the Zabbix agent.

systemctl restart zabbix-agent.service

5. Now Add the MySQL Template in Zabbix for the DB Host.