热门标签
热门文章
- 1kafka依赖_kafka的基本介绍、架构
- 2Big Data技术综述_big data 可以写综述吗
- 3Android 9.0自动更新 安装包解析错误 java.lang.SecurityException: Permission Denial_java.lang.securityexception: permission denial: re
- 4开发语言怎么选？ 别急！深度了解【2024年全球排行榜TOP20编程语言榜单排名的特点、难易程度、跨平台性、适用领域】是什么，能干嘛？重点不是语言的新旧，是否热门语言，而是找到适合自己的技术栈！_开发语言排名
- 5博科趣二维码在线生成网页源码
- 6Docker安全管理_docker 安全管理 security-howto
- 7STM32学习笔记【江科协】【7-1】ADC模数转换器_江协科技stm32笔记
- 8JavaScript Fetch_js fetch
- 9人工智能与气候变化：实现碳中和目标
- 10oracle两个字段模糊匹配_Oracle 模糊查询中不包括某字符串的实现方式
当前位置: article > 正文
自定义监控mysql主从状态与延迟_mysql 主从库落后的时间怎么监控
作者：码创造者 | 2024-08-10 22:16:41
赞
踩
mysql 主从库落后的时间怎么监控
自定义监控mysql主从状态
前提
| 主机名 | ip |
|---|---|
| dabao-服务端 | 192.168.140.142 |
| client-客户端（主） | 192.168.140.143 |
| secondary-客户端（从） | 192.168.140.144 |
首先在服务端配置好lamp和zabbix
[root@dabao ~]# ss -antl
[root@dabao ~]# 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 0.0.0.0:10051 0.0.0.0:*
LISTEN 0 128 127.0.0.1:9000 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 *:80 *:*
LISTEN 0 128 [::]:22 [::]:*
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
配置两台客户机 ls anaconda-ks.cfg zabbix-5.0.25.tar.gz tar xf zabbix-5.0.25.tar.gz useradd -r -M -s /sbin/nologin zabbix yum -y install gcc gcc-c++ vim wegt make yum -y install pcre-devel cd zabbix-5.0.25 ./configure --enable-agent make install systemctl disable --now firewalld setenforce 0 vim /etc/selinux/config cat /etc/selinux/config ... SELINUX=disabled ...
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
配置client [root@client ~]# cd /usr/local/etc/ [root@client etc]# ls zabbix_agentd.conf zabbix_agentd.conf.d [root@client etc]# vim zabbix_agentd.conf Server=192.168.140.142 #服务端ip ServerActive=192.168.140.142#服务端ip Hostname= 192.168.140.143#客户端ip [root@client ~]# zabbix_agentd [root@client ~]# yum -y install mysql [root@client ~]# systemctl start mysqld [root@client ~]# 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 *:3306 *:* LISTEN 0 128 [::]:22 [::]:* LISTEN 0 70 *:33060 *:* 配置secondary客户端 [root@secondary ~]# cd /usr/local/etc/ [root@secondary etc]# ls scripts zabbix_agentd.conf zabbix_agentd.conf.d [root@secondary etc]# vim zabbix_agentd.conf Server=192.168.140.142 #服务端ip ServerActive=192.168.140.142#服务端ip Hostname= 192.168.140.144#客户端ip [root@secondary etc]# zabbix_agentd [root@secondary etc]# yum -y install mysql [root@secondary etc]# systemctl restart mysqld [root@secondary etc]# 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 70 *:33060 *:* LISTEN 0 128 *:3306 *:* LISTEN 0 128 [::]:22 [::]:*
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
自定义监控mysql主从状态
1.安装mysql
上面已经安装了
设置密码
mysql
mysql> alter user 'root'@'localhost' identified by 'dabaozi123!';
Query OK, 0 rows affected (0.04 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec
- 1
- 2
- 3
- 4
- 5
- 6
2.配置主从并验证是否能成功同步
配置主库 [root@secondary ~]# cat /etc/my.cnf [mysqld] basedir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock port = 3306 skip-name-resolve server-id=10 log-bin=mysql_bin [root@client ~]# mysql -uroot mysql> create user 'repl'@'192.168.140.144' identified by 'repl123'; Query OK, 0 rows affected (0.01 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.140.144'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> ALTER USER 'repl'@'192.168.140.144' IDENTIFIED WITH mysql_native_password BY 'reppl123!'; Query OK, 0 rows affected (0.01 sec) root@client ~]# systemctl restart mysqld mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql_bin.000013| 156 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
配置从库 [root@secondary ~]# vi /etc/my.cnf [root@secondary ~]# cat /etc/my.cnf basedir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock port = 3306 skip-name-resolve server-id=20 relay-log=mysql-relay-bin [root@secondary ~]# systemctl restart mysqld [root@secondary ~]# mysql mysql> change master to -> master_host='192.168.140.143', -> master_user='repl', -> master_password='repl123', -> master_log_file='mysql_bin.000014', -> master_log_pos=1343; Query OK, 0 rows affected, 8 warnings (0.04 sec) (mysql> change master to master_host='192.168.140.143',master_user='repl',master_password='repl123',master_log_file='mysql_bin.000014',master_log_pos=1343;) mysql> start slave; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show slave status \G; mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.140.143 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin.000014 Read_Master_Log_Pos: 1343 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 324 Relay_Master_Log_File: mysql_bin.000014 Slave_IO_Running: Yes Slave_SQL_Running: Yes
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
3.写脚本获取从库的状态，确保2个YES，如果不是2个yes就报错
vi ~/.my.cnf cat ~/.my.cnf [client] user = root password = dabaozi123! [root@secondary ~]#mysql -uroot -e 'show slave status\G;' 2>/dev/null | grep "Running" | grep -c 'Yes' 2 [root@secondary ~]# cd /usr/local/etc/ [root@secondary etc]# pwd /usr/local/etc scripts zabbix_agentd.conf zabbix_agentd.conf.d [root@secondary etc]# cd scripts/ [root@secondary scripts]# vi mysqlms.sh [root@secondary scripts]# cat mysqlms.sh #!/bin/bash count=$(mysql -uroot -e 'show slave status\G;' 2>/dev/null | grep "Running" | grep -c 'Yes' ) if [ $count_slave -eq 2 ];then echo "0" else echo "1" fi [root@secondary scripts]# chmod +x mysqlms.sh
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
4.配置/usr/ local/etc/ zabbix_ agentd. conf文件自定义监控
[root@secondary scripts]# cd ..
[root@secondary etc]# ls
scripts zabbix_agentd.conf zabbix_agentd.conf.d
[root@secondary etc]# vim zabbix_agentd.conf
UnsafeUserParameters=1
UserParameter=check_mysqlms,/usr/bin/bash /usr/local/etc/scripts/mysqlms.sh
- 1
- 2
- 3
- 4
- 5
- 6
5.重启zabbix_ agentd
[root@secondary etc]# pkill zabbix
[root@secondary etc]# zabbix_agentd
- 1
- 2
6.在从库本地执行脚本看结果是否正常匹配
[root@secondary scripts]# ./mysqlms.sh
0
- 1
- 2
7.确保zabbix_ server端用zabbix_ get命令手动获取下状态并且确保状态与从库手动执行时一致
[root@dabao ~]# zabbix_get -s 192.168.140.144 -k check_mysqlms
0
- 1
- 2
8.配置web界面添加监控项与触发器
9.手动触发(把主库mysql服务停掉)并验证是否报警
[root@client ~]# systemctl stop mysqld
- 1
主从延迟
[root@secondary scripts]# pwd /usr/local/etc/scripts [root@secondary scripts]# vi database_delay.sh [root@secondary scripts]# cat database_delay.sh #!/bin/bash delay=$( mysql -uroot -pdabaozi123! -e 'show slave status\G;' 2>/dev/null | grep 'Seconds_Behind_Master' | awk -F ': '\+ '{print $2}' ) if [ $delay -eq 0 ];then echo "0" else echo "1" fi
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
[root@secondary scripts]# cd ..
[root@secondary etc]# vi zabbix_agentd.conf
添加
UserParameter=database_delay,/bin/bash /usr/local/etc/scripts/database_delay.sh
- 1
- 2
- 3
- 4
[root@secondary ~]# pkill zabbix
[root@secondary ~]# zabbix_agentd
- 1
- 2
[root@dabao ~]# zabbix_get -s 192.168.140.144 -k database_delay
0
- 1
- 2
- 3
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/码创造者/article/detail/960742
推荐阅读
相关标签
