测试MySQL增强半同步+GTID+keepalived高可用架构

晟数学苑 2021-09-18

994

架构：

结点	版本	IP	端口	主机名
主	MySQL8.0	192.168.8.222	3306	cdh1
备	MySQL8.0	192.168.8.224	3306	cdh3
vip		192.168.8.230

GTID的限制

1) 不支持非事务引擎，从库报错，stop ,start slave 忽略报错
2) 不支持create table .. select 语句复制
3) 不允许一个SQL同时更新一个事务引擎和非事务引擎的表
4) 在一个复制组中，必须要求统一开启GTID或者关闭GTID。5.7开始支持GTID的在线切换
5) 5.7之前，开启GTID需要重启
6) 开启GTID后，就不在使用原来传统的复制方式。MHA也被GTID功能替代。
7) 对于create temporary table不会同步，但是drop temporary table会同步，要避开这个坑
8) 不支持sql slave skip counter
复制

1.搭建MySQL GTID+row+增强半同步

222结点数据库配置文件

[root@cdh1 ~]# cat etc/my.cnf
 
[client]
 
default-character-set=utf8
 
[mysqld]
datadir=/home/mysql
socket=/tmp/mysql.sock
user=mysql
port=3306
character-set-client-handshake = FALSE
character-set-server = utf8
collation-server = utf8_unicode_ci
init_connect='SET NAMES utf8'
 
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
bind-address = 0.0.0.0
lower_case_table_names=1
max_connections=1000
skip-host-cache
skip-name-resolve
server-id = 1                    
log-bin = mysql-bin              
binlog_format=row
expire_logs_days=5
open_files_limit = 20480
table_open_cache=8192
explicit_defaults_for_timestamp=true
innodb_file_per_table=1
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"
log_timestamps=SYSTEM
 
max_allowed_packet=256M
wait_timeout=2880000
interactive_timeout=2880000
innodb_dedicated_server=no #尽量的使用系统内存资源，做到数据库服务器性能最大化
skip-host-cache
skip-name-resolve
 
############replication###############
binlog_cache_size = 4M
relay_log_recovery = 1
relay_log_purge=1
log_slave_updates = 0 #应用主库日志不产生新的日志
 
#######gtid####################
gtid_mode = on
enforce_gtid_consistency = 1 
binlog_gtid_simple_recovery = 1
 
############多线程复制##############
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=4
 
########semi sync replication settings########
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled=on
rpl_semi_sync_slave_enabled=on 
 
[mysqld_safe]
log-error=/home/mysql/mysqld.log
pid-file=/home/mysql/db.pid
复制

224结点数据库配置文件

[root@cdh3 ~]# cat etc/my.cnf
[client]
 
default-character-set=utf8
 
[mysqld]
datadir=/home/mysql
socket=/tmp/mysql.sock
user=mysql
port=3306
 
character-set-client-handshake = FALSE
character-set-server = utf8
collation-server = utf8_unicode_ci
init_connect='SET NAMES utf8'
 
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
lower_case_table_names=1
max_connections=2000
skip-host-cache
skip-name-resolve
server-id = 3                    
log-bin = mysql-bin              
binlog_format=row
expire_logs_days=5
open_files_limit = 20480
table_open_cache=8192
explicit_defaults_for_timestamp=true
innodb_file_per_table=1
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"
log_timestamps=SYSTEM
 
innodb_dedicated_server=no #尽量的使用系统内存资源，做到数据库服务器性能最大化
skip-host-cache
skip-name-resolve
 
############replication###############
binlog_cache_size = 4M
relay_log_recovery = 1
relay_log_purge=1
log_slave_updates = 0 #应用主库日志不产生新的日志
 
#######gtid####################
gtid_mode = on
enforce_gtid_consistency = 1 
binlog_gtid_simple_recovery = 1
 
############多线程复制##############
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=4
 
########semi sync replication settings########
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled=on
rpl_semi_sync_slave_enabled=on 
 
[mysqld_safe]
log-error=/home/mysql/mysqld.log
pid-file=/home/mysql/db.pid
复制

2.搭建主从

<1>222结点创建复制用户
   create user repl@'%' identified by 'repl';
   grant replication slave,replication client on *.* to repl@'%';
<2>停止222结点数据库，冷复制数据库
   scp -p root@192.168.8.222:/home/mysql home/
<3>复制到224结点后需要删除数据库UUID，重启备库生成新的UUID
   rm home/mysql/auto.cnf
   chown mysql.mysql -R home/mysql 
<4>加载半同步复制插件，开启半同步复制
   两个结点都执行：
   INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
   INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
   set global rpl_semi_sync_master_enabled=on;
   set global rpl_semi_sync_slave_enabled=on;
   show variables like '%rpl_semi_sync_%';
<5>启动主备，相互复制
   222结点：change master to master_host='192.168.8.224', master_user='repl', master_password='repl', master_port=3306, master_auto_position=1;
   start slave;
   show slave status\G
   224结点：change master to master_host='192.168.8.222', master_user='repl', master_password='repl', master_port=3306, master_auto_position=1;
   start slave;
   show slave status\G
<6>测试主从略
复制

3.安装keepalived，这里选择yum安装

