zabbix监控mysql

zabbix监控mysql

实验环境

主机名	IP地址	服务
master-mysql	192.168.100.33	主数据库
backup-mysql	192.168.100.116	从数据库
zabbix	192.168.100.135	zabbix

• 关闭防火墙和selinux

第一步搭建zabbix

//这里用的是yum安装的，如需源码安装请看zabbix

[root@zabbix ~]# rpm -ivh http://mirrors.aliyun.com/zabbix/zabbix/3.0/rhel/7/x86_64/zabbix-release-3.0-1.el7.noarch.rpm
[root@zabbix ~]# yum install -y zabbix-server-mysql zabbix-web-mysql zabbix-agent mariadb-server
[root@zabbix ~]# systemctl start mariadb.service 
[root@zabbix ~]# mysql_secure_installation
[root@zabbix ~]# mysql -uroot -p123.com
MariaDB [(none)]> create database zabbix character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> grant all privileges on zabbix.* to zabbix@localhost identified by '123.com';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@zabbix ~]# cd /usr/share/doc/zabbix-server-mysql-3.0.23/
[root@zabbix zabbix-server-mysql-3.0.23]# zcat create.sql.gz |mysql -p -u zabbix zabbix
[root@zabbix zabbix-server-mysql-3.0.23]# tail -1 /etc/zabbix/zabbix_server.conf
DBPassword=123.com
[root@zabbix zabbix-server-mysql-3.0.23]# systemctl start zabbix-server.service 
[root@zabbix zabbix-server-mysql-3.0.23]# ss -anlt
State      Recv-Q Send-Q       Local Address:Port                      Peer Address:Port              
LISTEN     0      128                      *:80                                   *:*                  
LISTEN     0      128                      *:22                                   *:*                  
LISTEN     0      100              127.0.0.1:25                                   *:*                  
LISTEN     0      128                      *:10051                                *:*                  
LISTEN     0      50                       *:3306                                 *:*                  
LISTEN     0      128                     :::80                                  :::*                  
LISTEN     0      128                     :::22                                  :::*                  
LISTEN     0      100                    ::1:25                                  :::*                  
LISTEN     0      128                     :::10051                               :::*
[root@zabbix zabbix-server-mysql-3.0.23]# vim /etc/httpd/conf.d/zabbix.conf
    <IfModule mod_php5.c>
        php_value max_execution_time 300
        php_value memory_limit 128M
        php_value post_max_size 16M
        php_value upload_max_filesize 2M
        php_value max_input_time 300
        php_value max_input_vars 10000
        php_value always_populate_raw_post_data -1
        # php_value date.timezone Europe/Riga
        php_value date.timezone Asia/Shanghai
    </IfModule>
 [root@zabbix zabbix-server-mysql-3.0.23]# systemctl start httpd

• 登录
  
  

数据库的主从请看mysql

监控mysql主机

• 在主数据库安装zabbix-agent

[root@master-mysql ~]# yum -y install zabbix-agent
[root@master-mysql ~]# vim /etc/zabbix/zabbix_agentd.conf
Server=192.168.100.135
...
ServerActive=192.168.100.135
...
Hostname=192.168.100.33
[root@master-mysql ~]# systemctl start zabbix-agent.service
[root@master-mysql ~]# ss -anlt
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                                  :::*                  
LISTEN     0      128                     :::10050                               :::* 

• 从数据库操作一样，安装zabbix-agent

监控Mysql主机

创建触发器

• 自定义key值

\\写脚本
[root@master-mysql opt]# cat ckmysql.sh 
#!/bin/bash
stat=$(ps -ef |grep -Ev "grep|$0" |grep "\b$1\b"|wc -l)


if [ $stat -ne 0 ];then
    echo "1"
else
    echo "0"
fi
\\编辑zabbix_agent.conf
[root@master-mysql opt]# vim /etc/zabbix/zabbix_agentd.conf
UnsafeUserParameters=1  //修改注释，讲值改为1
UserParameter=ckmysql,/bin/bash /opt/ckmysql.sh mysql
\\服务端检测
[root@zabbix ~]# zabbix_get -s 192.168.100.33 -k 'ckmysql'
1

• 在web页面添加项目
  
  
• 添加触发器
  
  
  \主mysql和从Mysql操作一样
• 添加报警邮箱发送
  
  
  
  
  
• 测试
  \dowm掉master-mysql的mysql服务

[root@master-mysql opt]# systemctl stop mariadb.service

监控Mysql的主从

mysql主从配置请看mysql主从

• 编写从库脚本脚本检测

\\重写记录
[root@slave-mysql opt]# cat ckread.sh 
#!/bin/bash

A=$(mysql -uroot -p123.com -e "show slave status\G" | grep "Read_Master_Log_Pos"|awk -F':' '{print $2}')
B=$(mysql -uroot -p123.com -e "show slave status\G" | grep "Exec_Master_Log_Pos"|awk -F':' '{print $2}')

if [ $A == $B ];then
    echo '1'
else
    echo '0'
fi
\\定义key值
[root@slave-mysql opt]# tail -5 /etc/zabbix/zabbix_agentd.conf 
#
UserParameter=ckmysql,/bin/bash /opt/ckmysql.sh mysql
UserParameter=ckread,/bin/bash /opt/ckread.sh
\\重启zabbix客户端服务端

• 服务端检测

[root@zabbix ~]# zabbix_get -s 192.168.100.116 -k 'ckread'
1

• 在web页面
  \添加项目一
  
  
• 创建触发器

\添加项目二

添加触发器

\ 添加项目三

\添加触发器

• 测试

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.100.33
                  Master_User: dsb
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 245
               Relay_Log_File: mysql-relay-bin.000014
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: No
            Slave_SQL_Running: No

• IO线程和SQL写同一脚本实现监控

[root@slave-mysql opt]# cat /opt/ckIS.sh 
#!/bin/bash
A=$1
B=$2

stat=$(mysql -uroot -p123.com -e 'show slave status\G'|egrep "$A|$B"|awk -F ':' '{print $2}'|grep -c Yes)

if [ $stat -eq 2 ];then
    echo "1"
else
    echo "0"
fi
[root@slave-mysql opt]# tail -1 /etc/zabbix/zabbix_agentd.conf 
UserParameter=ckIS,/bin/bash /opt/ckIS.sh Slave_IO_Running Slave_SQL_Running
\\这里需要重启服务

• 监控端使用zabbix_get工具测试

[root@zabbix ~]# zabbix_get -s 192.168.100.116 -p10050 -k 'ckIS'
1

• 在web页面添加监控项
  
  
  \添加触发器
  
  
  \添加是否成功
  

• 模拟故障检测

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

\收到了来自邮件的报警，证明此监控项添加成功