devbox
盐析白兔
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

自定义监控mysql主从状态与主从延迟_mysql active: inactive (dead)

作者：盐析白兔 | 2024-08-10 22:08:14

赞

踩

mysql active: inactive (dead)

一.准备工作

环境部署：

server：ip——192.168.80.128——服务器

agent：ip——192.168.80.130——客户端，从库

master：ip——192.168.80.129——主库

前提：

server主机安装 zabbix_server zabbix_agentd   
agent主机安装zabbix_agent  
在master和agent安装mysql  

  1.  
  2. //关闭主从端的防火墙和selinux
  3. [root@master ~]# systemctl status firewalld.service 
  4. ● firewalld.service - firewalld - dynamic firewall daemon
  5.    Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
  6.    Active: inactive (dead)
  7.      Docs: man:firewalld(1)
  8.  
  9.  
  10. [root@agent ~]# systemctl status firewalld.service 
  11. ● firewalld.service - firewalld - dynamic firewall daemon
  12.    Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
  13.    Active: inactive (dead) since Thu 2022-07-08 8:04:09 CST; 4 days ago
  14.      Docs: man:firewalld(1)
  15.  Main PID: 978 (code=exited, status=0/SUCCESS)
  16.

1.搭建mysql主从

配置主库

  1. //在主库创建用户repl授权给从库使用
  2. mysql> CREATE USER 'repl'@'192.168.80.130' IDENTIFIED BY 'repl123';
  3. Query OK, 0 rows affected (0.00 sec)
  4.  
  5. mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.80.130';
  6. Query OK, 0 rows affected (0.00 sec)
  7.  
  8. mysql> FLUSH PRIVILEGES;
  9. Query OK, 0 rows affected (0.00 sec)

配置主库文件

  1. [root@master ~]# cat /etc/my.cnf 
  2. [mysqld]
  3. basedir = /usr/local/mysql
  4. datadir = /opt/data
  5. socket = /tmp/mysql.sock
  6. port = 3306
  7. pid-file = /opt/data/mysql.pid
  8. user = mysql
  9. skip-name-resolve
  10.  
  11. server-id=10 
  12. log-bin=mysql_bin
  13.  
  14. [root@master ~]# systemctl restart mysqld.service
  15.  
  16. mysql> show master status;  //里面的参数后续会用到
  17. +------------------+----------+--------------+------------------+-------------------+
  18. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  19. +------------------+----------+--------------+------------------+-------------------+
  20. | mysql_bin.000001 |      154 |              |                  |                   |
  21. +------------------+----------+--------------+------------------+-------------------+
  22. 1 row in set (0.00 sec)
  23.  
  24.

配置从库

  1. //配置从库文件
  2. [root@agent ~]# cat /etc/my.cnf 
  3. [mysqld]
  4. basedir = /usr/local/mysql
  5. datadir = /opt/data
  6. socket = /tmp/mysql.sock
  7. port = 3306
  8. pid-file = /opt/data/mysql.pid
  9. user = mysql
  10. skip-name-resolve
  11.  
  12. server-id=20
  13. relay-log=mysql-relay-bin
  14.  
  15. [root@agent ~]# systemctl restart mysqld.service 
  16.  
  17.  
  18.  
  19. //连接到从数据库配置主从
  20. mysql> CHANGE MASTER TO
  21.     -> MASTER_HOST= '192.168.80.129',
  22.     -> MASTER_USER='repl',
  23.     -> MASTER_PASSWORD='repl123',
  24.     ->  MASTER_LOG_FILE='mysql_bin.000001',
  25.     -> MASTER_LOG_POS=154;
  26. Query OK, 0 rows affected, 2 warnings (0.03 sec)
  27.  
  28. mysql> start slave;   //启动主从
  29.  
  30. mysql> show slave status \G
  31. *************************** 1. row ***************************
  32.                Slave_IO_State: Waiting for master to send event
  33.                   Master_Host: 192.168.80.129
  34.                   Master_User: repl
  35.                   Master_Port: 3306
  36.                 Connect_Retry: 60
  37.               Master_Log_File: mysql_bin.000001
  38.           Read_Master_Log_Pos: 154
  39.                Relay_Log_File: mysql-relay-bin.000003
  40.                 Relay_Log_Pos: 320
  41.         Relay_Master_Log_File: mysql_bin.000001
  42.              Slave_IO_Running: Yes  //此处为双yes 就是成功开启了
  43.             Slave_SQL_Running: Yes
  44.               Replicate_Do_DB: 
  45.           Replicate_Ignore_DB: 
  46.            Replicate_Do_Table: 
  47.        Replicate_Ignore_Table: 
  48.       Replicate_Wild_Do_Table: 
  49.   Replicate_Wild_Ignore_Table: 
  50.                    Last_Errno: 0
  51.                    Last_Error: 
  52.                  Skip_Counter: 0
  53.           Exec_Master_Log_Pos: 154
  54.               Relay_Log_Space: 693
  55.               Until_Condition: None
  56.                Until_Log_File: 
  57.                 Until_Log_Pos: 0
  58.            Master_SSL_Allowed: No
  59.            Master_SSL_CA_File: 
  60.            Master_SSL_CA_Path: 
  61.               Master_SSL_Cert: 
  62.             Master_SSL_Cipher: 
  63.                Master_SSL_Key: 
  64.         Seconds_Behind_Master: 0
  65. Master_SSL_Verify_Server_Cert: No
  66.                 Last_IO_Errno: 0
  67.                 Last_IO_Error: 
  68.                Last_SQL_Errno: 0
  69.                Last_SQL_Error: 
  70.   Replicate_Ignore_Server_Ids: 
  71.              Master_Server_Id: 10
  72.                   Master_UUID: 32748ca6-00f6-11ed-a5ea-000c29972037
  73.              Master_Info_File: /opt/data/master.info
  74.                     SQL_Delay: 0
  75.           SQL_Remaining_Delay: NULL
  76.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  77.            Master_Retry_Count: 86400
  78.                   Master_Bind: 
  79.       Last_IO_Error_Timestamp: 
  80.      Last_SQL_Error_Timestamp: 
  81.                Master_SSL_Crl: 
  82.            Master_SSL_Crlpath: 
  83.            Retrieved_Gtid_Set: 
  84.             Executed_Gtid_Set: 
  85.                 Auto_Position: 0
  86.          Replicate_Rewrite_DB: 
  87.                  Channel_Name: 
  88.            Master_TLS_Version: 
  89. 1 row in set (0.00 sec)
  90.  
  91.

