实现MySQL高可用性：从原理到实践

目录

一、概述

1.什么是MySQL高可用

2.方案组成

3.优势

二、资源清单

三、案例实施

1.修改主机名

2.安装MySQL数据库（Master1、Master2）

3.配置mysql双主复制

4.安装haproxy（keepalived1、keepalived2）

5.安装keepalived（keepalived1、keepalived2）

6.测试故障转移

一、概述

1.什么是MySQL高可用

MySQL高可用是指通过冗余设计，确保数据库服务在单点故障、网络中断或硬件随换等异常情况下，仍能持续对外提供服务，同时保证数据一致性。其核心目标实现‘零停机、零数据丢失’的业务连续性

2.方案组成

• MySQL主主复制：两台Mysql实例互为主从，双向同步数据，均支持同步数据，均支持读写操作，提供冗余和扩展能力
• Keepalivend：通过VRRP协议管理虚拟IP（VIP），监控MySQL状态，故障时自动将VIP漂移至存活节点，确保服务地址不变
• HAProxy：作为反向代理和负载均衡器，将流量分发至MySQL节点，支持监控检查，读写分离和故障节点自动删除

3.优势

• 高可用性：Keeplived实现秒级故障切换，HAProxy健康检查确保流量路由到正常节点，避免单点故障
• 读写扩展：主主架构支持双节点并发写入，提高写入性能；HAProxy可配置读写分离，利用备份节点分担读压力
• 灵活扩展：可横向扩展HAProxy和MySQL节点，支持动态调整负载均衡策略（如权重、轮询）

二、资源清单

三、案例实施

1.修改主机名

hostnamectl set-hostname master1
hostnamectl set-hostname master2
hostnamectl set-hostname Keepalived1
hostnamectl set-hostname Keepalived2

2.安装MySQL数据库（Master1、Master2）

dnf install -y tar

tar zxf autoinstall-mysql.tar.gz

 cd autoinstall-mysql
 ./start.sh
 cd
source /etc/profile
mysql -uroot -p'临时密码'
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql -uroot -p123456

3.配置mysql双主复制

• 二进制日志配置
  • Master1

    vi /etc/my.cnf
    
    [mysqld]
    log-bin=master1-bin    #启用二进制日志并指定其存储路径
    binlog_format = MIXED    #定义二进制日志的记录格式为混合模式
    server-id=1    #为mysql实例分配一个唯一的服务器标识符

  • Master2

    vi /etc/my.cnf
    
    [mysqld]
    log-bin=master2-bin    #启用二进制日志并指定其存储路径
    binlog_format = MIXED    #定义二进制日志的记录格式为混合模式
    server-id=2   #为mysql实例分配一个唯一的服务器标识符

• 重启服务（Master1、Master2）

  systemctl restart mysqld

• 登录mysql程序，给从服务器授权（Master1、Master2）

  mysql -uroot -p123456
  
  #创建用户
  CREATE USER 'myslave'@'%' IDENTIFIED BY '123456';
  #授权同步给所有用户
  GRANT REPLICATION SLAVE ON  *.* TO 'myslave'@'%';
  #修改密码
  ALTER USER 'myslave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
  #刷新配置
  FLUSH PRIVILEGES;
  #查看状态 
  show master status;
  #Master1
  +--------------------+----------+--------------+------------------+-------------------+
  | File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  +--------------------+----------+--------------+------------------+-------------------+
  | master1-bin.000001 |     1147 |              |                  |                   |
  +--------------------+----------+--------------+------------------+-------------------+
  1 row in set (0.00 sec)
  
  #Master2
  +--------------------+----------+--------------+------------------+-------------------+
  | File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  +--------------------+----------+--------------+------------------+-------------------+
  | master2-bin.000001 |     1150 |              |                  |                   |
  +--------------------+----------+--------------+------------------+-------------------+
  1 row in set (0.00 sec)

• 登录mysql，配置同步
  • Master1

    mysql -uroot -p123456
    #连接主节点
    change master to master_host='192.168.16.143', master_user='myslave', master_password='123456',master_log_file='master2-bin.000001',master_log_pos=1150;
    #开启同步
    start slave;
    #查看状态
    show slave status\G
    
    #查看出来显示
     #            Slave_IO_Running: Yes
     #           Slave_SQL_Running: Yes

  • Master2

    mysql -uroot -p123456
    #连接主节点
    change master to master_host='192.168.16.142', master_user='myslave', master_password='123456',master_log_file='master1-bin.000001',master_log_pos=1147;
    #开启同步
    start slave;
    #查看状态
    show slave status\G
    
    #查看出来显示
     #            Slave_IO_Running: Yes
     #           Slave_SQL_Running: Yes

4.安装haproxy（keepalived1、keepalived2）

• 安装haproxy

  dnf install  -y haproxy

