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

古城微笑少年丶 2022-11-17 05:28 152阅读 0赞

架构：

<table> 
 <thead> 
  <tr> 
   <th>结点</th> 
   <th>版本</th> 
   <th>IP</th> 
   <th>端口</th> 
   <th>主机名</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>主</th> 
   <td>MySQL8.0</td> 
   <td>192.168.8.222</td> 
   <td>3306</td> 
   <td>cdh1</td> 
  </tr> 
  <tr> 
   <th>备</th> 
   <td>MySQL8.0</td> 
   <td>192.168.8.224</td> 
   <td>3306</td> 
   <td>cdh3</td> 
  </tr> 
  <tr> 
   <th>vip</th> 
   <td>&nbsp;</td> 
   <td>192.168.8.230</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

### 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到其他结点，操作方便

# 参考文件 #

MySQL 增强半同步

[https://blog.csdn.net/u010520724/article/details/108069178][https_blog.csdn.net_u010520724_article_details_108069178]

MySQL GTID+row+增强半同步

[https://blog.csdn.net/u010033674/article/details/104320973][https_blog.csdn.net_u010033674_article_details_104320973]

mysql双主+keepalived

[https://www.cnblogs.com/benjamin77/p/8682360.html][https_www.cnblogs.com_benjamin77_p_8682360.html]

[https_blog.csdn.net_u010520724_article_details_108069178]: https://blog.csdn.net/u010520724/article/details/108069178
[https_blog.csdn.net_u010033674_article_details_104320973]: https://blog.csdn.net/u010033674/article/details/104320973
[https_www.cnblogs.com_benjamin77_p_8682360.html]: https://www.cnblogs.com/benjamin77/p/8682360.html