zabbix监控mysql主从

zabbix监控mysql主从

服务	ip	主机名
服务端	192.168.87.129	server
客户端	192.168.87.133	agent
客户端-主	192.168.87.138	master
客户端-从	192.168.87.128	slave

一、Zabbix监控mysql主从

1.修改主机名

[root@master ~]# hostnamectl set-hostname master.example.com
[root@master ~]# bash


[root@slave ~]# hostnamectl set-hostname slave.example.com
[root@slave ~]# bash

2.设置server、agent、master、slave主机的/etc/hosts文件

[root@server ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.87.129 server.example.com server
192.168.87.133 agent.example.com  agent
192.168.87.128 slave.example.com slave
192.168.87.138 master.example.com master


[root@agent ~]# cat  /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.87.129 server.example.com server
192.168.87.133 agent.example.com  agent
192.168.87.128 slave.example.com slave
192.168.87.138 master.example.com master


[root@master ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.87.129 server.example.com server
192.168.87.133 agent.example.com  agent
192.168.87.128 slave.example.com slave
192.168.87.138 master.example.com master

[root@slave ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.87.129 server.example.com server
192.168.87.133 agent.example.com  agent
192.168.87.128 slave.example.com slave
192.168.87.138 master.example.com master

3.修改数据库配置文件，然后两台主机都重启mariadb服

Master：
[root@master ~]# vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
#添加两行数据
log_bin=mysql-bin
server_id=20
[root@master ~]# systemctl restart mariadb


Slave：
[root@slave ~]# vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
#添加两行数据
log_bin=mysql-bin
server_id=30
[root@slave ~]# systemctl restart mariadb

4.进入数据库配置主从

Master：
[root@master ~]# mysql -uroot -predhat

MariaDB [(none)]> grant all privileges  on *.* to 'root'@'%' identified by "redhat";
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> grant replication slave on *.* to 'user'@'slave' identified by 'redhat';
Query OK, 0 rows affected (0.001 sec)


Slave：
[root@slave ~]# mysql -uroot -predhat
MariaDB [(none)]> grant all privileges  on *.* to 'root'@'%' identified by "redhat";
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> change master to master_host='master',master_user='user',master_password='redhat';
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> show slave status\G

···
              Slave_IO_Running: Connecting
             Slave_SQL_Running: Yes
···

5.在slave主机中安装zabbix-agent软件包，将slave添加到zabbix web监控平台中

[root@slave ~]# vim /etc/yum.repos.d/zabbix.repo
[root@slave ~]# cat /etc/yum.repos.d/zabbix.repo 
[aliyun]
name=aliyun
baseurl=https://mirrors.aliyun.com/zabbix/zabbix/4.4/rhel/8/x86_64/
enable=1
gpgcheck=0
[qinghua]
name=Zabbix Official Repository - $basearch
#baseurl=http://repo.zabbix.com/zabbix/3.4/rhel/7/$basearch/
baseurl=https://mirrors.tuna.tsinghua.edu.cn/zabbix/zabbix/4.4/rhel/8/$basearch/
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-ZABBIX-A14FE591

[root@slave ~]# yum install -y zabbix-agent

6.修改 /etc/zabbix/zabbix_agentd.conf，重启服务

[root@slave ~]# vim /etc/zabbix/zabbix_agentd.conf 
Server=192.168.87.129
ServerActive=192.168.87.129
Hostname=slave

[root@slave ~]# systemctl restart zabbix-agent.service 
[root@slave ~]# systemctl enable zabbix-agent.service 
Created symlink /etc/systemd/system/multi-user.target.wants/zabbix-agent.service → /usr/lib/systemd/system/zabbix-agent.service.

7.进入zabbix web监控平台，添加主机

8.在slave主机上配置脚本

[root@slave script]# cat mysql_slave_status.sh 
#!/bin/bash
USER="root"
PASSWD="redhat"
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 == "Connecting" ];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

[root@slave script]# chmod +x mysql_slave_status.sh 
[root@slave script]# chown -R zabbix.zabbix /etc/zabbix/script/

9.编写一个自配置文件，里面指定上面编写的脚本的路径，然后重启服务

[root@slave ~]# cd /etc/zabbix/zabbix_agentd.d/
[root@slave zabbix_agentd.d]# vim userparameter_mysql_slave.conf
[root@slave zabbix_agentd.d]# cat  userparameter_mysql_slave.conf
UserParameter=mysql.slave[*],/etc/zabbix/script/mysql_slave_status.sh $1

[root@slave zabbix_agentd.d]# chown -R zabbix.zabbix /etc/zabbix/zabbix_agentd.d/
[root@slave zabbix_agentd.d]# ll
total 4
-rw-r--r--. 1 zabbix zabbix 73 Sep  8 03:47 userparameter_mysql_slave.conf
[root@slave zabbix_agentd.d]# systemctl restart zabbix-agent.service 

10.去zabbix server验证状态，使用zabbix_get命令验证

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

11.在zabbix web平台配置
新建监控项

新建触发器

创建图形

检验
关闭主从

[root@slave ~]# mysql -uroot -predhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 43
Server version: 10.3.17-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> stop slave
    -> ;
Query OK, 0 rows affected (0.002 sec)

开启主从

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)

二、zabbix监控主从延迟

1.配置库脚本

[root@slave ~]# cd /etc/zabbix/script/
[root@slave script]# ll
total 4
-rwxr-xr-x. 1 zabbix zabbix 634 Sep  8 03:36 mysql_slave_status.sh
[root@slave script]# vim mysql_dalay.sh
[root@slave script]# cat mysql_dalay.sh 
#!/bin/bash     
delay=$(mysql -uroot -predhat -e 'show slave status\G' 2> /dev/null | grep 'Seconds_Behind_Master' | awk '{print $2}')
if [ $delay == "NULL" ];then
echo 0
elif [ $delay -ge 0 ] && [ $delay -le 200 ];then         
echo 0
else
echo $delay
fi
[root@slave script]# chown -R zabbix.zabbix mysql_dalay.sh 
[root@slave script]# chmod +x mysql_dalay.sh 

2.配置agentd文件，并重启服务

[root@slave ~]# vim /etc/zabbix/zabbix_agentd.d/userparameter_mysql_slave.conf 
[root@slave ~]# cat /etc/zabbix/zabbix_agentd.d/userparameter_mysql_slave.conf 
UserParameter=mysql.slave[*],/etc/zabbix/script/mysql_slave_status.sh $1
UserParameter=check_mysql_dalay,/bin/bash /etc/zabbix/script/mysql_dalay.sh
[root@slave ~]# systemctl restart zabbix-agent.service 

3.测试mysql_delay.sh脚本

[root@slave script]# ./mysql_dalay.sh 
0

4.zabbix server主机进行脚本测试

[root@server ~]# zabbix_get -s 192.168.87.128 -k check_mysql_dalay
0

5.添加监控项

6.创建触发器

7.测试一下0是否会告警