Detailed explanation of how Zabbix monitors the master-slave status of MySQL

After setting up the MySQL master-slave, you often don't know whether the slave status is OK, and sometimes you can't know in time when an exception occurs. Here, you can use shell scripts combined with zabbix to achieve monitoring and alarm

Generally, the running status of the slave in MySQL is checked by checking whether the Slave_IO_Running thread and the Slave_SQL_Running thread are ok. You can check it by using the command "show slave status\G;". So here we make a judgment based on these two values.

Agent-side script writing and configuration

Note: I put all zabbix-related scripts in the /etc/zabbix/script/ directory. The following are all operated on the monitored end of zabbix, and the above database belongs to the slave of MySQL master-slave

1) Scripting

[root@srt-xt ~]# cd /etc/zabbix/script/
[root@srt-xt /etc/zabbix/script]# cat mysql_slvae_status.sh 
#!/bin/bash
#Desc: Used to obtain master-slave synchronization information, determine whether the master-slave is abnormal, and then submit it to zabbix
#Date: 2019-06-06
#by:Lee-YJ
USER="root"
PASSWD="nae3eabo9naeli1Oov1a"
NAME=$1
function IO {
  Slave_IO_Running=`mysql -u $USER -p$PASSWD -e "show slave status\G;" 2> /dev/null |grep Slave_IO_Running |awk '{print $2}'`
  if [ $Slave_IO_Running == "Yes" ]; then
    echo 0 
  else
    echo 1 
  fi
}
function SQL {
  Slave_SQL_Running=`mysql -u $USER -p$PASSWD -e "show slave status\G;" 2> /dev/null |grep Slave_SQL_Running: |awk '{print $2}'`
  if [ $Slave_SQL_Running == "Yes" ]; then
    echo 0 
  else
    echo 1 
  fi
}
case $NAME in
  io)
    IO
  ;;
  sql)
    SQL
  ;;
  *)
    echo -e "Usage: $0 [io | sql]"
esac

2) Modify the configuration file and write a self-configuration file that specifies the path of the script written above

[root@srt-xt ~]# cd /etc/zabbix/zabbix_agentd.d/ 

[root@srt-xt /etc/zabbix/zabbix_agentd.d]# cat userparameter_mysql_slave.conf 
# Get MySQL slave status UserParameter=mysql.slave[*],/etc/zabbix/script/mysql_slvae_status.sh $1

3) Restart zabbix-agent

[root@srt-xt /etc/zabbix/zabbix_agentd.d]# /etc/init.d/zabbix-agent restart

4) Test on the zabbix-server to see if the value can be successfully obtained. According to the above script, 0 here means normal, and 1 means abnormal.

[root@xxxxx ~]# zabbix_get -s 218.75.249.55 -k mysql.slave[sql]
0
[root@xxxxx ~]# zabbix_get -s 218.75.249.55 -k mysql.slave[io]
0

Server-side web configuration

1) Configure the Slave_IO_Running thread monitoring item

2) Configure the Slave_SQL_Running thread monitoring item

3) Configure the trigger of the Slave_IO_Running thread

4) Configure the trigger of the Slave_SQL_Running thread

5) Configure trigger actions

Configure the actions to be performed (send a message to the administrator)

Configuration status recovery operation (also sends a message to the administrator)

Finally view the monitoring items

At this point, the status monitoring of the MySQL master-slave slave is completed.

Summarize

The above is a detailed explanation of the method of Zabbix monitoring the master-slave status of MySQL introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!