Use zabbix to automatically detect and monitor MySQL and zabbixmysql

Use zabbix to automatically detect and monitor MySQL and zabbixmysql
Introduction

Use the zabbix's low-level automatic discovery function to monitor multiple ports on a single host. For details, see low_level_discovery. The overall monitoring plug-in is similar to the zabbix monitoring plug-in of percona.mymonitor.plReplaced by scriptss_get_mysql_stats.phpAnd the configuration is a little different.

For detailed code and configuration, see zabbix_mysql.

1. Structure Description:

Zabbix_mysql/| -- README. md | -- bin | -- get_mysql_stats_wrapper.sh # For mymonitor. an encapsulation script of pl running results, which runs once every five minutes by default | -- mymonitor. pl # equivalent to ss_get_mysql_stats.php script | '-- mysql_port.pl # automatically discovers the MySQL port script and returns the output in json format | -- install. sh # installation script '-- templates | -- userparameter_discovery_mysql.conf # zabbix_agent Configuration Parameter' -- zabbix_mysql_multiport.xml # zabbix_server Template

mymonitor.plThe script depends on the following modules:

perl-DBIperl-DBD-mysql

 

The required monitoring permissions include:PROCESS, SUPER, REPLICATION SLAVE, Inmysql_port.plIn the scriptnetstatCommand to obtain the port for running MySQL. The script is started by zabbix users (or normal users). To avoid permission problems, the script install. sh is executed by default.chmod +s /bin/netstatCommand.

The mymonitor. pl script defaultsmonitor/monitorThe user and password can connect to MySQL to obtain the status. You can change the default password at the beginning of the script or specify--userAnd--passwordYou can also/home/mysql/.my.cnfSpecify user information. For more information, seeperldoc mymonitor.pl

get_mysql_stats_wrapper.shRead scripts by defaultmymonitor.plThe generated file is used to respond to the request of zabbix. The default value is 5 minutes. Because it requires frequent (1 minute by default) detection of the running status of slave, MySQL login information must be provided for slave detection.

2. Installation instructions

On the agent side:

# git clone https://github.com/chenzhe07/zabbix_mysql.git /usr/local/zabbix_mysql# bash /usr/local/zabbix_mysql/install.sh 192.168.1.2

 

192.168.1.2It is an intranet ip address. Considering that there may be multiple Intranet ip addresses, It is not automatically obtained and needs to be manually added.

On the server side:

import templates/zabbix_mysql_multiport.xml using Zabbix UI(Configuration -> Templates -> Import), and Create/edit hosts by assigning them “MySQL” group and linking the template “MySQL_zabbix” (Templates tab).

 

Import the template and add it to the machine to be monitored.

3. Test

# perl  mymonitor.pl --host 10.0.0.10 --port 3300 --items hvhv:36968# perl  mymonitor.pl --host 10.0.0.10 --port 3300 --items kxkx:1070879944# php ss_get_mysql_stats.php --host 10.0.0.10 --port 3300 --items hvhv:36968# php ss_get_mysql_stats.php --host 10.0.0.10 --port 3300 --items kx kx:1070911408# zabbix_get -s 10.0.0.10 -p 10050 -k "MySQL.Bytes-received[3300]"472339244134

 

Other features

Compared with the new version, innodb transactions, locks, and long statement run detection are added:

item                       throttlemax_duration             if > 100s, then trigger an alarmwaiter_count             if > 10, then trigger an alarmidle_blocker_duration    if > 200s, then trigger an alarm

Test as follows:

# zabbix_get -s cz-test2 -p 10050 -k "MySQL.max_duration[3301]"max_duration:longest transaction active seconds: max time: 18, thread_id: 4838781, user: root@10.0.21.5:59980