MySQL 高可用

架构组件作用核心优势MySQL 主主复制两台 MySQL 互为主从双向同步数据数据冗余、无单点、支持读写分离HAProxy数据库负载均衡、健康检查、连接管理读写分发、自动剔除异常节点Keepalived提供 VIP、故障漂移、HAProxy 高可用秒级切换、对业务透明整体访问链路客户端 → VIP(Keepalived) → HAProxy → MySQL Master1/Master2环境节点IP角色Master1192.168.1.101MySQL 主、HAProxy、Keepalived 主Master2192.168.1.102MySQL 主、HAProxy、Keepalived 备VIP192.168.1.200对外统一入口前置要求关闭防火墙 / 开放 3306、8080、VRRP 端口关闭 SELinux 或设为 Permissive时间同步NTP两台 MySQL 版本一致MySQL 双主复制1. Mater1 my.cnf 配置[mysqld] server-id 101 log-bin mysql-bin binlog-format ROW auto-increment-increment 2 auto-increment-offset 1 relay-log mysql-relay-bin log-slave-updates 1 sync-binlog 1 innodb_flush_log_at_trx_commit 12. Master2 my.cnf[mysqld] server-id 102 log-bin mysql-bin binlog-format ROW auto-increment-increment 2 auto-increment-offset 2 relay-log mysql-relay-bin log-slave-updates 1 sync-binlog 1 innodb_flush_log_at_trx_commit 13. 创建复制账号两台都执行CREATEUSERrepl192.168.1.%IDENTIFIEDBYRepl123;GRANTREPLICATIONSLAVEON*.*TOrepl192.168.1.%;FLUSHPRIVILEGES;4. 建立双向复制节点 1 执行show master status;记录 File Position节点 2 执行change mastertomaster_host192.168.1.101,master_userrepl,master_passwordRepl123,master_log_filemysql-bin.000001,master_log_pos154;startslave;showmasterstatus;反向配置节点 1 指向节点 2完成主主HAProxy 配置1. 安装yum-yinstallhaproxy2. haproxy.cfgvim /etc/haproxy/haproxy.cfgglobal log 127.0.0.1 local2 chroot /var/lib/haproxy pidfile /var/run/haproxy.pid user haproxy group haproxy daemon maxconn 4000 defaults # MySQL使用四层代理 mode tcp log global option tcplog retries 3 timeout connect 5s timeout client 50s timeout server 50s listen mysql-read-write # 监听 bind 0.0.0.0:3306 mode tcp balance roundrobin option mysql-check user haproxy_check # 配置MySQL服务 server mysql1 192.168.1.101:3306 check weight 1 server mysql2 192.168.1.102:3306 check weight 1 listen stats bind 0.0.0.0:8080 mode http stats enable stats uri /haproxy-stats stats auth admin:admin3. MySQL 健康检查用户CREATEUSERhaproxy_check%IDENTIFIEDBY;FLUSHPRIVILEGES;4. 启动systemctl start haproxy systemctlenablehaproxyKeepalived 配置1. 安装yuminstall-ykeepalived2. MySQL 检测脚本#!/bin/bashMYSQL_USERhaproxy_checkMYSQL_HOSTlocalhostMYSQL_PORT3306mysql -u$MYSQL_USER-h$MYSQL_HOST-P$MYSQL_PORT-eSELECT 1/dev/null21if[$?-ne0];thensystemctl stop haproxyexit1fiexit0chmodx /usr/local/bin/check_mysql.sh3. Mater1 keepalived.confglobal_defs { router_id mysql_ha_master } vrrp_script check_mysql { script /usr/local/bin/check_mysql.sh interval 2 weight -20 fall 3 rise 2 } vrrp_instance VI_1 { state MASTER interface eth0 virtual_router_id 51 priority 150 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.1.200/24 } track_script { check_mysql } }4. Master2 keepalived.confglobal_defs { router_id mysql_ha_backup } vrrp_script check_mysql { script /usr/local/bin/check_mysql.sh interval 2 weight -20 fall 3 rise 2 } vrrp_instance VI_1 { state BACKUP interface eth0 virtual_router_id 51 priority 100 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.1.200/24 } track_script { check_mysql } }5. 启动systemctl start keepalived systemctlenablekeepalived