zabbix监控mysql主从

最新推荐文章于 2024-07-15 11:48:23 发布

aleihello

最新推荐文章于 2024-07-15 11:48:23 发布

阅读量199

点赞数

文章标签： linux zabbix

本文链接：https://blog.csdn.net/aleihello/article/details/107533555

版权

MySQL主从监控

1.环境说明

服务角色	ip	主机名
主数据库	192.168.214.100	localhost
从数据库	192.168.214.150	vm3
zabbix 服务端	192.168.214.200	yanlei

1.1主数据库配置

//安装mysql并设置开机自动启动
[root@localhost ~]# yum -y install mariadb*
[root@localhost ~]# systemctl enable --now mariadb

//创建同步账号
[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB 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)]> CREATE USER 'repl'@'192.168.214.150' IDENTIFIED BY 'repl123';
Query OK, 0 rows affected (0.00 sec)

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

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

MariaDB [(none)]> exit
Bye


//配置my.cnf文件

[root@localhost ~]# vim /etc/my.cnf

[mysqld]
[mysqld]
datadir=/var/lib/mysql
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id = 10            添加
log-bin = mysql_bin    添加
[root@localhost ~]# systemctl restart mariadb

状态
[root@localhost ~]# mysql -e 'show master status;'
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000001 |      245 |              |                  |
+------------------+----------+--------------+----------------

1.2从数据库配置

配置并安装
[root@vm3 ~]# yum -y install mariadb*
[root@vm3 ~]# systemctl enable --now mariadb
[root@vm3 ~]#  vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id = 20    
relay-log = myrelay

[root@vm3 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB 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)]> change master to master_host='192.168.214.100',master_user='repl',master_password='repl123',master_log_file='mysql_bin.000001',master_log_pos=245;

MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************

Query OK, 0 rows affected (0.01 sec)
  Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.214.100
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000002
          Read_Master_Log_Pos: 245
               Relay_Log_File: myrelay.000004
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql_bin.000002
             Slave_IO_Running: 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: 245
              Relay_Log_Space: 1099
              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
1 row in set (0.00 sec)

1.3安装agent客户端

[root@vm3 ~]# yum -y install gcc gcc-c++ vim wget pcre-devel openssl-devel
[root@vm3 ~]# ls
anaconda-ks.cfg  zabbix-5.0.2.tar.gz
[root@vm3 ~]# tar xf zabbix-5.0.2.tar.gz 
[root@vm3 ~]# ls
anaconda-ks.cfg  zabbix-5.0.2  zabbix-5.0.2.tar.gz
[root@vm3 ~]# cd zabbix-5.0.2
[root@vm3 zabbix-5.0.2]# ./configure --enable-agent
[root@vm3 zabbix-5.0.2]# make install
[root@vm3 zabbix-5.0.2]# vim /usr/local/etc/zabbix_agentd.conf

# Server=

Server=192.168.214.200       添加

### Option: ListenPort
# Range: 1024-32767
# Default:
# ListenPort=10050

### Option: ListenIP
# Default:
#
# Mandatory: no
# Range: 0-100
# Default:
# StartAgents=3

##### Active checks related

### Option: ServerActive
#       If port is not specified, default port is used.
#
# ServerActive=

ServerActive=192.168.214.200     添加

### Option: Hostname
#       Unique, case sensitive hostname.
#       Value is acquired from HostnameItem if undefined.
#
# Mandatory: no
# Default:
# Hostname=

Hostname=mysql_slave1       添加

### Option: HostnameItem
#       Item used for generating Hostname

[root@vm3 zabbix-5.0.2]# useradd -r -M -s /sbin/nologin zabbix
[root@vm3 zabbix-5.0.2]# zabbix_agentd 
[root@vm3 zabbix-5.0.2]# ss -tanl
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN      0      128     *:10050               *:*                  
LISTEN      0      50      *:3306                *:*                  
LISTEN      0      128     *:22                  *:*                  
LISTEN      0      128    :::22                 :::*

1.4 配置监控MySQL主从状态

[root@vm3 ~]# mkdir /scripts
[root@vm3 ~]# touch /scripts/check_replication.sh
[root@vm3 ~]# chmod +x /scripts/check_replication.sh 
[root@vm3 ~]# vim /scripts/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@vm3 ~]# vim /usr/local/etc/zabbix_agentd.conf


# UnsafeUserParameters=0 
UnsafeUserParameters=1    添加
UserParameter=check_replication,/bin/bash /scripts/check_replication.sh    添加
[root@vm3 zabbix-5.0.2]# pkill zabbix
[root@vm3 zabbix-5.0.2]# zabbix_agentd 
[root@vm3 zabbix-5.0.2]# ss -tanl
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN      0      128     *:10050               *:*                  
LISTEN      0      50      *:3306                *:*                  
LISTEN      0      128     *:22                  *:*                  
LISTEN      0      128    :::22                 :::*

1.5配置zabbix

在这里插入图片描述

测试停止客户端数据库服务

[root@vm3 zabbix-5.0.2]# systemctl stop mariadb
[root@vm3 zabbix-5.0.2]# ss -tanl
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN      0      128     *:10050               *:*                  
LISTEN      0      128     *:22                  *:*                  
LISTEN      0      128    :::22                 :::*                  
[root@vm3 zabbix-5.0.2]#

在这里插入图片描述

mysql主从延迟监控

1.配置agent客户端

[root@vm3 ~]# vim /scripts/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@vm3 ~]# chmod +x /scripts/delay_replication.sh


[root@vm3 ~]# vim /usr/local/etc/zabbix_agentd.conf

# UnsafeUserParameters=0
UnsafeUserParameters=1
UserParameter=check_replication,/bin/bash /scripts/check_replication.sh
UserParameter=delay_replication,/bin/bash /scripts/delay_replication.sh      添加

//重启服务
[root@vm3 ~]# pkill zabbix
[root@vm3 ~]# zabbix_agentd
[root@vm3 zabbix-5.0.2]# ss -tanl
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN      0      128     *:10050               *:*                  
LISTEN      0      50      *:3306                *:*                  
LISTEN      0      128     *:22                  *:*                  
LISTEN      0      128    :::22                 :::*

2.zabbix配置

在这里插入图片描述

aleihello

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
zabbix监控mysql主从

MySQL主从监控1.环境说明服务角色ip主机名主数据库192.168.214.100localhost从数据库192.168.214.150vm3zabbix 服务端192.168.214.200yanlei1.1主数据库配置//安装mysql并设置开机自动启动[root@localhost ~]# yum -y install mariadb*[root@localhost ~]# systemctl enable --now mariadb
复制链接

扫一扫