[系统运维]Zabbix监控mysql主从

一、Zabbix监控mysql主从

1、部署mysql主从，使用mariadb进行操作
//先修改其主机名

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

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

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.47.137 server.example.com server
192.168.47.136 agent.example.com  agent
192.168.47.199 master.example.com master
192.168.47.200 slave.example.com  slave

3、master和slave为centos8的操作系统，将centos8的安装源下载下来，然后两台主机都安装mariadb mariadb-server

[root@computer ~]# yum -y install mariadb mariadb-server
[root@computer ~]# systemctl restart mariadb.service 
[root@computer ~]# systemctl enable --now mariadb.service 
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[root@computer ~]# ss -antl
State      Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN     0      50           *:3306                     *:*                                  
[root@computer ~]# mysql_secure_installation 


//从库
[root@slave yum.repos.d]# curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-8.repo
[root@slave yum.repos.d]# yum -y install mariadb mariadb-server
[root@slave yum.repos.d]# systemctl restart mariadb.service
[root@slave yum.repos.d]# systemctl enable --now mariadb.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[root@slave yum.repos.d]# ss -antl
State      Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN     0      50           *:3306                     *:*        
[root@slave yum.repos.d]# mysql_secure_installation

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

//主库
[root@master ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
log bin=mysql-bin
server_id=20



从库
[root@slave ~]# vim /etc/my.cnf
log_bin=mysql-bin        #高可用切换，最好开启该功能
server_id=30
[root@slave ~]# systemctl restart mariadb.service 

5、进入数据库配置主从

//主库对用户授权并允许从库连接
MariaDB [(none)]> grant all privileges  on *.* to root@'%' identified by "123";
Query OK, 0 rows affected (0.000 sec)

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

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.004 sec)



//从库允许任何网段连接
MariaDB [(none)]> grant all privileges  on *.* to root@'%' identified by "123";
Query OK, 0 rows affected (0.00 sec)

//从库设置要同步的主库信息，并开启同步
MariaDB [(none)]> change master to master_host='master',master_user='user',master_password='123';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 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

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

[root@slave yum.repos.d]# cat zabbix.repo 
[aliyun]
name=aliyun
baseurl=https://mirrors.aliyun.com/zabbix/zabbix/4.4/rhel/7/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/7/$basearch/
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-ZABBIX-A14FE591
[root@slave yum.repos.d]# yum -y install zabbix-agent
[root@slave yum.repos.d]# cd /etc/zabbix/
[root@slave zabbix]# ls
zabbix_agentd.conf  zabbix_agentd.d

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

vim /etc/zabbix/zabbix_agentd.conf
Server=192.168.47.137           //指定服务端ip
ServerActive=192.168.47.137
Hostname=slave
[root@slave zabbix]# systemctl restart zabbix-agent.service 
[root@slave zabbix]# systemctl enable --now zabbix-agent.service 
Created symlink from /etc/systemd/system/multi-user.target.wants/zabbix-agent.service to /usr/lib/systemd/system/zabbix-agent.service.

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

9、在slave主机上配置脚本

[root@slave script]# cd /etc/zabbix/
[root@slave zabbix]# ls
zabbix_agentd.conf  zabbix_agentd.d
[root@slave zabbix]# mkdir script
[root@slave zabbix]# cd script/
[root@slave script]# vim mysql_slave_status.sh
[root@slave script]# cat mysql_slave_status.sh 
#!/bin/bash
USER="root"
PASSWD="123"
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 zabbix.zabbix mysql_slave_status.sh 
[root@slave script]# ll
总用量 4
-rwxr-xr-x 1 zabbix zabbix 631 9月   7 16:31 mysql_slave_status.sh

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

[root@slave script]# cd /etc/zabbix/zabbix_agentd.d/
[root@slave zabbix_agentd.d]# ls
[root@slave zabbix_agentd.d]# vim 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/userparameter_mysql_slave.conf 
[root@slave zabbix_agentd.d]# ll
总用量 4
-rw-r--r-- 1 zabbix zabbix 73 9月   7 16:36 userparameter_mysql_slave.conf
[root@slave zabbix_agentd.d]# systemctl restart zabbix-agent.service

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

[root@server ~]# yum -y install zabbix-get
Last metadata expiration check: 0:22:40 ago on Wed 07 Sep 2022 04:15:48 PM CST.
Package zabbix-get-4.4.10-1.el8.x86_64 is already installed.
Dependencies resolved.
Nothing to do.
Complete!
[root@server ~]# zabbix_get -s 192.168.47.200 -k mysql.slave[sql]
0
[root@server ~]# zabbix_get -s 192.168.47.200 -k mysql.slave[io]
0

12、在zabbix web平台配置
新建监控项


新建触发器

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

mysql -u root -predhat -e "stop slave;"
[root@slave zabbix_agentd.d]# mysql -uroot -p123 -e"stop slave;"
[root@slave zabbix_agentd.d]# mysql -uroot -p123 -e"show slave status\G;"
*************************** 1. row ***************************
               Slave_IO_State: 
                  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: No
            Slave_SQL_Running: No

二、zabbix监控主从延迟

1、配置库脚本

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

#!/bin/bash     
delay=$(mysql -uroot -p123 -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_delay.sh 
[root@slave script]# chmod +x mysql_delay.sh 
[root@slave script]# ll
总用量 8
-rwxr-xr-x 1 zabbix zabbix 258 9月   7 17:37 mysql_delay.sh

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

[root@slave script]# vim /etc/zabbix/zabbix_agentd.d/userparameter_mysql_slave.conf 

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

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

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

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

4、在zabbix web平台操作
添加监控项