两节点各自执行：

yum install keepalived -y
复制

修改222结点keepalived配置文件(直接覆盖)

[root@cdh1 ~]# cat etc/keepalived/keepalived.conf 
! Configuration File for keepalived
       
global_defs {
    router_id MASTER-HA
}
       
vrrp_script chk_mysql_port {                #检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等
    script "/etc/keepalived/chk_mysql.sh"   #这里通过脚本监测
    interval 2                              #脚本执行间隔，每2s检测一次
}
       
vrrp_instance VI_1 {
    state BACKUP    
    interface eth0          #指定虚拟ip的网卡接口
    mcast_src_ip 192.168.8.22
    virtual_router_id 51    #路由器标识，MASTER和BACKUP必须是一致的
    priority 100            #定义优先级，数字越大，优先级越高 
    advert_int 1         
    nopreempt               #优先级高的结点设置为不抢占vip
    authentication {   
        auth_type PASS 
        auth_pass 1111     
    }
    virtual_ipaddress {    
        192.168.8.230
    }
      
    track_script {               
       chk_mysql_port             
    }
}

复制

222结点创建MySQL检测脚本

[root@cdh1 ~]# cat etc/keepalived/chk_mysql.sh
#!/bin/bash
export etc/profile
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    service keepalived stop
fi
 
 
#chmod 755 etc/keepalived/chk_mysql.sh
复制

修改224结点keepalived配置文件(直接覆盖)

[root@cdh3 ~]# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived
       
global_defs {
    router_id MASTER-HA
}
       
vrrp_script chk_mysql_port {                #检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等
    script "/etc/keepalived/chk_mysql.sh"   #这里通过脚本监测
    interval 2                              #脚本执行间隔，每2s检测一次
}
       
vrrp_instance VI_1 {
    state BACKUP    
    interface eth0          #指定虚拟ip的网卡接口
    mcast_src_ip 192.168.8.224
    virtual_router_id 51    #路由器标识，MASTER和BACKUP必须是一致的
    priority 90            #定义优先级，数字越大，优先级越高 
    advert_int 1         
    #nopreempt             #优先级高的结点设置为不抢占vip
    authentication {   
        auth_type PASS 
        auth_pass 1111     
    }
    virtual_ipaddress {    
        192.168.8.230
    }
      
    track_script {               
       chk_mysql_port             
    }
}


复制

224结点创建MySQL检测脚本

[root@cdh3 ~]# cat /etc/keepalived/chk_mysql.sh 
#!/bin/bash
export /etc/profile
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    service keepalived stop
fi
 
#chmod 755 /etc/keepalived/chk_mysql.sh
复制

4.防火墙开放端口

mysql 使用 3306 端口通信
service firewalld status
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
firewall-cmd --zone=public --list-ports
 
Keepalived是一个轻量级的HA集群解决方案，但开启防火墙后各节点无法感知其它节点的状态，各自都绑定了虚拟IP。网上很多文章讲要配置防火墙放过tcp/112，在CentOS7下是无效的，正确的做法是配置放过vrrp协议，方法如下：
 
firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
firewall-cmd --direct --permanent --add-rule ipv4 filter OUTPUT 0 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
firewall-cmd --reload
firewall-cmd --direct --get-all-rules
 
#Keepalived使用vrrp组播，默认地址是224.0.0.18，因此要配置防火墙放过。
#完成后再用ip addr查看，集群已经正常了，只有主节点绑定虚拟IP，备份节点不会绑定了。
复制

5.启动keepalived,测试高可用

启动主备keepalived

启动222结点keepalived
service keepalived start
检测vip
 ip a |grep 230
    inet 192.168.8.230/32 scope global eth0
 
启动224结点keepalived
service keepalived start
检测224vip
 ip a |grep 230
空


复制

测试主库停止，vip漂移状态

停止222结点mysql
service mysql stop
检测222vip
 ip a |grep 230
空
查看keepalived状态
service keepalived status
dead
 
检测224vip
 ip a |grep 230
    inet 192.168.8.230/32 scope global eth0
查看keepalived状态
service keepalived status
running
 
结论：主库MySQL停止，会停止该服务器上的keepalived，vip漂移到备库成功


复制

测试重启主库，vip漂移状态

重新启动222结点mysql和keepalived
service mysql start
service keepalived start
 
检测222vip
 ip a |grep 230
空 (确定222结点不会主动抢占vip)
查看keepalived状态
service keepalived status
running
 
结论：之前设置的配置文件满足主库宕机重启后vip不会漂移回来，还是在备库服务


复制

若是想要vip结点漂移到222结点

只需要在224上重启下keepalived即可
service keepalived restart
service keepalived status
running
检测224vip
ip a |grep 230
空 (vip自动漂移到222上)
 
检测222vip
ip a |grep 230
    inet 192.168.8.230/32 scope global eth0
    
在222结点重复上面操作，vip会重新漂移到224上
service keepalived restart
service keepalived status
running
检测222vip
ip a |grep 230
空 (vip自动漂移到222上)
 
检测224vip
ip a |grep 230
    inet 192.168.8.230/32 scope global eth0
 
结论：该配置符合在手动切换vip到其他结点，操作方便




复制