zabbix监控的MySQL主从状态与延迟

程序员文章站 2022-09-27 10:15:23

环境名称主机名ipzabbix服务端WangJiayue192.168.222.128zabbix客户端、MySQL主库WangJiayue-2192.168.222.129zabbix客户端、MySQL从库WangJiayue-3192.168.222.1301.配置MySQL主从1.1安装数据库、[root@WangJiayue ~]# mysql //在主数据库里创建一个同步账号授权给从数据库使用MariaDB [(none)]> g...

环境

名称	主机名	ip
zabbix服务端	WangJiayue	192.168.222.128
zabbix客户端、MySQL主库	WangJiayue-2	192.168.222.129
zabbix客户端、MySQL从库	WangJiayue-3	192.168.222.130

1.配置MySQL主从

1.1安装数据库、

[root@WangJiayue-2 ~]# yum -y install mariadb*
[root@WangJiayue-3 ~]# yum -y install mariadb*

1.2配置主数据库

[root@WangJiayue-2 ~]# mysql  //在主数据库里创建一个同步账号授权给从数据库使用
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.222.130' identified by 'repl123';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.222.130';
Query OK, 0 rows affected (0.00 sec)

ariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

[root@WangJiayue-2 ~]# vim /etc/my.cnf  //编辑配置文件
server-id = 10  //数据库服务器唯一标识符，主库的server-id值必须比从库的大
log-bin = mysql_bin  //启用binlog日志

[root@WangJiayue-2 ~]# systemctl restart mariadb //重启mysql服务
[root@WangJiayue-2 scripts]# ss -antl|grep 3306
LISTEN     0      50           *:3306                     *:* 

[root@WangJiayue-2 ~]# mysql 
MariaDB [(none)]> show master status; //查看主库状态
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      245 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

1.3配置从数据库

[root@WangJiayue-3 ~]# vim /etc/my.cnf //编辑配置文件
server-id = 20  //设置从库的唯一标识符，从库的server-id值必须小于主库的该值
relay-log = myrelay_bin //启用中继日志relay-log

[root@WangJiayue-2 ~]# systemctl restart mariadb //重启mysql服务
[root@WangJiayue-2 scripts]# ss -antl|grep 3306
LISTEN     0      50           *:3306                     *:* 

//配置并启动主从复制
MariaDB [(none)]> change master to \
    -> master_host='192.168.222.129',
    -> master_user='repl',
    -> master_password='repl123',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=245;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

MariaDB [(none)]> start slave; //启用
Query OK, 0 rows affected (0.03 sec)

//查看从服务器状态
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.222.129
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: myrelay.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes   //此处必须是Yes
            Slave_SQL_Running: Yes  //此处必须是Yes
.....

2.安装zabbix服务端、客户端

2.1配置zabbix服务端

[root@WangJiayue ~]# wget https://cdn.zabbix.com/zabbix/sources/stable/5.0/zabbix-5.0.1.tar.gz //下载zabbix
[root@WangJiayue ~]# ls
anaconda-ks.cfg  lamp  zabbix-5.0.1.tar.gz
[root@WangJiayue ~]# tar xf zabbix-5.0.1.tar.gz //解压
[root@WangJiayue ~]# useradd -r -M -s /sbin/nologin zabbix  //创建zabbix用户和组
[root@WangJiayue ~]# mysql -uroot -p123456 //配置zabbix数据库

mysql> create database zabbix character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.01 sec)

mysql> grant all privileges on zabbix.* to zabbix@localhost identified by 'zabbix123';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

[root@WangJiayue ~]# cd zabbix-5.0.1/database/mysql/
[root@WangJiayue mysql]# ls
data.sql  double.sql  images.sql  Makefile.am  Makefile.in  schema.sql
[root@WangJiayue mysql]# mysql -uzabbix -pzabbix123 zabbix < schema.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@WangJiayue mysql]# mysql -uzabbix -pzabbix123 zabbix < images.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@WangJiayue mysql]# mysql -uzabbix -pzabbix123 zabbix < data.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@WangJiayue ~]# cd zabbix-5.0.1 //编译安装zabbix
[root@WangJiayue zabbix-5.0.1]# ./configure --enable-server \
> --enable-agent \
> --with-mysql \
> --with-net-snmp \
> --with-libcurl \
> --with-libxml2
....
[root@WangJiayue zabbix-5.0.1]# make insatall
[root@WangJiayue zabbix-5.0.1]# cd /usr/local/etc/
[root@WangJiayue etc]# ls
zabbix_agentd.conf    zabbix_server.conf
zabbix_agentd.conf.d  zabbix_server.conf.d
[root@WangJiayue etc]# vim zabbix_server.conf  

DBPassword=zabbix123      //设置zabbix数据库连接密码

