zabbix监控mysql主从
更改server、agent1、master、slave主机的/etc/hosts文件
192.168.159.139 server-139
192.168.159.100 agent-100
192.168.159.200 master-200
192.168.159.201 slave-201
master和slave主机安装mariadb
[root@slave-201 ~]# yum -y install mariadb mariadb-server
[root@slave-201 ~]# systemctl restart mariadb
[root@slave-201 ~]# systemctl enable mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
初始化mariadb
[root@slave-201 ~]# mysql_secure_installation
修改master配置文件
[root@master-200 ~]# 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
[root@master-200 ~]# systemctl restart mariadb
修改slave配置文件
[root@slave-201 ~]# 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@slave-201 ~]# systemctl restart mariadb
配置主从
master
[root@master-200 ~]# mysql -uroot -p123456
MariaDB [(none)]> grant all privileges on *.* to root@'%' identified by "123456";
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> grant replication slave on *.* to 'user'@'slave' identified by '123456';
Query OK, 0 rows affected (0.000 sec)
slave
[root@slave-201 ~]# mysql -uroot -p123456
MariaDB [(none)]> grant all privileges on *.* to root@'%' identified by "123456";
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> change master to master_host='master-200',master_user='user',master_password='123456';
Query OK, 0 rows affected (0.015 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-200
Master_User: user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
在slave主机安装zabbix-agent包，并添加到zabbix web监控平台
[root@slave-201 ~]# yum -y install zabbix-agent
修改zabbix_agent.conf配置文件
[root@slave-201 ~]# vim /etc/zabbix/zabbix_agentd.conf
Server=192.168.159.139
ServerActive=192.168.159.139
Hostname=slave-201
[root@slave-201 ~]# systemctl restart zabbix-agent.service
[root@slave-201 ~]# systemctl enable zabbix-agent.service
Created symlink /etc/systemd/system/multi-user.target.wants/zabbix-agent.service → /usr/lib/systemd/system/zabbix-agent.service.
在zabbix监控平台添加主机
slave主机配置脚本
[root@slave-201 ~]# mkdir /etc/zabbix/script
[root@slave-201 ~]# cd /etc/zabbix/script/
[root@slave-201 scripts]# vim mysql-slave.sh
#!/bin/bash
USER="root"
PASSWD="123456"
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-201 scripts]# chmod +x mysql-slave.sh
[root@slave-201 scripts]# chown -R zabbix.zabbix mysql-slave.sh
编写一个自配置文件，里面指定上面编写的脚本的路径，然后重启服务
[root@slave-201 zabbix_agentd.d]# vim user_mysql_slave.conf
UserParameter=mysql.slave[*],/etc/zabbix/script/mysql-slave.sh $1
[root@slave-201 zabbix_agentd.d]# chown -R zabbix.zabbix user_mysql_slave.conf
[root@slave-201 zabbix_agentd.d]# systemctl restart zabbix-agent.service
去server主机验证
[root@server-139 ~]# zabbix_get -s 192.168.159.201 -k mysql.slave[sql]
0
[root@server-139 ~]# zabbix_get -s 192.168.159.201 -k mysql.slave[io]
0
在zabbix监控平台创建监控项
新建触发器
创建图形
关闭mysql主从进行测试
[root@slave-201 ~]# mysql -uroot -p123456 -e "stop slave;"
zabbix主从延迟监控
配置库脚本
[root@slave-201 ~]# cd /etc/zabbix/script/
[root@slave-201 script]# vim mysql-delay.sh
#!/bin/bash
delay=$(mysql -uroot -p123456 -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-201 script]# chmod +x mysql-delay.sh
[root@slave-201 script]# chown -R zabbix.zabbix mysql-delay.sh
更改配置文件
[root@slave-201 ~]# vim /etc/zabbix/zabbix_agentd.d/user_mysql_slave.conf
UserParameter=check_mysql_delay,/bin/bash /etc/zabbix/script/mysql-delay.sh
[root@slave-201 ~]# systemctl restart zabbix-agent.service
在server主机测试
[root@server-139 ~]# zabbix_get -s 192.168.159.201 -k check_mysql_delay
0
在zabbix监控平台添加监控项
创建触发器
测试0是否能告警
