Zabbix enterprise application-Mysql master/slave monitoring _ MySQL

Now I will introduce how to monitor the master-slave status of mysql. The following is monitoring: 1. MysqlSlaveStatusof3306Port is also monitored using lowleveldiscovery, I have merged a template with the previous lld template for mysql monitoring. now I am going to show you how to monitor the master-slave status of mysql.

The following is monitoring:

1. Mysql Slave Status of 3306 Port

This is also the use of low level discovery for monitoring, and has merged a template with the previous lld monitoring mysql template (so this article will only introduce the mysql master/slave status, other performance, in this way, a template can monitor both mysql Performance and mysql master/slave status automatically. if I/O or SQL is not in the yew status, an alarm is triggered.

How to implement:

I. client

1. json-based monitoring content

In the client, the monitoring content needs to be displayed in json format, and then the server can filter the results through regular expressions.

The following is the json display of my json monitoring mysql

The script content is as follows:

 
 

  
  
#!/bin/bash 
  
  
#Fucation:mysql low-level discovery 
  
  
#Script_name mysql_low_discovery.sh 
  
  
mysql() { 
  
  
 port=($(sudo netstat -tpln | awk -F "[ :]+" '/[m]ysql/ && /0.0.0.0/ {print $5}')) 
  
  
 printf '{\n' 
  
  
 printf '\t"data":[\n' 
  
  
 for key in ${!port[@]} 
  
  
 do 
  
  
 if [[ "${#port[@]}" -gt 1 && "${key}" -ne "$((${#port[@]}-1))" ]];then 
  
  
 socket=`ps aux|grep ${port[${key}]}|grep -v grep|awk -F '=' '{print $10}'|cut -d ' ' -f 1` 
  
  
 printf '\t {\n' 
  
  
 printf "\t\t\t\"{#MYSQLPORT}\":\"${port[${key}]}\"},\n" 
  
  
 else [[ "${key}" -eq "((${#port[@]}-1))" ]] 
  
  
 socket=`ps aux|grep ${port[${key}]}|grep -v grep|awk -F '=' '{print $10}'|cut -d ' ' -f 1` 
  
  
 printf '\t {\n' 
  
  
 printf "\t\t\t\"{#MYSQLPORT}\":\"${port[${key}]}\"}\n" 
  
  
 fi 
  
  
 done 
  
  
 printf '\t ]\n' 
  
  
 printf '}\n' 
  
  
} 
  
  
$1 
 
 

Store the file in/usr/local/zabbix/bin, Grant 755 permissions, modify the user and group to zabbix, and allow zabbix users to run netstat without a password.

 
 

  
  
echo "zabbix ALL=(root) NOPASSWD:/bin/netstat">>/etc/sudoers 
 
 

# Disable requiretty

 
 

  
  
sed -i 's/^Defaults.*.requiretty/#Defaults requiretty/' /etc/sudoers 
 
 

If it is not disabled, data cannot be obtained and the zabbix log reports

2. modify the zabbix_agentd.conf file

Add the following content at the end of zabbix_agentd.conf:

 
 

  
  
UserParameter=zabbix_low_discovery[*],/bin/bash /usr/local/zabbix/bin/mysql_low_discovery.sh $1 
  
  
UserParameter=mysql_stats[*],mysql -h localhost -P $1 -uzabbix -pzabbix -e "show global status"|grep "\<$2\>"|cut -f2 
  
  
UserParameter=mysql_stats_slave[*],mysql -h localhost -P $1 -uzabbix -pzabbix -e "show slave status\G"|grep "\<$2\>"|awk '{if($NF=="Yes") {print 1} else {print 0}}' 
 
 

3. test

If you can use/usr/local/zabbix/bin/zabbix_get-s 127.0.0.1-k Runtime [mysql] and/usr/local/zabbix/bin/zabbix_get-s 127.0.0.1-k mysql_stats_slave [3306, slave_IO_Running],/usr/local/zabbix/bin/zabbix_get-s 127.0.0.1-k mysql_stats_slave [3306, Slave_ SQL _Running] command to obtain data indicates no problem. The following is my test result

4. mysql authorization

 
 

  
  
GRANT PROCESS，REPLICATION CLIENT ON *.* TO 'zabbix'@'localhost' identified BY 'zabbix'; 
  
  
Flush privileges; 
 
 

5. restart the zabbix service.

 
 

  
  
ps -ef|grep zabbix|grep -v grep|awk '{print $2}'|xargs kill -9 
  
  
/usr/local/zabbix/sbin/zabbix_agentd -c /usr/local/zabbix/conf/zabbix_agentd.conf 
 
 

II. server

1. Template import

Import the Template Mysql Auto Discovery into zabbix (the Template is included in the attachment). the specific operations are not described.

2. set the regular expression

In "manage" = "general" = "regular expression", select "New Regular expression"

Then, set the settings as follows:

I am running these 9 mysql instances from 3301 to 3309

3. host Association template

You can monitor the host management template of the mysql instance. the default value is automatically updated 3600 seconds later, as shown in figure

Why set 3600 seconds? The reason is that if the setting time is too short, for example, if you set 60 s, one is the pressure on the server, and the other is that if the port you detect suddenly drops, there is no urgent alarm, if the host cannot obtain this information through json, it is deemed that this port is not available. the content of this metric item is automatically disabled in the template (30 days by default, and then automatically deleted ), in this way, the alarm function is basically unavailable, so it is better to find a longer interval.