自定义监控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                [::]:*

配置两台客户机
 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
...

配置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                  [::]:*       

自定义监控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

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)

配置从库
[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

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 

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

5.重启zabbix_ agentd

[root@secondary etc]# pkill zabbix
[root@secondary etc]# zabbix_agentd

6.在从库本地执行脚本看结果是否正常匹配

[root@secondary scripts]# ./mysqlms.sh 
0

7.确保zabbix_ server端用zabbix_ get命令手动获取下状态并且确保状态与从库手动执行时一致

[root@dabao ~]# zabbix_get -s 192.168.140.144 -k check_mysqlms
0

8.配置web界面添加监控项与触发器

9.手动触发(把主库mysql服务停掉)并验证是否报警

[root@client ~]# systemctl stop mysqld

主从延迟

[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

[root@secondary scripts]# cd ..
[root@secondary etc]# vi zabbix_agentd.conf
添加
UserParameter=database_delay,/bin/bash /usr/local/etc/scripts/database_delay.sh

[root@secondary ~]# pkill zabbix
[root@secondary ~]# zabbix_agentd 

[root@dabao ~]# zabbix_get -s 192.168.140.144 -k database_delay 
0