目錄
一、概述
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>
