主从复制
[root@localhost ~]# yum -y install mariadb*
[root@localhost ~]# systemctl start mariadb //开机自启
[root@localhost ~]# systemctl disable --now firewall.service //关闭防火墙
[root@localhost ~]# getenforce 0 //关闭selinux
Disabled
[root@localhost ~]# mysql //进入数据库
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> set password = password("123456"); //设置密码
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> quit
Bye
// 从库
[root@localhost ~]# yum -y install mariadb*
[root@localhost ~]# systemctl start mariadb //开机自启
[root@localhost ~]# systemctl disable --now firewall.service //关闭防火墙[root@slave ~]# getenforce 0 //关闭selinux
Disabled
[root@localhost ~]# mysql //进入数据库
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> set password = password("123456"); //设置密码
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> quit
Bye
在主库创建用户给从库使用
[root@localhost ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> grant replication slave on *.* to 'lzx'@'192.168.14.12' identified by 'lzx123!';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> quit
Bye
配置主数据库
[root@localhost ~]# vim /etc/my.cnf.d/mariadb-server.cnf
16 [mysqld]
17 datadir=/var/lib/mysql
18 socket=/var/lib/mysql/mysql.sock
19 log-error=/var/log/mariadb/mariadb.log
20 pid-file=/run/mariadb/mariadb.pid
21
22 server-id = 10
23 log-bin = mysql_bin
配置从库
[root@localhost ~]# vim /etc/my.cnf.d/mariadb-server.cnf
16 [mysqld]
17 datadir=/var/lib/mysql
18 socket=/var/lib/mysql/mysql.sock
19 log-error=/var/log/mariadb/mariadb.log
20 pid-file=/run/mariadb/mariadb.pid
21
22 server-id = 20
23 relay-log = myrelay
[root@localhost ~]# systemctl restart mariadb.service
//配置并启动主从复制
MariaDB [(none)]> change master to
-> master_host='192.168.14.11',
-> master_user='zrz',
-> master_password='zrz123!',
-> master_log_file='mysql_bin.000001',
-> master_log_pos=328;
Query OK, 0 rows affected (0.008 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
在从库写脚本
[root@localhost ~]# cd /scripts/
[root@localhost scripts]# touch check_replication.sh
[root@localhost scripts]# chmod +x check_replication.sh
[root@localhost scripts]# ls
check_replication.sh
//为了让脚本更加安全,需要授权一个zabbix账户,除了要给select权限外还得给super,replication client权限
MariaDB [(none)]> grant select on *.* to 'zabbix'@'localhost' identified by 'zabbix123!';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> grant super,replication client on *.* to 'zabbix'@'localhost' identified by 'zabbix123!';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)
[root@slave scripts]# cat check_replication.sh
#!/bin/bash
count=$(mysql -uzabbix -p'zabbix123!' -e 'show slave status\G'|grep '_Running'|grep -c 'Yes')
if [ $count -ne 2 ];then
echo '1'
else
echo '0'
fi
// 如果在从服务器上关闭主从,再去执行脚本,就会显示1
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.002 sec)
[root@localhost scripts]# ./check_replication.sh
1
[root@localhost ~]# vim /usr/local/etc/zabbix_agentd.conf //修改zabbix_agentd.conf配置文件
322 UnsafeUserParameters=1
525 UserParameter=check_replication,/scripts/check_replication.sh //在配置文件末尾加上这一行
[root@localhost ~]# pkill zabbix
[root@localhost ~]# zabbix_agentd
主从延迟
[root@localhost scripts]# chmod +x check_replication_delay.sh
[root@localhost scripts]# cat check_replication.sh
#!/bin/bash
delay=$(mysql -uzabbix -p'zabbix123!' -e 'show slave status\G'|grep 'Behind'|awk '{print $2}')
if [ $delay != NULL ];then
echo $delay
else
echo '0'
fi
[root@localhost scripts]# ./check_replication_delay.sh
0
//修改zabbix_agentd.conf配置文件
[root@localhost ~]# vim /usr/local/etc/zabbix_agentd.conf
525 UserParameter=check_replication,/scripts/check_replication.sh
526 UserParameter=check_replication_delay,/scripts/check_replicatio n_delay.sh
[root@localhost ~]# pkill zabbix
[root@localhost ~]# zabbix_agentd