测试

在主库创建一个数据库到从库查看，是否同步

  1. [root@master ~]# mysql -uroot -p'runtime123!' -e  'create database abc;'
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. [root@master ~]# mysql -uroot -p'runtime123!' -e  'show databases;'
  4. mysql: [Warning] Using a password on the command line interface can be insecure.
  5. +--------------------+
  6. | Database           |
  7. +--------------------+
  8. | information_schema |
  9. | abc                |
  10. | mysql              |
  11. | performance_schema |
  12. | sys                |
  13. | test               |
  14. +--------------------+

从库查看

  1. [root@agent ~]# mysql -uroot -p'runtime123!' -e 'show databases;'
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. +--------------------+
  4. | Database           |
  5. +--------------------+
  6. | information_schema |
  7. | abc                |
  8. | mysql              |
  9. | performance_schema |
  10. | sys                |
  11. | test               |
  12. +--------------------+

二.自定义监控mysql主从状态

1.编写脚本

因为正常状态为两个yes，所以将两个yes的信息过滤并且做判断即可

  1. [root@agent scropts]# cat mysqlms.sh 
  2. #!/bin/bash
  3.  
  4. count=$( mysql -uroot -pruntime123! -e "show slave status\G" 2>/dev/null | grep -v grep | grep -c 'Yes')
  5.  
  6. if [ $count -ne 2 ];then   //当count不等于2打印1 反之打印0
  7.     echo '1'
  8. else
  9.     echo '0'
  10. fi
  11.  
  12.  
  13. ps：
  14.  
  15.  2>dev/null 去掉告警信息  
  16.  grep -v grep 将grep本身的进程去掉
  17.  grep -c 'Yes'  //将匹配yes的行数取出来
  18.  
  19.

2.测试脚本

  1. [root@agent scropts]# chmod +x mysqlms.sh   //赋予脚本执行权限
  2. [root@agent scropts]# ll
  3. 总用量 12
  4. -rwxr-xr-x. 1 root root  124 7月  10 21:34 check_process.sh
  5. -rwxr-xr-x. 1 root root 1854 7月  10 22:15 log.py
  6. -rwxr-xr-x. 1 root root  178 7月  11 17:40 mysqlms.sh
  7.  
  8. [root@agent scropts]# bash mysqlms.sh    //打印0 因为主从没问题所以是正常的
  9. 0
  10. [root@agent scropts]#

3.修改配置文件

  1. [root@agent ~]# vim zabbix_agentd.conf
  2. UserParameter=check_mysqlms,/bin/bash /scropts/mysqlms.sh   //添加自定义监控
  3.  
  4. [root@agent ~]# pkill zabbix_agentd  
  5. [root@agent ~]# zabbix_agentd   //杀死进程 重新启动让配置文件生效
  6.  
  7.  
  8. //服务器测试
  9. [root@server ~]# zabbix_get -s 192.168.80.130 -k check_mysqlms
  10. 0

4.zabbix网页配置

添加监控项

添加触发器

当监控项的结果为1时就触发报警

测试

打开主库防火墙

  1. [root@master ~]# systemctl start firewalld.service 
  2. [root@master ~]# systemctl status firewalld.service 
  3. ● firewalld.service - firewalld - dynamic firewall daemon
  4.    Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
  5.    Active: active (running) since Mon 2022-07-11 06:10:40 EDT; 32s ago
  6.      Docs: man:firewalld(1)
  7.  Main PID: 258109 (firewalld)
  8.     Tasks: 2 (limit: 11160)
  9.    Memory: 28.8M
  10.    CGroup: /system.slice/firewalld.service
  11.            └─258109 /usr/libexec/platform-python -s /usr/sbin/firewalld --nofork --nopid
  12.  
  13.  
  14.  
  15.  
  16. //连接到从库
  17. [root@agent ~]# mysql -u root -p'runtime123!'   //进到数据库 重启主从 让其刷新
  18. mysql> stop slave;
  19. Query OK, 0 rows affected (0.00 sec)
  20.  
  21. mysql> start slave;
  22. Query OK, 0 rows affected (0.01 sec)
  23.

