自定义监控mysql主从状态与主从延迟

一、自定义监控主从状态

监控服务端	server——192.168.31.129
监控客户端（主）	192.168.31.132
监控客户端（从）	agentd——192.168.31.131

//服务端
[root@zabbix_server ~]# ss -antl
State  Recv-Q Send-Q Local Address:Port   Peer Address:Port Proces
LISTEN 0      128          0.0.0.0:10050       0.0.0.0:*          
LISTEN 0      128          0.0.0.0:10051       0.0.0.0:*          
LISTEN 0      128        127.0.0.1:9000        0.0.0.0:*          
LISTEN 0      128          0.0.0.0:22          0.0.0.0:*          
LISTEN 0      100        127.0.0.1:25          0.0.0.0:*          
LISTEN 0      80                 *:3306              *:*          
LISTEN 0      128                *:80                *:*          
LISTEN 0      128             [::]:22             [::]:*          
LISTEN 0      100            [::1]:25             [::]:*     

//客户端从
[root@localhost ~]# ss -antl
State  Recv-Q Send-Q Local Address:Port  Peer Address:PortProcess
LISTEN 0      128          0.0.0.0:10050      0.0.0.0:*          
LISTEN 0      128          0.0.0.0:111        0.0.0.0:*          
LISTEN 0      128          0.0.0.0:22         0.0.0.0:*          
LISTEN 0      5          127.0.0.1:631        0.0.0.0:*          
LISTEN 0      128        127.0.0.1:6010       0.0.0.0:*          
LISTEN 0      128             [::]:111           [::]:*          
LISTEN 0      128             [::]:22            [::]:*          
LISTEN 0      5              [::1]:631           [::]:*          
LISTEN 0      128            [::1]:6010          [::]:*          
[root@localhost ~]# hostnamectl set-hostname slave
[root@localhost ~]# bash
[root@slave ~]# 


//客户端主
[root@localhost ~]# hostnamectl set-hostname master
[root@localhost ~]# bash
[root@master ~]# 

 

配置yum源

[root@slave ~]# cd /etc/yum.repos.d/
[root@slave yum.repos.d]# ls
CentOS-Base.repo  redhat.repo
[root@slave yum.repos.d]# scp 
::1:                      localhost6:
CentOS-Base.repo          localhost6.localdomain6:
localhost:                localhost.localdomain:
localhost4:               redhat.repo 
localhost4.localdomain4:  
[root@slave yum.repos.d]# scp CentOS-Base.repo 192.168.31.132:/etc/yum.repos.d/
The authenticity of host '192.168.31.132 (192.168.31.132)' can't be established.
ECDSA key fingerprint is SHA256:alPF6ShX7CLyTLqV9g6EV+jEmvFnpMQ8Xe8CCsSNDwI.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.31.132' (ECDSA) to the list of known hosts.
root@192.168.31.132's password: 
CentOS-Base.repo               100% 1653     1.5MB/s   00:00    

[root@master ~]# cd /etc/yum.repos.d/
[root@master yum.repos.d]# ls
[root@master yum.repos.d]# ls
CentOS-Base.repo
[root@master yum.repos.d]# 

mysql配置

1.安装二进制mysql（在主master从slave上）

2.搭建主从

#配置主库
//在主库创建用户repl授权给从库使用
mysql> CREATE USER 'repl'@'192.168.31.131' IDENTIFIED BY 'repl123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.31.131';
Query OK, 0 rows affected (0.00 sec)      //从库的ip

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

