zabbix监控mysql主从

环境说明：

主机名	ip	服务
zhu	192.168.100.233	主数据库
cong	192.168.100.234	从数据库
zabbix	192.168.100.11	zabbix

关闭防火墙、selinux

搭建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主从

[root@zhu ~]# yum -y install zabbix-agent
[root@zhu ~]# vim /etc/zabbix/zabbix_agentd.conf
Server=192.168.100.11
...
ServerActive=192.168.100.11
...
Hostname=192.168.100.233
[root@zhu ~]# systemctl start zabbix-agent.service
[root@zhu ~]# 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           

创建触发器

编辑从库脚本检测

\\重写记录
[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.234 -p10050 -k 'ckIS'
1

在web页面添加监控项

模拟故障检测

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