Zabbix监控之自定义监控
自定义监控
自定义监控MySQL主从状态
- 安装MySQL
- 配置主从并验证是否能成功同步
- 写脚本获取从库的状态，确保2个yes，如果不是两个也是就报错
- 配置/usr/local/etc/zabbix_agentd.conf 文件自定义监控
- 重启zabbix_agentd
- 在从库本地执行脚本看结果是否正常匹配
- 去zabbix_server 端用zabbix_get 名令手动获取下的状态并且确保状态与从库手动执行结果时一致
- 配置web 界面添加监控项与触发器
- 手动触发(把主库MySQL服务停掉) 并验证是否报警
监控是在从库上
自定义监控MySQL主从延迟
1.自定义监控MySQL主从状态
环境准备
| 主机 | ip | 主机名 | 系统版本 | MySQL版本 |
|---|---|---|---|---|
| zabbix_server服务端 | 192.168.229.145 | node1 | centos8/redhat8 | |
| 从库 | 192.168.229.130 | node2 | centos8/redhat8 | MySQL5.7 |
| 主库 | 192.168.229.150 | node3 | centos8/redhat8 | MySQL5.7 |
192.168.229.145的主机已经安装好zabbix_server服务端 http://t.csdn.cn/BPla7，在从库上安装zabbix_agentd客户端已安装好。
安装MySQL
从库
# 从库安装MySQL
[root@node2 ~]# cd /opt/
[root@node2 opt]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
# 解压
[root@node2 opt]# tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
# 重命名为MySQL
[root@node2 ~]# cd /usr/local/
[root@node2 local]# ls
bin etc games include lib lib64 libexec mysql-5.7.37-linux-glibc2.12-x86_64 sbin share src
[root@node2 local]# mv mysql-5.7.37-linux-glibc2.12-x86_64/ mysql
[root@node2 local]# ls
bin etc games include lib lib64 libexec mysql sbin share src
# 创建mysql系统用户，没有家目录，不需要登录
[root@node2 ~]# useradd -r -M -s /sbin/nologin mysql
[root@node2 ~]# id mysql
uid=995(mysql) gid=992(mysql) groups=992(mysql)
#修改目录/usr/local/mysql的属主属组
[root@node2 local]# chown -R mysql.mysql mysql
[root@node2 local]# ll mysql -d
drwxr-xr-x 9 mysql mysql 129 Jul 11 17:17 mysql
# 添加环境变量(你也可以直接vim 写到/etc/profile.d/mysql.sh下面)
[root@node2 ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@node2 ~]# source /etc/profile.d/mysql.sh
[root@node2 ~]# which mysqld
/usr/local/mysql/bin/mysqld
# 配置include
## 软链接到/usr/include/mysql，叫mysql
[root@node2 ~]# ln -s /usr/local/mysql/include /usr/include/mysql
# 配置 man 文档
[root@node2 ~]# vim /etc/man_db.conf
MANDATORY_MANPATH /usr/local/share/man
MANDATORY_MANPATH /usr/local/mysql/man # 添加这条
# 映射库文件
[root@node2 ~]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@node2 ~]# ldconfig # 让其生效
# 建立数据存放目录
[root@node2 ~]# mkdir /opt/data
# 设置所有者和所属组为mysql
[root@node2 ~]# chown -R mysql.mysql /opt/data/
[root@node2 ~]# ll /opt/data/ -d
drwxr-xr-x. 2 mysql mysql 6 Jul 12 17:33 /opt/data/
# 初始化数据库
[root@node2 ~]# mysqld --initialize --user=mysql --datadir=/opt/data/
.....省略
root@node2: 9g_My:#WAyy. # 临时密码
# 请注意，这个命令的最后会生成一个临时密码，此处密码是 9g_My:#WAyy.
# 再次注意，这个密码是随机的，你的不会跟我一样，一定要记住这个密码，因为一会登录时会用到
# 生成配置文件
[root@node2 ~]# 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 # 跳过名称解析，使用IP地址访问
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# 配置服务启动脚本
[root@node2 ~]# cd /usr/local/mysql/support-files/
[root@node2 support-files]# ls
magic mysqld_multi.server mysql-log-rotate mysql.server
# 复制到/etc/init.d/下面并叫MySQLd
[root@node2 support-files]# cp mysql.server /etc/init.d/mysqld
# 修改所有者和所属组
[root@node2 support-files]# chown -R mysql.mysql /etc/init.d/mysqld
# 启动
[root@node2 support-files]# service mysqld start
Starting MySQL.Logging to '/opt/data/node3.err'.
SUCCESS!
[root@node2 support-files]# ss -antl
State Recv-Q Send-Q Local Address:Port
LISTEN 0 128 0.0.0.0:22
LISTEN 0 128 0.0.0.0:1005
LISTEN 0 128 [::]:22
LISTEN 0 80 *:3306
[root@node2 support-files]#
# 使用临时密码登录并修改密码
[root@node2 ~]# mysql -uroot -p'9g_My:#WAyy.'
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 2
Server version: 5.7.37
Copyright (c) 2000, 2022, 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> set password = password('runtime123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye
# 使用临时密码登录
[root@node2 ~]# mysql -uroot -pruntime123!
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.37 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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>
主库
# 主库安装MySQL
[root@node3 ~]# cd /opt/
[root@node3 opt]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
# 解压
[root@node3 opt]# tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
# 重命名为MySQL
[root@node3 ~]# cd /usr/local/
[root@node3 local]# ls
bin etc games include lib lib64 libexec mysql-5.7.37-linux-glibc2.12-x86_64 sbin share src
[root@node3 local]# mv mysql-5.7.37-linux-glibc2.12-x86_64/ mysql
[root@node3 local]# ls
bin etc games include lib lib64 libexec mysql sbin share src
# 创建mysql系统用户，没有家目录，不需要登录
[root@node3 ~]# useradd -r -M -s /sbin/nologin mysql
[root@node3 ~]# id mysql
uid=995(mysql) gid=992(mysql) groups=992(mysql)
#修改目录/usr/local/mysql的属主属组
[root@node3 local]# chown -R mysql.mysql mysql
[root@node3 local]# ll mysql -d
drwxr-xr-x 9 mysql mysql 129 Jul 11 17:17 mysql
# 添加环境变量(你也可以直接vim 写到/etc/profile.d/mysql.sh下面)
[root@node3 ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@node3 ~]# source /etc/profile.d/mysql.sh
[root@node3 ~]# which mysqld
/usr/local/mysql/bin/mysqld
# 配置include
## 软链接到/usr/include/mysql，叫mysql
[root@node3 ~]# ln -s /usr/local/mysql/include /usr/include/mysql
# 配置 man 文档
[root@node3 ~]# vim /etc/man_db.conf
MANDATORY_MANPATH /usr/local/share/man
MANDATORY_MANPATH /usr/local/mysql/man # 添加这条
# 映射库文件
[root@node3 ~]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@node3 ~]# ldconfig # 让其生效
# 建立数据存放目录
[root@node3 ~]# mkdir /opt/data
# 设置所有者和所属组为mysql
[root@node3 ~]# chown -R mysql.mysql /opt/data/
[root@node3 ~]# ll /opt/data/ -d
drwxr-xr-x. 2 mysql mysql 6 Jul 12 17:33 /opt/data/
# 初始化数据库
[root@node3 ~]# mysqld --initialize --user=mysql --datadir=/opt/data/
.....省略
root@node3: uwA(pVBG&9aU # 临时密码
# 请注意，这个命令的最后会生成一个临时密码，此处密码是uwA(pVBG&9aU
# 再次注意，这个密码是随机的，你的不会跟我一样，一定要记住这个密码，因为一会登录时会用到
# 生成配置文件
[root@node3 ~]# 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 # 跳过名称解析，使用IP地址访问
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# 配置服务启动脚本
[root@node3 ~]# cd /usr/local/mysql/support-files/
[root@node3 support-files]# ls
magic mysqld_multi.server mysql-log-rotate mysql.server
# 复制到/etc/init.d/下面并叫MySQLd
[root@node3 support-files]# cp mysql.server /etc/init.d/mysqld
# 修改所有者和所属组
[root@node3 support-files]# chown -R mysql.mysql /etc/init.d/mysqld
# 启动
[root@node3 support-files]# service mysqld start
Starting MySQL.Logging to '/opt/data/node3.err'.
SUCCESS!
[root@node3 support-files]# ss -antl
State Recv-Q Send-Q Local Address:Por
LISTEN 0 128 0.0.0.0:22
LISTEN 0 80 *:330
LISTEN 0 128 [::]:22
[root@node3 support-files]#
# 使用临时密码登录并修改密码
[root@node3 ~]# mysql -uroot -p'uwA(pVBG&9aU'
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 2
Server version: 5.7.37
Copyright (c) 2000, 2022, 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> set password = password('runtime123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
# 使用临时密码登录
[root@node3 ~]# mysql -uroot -pruntime123!
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.37 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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>
1.1.1配置MySQL主从
注意：配置MySQL主从需要两边数据库一样。因为是刚创建的数据库所有两边的数据库一样。
如果主库有数据库从库没有数据库，把主库数据库全备。使用scp传到从库里再恢复到从库里面。
如果不清楚看我这篇文章： http://t.csdn.cn/SGcqn
1.1.2在主数据库里创建一个同步账号授权给从数据库使用
# 在MySQL版本5.7里在你授权给这个用户的时候，用户不存在会自动创建，但是在8.0里不行。
[root@node3 ~]# mysql -uroot -pruntime123!
# 授权
mysql> grant replication slave on *.* to 'tom'@'192.168.229.130' identified by
'tom123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
# 刷新授权表
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
1.1.3配置主数据库
# 配置/etc/my.cnf
[root@node3 ~]# 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
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
log-bin = mysql-bin # 启用binlog日志
server-id = 10 # 数据库服务器唯一标识符，主库的server-id值必须比从库的小
# 重启mysql服务
[root@node3 ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@node3 ~]# 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 80 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
[root@node3 ~]#
# 查看主库的状态
[root@node1 ~]# mysql -uroot -pruntime123! -e 'show master status'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
# 关闭防火墙和SElinux
[root@node3 ~]# systemctl disable --now firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@node3 ~]# vi /etc/selinux/config
[root@node3 ~]# head -7 /etc/selinux/config | tail -1
SELINUX=disabled
[root@node3 ~]# setenforce 0
1.1.4配置从数据库
[root@node2 ~]# 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
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
server-id = 20 # 设置从库的唯一标识符，从库的server-id值必须大于主库的该值
relay-log=mysql-relay-bin # 启用中继日志relay-log
# 这个名字没有要求，随便mysql-relay-bin
# 重启从库的mysql服务
[root@node2 ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@node2 ~]# 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:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 80 *:3306 *:*
[root@node2 ~]#
## 进去从库里配置主从
# 配置并启动主从复制
[root@node2 ~]# mysql -uroot -pruntime123!
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.229.150', # 主库ip
MASTER_USER='tom',
MASTER_PASSWORD='tom123!',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.33 sec)
# 启动主从
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
# 查看从服务器状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.229.150
Master_User: tom
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes # 此处必须为Yes
Slave_SQL_Running: Yes # 此处必须为Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
......省略N
1.1.5测试验证
# 在主库上创建school数据库，查看从库是否同步。
[root@node3 ~]# mysql -uroot -pruntime123! -e 'create database school';
[root@node3 ~]# mysql -uroot -pruntime123! -e 'show databases';
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
# 在从库查看
[root@node2 ~]# mysql -uroot -pruntime123! -e 'show databases';
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
[root@node2 ~]#
1.1.6配置/usr/local/etc/zabbix_agentd.conf 文件自定义监控
[root@node2 ~]# cd /usr/local/etc/
[root@node2 etc]# ls
zabbix_agentd.conf zabbix_agentd.conf.d
[root@node2 etc]# vim zabbix_agentd.conf
ated to implementation-specified maximum)
# Default: SOMAXCONN (hard-coded constant, depends on system)
# ListenBacklog=
UserParameter=check_process[*],/bin/bash /scripts/check_process.sh $1
UserParameter=check_logs[*],python3 /scripts/log.py $1 $2 $3
UserParameter=check_mysqlms,/bin/bash /scripts/mysqlms.sh # 添加
1.1.7重启zabbix_agentd
[root@node2 ~]# pkill zabbix_agentd
[root@node2 ~]# 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 [::]:22 [::]:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 *:80 *:*
[root@node2 ~]# zabbix_agentd
[root@node2 ~]# 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:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 *:80 *:*
[root@node2 ~]#
1.1.8在从库本地执行脚本看结果是否正常匹配
# 做这一步为了登录不需要密码，它会来这里找
[root@node2 ~]# vim ~/.my.cnf
[client]
user = root
password = runtime123!
# 创建脚本
[root@node2 ~]# cd /scripts/
[root@node2 scripts]# vim mysqlms.sh
#!/bin/bash
count=$(mysql -uroot -pruntime123! -e "show slave status\G" 2> /dev/null | grep -v grep | grep -c 'Yes')
if [ $count -ne 2 ];then
echo '1'
else
echo '0'
fi
[root@node2 scripts]#
# 给它执行权限
[root@node2 scripts]# chmod +x mysqlms.sh
# 执行是否打印输出0,
[root@node2 scripts]# ./mysqlms.sh
0
[root@node2 scripts]#
1.1.9去zabbix_server 端用zabbix_get 名令手动获取下的状态并且确保状态与从库手动执行结果时一致
[root@node1 ~]# zabbix_get -s 192.168.229.130 -k check_mysqlms
0
[root@node1 ~]#
1.2.1手动触发(把主库MySQL服务停掉) 并验证是否报警
[root@node3 ~]# service mysqld stop
Shutting down MySQL............ SUCCESS!
[root@node3 ~]# 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 [::]:22 [::]:*
[root@node3 ~]#
1.2.2配置web 界面添加监控项与触发器
创建监控项
创建触发器
已经报警
查看邮件
2.自定义监控MySQL主从延迟
2.1.1在从库本地执行脚本看结果是否正常匹配
# 启动
[root@node3 ~]# service mysqld start
Starting MySQL... SUCCESS!
[root@node2 ~]# cd /scripts/
[root@node2 scripts]# vim mysql_delay.sh
[root@node2 scripts]# chmod +x mysql_delay
[root@node2 scripts]# ls
check_process.sh log.py mysql_delay.sh mysqlms.sh
[root@node2 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
[root@node2 scripts]#
# 执行
[root@node2 scripts]# ./mysql_delay.sh
0
[root@node2 scripts]#
# 去zabbix服务端执行是否一致
2.1.2配置/usr/local/etc/zabbix_agentd.conf 文件自定义监控
[root@node2 ~]# vim /usr/local/etc/zabbix_agentd.conf
# Mandatory: no
# Range: 0 - INT_MAX (depends on system, too large values may be silently truncated toimplementation-specified maximum)
# Default: SOMAXCONN (hard-coded constant, depends on system)
# ListenBacklog=
UserParameter=check_process[*],/bin/bash /scripts/check_process.sh $1
UserParameter=check_logs[*],python3 /scripts/log.py $1 $2 $3
UserParameter=check_mysqlms,/bin/bash /scripts/mysqlms.sh
UserParameter=check_mysql_delay,/bin/bash /scripts/mysql_delay.sh # 添加
# 重启
[root@node2 ~]# pkill zabbix_agentd
[root@node2 ~]# zabbix_agentd
2.1.3去zabbix_server 端用zabbix_get 名令手动获取下的状态并且确保状态与从库手动执行结果时一致
[root@node1 ~]# zabbix_get -s 192.168.229.130 -k check_mysql_delay
0
[root@node1 ~]#
2.1.4配置web 界面添加监控项与触发器
创建监控项
建触发器
查看最新数据
邮箱查看
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
