zabbix自定义监控mysql主从

zabbix自定义监控mysql主从

监控mysql主从状态

修改数据库密码
[root@zhu ~]# mysql -uroot -pyqz123!
 MariaDB [(none)]> set password = password('yqz123!');
Query OK, 0 rows affected (0.000 sec)
   

在主库上创建同步账号
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.17.135' identified by 'yqz123!';
Query OK, 0 rows affected (0.000 sec)


MariaDB [(none)]> exit
Bye



修改配置文件
[root@zhu ~]# vim /etc/my.cnf.d/mariadb-server.cnf 
log-bin = mysql_bin
sercer-id = 10
重启服务
[root@zhu ~]# systemctl restart mariadb.service 


[root@zhu ~]# mysql -uroot -pyqz123!
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.5.9-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
查看主数据库的状态
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000002 |      530 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

从数据库配置

[root@cong ~]# systemctl start mariadb.service 
[root@cong ~]# mysql
MariaDB [(none)]> set password = password('yqz123!')
    -> ;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> exit
Bye
[root@cong ~]# vim /etc/my.cnf.d/mariadb-server.cnf 
server-id = 20
relay-log = myrelay
[root@cong ~]# systemctl restart mariadb.service 




[root@cong ~]# mysql -uroot -pyqz123!
MariaDB [(none)]> change master to master_host='192.168.17.130', master_user='repl', master_password='yqz123!', master_log_file='mysql_bin.000002', master_log_pos=530;
Query OK, 0 rows affected (0.005 sec)


MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> show slave status\G; 
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.17.130
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql_bin.000002
           Read_Master_Log_Pos: 530
                Relay_Log_File: myrelay.000002
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql_bin.000002
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
测试验证主从是否同步
MariaDB [(none)]> create database yz;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| yz                 |
+--------------------+
4 rows in set (0.001 sec)

在进入从数据库看有没有同步
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| yz                 |
+--------------------+
4 rows in set (0.001 sec)

从数据库写个脚本

[root@cong scripts]#  ls
check_process.sh  log.py 
[root@cong scripts]#  vim mysqlms.sh 
[root@cong scripts]# cat mysqlms.sh 
#!/bin/bash 

count=$(mysql -uroot -pyqz123! -e "show slave status\G" | grep -v grep | grep -c 'Yes')

if [ $count -ne 2 ];then
    echo '1'
else
    echo '0'
fi
这里给他执行权限
[root@cong scripts]# chmod +x mysqlms.sh 

运行脚本，出现0说明那两个数据库的条件都是yes
[root@cong scripts]#  ./mysqlms.sh 
0
修改zabbix服务端的主配置文件
[root@cong etc]# vim zabbix_agentd.conf
[root@cong etc]# pwd
/usr/local/etc
[root@cong etc]# 
UserParameter=check_mysqlms, /usr/local/etc/scripts/mysqlms.sh   #在最后一行加入此配置文件

这里需要把zabbix服务重启一下
[root@cong etc]# pkill zabbix
[root@cong etc]# ss -antl
State   Recv-Q  Send-Q   Local Address:Port   Peer Address:Port Process  
LISTEN  0       80             0.0.0.0:3306        0.0.0.0:*             
LISTEN  0       128            0.0.0.0:22          0.0.0.0:*             
LISTEN  0       128               [::]:22             [::]:*             
[root@cong ~]#  zabbix_agentd 
[root@cong ~]#  ss -antl
State   Recv-Q  Send-Q   Local Address:Port    Peer Address:Port Process 
LISTEN  0       80             0.0.0.0:3306         0.0.0.0:*            
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               [::]:22              [::]:*            

zabbix服务端测试

#先用命令测试一下
[root@yz ~]#  zabbix_get -s 192.168.17.130 -k check_mysqlms
0
[root@yz ~]# 

配置网页监控

添加监控项

添加触发器

收到报警邮件

监控mysql主从延迟

[root@zhu scripts]# vim mysql_delay.sh
[root@zhu scripts]# chmod +x mysql_delay.sh 
[root@zhu scripts]# ./mysql_delay.sh 
0
[root@zhu etc]# vim zabbix_agentd.conf
UserParameter=check_mysql_delay,  /scripts/mysql_delay.sh
[root@zhu etc]# pkill zabbix
[root@zhu etc]# zabbix_agentd