//配置主库文件
[root@master ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id=10 
log-bin=mysql_bin

[root@master ~]# systemctl restart mysqld.service

mysql> show master status;  //参数，后续会用到
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


#配置从库
//配置从库文件
[root@slave ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id=20
relay-log=mysql-relay-bin

[root@slave ~]# systemctl restart mysqld.service 

//连接到从数据库配置主从
mysql> CHANGE MASTER TO
    -> MASTER_HOST= '192.168.31.132',     //主库ip
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl123',
    ->  MASTER_LOG_FILE='mysql_bin.000001',
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;   //启动主从

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.31.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes  //此处为2个yes 就是成功开启了
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 693
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: 32748ca6-00f6-11ed-a5ea-000c29972037
             Master_Info_File: /opt/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

 3.测试

//主库创建名为abc的数据库
[root@master ~]# mysql -uroot -p'runtime123!' -e  'create database abc;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@master ~]# mysql -uroot -p'runtime123!' -e  'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

//从库查看
[root@slave ~]# mysql -uroot -p'runtime123!' -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

 

监控主从状态配置

1.编写脚本

[root@slave ~]# ls /
bin   dev  home  lib64  mnt  proc  run   scripts  sys  usr
boot  etc  lib   media  opt  root  sbin  srv      tmp  var
[root@slave ~]# cd /scripts/
[root@slave scripts]# ls
check_process.sh  log.py
[root@slave scripts]# vim mysqlms.sh
[root@slave scripts]# cat mysqlms.sh
#!/bin/bash

count=$(mysql -uroot -e "show slave status\G | grep -v grep | grep -c 'Yes'")

if [ $count -ne 2 ];then
    echo '1'    //不等于2就报错              //  2>dev/null 去掉告警信息 
else                                         // grep -v grep 去掉grep本身的进程
    echo '0'     //等于2，就没问题，打印0        // grep -c 'Yes'  //取出匹配yes的行数
fi
[root@slave scripts]# ls
check_process.sh  log.py  mysqlms.sh
 

[root@slave scripts]# vim ~/.my.cnf    //隐藏文件
[root@slave scripts]# cat ~/.my.cnf
[client]
user = root
password = runtime123!

2.测试脚本

//给执行权限
[root@slave scripts]# chmod +x mysqlms.sh   
[root@slave scripts]# ll
总用量 12
-rwxr-xr-x. 1 root root  124 7月   9 11:17 check_process.sh
-rwxr-xr-x. 1 root root 1854 7月   9 16:15 log.py
-rwxr-xr-x. 1 root root  178 7月  11 17:40 mysqlms.sh

[root@slave scripts]# bash mysqlms.sh    //打印0 因为主从没问题所以是正常的
0
 

//配置文件zabbix_agentd.conf
[root@slave ~]# vim zabbix_agentd.conf
UserParameter=check_mysqlms,/bin/bash /scripts/mysqlms.sh   //添加自定义监控
[root@slave ~]# pkill zabbix_agentd  
[root@slave ~]# zabbix_agentd   //重新启动让其生效


//服务器测试
[root@zabbix_server ~]# zabbix_get -s 192.168.31.131 -k check_mysqlms
0

zabbix网页监控配置

1.添加监控项

2. 添加触发器

 3.测试

 

打开主库防火墙

[root@master ~]# systemctl start firewalld.service 
[root@master ~]# systemctl status firewalld.service 
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: active (running) since Mon 2022-07-11 06:10:40 EDT; 32s ago
     Docs: man:firewalld(1)
 Main PID: 258109 (firewalld)
    Tasks: 2 (limit: 11160)
   Memory: 28.8M
   CGroup: /system.slice/firewalld.service
           └─258109 /usr/libexec/platform-python -s /usr/sbin/firewalld --nofork --nopid




//连接到从库
[root@slave ~]# mysql -u root -p'runtime123!'   //进入mysql
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)      //重启主从 让其刷新

 

二、自定义监控mysql主从延迟

1.编写脚本

[root@slave scripts]# cat mysql_delay.sh 
#!/bin/bash

delay=$( mysql -uroot -pruntime123! -e "show slave status\G" 2>/dev/null | grep 'Seconds_Behind_Master'| awk '{print $2}')

echo $delay

2.测试

[root@slave scripts]# chmod +x mysql_delay.sh 
[root@slave scripts]# ll
总用量 16
-rwxr-xr-x. 1 root root  124 7月   9 11:17 check_process.sh
-rwxr-xr-x. 1 root root 1854 7月   9 16:15 log.py
-rwxr-xr-x. 1 root root  151 7月  11 18:30 mysql_delay.sh
-rwxr-xr-x. 1 root root  178 7月  11 17:40 mysqlms.sh

[root@slave scripts]# bash mysql_delay.sh   //因为是在虚拟机中所以延迟为0
0

3.修改配置文件

[root@slave etc]# cd /usr/local/etc/
[root@slave etc]# cat zabbix_agentd.conf
UserParameter=mysql_delay,/bin/bash /scropts/mysql_delay.sh
[root@slave etc]# pkill zabbix_agentd   //让文件生效
[root@slave etc]# zabbix_agentd 

4.测试（服务端上）

[root@zabbix_server ~]# zabbix_get -s 192.168.31.131 -k mysql_delay
0

zabbix网页配置

1.添加监控项

 2.添加触发器

 3.测试

虚拟机环境内延迟为0 ，直接报警
触发器的{ITEM.VALUE}为显示延迟数