zabbix自定义监控mysql主从状态和延迟及模板和用户管理
文章目录
监控mysql主从状态
配置mysql主从同步
//安装数据库
[root@master ~]# dnf install -y mariadb*
[root@jiang ~]# dnf install -y 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.
[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.001 sec)
MariaDB [(none)]> exit
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)]> exit
Bye
//在主库上创建同步账号
[root@master ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
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)]> grant replication slave on *.* to 'repl@192.168.205.147' identified by '123456';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> exit
Bye
//写配置文件
[root@master ~]# vim /etc/my.cnf.d/mariadb-server.cnf
log-bin = mysql_bin
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-log 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)
MariaDB [(none)]> exit
Bye
//关闭防火墙
[root@master ~]# systemctl disable --now firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@master ~]# setenforce 0
//配置从库
[root@jiang ~]# vim /etc/my.cnf.d/mariadb-server.cnf
erver-id = 20
relay-log = myrelay
[root@jiang ~]# systemctl restart mariadb
[root@jiang ~]# mysql -uroot -p123456
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)]> change master to
-> master_host='192.168.205.148',
-> master_user='repl',
-> master_password='123456',
-> master_log_file='mysql_bin.000001',
-> master_log_pos=328;
Query OK, 0 rows affected (0.005 sec)
MariaDB [(none)]> start slave
-> ;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> show slave status\G;
//测试验证主从是否同步
[root@master ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.28-MariaDB-log 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)]> create database george;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| george |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)
[root@jiang ~]# mysql -uroot -p123456
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| george |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
配置监控脚本
//在从库修改配置文件
[root@jiang ~]# vim /usr/local/etc/zabbix_agentd.conf
UserParameter=check_mysql_status,/bin/bash /scripts/mysql_status.sh //在末行加入此配置
//重启生效
[root@jiang ~]# pkill zabbix_agentd
[root@jiang ~]# zabbix_agentd
[root@jiang ~]# 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 80 0.0.0.0:3306 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
//编写脚本
[root@jiang ~]# vim /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@web ~]# chmod +x /scripts/mysql_status.sh
//在服务端检测key是否可用
[root@zabbix ~]# zabbix_get -s 192.168.205.147 -k check_mysql_status
0
添加监控项
添加触发器
手动触发告警
邮件发送完成
查看邮件
监控mysql主从延迟
配置监控脚本
//在从库添加配置
[root@jiang ~]# vim /usr/local/etc/zabbix_agentd.conf
UserParameter=check_mysqlyc,/bin/bash /scripts/mysqlyc.sh
//重启生效
[root@jiang ~]# pkill zabbix_agentd
[root@jiang ~]# zabbix_agentd
//编写脚本
[root@jiang ~]# vim /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@jiang ~]# chmod +x /scripts/mysqlyc.sh
//在服务端检测key是否可用
[root@zabbix ~]# zabbix_get -s 192.168.205.147 -k check_mysqlyc
0
添加监控项
添加触发器
等延迟达到200以上触发告警
用户管理
用户组
用户角色
用户
模板管理
模板组
模板
版权声明：本文为m0_72915775原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。