自定义监控MySQL主从状态及主从延迟

头发保卫者

已于 2022-07-12 08:32:16 修改

阅读量530

点赞数

文章标签： mysql 数据库服务器

于 2022-07-11 20:27:35 首次发布

本文链接：https://blog.csdn.net/qq_52943068/article/details/125720496

版权

文章目录

1.配置主从

需求：
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

环境说明：

数据库角色	IP	应用与系统版本	有无数据
主数据库	192.168.106.19	centos8/redhat8mysql-5.7	无数据
从数据库	192.168.106.17	centos8/redhat8mysql-5.7	无数据

安装MySQL，我这里使用的二进制安装，可以查看《二进制安装MySQL以及密码破解》

1.2.修改MySQL密码

//主数据库
mysql> set password = password('Runtime123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
//从数据库
mysql>  set password = password('Runtime123!');
Query OK, 0 rows affected, 1 warning (0.03 sec)

1.3关闭主从的防火墙以及selinux

//主数据库
[root@localhost ~]# systemctl disable --now firewalld.service
[root@localhost ~]# systemctl status firewalld.service 
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor prese>
   Active: inactive (dead)
     Docs: man:firewalld(1)
     
[root@localhost ~]# setenforce 0
[root@localhost ~]# vim /etc/selinux/config      //把文件中的enforcing变成disabled
SELINUX=disabled
/从数据库
[root@agent ~]# systemctl disable --now firewalld.service
[root@agent ~]# systemctl status firewalld.service 
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor prese>
   Active: inactive (dead)
     Docs: man:firewalld(1)
     
[root@agent ~]# setenforce 0
[root@agent~]# vim /etc/selinux/config      //把文件中的enforcing变成disabled
SELINUX=disabled

1.4.修改/etc/my.cnf文件

//主数据库
[root@localhosts ~]# vim /etc/my.cnf 
[root@localhosts ~]# 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
log-bin=mysql-bin                  //添加以下这两行
server-id=10

//从数据库
[root@agent ~]# vim /etc/my.cnf 
[root@agent ~]# 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
server-id=20                        //设置从库的唯一标识符，从库的server-id值必须小于主库的该值
relay-log=mysql-relay-bin        //启用中继日志relay-log
//重启主库和从库的MySQL服务
//主数据库
[root@localhosts ~]# systemctl restart mysqld
[root@localhosts ~]# systemctl status mysqld.service 
● mysqld.service - mysql server daemon
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: >
   Active: active (running) since Mon 2022-07-11 03:29:53 EDT; 10s ago
  Process: 34676 ExecStart=/usr/local/mysql/support-files/mysqld start (code=exited,>
 Main PID: 34689 (mysqld_safe)
    Tasks: 28 (limit: 11152)
   Memory: 179.8M
   CGroup: /system.slice/mysqld.service
           ├─34689 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pi>
           └─34905 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=>

7月 11 03:29:53 localhosts systemd[1]: Starting mysql server daemon...
7月 11 03:29:53 localhosts mysqld[34676]: Starting MySQL SUCCESS!
7月 11 03:29:53 localhosts systemd[1]: Started mysql server daemon.
7月 11 03:29:54 localhosts mysqld[34676]: Logging to '/opt/data/localhosts.err'.

//查看端口是否起来
[root@localhosts ~]# ss -antl
State      Recv-Q     Send-Q         Local Address:Port         Peer Address:Port    
LISTEN     0          128                  0.0.0.0:111               0.0.0.0:*       
LISTEN     0          32             192.168.122.1:53                0.0.0.0:*       
LISTEN     0          128                  0.0.0.0:22                0.0.0.0:*       
LISTEN     0          5                  127.0.0.1:631               0.0.0.0:*       
LISTEN     0          80                         *:3306                    *:*       
LISTEN     0          128                     [::]:111                  [::]:*       
LISTEN     0          128                     [::]:22                   [::]:*       
LISTEN     0          5                      [::1]:631                  [::]:*   

//从数据库
[root@agent ~]# systemctl restart mysqld.service 
[root@agent ~]# systemctl status mysqld.service 
● mysqld.service - mysql server daemon
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: >
   Active: active (running) since Mon 2022-07-11 03:32:05 EDT; 11s ago
  Process: 35564 ExecStart=/usr/local/mysql/support-files/mysqld start (code=exited,>
 Main PID: 35577 (mysqld_safe)
    Tasks: 28 (limit: 11160)
   Memory: 177.5M
   CGroup: /system.slice/mysqld.service
           ├─35577 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pi>
           └─35793 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=>
//查看端口是否起来
[root@agent ~]# ss -antl
State      Recv-Q     Send-Q         Local Address:Port         Peer Address:Port    
LISTEN     0          128                  0.0.0.0:111               0.0.0.0:*       
LISTEN     0          32             192.168.122.1:53                0.0.0.0:*       
LISTEN     0          128                  0.0.0.0:22                0.0.0.0:*       
LISTEN     0          5                  127.0.0.1:631               0.0.0.0:*       
LISTEN     0          80                         *:3306                    *:*       
LISTEN     0          128                     [::]:111                  [::]:*       
LISTEN     0          128                     [::]:22                   [::]:*       
LISTEN     0          5                      [::1]:631                  [::]:*    


//查看主库状态
[root@localhosts ~]# mysql -uroot -pRuntime123!
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
//在主库上创建一个账户授权给从库使用
mysql> CREATE USER 'repl'@'192.168.106.17' IDENTIFIED BY 'repl123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.106.17';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
//在从库上配置
[root@agent ~]# mysql -uroot -pRuntime123!
mysql> CHANGE MASTER TO
    -> MASTER_HOST= '192.168.106.19',           //主库的ip
    -> MASTER_USER='repl',              //刚才授权的用户
    -> MASTER_PASSWORD='repl123',             //刚才用户的密码
    -> MASTER_LOG_FILE='mysql_bin.000001',      //主库上刚才查看的内容
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.01 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.106.19
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000004
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes             //以下两行显示yes就表示成功了
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 527
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: 7ab8e54d-00e4-11ed-90b3-000c29f21d1f
             Master_Info_File: /opt/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

2.配置主从监控脚本

/在客户端上操作
[root@agent ~]# mkdir /scripts
[root@agent ~]# vim /scripts/mysqlmd.sh
[root@agent scripts]# chmod +x /scripts/mysqlmd.sh    /给脚本添加执行权限
[root@agent scripts]# cat mysqlmd.sh          //如果不等于2就显示1否则显示0
#!/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

//修改zabbix文件
[root@agent scripts]# vim /usr/local/etc/zabbix_agentd.conf        //在这个文件下添加下面这行
UserParameter=check_mysqlmd,/bin/bash /scripts/mysqlmd.sh
//重启一下服务
[root@agent scripts]# pkill zabbix
[root@agent scripts]# zabbix_agentd

2.1测试脚本

//在服务端上面执行


[root@zabbix_server ~]# zabbix_get -s 192.168.106.17 -k check_mysqlmd
0
//关闭主库的MySQL服务，再次执行命令看看有没有变化
[root@zabbix_server ~]# zabbix_get -s 192.168.106.17 -k check_mysqlmd
1                     //变成1就说明脚本以及配置好了

2.2 到zabbix网页配置主从监控

2.2.1配置监控项

在这里插入图片描述

2.2.2 配置触发器

在这里插入图片描述

//在主库上再次打开mysql服务
[root@localhosts ~]# systemctl restart mysqld.service

在这里插入图片描述

2.2.3 手动触发

//再次关掉服务器
[root@localhosts ~]# systemctl stop mysqld.service

在这里插入图片描述

3.配置主从延迟

3.1 编写脚本

把主从延迟的值取出来

[root@agent ~]# cd /scripts/
[root@agent scripts]# vim mysql_delay.sh 
[root@agent 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@agent scripts]# chmod +x mysql_delay.sh       //添加执行权限

3.2 配置zabbix文件

[root@agent scripts]# vim /usr/local/etc/zabbix_agentd.conf    //添加下面这行
UserParameter=check_mysql_delay,/bin/bash /scripts/mysql_delay.sh
//重启zabbix服务
[root@agent scripts]# pkill zabbix
[root@agent scripts]# zabbix_agentd

3.3 测试脚本

[root@agent scripts]# ./mysql_delay.sh        //可以看到是可以取出延迟的值的，只不过这里的延迟太小几乎没有，所有就为零
0
[root@zabbix_server ~]# zabbix_get -s 192.168.106.17 -k check_mysql_delay        //在服务端也是可以取到值的
0

3.4 配置主从延迟监控

回到zabbix网页

3.4.1 配置监控项

在这里插入图片描述

3.4.2 添加触发器

在这里插入图片描述

3.4.3 查看是否有有数值

在这里插入图片描述

3.4.4 查看告警信息

在这里插入图片描述

头发保卫者

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
自定义监控MySQL主从状态及主从延迟

需求：搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作环境说明：1.3关闭主从的防火墙以及selinux1.4.修改/etc/my.cnf文件2.配置主从监控脚本2.1测试脚本2.2 到zabbix网页配置主从监控2.2.1配置监控项3.2 配置zabbix文件3.3 测试脚本3.4 配置主从延迟监控回到zabbix网页3.4.1 配置监控项......
复制链接

扫一扫