MySQL主从

主从复制

[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