目录
一、概述
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节点，支持动态调整负载均衡策略（如权重、轮询）
二、资源清单
| 主机 | 操作系统 | IP地址 | 应用 |
| Master1 | OpenEuler 24.03 | 192.168.16.142 | Mysql8 |
| Master2 | OpenEuler 24.03 | 192.168.16.143 | Mysql8 |
| Keepalived1 | OpenEuler 24.03 | 192.168.16.144 | Keepalived、haproxy |
| Keepalived2 | OpenEuler 24.03 | 192.168.16.145 | Keepalived、haproxy |
三、案例实施
1.修改主机名
hostnamectl set-hostname master1
hostnamectl set-hostname master2
hostnamectl set-hostname Keepalived1
hostnamectl set-hostname Keepalived22.安装MySQL数据库（Master1、Master2）
dnf install -y tartar zxf autoinstall-mysql.tar.gzcd autoinstall-mysql./start.shcd
source /etc/profile
mysql -uroot -p'临时密码'
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql -uroot -p1234563.配置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
- 重启服务（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
- Master1
4.安装haproxy（keepalived1、keepalived2）
- 安装haproxy
dnf install -y haproxy - 编辑haproxy配置文件
vi /etc/haproxy/haproxy.cfg globallog 127.0.0.1 local2chroot /var/lib/haproxypidfile /var/run/haproxy.piduser haproxygroup haproxydaemonmaxconn 4000defaultsmode tcplog globaloption tcplogoption dontlognullretries 3timeout http-request 5stimeout queue 1mtimeout connect 5stimeout client 1mtimeout server 1mtimeout http-keep-alive 5stimeout check 5smaxconn 3000listen mysqlbind 0.0.0.0:3306 # 显式指定监听地址和端口balance leastconn # 负载均衡算法server mysql1 192.168.16.142:3306 check port 3306 maxconn 300server 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 - GPLCopyright (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 keepalivedglobal_defs {router_id r1 }vrrp_script chk_haproxy {script "/etc/keepalived/chk.sh"interval 2 }vrrp_instance VI_1 {state BACKUPnopreemptinterface ens33virtual_router_id 51priority 100advert_int 1authentication {auth_type PASSauth_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 fichmod +x /etc/keepalived/chk.sh systemctl start keepalived systemctl enable keepalived - keepalived2配置
vi /etc/keepalived/keepalived.conf! Configuration File for keepalivedglobal_defs {router_id r2 }vrrp_script chk_haproxy {script "/etc/keepalived/chk.sh"interval 2 }vrrp_instance VI_1 {state BACKUPnopreemptinterface ens33virtual_router_id 51priority 99advert_int 1authentication {auth_type PASSauth_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 fichmod +x /etc/keepalived/chk.sh systemctl start keepalived systemctl enable keepalived
- keepalived1配置
- 在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 - GPLCopyright (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 - GPLCopyright (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 - GPLCopyright (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>