zabbix自定义监控Mysql主从状态和延迟

zabbix自定义监控Mysql主从状态和延迟
- 自定义监控Mysql主从状态
- 自定义监控Mysql延迟

自定义监控Mysql主从状态

主从同步

监控mysql主从状态监控的是从库，所以只用在从库是安装并启动zabbix_aength

以下启动安装可参考

zabbix介绍与部署

这里实验使用的yum安装的mariadb，实战中请2进制安装mysql

mysql主从同步可参考

mysql主从介绍与配置

主库配置

#给主库改一个名字
[root@localhost ~]# hostnamectl  set-hostname master
[root@localhost ~]# bash

#这里实验使用yum安装mariadb，并设置开机自启立即启动
[root@master ~]# dnf -y install mariadb*
[root@master ~]# systemctl enable --now 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.

#更改mariadb数据库密码
[root@master ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.28-MariaDB 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)]> set password = password('123456');
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> quit
Bye
[root@master ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB 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)]> 

#在主库上创建同步账号
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.118.136' identified by '123456';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> quit
Bye

#编写主库配置文件
[root@master ~]# vim /etc/my.cnf.d/mariadb-server.cnf 
...
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid

log-bin = mysql_bin   //启用binlog日志
server-id=10          //数据库服务器唯一标识符，此处需要小于从库
...
[root@master ~]# systemctl restart mariadb

#查看主库状态
[root@master ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB 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)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

从库配置

#从库中安装并启动zabbix_agent
[root@136 ~]# ss -antl
State   Recv-Q  Send-Q   Local Address:Port      Peer Address:Port  Process  
LISTEN  0       128            0.0.0.0:22             0.0.0.0:*              
LISTEN  0       128            0.0.0.0:10050          0.0.0.0:*   

#这里实验使用yum安装mariadb，并设置开机自启立即启动
[root@136 ~]# dnf -y install mariadb*
[root@136 ~]# systemctl enable --now 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.

#更改mariadb数据库密码
[root@136 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.28-MariaDB 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)]> set password = password('123456');
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> quit
Bye
[root@136 ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB 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)]> 

#编写从库配置文件
[root@136 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
...
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid

relay-log = myrelay
server-id = 20
...
[root@136 ~]# systemctl restart mariadb

#配置主从
[root@136 ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB 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)]> change master to
    -> master_host='192.168.118.130',
    -> master_user='repl',
    -> master_password='123456',
    -> master_log_file='mysql_bin.000001',
    -> master_log_pos=328;
Query OK, 0 rows affected (0.003 sec)

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

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.118.130
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql_bin.000001
           Read_Master_Log_Pos: 328
                Relay_Log_File: myrelay.000002
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql_bin.000001
              Slave_IO_Running: Yes         //此处必须为yes  
             Slave_SQL_Running: Yes         //此处必须为yes
               Replicate_Do_DB: 
...

测试验证

在主数据库中创建一个runtime数据库

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)

MariaDB [(none)]> create database runtime;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
+--------------------+
4 rows in set (0.000 sec)

在从库中查看，这里也多了一个runtime数据库，验证成功。

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
+--------------------+
4 rows in set (0.000 sec)

配置监控脚本

从库配置文件与编写脚本

#编写脚本
[root@136 ~]# vim /etc/scripts/mysql_status.sh
#!/bin/bash
yes=`mysql -uroot -p123456 -e " show slave status\G" 2> /dev/null |grep "Running:"|grep -c "Yes"`
if [ $yes == 2  ]; then
        echo "0"
else    
        echo "1"
fi 
[root@136 ~]# chmod +x /etc/scripts/mysql_status.sh

#在从库修改配置文件
[root@136 ~]# vim /usr/local/etc/zabbix_agentd.conf
...
UserParameter=check_mysql_status,/bin/bash /etc/scripts/mysql_status.sh

#重启服务生效配置
[root@136 ~]# pkill zabbix_agentd 
[root@136 ~]# zabbix_agentd

服务端检查key是否可以使用

[root@zabbix ~]# zabbix_get -s 192.168.118.136 -k check_mysql_status
0

添加监控项

添加触发器

手动触发报警，查看报警信息

#关闭主从
[root@136 ~]# mysql -uroot -p123456 -e "stop slave;"

自定义监控Mysql延迟

配置监控脚本

在从库配置文件和编写脚本

#刚刚做mysql主从监控关闭了mysql主从，记得开启
[root@136 ~]# mysql -uroot -p123456 -e "start slave;"

#编写脚本
[root@136 ~]# vim /etc/scripts/mysqlyc.sh
#!/bin/bash
yc=`mysql -uroot -p123456 -e "show slave status\G" 2> /dev/null |awk '/Seconds_Behind_Master/ {print $2}' `
echo $yc
[root@136 ~]# chmod +x /etc/scripts/mysqlyc.sh 

#修改配置文件
[root@136 ~]# vim /usr/local/etc/zabbix_agentd.conf
...
UserParameter=check_mysqlyc,/bin/bash /etc/scripts/mysqlyc.sh

#重启服务生效配置
[root@136 ~]# pkill zabbix_agentd 
[root@136 ~]# zabbix_agentd

服务端测试key

[root@zabbix ~]# zabbix_get -s 192.168.118.136 -k check_mysqlyc
0

添加监控项

添加触发器

查看报警信息

因为是本地主机之前，延迟不可能达到200以上，所以我把触发信息改为=0时触发报警了。

posted on 2022-09-07 22:57 linux-ada 阅读(144) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Clannaddada

导航

公告

zabbix自定义监控Mysql主从状态和延迟

zabbix自定义监控Mysql主从状态和延迟

自定义监控Mysql主从状态

主从同步

主库配置

从库配置

测试验证

配置监控脚本

添加监控项

添加触发器

手动触发报警，查看报警信息

自定义监控Mysql延迟

配置监控脚本

添加监控项

添加触发器

查看报警信息