//启动zabbix_server和zabbix_agentd
[root@WangJiayue ~]# zabbix_server 
[root@WangJiayue ~]# zabbix_agentd
[root@WangJiayue ~]# ss -antl
State      Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN     0      128         *:22                      *:*                  
LISTEN     0      100    127.0.0.1:25                      *:*                  
LISTEN     0      128         *:10050                   *:*                  
LISTEN     0      128         *:10051                   *:*                  
LISTEN     0      128    127.0.0.1:9000                    *:*                  
LISTEN     0      128        :::80                     :::*                  
LISTEN     0      128        :::22                     :::*                  
LISTEN     0      100       ::1:25                     :::*                  
LISTEN     0      80         :::3306                   :::*  

//修改/etc/php.ini的配置并重启php-fpm
[root@WangJiayue ~]# sed -ri 's/(post_max_size =).*/\1 16M/g' /etc/php.ini
[root@WangJiayue ~]# sed -ri 's/(max_execution_time =).*/\1 300/g' /etc/php.ini
[root@WangJiayue ~]# sed -ri 's/(max_input_time =).*/\1 300/g' /etc/php.ini
[root@WangJiayue ~]# sed -i '/;date.timezone/a date.timezone = Asia/Shanghai' /etc/php.ini
[root@WangJiayue ~]# service php-fpm restart
Gracefully shutting down php-fpm . done
Starting php-fpm  done

//创建数据存放目录并修改目录属主和属组
[root@WangJiayue ~]# cd zabbix-5.0.1
[root@WangJiayue zabbix-5.0.1]# ls
aclocal.m4  conf           configure.ac  install-sh   misc     ui
AUTHORS     config.guess   COPYING       m4           missing
bin         config.log     database      Makefile     NEWS
build       config.status  depcomp       Makefile.am  README
ChangeLog   config.sub     include       Makefile.in  sass
compile     configure      INSTALL       man          src
[root@WangJiayue zabbix-5.0.1]# cp -r ui /usr/local/apache/htdocs/zabbix
[root@WangJiayue zabbix-5.0.1]# cd /usr/local/apache/htdocs/
[root@WangJiayue htdocs]# ls
index.html  test.com  zabbix
[root@WangJiayue htdocs]# chown -R apache.apache zabbix/
[root@WangJiayue htdocs]# ll
total 8
-rw-r--r--.  1 apache apache   45 Jun 12  2007 index.html
drwxr-xr-x.  2 apache apache   23 Jul 16 01:40 test.com
drwxr-xr-x. 12 apache apache 4096 Jul 16 20:16 zabbix

//配置apache虚拟主机
[root@WangJiayue ~]# vim /etc/httpd24/httpd.conf
#添加下面的内容
<VirtualHost *:80>
    DocumentRoot "/usr/local/apache/htdocs/zabbix"
    ServerName www.wyt.com
    ProxyRequests Off
    ProxyPassMatch ^/(.*\.php)$ fcgi://127.0.0.1:9000/usr/local/apache/htdocs/zabbix/$1
    <Directory "/usr/local/apache/htdocs/zabbix">
        Options none
        AllowOverride none
        Require all granted
    </Directory>
</VirtualHost>

//设置zabbix/conf目录的权限，让zabbix有权限生成配置文件zabbix.conf.php
[root@WangJiayue ~]# chmod 777 /usr/local/apache/htdocs/zabbix/conf
[root@WangJiayue ~]# ll -d /usr/local/apache/htdocs/zabbix/conf
drwxrwxrwx. 3 apache apache 94 Jul 16 20:16 /usr/local/apache/htdocs/zabbix/conf

//重启服务
[root@WangJiayue ~]# apachectl stop
[root@WangJiayue ~]# apachectl start
[root@WangJiayue ~]# service php-fpm restart
Gracefully shutting down php-fpm . done
Starting php-fpm  done
[root@WangJiayue~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@WangJiayue ~]# ss -antl
State      Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN     0      128         *:22                      *:*                  
LISTEN     0      100    127.0.0.1:25                      *:*                  
LISTEN     0      128         *:10050                   *:*                  
LISTEN     15     128         *:10051                   *:*                  
LISTEN     0      128    127.0.0.1:9000                    *:*                  
LISTEN     0      128        :::80                     :::*                  
LISTEN     0      128        :::22                     :::*                  
LISTEN     0      100       ::1:25                     :::*                  
LISTEN     0      80         :::3306                   :::*   

//安装zabbix web界面

//恢复zabbix/conf目录的权限为755
[root@WangJiayue ~]# chmod 755 /usr/local/apache/htdocs/zabbix/conf
[root@WangJiayue ~]# ll -d /usr/local/apache/htdocs/zabbix/conf
drwxr-xr-x. 3 apache apache 117 Jul 16 21:05 /usr/local/apache/htdocs/zabbix/conf

//登录zabbix
用户名: Admin
密码: zabbix

2.2配置zabbix客户端

