更改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;"
配置库脚本
[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是否能告警