开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 系统运维 -> zabbix监控mysql主从与主从延迟 -> 正文阅读

[系统运维]zabbix监控mysql主从与主从延迟

作者:recommend-item-box type_blog clearfix

zabbix监控mysql主从与主从延迟

文章目录

zabbix监控mysql主从与主从延迟

一、Zabbix监控mysql主从

1、部署mysql主从，使用mariadb进行操作

[root@master ~]# vim /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.89.151 server.example.com server
192.168.89.150 agent1.example.com agent1
192.168.89.10 master.example.com  master
192.168.89.20 slave.example.com slave
[root@master ~]#

[root@slave ~]# vim /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.89.151 server.example.com server
192.168.89.150 agent1.example.com agent1
192.168.89.10 master.example.com master
192.168.89.20 slave.example.com slave
[root@slave ~]#

2、将server、agent1、master、slave主机的/etc/hosts文件全部设置为

[root@server ~]# vim /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.89.151  server.example.com server
192.168.89.150  agent1.example.com agent1
192.168.89.10  master.example.com master
192.168.89.20  slave.example.com  slave
[root@server ~]#

[root@agent1 ~]# vim /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.89.151  server.example.com server
192.168.89.150  agent1.example.com agent1
192.168.89.10  master.example.com master
192.168.89.20  slave.example.com  slave
[root@agent1 ~]#

3、两台主机都安装mariadb mariadb-server

[root@master ~]# yum -y install mariadb mariadb-server
[root@master ~]# systemctl start mariadb
[root@master ~]# systemctl enable mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@master ~]#

[root@slave ~]#  yum -y install mariadb mariadb-server
[root@slave ~]# systemctl start mariadb
[root@slave ~]# systemctl enable mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@slave ~]#

4、两台主机都初始化mysql数据库

[root@master ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@master ~]#

[root@slave ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@slave ~]#

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

master配置：

[root@master ~]# vim /etc/my.cnf.d/mariadb-server.cnf
     16 [mysqld]
     17 datadir=/var/lib/mysql
     18 socket=/var/lib/mysql/mysql.sock
     19 log-error=/var/log/mariadb/mariadb.log
     20 pid-file=/run/mariadb/mariadb.pid
     21 log_bin=mysql-bin
     22 server_id=10
[root@master ~]# systemctl restart mariadb

slave配置：

[root@slave ~]# vi /etc/my.cnf.d/mariadb-server.cnf
     16 [mysqld]
     17 datadir=/var/lib/mysql
     18 socket=/var/lib/mysql/mysql.sock
     19 log-error=/var/log/mariadb/mariadb.log
     20 pid-file=/run/mariadb/mariadb.pid
     21 log_bin=mysql-bin
     22 server_id=20
[root@slave ~]# systemctl restart mariadb

6、进入数据库配置主从

[root@master ~]# mysql -uroot -predhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-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)]> grant all privileges  on *.* to root@'%' identified by "redhat";
Query OK, 0 rows affected (0.000 sec)

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

MariaDB [(none)]>

[root@slave ~]# mysql -uroot -predhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-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)]> 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.002 sec)

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

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Connecting to master
                   Master_Host: master
                   Master_User: user
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File:
           Read_Master_Log_Pos: 4
                Relay_Log_File: mariadb-relay-bin.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File:
              Slave_IO_Running: Connecting
             Slave_SQL_Running: Yes         //此处两行分别为connecting和yes

7、在slave主机中安装zabbix-agent软件包，将slave添加到zabbix web监控平台中，将server主机的zabbix.repo复制过来，接着安装zabbix-agent

[root@slave ~]# scp root@192.168.89.151:/etc/yum.repos.d/zabbix.repo /etc/yum.repos.d/
root@192.168.89.151's password:
zabbix.repo                                                                                                                             100%  385   461.2KB/s   00:00
[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 ~]# dnf -y install zabbix-agent

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

[root@slave ~]# vim /etc/zabbix/zabbix_agentd.conf
98 Server=192.168.89.151
139 ServerActive=192.168.89.151
150 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.
[root@slave ~]#

9、进入zabbix web监控平台，添加主机

[root@slave ~]# systemctl stop firewalld
[root@slave ~]# vim /etc/selinux/config
SELINUX=disabled
[root@slave ~]# setenforce 0

请添加图片描述

10、在slave主机上配置脚本

[root@slave ~]# cd /
[root@slave /]# vim 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 /]# chown -R zabbix.zabbix /mysql_slave_status.sh
[root@slave /]# chmod +x mysql_slave_status.sh

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

[root@slave /]# cd /etc/zabbix/zabbix_agentd.d/
[root@slave zabbix_agentd.d]# vim mysql_slave.conf
UserParameter=mysql.slave[*],/mysql_slave_status.sh $1
[root@slave zabbix_agentd.d]# chown -R zabbix.zabbix /etc/zabbix/zabbix_agentd.d/mysql_slave.conf
[root@slave zabbix_agentd.d]# systemctl restart zabbix-agent.service

12、去zabbix server验证状态，使用zabbix_get命令验证，需要先下载zabbix-get软件包

[root@server]# dnf -y install zabbix-get

#验证的结果如果是0，为正常，如果为1，则异常
[root@server ~]# zabbix_get -s 192.168.89.20 -k mysql.slave[sql]
0
[root@server ~]# zabbix_get -s 192.168.89.20 -k mysql.slave[io]
0

13、在zabbix web平台配置

新建监控项
请添加图片描述

新建触发器

创建图形

14、测试验证，将mysql主从关闭，查看zabbix告警信息，验证邮箱是否能接收到邮件

请添加图片描述

二、zabbix监控主从延迟

1、配置库脚本

[root@slave ~]# cd /etc/zabbix/
[root@slave zabbix]# mkdir script
[root@slave zabbix]# cd script/
[root@slave script]# vim mysql_delay.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
#Behind：落后主库多少秒，存在秒数则出现主库复制之间的延迟    
#只要当延迟数据为NULL，以及0-200是正常的，否则其他数字输入1表示错误
[root@slave script]# chown -R zabbix.zabbix mysql_delay.sh
[root@slave script]# chmod +x mysql_delay.sh
[root@slave script]#

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

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

测试mysql_delay.sh脚本

[root@slave zabbix_agentd.d]# cd /etc/zabbix/script/
[root@slave script]# ./mysql_delay.sh
0
[root@slave script]#

3、zabbix server主机进行脚本测试

[root@server ~]# zabbix_get -s 192.168.89.20 -k check_mysql_delay
0
[root@server ~]#

4、在zabbix web平台操作

添加监控项
请添加图片描述

5、创建触发器

请添加图片描述

[root@slave script]# mysql -uroot -predhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 99
Server version: 10.3.28-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, 1 warning (0.001 sec)

MariaDB [(none)]>

请添加图片描述