• 编辑haproxy配置文件

  vi /etc/haproxy/haproxy.cfg 
  
  global
      log         127.0.0.1 local2
      chroot      /var/lib/haproxy
      pidfile     /var/run/haproxy.pid
      user        haproxy
      group       haproxy
      daemon
      maxconn     4000
  
  defaults
      mode                    tcp
      log                     global
      option                  tcplog
      option                  dontlognull
      retries                 3
      timeout http-request    5s
      timeout queue           1m
      timeout connect         5s
      timeout client          1m
      timeout server          1m
      timeout http-keep-alive 5s
      timeout check           5s
      maxconn                 3000
  
  listen mysql
      bind 0.0.0.0:3306          # 显式指定监听地址和端口
      balance leastconn           # 负载均衡算法
      server mysql1 192.168.16.142:3306 check port 3306 maxconn 300
      server mysql2 192.168.16.143:3306 check port 3306 maxconn 300
  
  
  
  #mode tcp:表示tcp代理
  #listen mysql 0.0.0.0:3306:创建一个名为mysql的监听服务
  #bind 0.0.0.0:3306:绑定到所有网卡的3306端口，作为流量入口
  #balance leastcnn:指定使用最少连接数分配在请求，将新连接导向当前活跃最少的后端服务器，避免单点过载
  #Server声明两个MySqL服务器节点mysql1和mysql2，分别指192.168.16.142：3306和192.168.16.143：3306
  #check prot 3306：通过检查节点的3306端口是否响应，判断存活状态
  #maxconn 300 ：限制每个后端节点的最大并发连接数300
  

• 检查配置文件并启动服务

  haproxy -c -f /etc/haproxy/haproxy.cfg 
  systemctl start haproxy
  systemctl enable haproxy
  ss -nlpt | grep 3306

• 测试

  [root@master1 ~]# mysql -umyslave -p123456 -h192.168.16.144 -P3306
  mysql: [Warning] Using a password on the command line interface can be insecure.
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 16
  Server version: 8.0.36 MySQL Community Server - GPL
  
  Copyright (c) 2000, 2024, Oracle and/or its affiliates.
  
  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.
  
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
  mysql> exit

5.安装keepalived（keepalived1、keepalived2）

• 安装keepalived

  dnf install -y keepalived

• 编辑keepalived配置文件
  • keepalived1配置

    vi /etc/keepalived/keepalived.conf
    
    ! Configuration File for keepalived
    
    global_defs {
       router_id r1
    }
    
    vrrp_script chk_haproxy {
        script "/etc/keepalived/chk.sh"
        interval 2
    }
    
    vrrp_instance VI_1 {
        state BACKUP
        nopreempt
        interface ens33
        virtual_router_id 51
        priority 100
        advert_int 1
        authentication {
            auth_type PASS
            auth_pass 1111
        }
        virtual_ipaddress {
            192.168.16.100
        }
    
        track_script {
            chk_haproxy
        }
    
        notify_backup "/etc/init.d/haproxy restart"
        notify_fault "/etc/init.d/haproxy stop"
    }

  • 添加监控脚本并启动keepailved

    vi /etc/keepalived/chk.sh
    #!/bin/bash
    #
    if [ $(ps -C haproxy --no-header | wc -l) -eq 0 ]; then
           /etc/init.d/keepalived stop
    fi
    
    chmod +x /etc/keepalived/chk.sh 
    systemctl start keepalived
    systemctl enable keepalived

  • keepalived2配置

    vi /etc/keepalived/keepalived.conf
    
    ! Configuration File for keepalived
    
    global_defs {
       router_id r2
    }
    
    vrrp_script chk_haproxy {
        script "/etc/keepalived/chk.sh"
        interval 2
    }
    
    vrrp_instance VI_1 {
        state BACKUP
        nopreempt
        interface ens33
        virtual_router_id 51
        priority 99
        advert_int 1
        authentication {
            auth_type PASS
            auth_pass 1111
        }
        virtual_ipaddress {
            192.168.16.100
        }
    
        track_script {
            chk_haproxy
        }
    
        notify_backup "/etc/init.d/haproxy restart"
        notify_fault "/etc/init.d/haproxy stop"
    }

  • 添加监控脚本并启动keepailved

    vi /etc/keepalived/chk.sh
    #!/bin/bash
    #
    if [ $(ps -C haproxy --no-header | wc -l) -eq 0 ]; then
           /etc/init.d/keepalived stop
    fi
    
    chmod +x /etc/keepalived/chk.sh 
    systemctl start keepalived
    systemctl enable keepalived

• 在keepalived1上查看VIP

  ip a
  
    #inet 192.168.16.100/32 scope global ens33
    #valid_lft forever preferred_lft forever

• 使用VIP连接Mysql（Master1）

  [root@master1 ~]# mysql -umyslave -p123456 -P3306 -h192.168.16.100
  mysql: [Warning] Using a password on the command line interface can be insecure.
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 16
  Server version: 8.0.36 MySQL Community Server - GPL
  
  Copyright (c) 2000, 2024, Oracle and/or its affiliates.
  
  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.
  
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
  mysql> 

6.测试故障转移

• 关闭master1主机，测试使用vip能否正常访问mysql数据库(Master2)

  ping 192.168.16.142
  
  [root@master1 ~]# mysql -umyslave -p123456 -P3306 -h192.168.16.100
  mysql: [Warning] Using a password on the command line interface can be insecure.
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 16
  Server version: 8.0.36 MySQL Community Server - GPL
  
  Copyright (c) 2000, 2024, Oracle and/or its affiliates.
  
  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.
  
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
  mysql> 

• 关闭keeplived1，测试使用vip能否正常访问mysql数据库(Master2)

  ping 192.168.16.142
  
  [root@master1 ~]# mysql -umyslave -p123456 -P3306 -h192.168.16.100
  mysql: [Warning] Using a password on the command line interface can be insecure.
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 16
  Server version: 8.0.36 MySQL Community Server - GPL
  
  Copyright (c) 2000, 2024, Oracle and/or its affiliates.
  
  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.
  
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
  mysql>