zabbix自定义监控（主从、主从延迟）

1.mysql主从监控

主库授权

mysql> grant  replication slave  on *.* to 'csl'@'192.168.153.137' identified by '123' ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

//从库上测试
[root@localhost ~]# mysql -ucsl -p123 -h192.168.153.138
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> exit
Bye

修改配置文件

//修改主库配置文件
[root@localhost ~]# vim /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@localhost ~]# systemctl restart mysqld.service

//修改从库配置文件
[root@localhost ~]# vim /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 = my_relay

//重启服务
[root@localhost ~]# systemctl restart mysqld.service

开启主从服务

//在主库上查看主库信息
mysql>  show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


//在从库上配置主库信息
mysql> change master to
    -> master_host='192.168.153.138' ,
    -> master_user='csl' ,
    -> master_password='123' ,
    -> master_log_file=' mysql_bin.000003' ,
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave ;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.153.138
                  Master_User: csl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: my_relay.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000003
             Slave_IO_Running: 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

检查主从

//主库创建数据库
mysql> create database csl;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| csl                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


//从库查看
mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| csl                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> 

在zabbix agent端编写脚本

[root@localhost ~]# mkdir /scripts
[root@localhost ~]# cd /scripts/
[root@localhost scripts]# touch check_replication.sh
[root@localhost scripts]# chmod +x check_replication.sh
[root@localhost scripts]# vim check_replication.sh
#!/bin/bash
count=$(mysql --defaults-file=/scripts/.password  -e 'show slave  status\G'  | grep '_Running:' |grep -c 'Yes' )
if [ $count -ne 2  ];then
        echo  '1'
else
        echo  '0'
fi
[root@localhost ~]# vim /scripts/.password
[client]
user=zabbix
password=123


[root@localhost scripts]# ./check_replication.sh 
0

//授权登录的用户
[root@localhost scripts]# mysql -uroot -p123

mysql> grant select on *.* to 'zabbix'@'localhost' identified by  '123';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> grant SUPER, REPLICATION CLIENT  on *.* to 'zabbix'@'localhost' identified by '123';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

修改agent的配置文件

[root@localhost ~]# cd /usr/local/etc/
[root@localhost etc]# vim zabbix_agentd.conf
# Mandatory: no
# Range: 0-1
# Default:
UnsafeUserParameters=1     //取消注释，并改为1

UserParameter=check_replication,/scripts/check_replication.sh    //在文件末尾添加此行

//重启
[root@localhost ~]# zabbix_agentd


//在server端检查key是否可用
[root@localhost ~]# zabbix_get  -s 192.168.153.137 -k check_replication 
0

创建监控项

添加触发器

触发报警

//从库触发报警
[root@localhost ~]# mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.7.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> 



//zabbix server端 查看键值
[root@localhost ~]# zabbix_get  -s 192.168.153.137 -k check_replication 
1

查看邮件

2.mysql主从延迟

agent端写脚本

//写主从延迟脚本
[root@localhost scripts]# vim check_replication_delay.sh
#!/bin/bash
  
delay_count=$(mysql --defaults-file=/scripts/.password  -e 'show slave status\G'| grep 'Behind' | awk '{print $2}')
if [ "$delay_count" != NULL ];then
        echo $delay_count
else
        echo 0
fi
[root@localhost scripts]# chmod +x check_replication_delay.sh
[root@localhost scripts]# ./check_replication_delay.sh 
0

agent端修改配置文件

//
[root@localhost scripts]# vim /usr/local/etc/zabbix_agentd.conf
UserParameter=check_replication_delay,/scripts/check_replication_delay.sh   //添加此行


[root@localhost scripts]# pkill zabbix
[root@localhost scripts]# zabbix_agentd 

服务端检查键值

[root@localhost ~]# zabbix_get  -s 192.168.153.137 -k check_replication_delay
0

创建监控项

添加触发器

效果