查看此时最新数据

数字打印为1，并且触发报警

 三.自定义监控mysql主从延迟

查看mysql主从延迟

  1. mysql> show slave status \G
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for master to send event
  4.                   Master_Host: 192.168.80.129
  5.                   Master_User: repl
  6.                   Master_Port: 3306
  7.                 Connect_Retry: 60
  8.               Master_Log_File: mysql_bin.000001
  9.           Read_Master_Log_Pos: 469
  10.                Relay_Log_File: mysql-relay-bin.000007
  11.                 Relay_Log_Pos: 320
  12.         Relay_Master_Log_File: mysql_bin.000001
  13.              Slave_IO_Running: Yes
  14.             Slave_SQL_Running: Yes
  15.               Replicate_Do_DB: 
  16.           Replicate_Ignore_DB: 
  17.            Replicate_Do_Table: 
  18.        Replicate_Ignore_Table: 
  19.       Replicate_Wild_Do_Table: 
  20.   Replicate_Wild_Ignore_Table: 
  21.                    Last_Errno: 0
  22.                    Last_Error: 
  23.                  Skip_Counter: 0
  24.           Exec_Master_Log_Pos: 469
  25.               Relay_Log_Space: 693
  26.               Until_Condition: None
  27.                Until_Log_File: 
  28.                 Until_Log_Pos: 0
  29.            Master_SSL_Allowed: No
  30.            Master_SSL_CA_File: 
  31.            Master_SSL_CA_Path: 
  32.               Master_SSL_Cert: 
  33.             Master_SSL_Cipher: 
  34.                Master_SSL_Key: 
  35.         Seconds_Behind_Master: 0   //取出这个值 这个是mysql主从延迟
  36. Master_SSL_Verify_Server_Cert: No
  37.                 Last_IO_Errno: 0
  38.                 Last_IO_Error: 
  39.                Last_SQL_Errno: 0
  40.                Last_SQL_Error: 
  41.   Replicate_Ignore_Server_Ids: 
  42.              Master_Server_Id: 10
  43.                   Master_UUID: 32748ca6-00f6-11ed-a5ea-000c29972037
  44.              Master_Info_File: /opt/data/master.info
  45.                     SQL_Delay: 0
  46.           SQL_Remaining_Delay: NULL
  47.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  48.            Master_Retry_Count: 86400
  49.                   Master_Bind: 
  50.       Last_IO_Error_Timestamp: 
  51.      Last_SQL_Error_Timestamp: 
  52.                Master_SSL_Crl: 
  53.            Master_SSL_Crlpath: 
  54.            Retrieved_Gtid_Set: 
  55.             Executed_Gtid_Set: 
  56.                 Auto_Position: 0
  57.          Replicate_Rewrite_DB: 
  58.                  Channel_Name: 
  59.            Master_TLS_Version: 
  60. 1 row in set (0.00 sec)

1.编写脚本

  1. [root@agent scropts]# cat mysql_delay.sh 
  2. #!/bin/bash
  3.  
  4. delay=$( mysql -uroot -pruntime123! -e "show slave status\G" 2>/dev/null | grep 'Seconds_Behind_Master'| awk '{print $2}')
  5.  
  6. echo $delay

测试脚本

  1. [root@agent scropts]# chmod +x mysql_delay.sh 
  2. [root@agent scropts]# ll
  3. 总用量 16
  4. -rwxr-xr-x. 1 root root  124 7月   9 19:34 check_process.sh
  5. -rwxr-xr-x. 1 root root 1854 7月   9 22:15 log.py
  6. -rwxr-xr-x. 1 root root  151 7月  11 18:36 mysql_delay.sh
  7. -rwxr-xr-x. 1 root root  178 7月  11 17:40 mysqlms.sh
  8.  
  9. [root@agent scropts]# bash mysql_delay.sh   //因为是在虚拟机中所以延迟为0
  10. 0

修改配置文件

  1. [root@agent etc]# cd /usr/local/etc/
  2. [root@agent etc]# cat zabbix_agentd.conf
  3. UserParameter=mysql_delay,/bin/bash /scropts/mysql_delay.sh
  4.  
  5. [root@agent etc]# pkill zabbix_agentd   //杀掉进程让文件生效
  6. [root@agent etc]# zabbix_agentd

服务端测试

  1. [root@server ~]# zabbix_get -s 192.168.80.128 -k mysql_delay
  2. 0

2.zabbix网页配置

添加监控项

添加触发器

 测试

因为虚拟机环境内延迟是0，所以直接触发报警

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/盐析白兔/article/detail/960696
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号