Monitoring mysql service on zabbix server

1. Configure mysql

In the / etc / zabbix / zabbix ﹣ agent D. D / userparameter ﹣ mysql.conf file, it is specified how zabbix agent obtains various information of MySQL service, and a new ". my.cnf" file is created in the / var/lib/zabbix directory to specify how zabbix agent logs in the database. By default, zabbix users are used, and passwords can be found in the zabbix ﹣ server.conf file.

[root@zabbix_server ~]# cd /etc/zabbix/zabbix_agentd.d
[root@zabbix_server zabbix_agentd.d]# ls
userparameter_mysql.conf
[root@zabbix_server zabbix_agentd.d]# cat userparameter_mysql.conf | grep mysql

# 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>].
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

[root@zabbix_server zabbix_agentd.d]# mkdir -p /var/lib/zabbix

[root@zabbix_server ~]# mysql_secure_installation      ##Add secret code
[root@zabbix_server ~]# mysql -ppasswd     ##Test landing
MariaDB [(none)]> 
[root@zabbix_server ~]# mysqladmin -uroot -ppasswd ping | grep -c alive        ##Test to get the information of mysql service
1
[root@zabbix_server zabbix]# cd /var/lib/zabbix/
[root@zabbix_server zabbix]# vim .my.cnf

[mysql]
host=localhost
user=root
password=passwd                     ##This password is the login password of root user
socket=/var/lib/mysql/mysql.sock

[mysqladmin]
host=localhost
user=root
password=passwd
socket=/var/lib/mysql/mysql.sock

[root@zabbix_server zabbix]# systemctl restart zabbix-agent.service
[root@zabbix_server el7]# yum install -y zabbix-get-3.2.6-1.el7.x86_64.rpm
[root@zabbix_server ~]# zabbix_get -s 127.0.0.1 -k mysql.version
mysql  Ver 15.1 Distrib 5.5.44-MariaDB, for Linux (x86_64) using readline 5.1

[root@zabbix_server ~]# zabbix_get -s 127.0.0.1 -k mysql.ping      ##Test to get the information of mysql service

If you are monitoring the mysql service of the remote host, you need to create a zabbix user and authorize
MariaDB [(none)]> GRANT USAGE ON . TO 'zabbix'@'localhost' IDENTIFIED BY 'passwd';
MariaDB [(none)]> FLUSH PRIVILEGES;

Click configuration - > host - > ZABBIX server - > template - > Select - > template app MySQL

Click configuration - > host - > ZABBIX server graph to see two new monitoring graphs