[root@WangJiayue-2 ~]# wget https://cdn.zabbix.com/zabbix/sources/stable/5.0/zabbix-5.0.1.tar.gz //下载zabbix
[root@WangJiayue-2 ~]# ls
anaconda-ks.cfg  lamp  zabbix-5.0.1.tar.gz
[root@WangJiayue-2 ~]# tar xf zabbix-5.0.1.tar.gz //解压
[root@WangJiayue-2 ~]# useradd -r -M -s /sbin/nologin zabbix //创建zabbix用户和组
[root@WangJiayue-2 ~]# cd zabbix-5.0.1
[root@WangJiayue-2 zabbix-5.0.1]# ./configure --enable-agent //编译
[root@WangJiayue-2 zabbix-5.0.1]# make insatall //安装
[root@WangJiayue-2 ~]# zabbix_agentd  //启动abbix_agentd
[root@WangJiayue-2 scripts]# ss -antl|grep 10050
LISTEN     0      128          *:10050                    *:* 
[root@WangJiayue-3 ~]# wget https://cdn.zabbix.com/zabbix/sources/stable/5.0/zabbix-5.0.1.tar.gz //下载zabbix
[root@WangJiayue-3 ~]# ls
anaconda-ks.cfg  lamp  zabbix-5.0.1.tar.gz
[root@WangJiayue-3 ~]# tar xf zabbix-5.0.1.tar.gz //解压
[root@WangJiayue-3 ~]# useradd -r -M -s /sbin/nologin zabbix //创建zabbix用户和组
[root@WangJiayue-3 ~]# cd zabbix-5.0.1
[root@WangJiayue-3 zabbix-5.0.1]# ./configure --enable-agent //编译
[root@WangJiayue-3 zabbix-5.0.1]# make insatall //安装
[root@WangJiayue-3 ~]# zabbix_agentd  //启动abbix_agentd
[root@WangJiayue-3 scripts]# ss -antl|grep 10050
LISTEN     0      128          *:10050                    *:*

3.创建校本

[root@WangJiayue-3 ~]# mkdir /scripts
[root@WangJiayue-3 ~]# cd /scripts/
[root@WangJiayue-3 scripts]# cat check_replication.sh 
#!/bin/bash
mysql_status=$(mysql -uroot -e 'show slave status\G' 2>/dev/null|grep 'Slave.*Running'|grep -c 'Yes')

if [ $mysql_status -ne 2 ];then
    echo '1'
else
    echo '0'
fi

[root@WangJiayue-3 scripts]# vim /usr/local/etc/zabbix_agentd.conf //修改配置文件
//添加下列内容
UnsafeUserParameters=1
UserParameter=check_replication,/bin/bash /scripts/check_replication.sh
[root@WangJiayue-3 ~]# pkill zabbix
[root@WangJiayue-3 ~]# zabbix_agentd  //重启服务
[root@WangJiayue-3 scripts]# mysql -uroot -e 'show slave status\G' 2>/dev/null|grep 'Slave.*Running'|grep -c 'Yes' //测试脚本能否取值
2
[root@WangJiayue ~]# zabbix_get -s 192.168.222.130 -k check_replication //手动触发测试
1
[root@WangJiayue ~]# zabbix_get -s 192.168.222.130 -k check_replication //手动触发测试
0

4.配置zabbix的Web界面

4.1添加主机

zabbix监控的MySQL主从状态与延迟

4.2添加监控项

zabbix监控的MySQL主从状态与延迟

填写完成后点击 Add 完成添加

4.3添加触发器

zabbix监控的MySQL主从状态与延迟

4.5收到邮件警告

zabbix监控的MySQL主从状态与延迟

zabbix监控的MySQL主从延迟

1.配置脚本

[root@WangJiayue-3 scripts]# vim delay_replication.sh
[root@WangJiayue-3 scripts]# bash delay_replication.sh 
0
[root@WangJiayue-3 scripts]# cat delay_replication.sh 
#!/bin/bash

delay_value=$(mysql -uroot -e 'show slave status\G' 2>/dev/null|grep 'Seconds_Behind_Master'|awk -F'[: ]+' '{print $3}')
echo $delay_value

//修改配置文件
[root@WangJiayue-3 ~]# vim /usr/local/etc/zabbix_agentd.conf 
UserParameter=delay_replication,/bin/bash /scripts/delay_replication.sh
[root@WangJiayue-3 ~]# pkill zabbix
[root@WangJiayue-3 ~]# zabbix_agentd  //重启服务
[root@WangJiayue-3 ~]# ss -antl
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN      0      128         *:22                      *:*                  
LISTEN      0      100    127.0.0.1:25                      *:*                  
LISTEN      0      128         *:10050                   *:*                  
LISTEN      0      50          *:3306                    *:*                  
LISTEN      0      128        :::22                     :::*                  
LISTEN      0      100       ::1:25                     :::*