zabbix监控mysql

zabbix监控mysql主从

更改server、agent1、master、slave主机的/etc/hosts文件

192.168.111.10 server.example.com server
192.168.111.20 agent1.example.com agent1
192.168.111.30 master.example.com master
192.168.111.40 slave.example.com  slave

master和slave主机安装mariadb

---master
[root@master.example.com ~]#yum -y install mariadb mariadb-server
[root@master.example.com ~]#systemctl restart mariadb
[root@master.example.com ~]#systemctl enable mariadb

---slave
[root@slave.example.com ~]#yum -y install mariadb mariadb-server
[root@slave.example.com ~]#systemctl restart mariadb
[root@slave.example.com ~]#systemctl enable mariadb

两台主机都初始化mysql数据库

mysql_secure_installation

修改master配置文件

[root@master.example.com ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
log_bin=mysql-bin
server_id=20

修改slave配置文件

[root@slave.example.com ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
log_bin=mysql-bin
server_id=30

配置主

[root@master.example.com ~]#mysql -uroot -p'redhat'
MariaDB [(none)]> grant all privileges  on *.* to root@'%' identified by "redhat";
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> grant replication slave on *.* to 'user'@'slave' identified by 'redhat';
Query OK, 0 rows affected (0.000 sec)

配置从

[root@slave.example.com ~]#mysql -uroot -p'redhat'
MariaDB [(none)]> grant all privileges  on *.* to root@'%' identified by "redhat";
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> change master to master_host='master',master_user='user',master_password='redhat';
Query OK, 0 rows affected (0.003 sec)

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

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Connecting to master
                   Master_Host: master
                   Master_User: user
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: 
           Read_Master_Log_Pos: 4
                Relay_Log_File: myrelay.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: 
              Slave_IO_Running: Connecting
             Slave_SQL_Running: Yes

在slave主机安装zabbix-agent包，并添加到zabbix web监控平台

[root@slave.example.com ~]#yum -y install zabbix-agent

修改zabbix_agent.conf配置文件

[root@slave.example.com ~]#vim /etc/zabbix/zabbix_agentd.conf
Server=192.168.111.10
ServerActive=192.168.111.10
Hostname=slave
[root@slave-201 ~]# systemctl restart zabbix-agent.service
[root@slave-201 ~]# systemctl enable zabbix-agent.service

slave主机配置脚本

[root@slave.example.com ~]#mkdir /etc/zabbix/script
[root@slave.example.com ~]#cd /etc/zabbix/script
[root@slave.example.com /etc/zabbix/script]#vim mysql_slave_status.sh
#!/bin/bash
USER="root"
PASSWD="redhat"
NAME=$1

function IO {
    Slave_IO_Running=`mysql -u $USER -p$PASSWD -e "show slave status\G;" 2> /dev/null |grep Slave_IO_Running |awk '{print $2}'`
    if [ $Slave_IO_Running == "Connecting" ];then
        echo 0
    else
        echo 1
    fi
}

function SQL {
    Slave_SQL_Running=`mysql -u $USER -p$PASSWD -e "show slave status\G;" 2> /dev/null |grep Slave_SQL_Running: |awk '{print $2}'`
if [ $Slave_SQL_Running == "Yes" ];then
echo 0
    else
        echo 1
    fi

}

case $NAME in
   io)
       IO
   ;;
   sql)
       SQL
   ;;
   *)
        echo -e "Usage: $0 [io | sql]"
esac
[root@slave.example.com /etc/zabbix/script]#chmod +x mysql_slave_status.sh 
[root@slave.example.com /etc/zabbix/script]#chown -R zabbix.zabbix /etc/zabbix/script/

编写一个自配置文件，里面指定上面编写的脚本的路径，然后重启服务

[root@slave.example.com ~]#cd /etc/zabbix/zabbix_agentd.d/
[root@slave.example.com /etc/zabbix/zabbix_agentd.d]#vim userparameter_mysql_slave.conf
UserParameter=mysql.slave[*],/etc/zabbix/script/mysql_slave_status.sh $1
[root@slave.example.com /etc/zabbix/zabbix_agentd.d]#chown -R zabbix.zabbix /etc/zabbix/zabbix_agentd.d/userparameter_mysql_slaf
[root@slave.example.com /etc/zabbix/zabbix_agentd.d]#systemctl restart zabbix-agent.service

去zabbix server主机验证

[root@server.example.com ~]#yum -y install zabbix-get
[root@server.example.com ~]#zabbix_get -s 192.168.111.40 -k mysql.slave[sql]
0
[root@server.example.com ~]#zabbix_get -s 192.168.111.40 -k mysql.slave[io]
0

在zabbix监控平台创建监控项

新建监控项

新建触发器

创建图形

测试验证，将mysql主从关闭，查看zabbix告警信息，验证邮箱是否能接收到邮件

[root@slave.example.com ~]#mysql -u root -predhat -e "stop slave;"

zabbix主从延迟监控

配置库脚本

[root@slave.example.com ~]#cd /etc/zabbix/script
[root@slave.example.com /etc/zabbix/script]#vim mysql_delay.sh

#!/bin/bash     
delay=$(mysql -uroot -predhat -e 'show slave status\G' 2> /dev/null | grep 'Seconds_Behind_Master' | awk '{print $2}')
if [ $delay == "NULL" ];then
echo 0
elif [ $delay -ge 0 ] && [ $delay -le 200 ];then         
echo 0
else
echo $delay
fi

[root@slave.example.com /etc/zabbix/script]#chown -R zabbix.zabbix mysql_delay.sh
[root@slave.example.com /etc/zabbix/script]#chmod +x mysql_delay.sh

更改配置文件

[root@slave.example.com ~]#vim /etc/zabbix/zabbix_agentd.d/userparameter_mysql_slave.conf
UserParameter=check_mysql_delay,/bin/bash /etc/zabbix/script/mysql_delay.sh
[root@slave.example.com ~]#systemctl restart zabbix-agent.service
[root@slave.example.com ~]#cd  /etc/zabbix/script/
[root@slave.example.com /etc/zabbix/script]#./mysql_delay.sh
0

zabbix server主机进行脚本测试

[root@server.example.com ~]#zabbix_get -s 192.168.111.40 -k check_mysql_delay
0

在zabbix web平台操作

添加监控项

创建触发器

测试0是否能告警