自定义监控MySQL主从状态及主从延迟_主从同步延时监控工具

1.配置主从

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

环境说明：

• 安装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
2
3
4
5
6
7
8

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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)

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177

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 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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就说明脚本以及配置好了
1
2
3
4
5
6
7
8

2.2 到zabbix网页配置主从监控

2.2.1配置监控项

2.2.2 配置触发器

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

2.2.3 手动触发

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

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       //添加执行权限
1
2
3
4
5
6
7
8
9

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 
1
2
3
4
5

3.3 测试脚本

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

1
2
3
4
5

3.4 配置主从延迟监控

• 回到zabbix网页

3.4.1 配置监控项

3.4.2 添加触发器

3.4.3 查看是否有有数值

3.4.4 查看告警信息