目录一、整体架构说明1. MySQL 复制拓扑2. ProxySQL 集群架构3. Orchestrator Raft 集群架构4. 整体流程二、MySQL 8.0.22 前置配置1. GTID 强制配置所有节点2. 创建监控账号主库执行自动同步到所有从库1Orchestrator 拓扑账号2ProxySQL 监控账号 monitor3 业务账号读写 只读三、Orchestrator Raft 集群部署三台机器全部操作1. 安装 rpm 包root 用户执行2. 目录初始化mysql 用户执行3. 核心配置文件 ~/orchestrator/orchestrator.conf.json4. 环境变量配置 ~/.bashrc5. systemd 服务文件 /etc/systemd/system/orchestrator.service6. 服务启动初始化三台依次执行7. 发现 MySQL 所有拓扑任意一台执行8. Orchestrator 常用运维命令9. 连续切换测试清理命令一小时内多次故障切换使用四、ProxySQL 三实例集群完整配置三台机器全部操作1. 下载与安装2. 启动 proxysqlMySQL 用户执行1创建数据目录2创建配置文件3启动 proxysql3. 配置一台 ProxySQL1登录 ProxySQL 管理端2配置 ProxySQL4. 配置 ProxySQL 集群1配置集群认证三台节点逐一执行2配置同步参数三台节点逐一执行3配置集群节点列表三台节点逐一执行4等待业务配置自动同步5验证集群状态五、高可用故障模拟测试1. 单业务从库故障读高可用验证1持续读流量2模拟从库宕机3观察业务读流量4恢复故障从库5观察从库自动加回读组2. 主库宕机自动故障切换核心高可用验证1持续写流量2模拟主库完全宕机3观察业务写流量4验证新主状态5恢复旧主库为指向新主库的从库参考组件版本CentOS 7.9、MySQL 8.0.22、ProxySQL 2.6.5、Orchestrator 3.2.6拓扑架构一主两业务从 大数据只读从大数据从不参与选主ProxySQL 集群三节点172.20.1.23/16/48管理端口 6032业务端口 6033Orchestrator Raft 三节点172.20.1.23 (leader)、172.20.1.16 (follow)、172.20.1.48 (follow)数据库端口统一18251一、整体架构说明1. MySQL 复制拓扑节点地址角色是否参与选主用途172.20.1.23:18251当前主库是业务读写主节点172.20.1.16:18251业务从库 1是读负载、故障候选主库172.20.1.48:18251业务从库 2是读负载、故障候选主库172.18.4.88:18251大数据从库否离线数仓同步排除选主2. ProxySQL 集群架构三实例部署在三台 MySQL 机器实现配置自动同步读写分离分组HG1 (writer_hostgroup)主库分组所有业务读写账号默认路由 HG1。HG2 (reader_hostgroup)从库分组只读账号固定路由 HG2。集群同步表proxysql_servers 维护三节点管理地址配置自动同步。3. Orchestrator Raft 集群架构三实例高可用故障仲裁自动故障切换核心Raft 三实例部署在三台 MySQL 机器避免单点。配置黑名单 PromotionIgnoreHostnameFilters 屏蔽大数据从参与选主。故障切换模式Elect 自动选举最优从库升主GTID 自动同步复制拓扑。故障切换完成后不需要自定义脚本同步 ProxySQL 后端分组ProxySQL 通过 read_only 自动更新读写分组。4. 整体流程Orchestrator 持续探测所有 MySQL 实例健康、read_only、复制延迟。主库宕机Raft 集群仲裁自动选举新主修改所有业务从库复制指向新主。ProxySQL 通过 mysql_replication_hostgroups 自动区分读写节点实现读写分离。二、MySQL 8.0.22 前置配置1. GTID 强制配置所有节点gtid_modeON enforce_gtid_consistencyON log_binmysql-bin binlog_formatROW log_slave_updatesON2. 创建监控账号主库执行自动同步到所有从库1Orchestrator 拓扑账号CREATE USER IF NOT EXISTS orchestrator% IDENTIFIED BY 123456; GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, RELOAD ON *.* TO orchestrator%; GRANT SELECT ON mysql.slave_master_info TO orchestrator%; GRANT SELECT ON mysql.slave_relay_log_info TO orchestrator%; FLUSH PRIVILEGES; SHOW GRANTS FOR orchestrator%;2ProxySQL 监控账号 monitorCREATE USER IF NOT EXISTS monitor% IDENTIFIED BY 123456; GRANT REPLICATION SLAVE, REPLICATION CLIENT, PROCESS, SELECT ON *.* TO monitor%; FLUSH PRIVILEGES;3 业务账号读写 只读-- 读写账号示例 vv_app CREATE USER IF NOT EXISTS vv_app% IDENTIFIED BY 业务密码; GRANT ALL ON business.* TO vv_app%; -- 只读账号 read_only_user CREATE USER IF NOT EXISTS read_only_user% IDENTIFIED BY 只读密码; GRANT SELECT ON business.* TO read_only_user%; FLUSH PRIVILEGES;三、Orchestrator Raft 集群部署三台机器全部操作1. 安装 rpm 包root 用户执行rpm -ivh orchestrator-3.2.6-1.x86_64.rpm rpm -ivh orchestrator-client-3.2.6-1.x86_64.rpm2. 目录初始化mysql 用户执行su - mysql mkdir -p ~/orchestrator/{scripts,data,log,raft} chmod 700 ~/orchestrator/data3. 核心配置文件 ~/orchestrator/orchestrator.conf.json注意三台机器仅修改 RaftBind 为本机 IP其余配置完全一致。{ Debug: false, ListenAddress: 0.0.0.0:3000, AuthenticationMethod: none, MySQLUseSSL: false, MySQLSSLSkipVerify: true, AutoFailover: true, MySQLTopologyUser: orchestrator, MySQLTopologyPassword: 123456, BackendDB: sqlite, SQLite3DataFile: /home/mysql/orchestrator/data/orchestrator.sqlite, PromotionIgnoreHostnameFilters: [ ^172\\.18\\.4\\.88:18251$ ], RecoverMasterClusterFilters: [*], RecoverIntermediateMasterClusterFilters: [*], AllowAnyMasterFailover: true, FailMasterPromotionIfSQLThreadNotUpToDate: false, MaxReplicationLagBeforeFailover: 0, FailoverGracePeriod: 5, PreFailoverProcesses: [], PostMasterFailoverProcesses: [], PostFailoverProcesses: [], Coordinator: true, InstancePollSeconds: 3, UnreachableInstanceAbandonSeconds: 5, DetectSemiSync: false, SemiSyncMustBePresentForPromotion: false, RaftEnabled: true, RaftDataDir: /home/mysql/orchestrator/raft, DefaultRaftPort: 10008, RaftBind: 本机IP分别填172.20.1.23/16/48, RaftNodes: [ 172.20.1.23, 172.20.1.16, 172.20.1.48 ], HostnameResolveMethod: none, MySQLHostnameResolveMethod: , DiscoverByShowSlaveHosts: false, RejectHostnameResolvePattern: , SkipBinlogServerUnresolveCheck: true, ExpiryHostnameResolvesMinutes: 60, UseSuperReadOnly: false, FailoverType: Elect, SkipMaxScaleCheck: true }4. 环境变量配置 ~/.bashrcexport PATH$PATH:/usr/local/orchestrator export ORCHESTRATOR_APIhttp://127.0.0.1:3000/api source ~/.bashrc5. systemd 服务文件 /etc/systemd/system/orchestrator.service[Unit] DescriptionOrchestrator Raft三节点集群 MySQL自动故障切换 Documentationhttps://github.com/openark/orchestrator Aftersyslog.target network.target [Service] Typesimple WorkingDirectory/usr/local/orchestrator ExecStart/usr/local/orchestrator/orchestrator -config /home/mysql/orchestrator/orchestrator.conf.json http ExecReload/bin/kill -HUP $MAINPID LimitNOFILE16384 Usermysql Groupmysql [Install] WantedBymulti-user.target6. 服务启动初始化三台依次执行# 重载systemd systemctl daemon-reload systemctl reset-failed orchestrator # 清理旧数据首次部署必执行 systemctl stop orchestrator rm -rf /home/mysql/orchestrator/data/* rm -rf /home/mysql/orchestrator/raft/* # 启动服务 systemctl start orchestrator systemctl enable orchestrator sleep 10 systemctl status orchestrator # 验证版本 orchestrator -version7. 发现 MySQL 所有拓扑任意一台执行orchestrator -config /home/mysql/orchestrator/orchestrator.conf.json --ignore-raft-setup -c discover -i 172.20.1.23:18251 orchestrator -config /home/mysql/orchestrator/orchestrator.conf.json --ignore-raft-setup -c discover -i 172.20.1.16:18251 orchestrator -config /home/mysql/orchestrator/orchestrator.conf.json --ignore-raft-setup -c discover -i 172.20.1.48:18251 orchestrator -config /home/mysql/orchestrator/orchestrator.conf.json --ignore-raft-setup -c discover -i 172.18.4.88:182518. Orchestrator 常用运维命令# 查看Raft leader节点 curl -s http://127.0.0.1:3000/api/status | jq . # 查看集群所有实例 orchestrator -config /home/mysql/orchestrator/orchestrator.conf.json --ignore-raft-setup -c all-instances # 查看集群主从拓扑 orchestrator -config /home/mysql/orchestrator/orchestrator.conf.json --ignore-raft-setup -c topology -i 172.20.1.23:18251 # 查看所有集群 orchestrator -config /home/mysql/orchestrator/orchestrator.conf.json --ignore-raft-setup -c clusters # 实时日志输出 journalctl -u orchestrator -f | tee -a /home/mysql/orchestrator/log/orchestrator_failover.log9. 连续切换测试清理命令一小时内多次故障切换使用# 查询未过期故障记录 sqlite3 /home/mysql/orchestrator/data/orchestrator.sqlite select recovery_id, hostname, port, in_active_period, acknowledged from topology_recovery; # 清除冷却期记录允许再次故障切换 sqlite3 /home/mysql/orchestrator/data/orchestrator.sqlite DELETE FROM topology_recovery WHERE in_active_period1; # 校验集群冷却状态 curl -s http://127.0.0.1:3000/api/cluster/172.20.1.23:18251 | jq .[] | {ClusterName: .ClusterName, InActivePeriod: .InActivePeriod}四、ProxySQL 三实例集群完整配置三台机器全部操作1. 下载与安装# 下载安装包 https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/centos/7/proxysql-2.6.5-1-centos7.x86_64.rpm # 安装依赖 yum install -y perl-DBI perl-DBD-MySQL # 安装 proxysql2. 启动 proxysqlMySQL 用户执行1创建数据目录mkdir proxysql_data2创建配置文件配置文件 ~/proxysql.cnf 内容如下datadir/home/mysql/proxysql_data errorlog/home/mysql/proxysql_data/proxysql.log admin_variables { admin_credentialsadmin:123456 mysql_ifaces127.0.0.1:6032;/tmp/proxysql_admin.sock } mysql_variables { threads4 max_connections2048 default_query_delay0 default_query_timeout36000000 have_compresstrue poll_timeout2000 interfaces0.0.0.0:6033;/tmp/proxysql.sock default_schemainformation_schema stacksize1048576 server_version8.0.22 connect_timeout_server3000 monitor_usernamemonitor monitor_password123456 monitor_history600000 monitor_connect_interval60000 monitor_ping_interval10000 monitor_read_only_interval1500 monitor_read_only_timeout500 ping_interval_server_msec120000 ping_timeout_server500 commands_statstrue sessions_sorttrue connect_retries_on_failure10 } mysql_servers ( ) mysql_users: ( ) mysql_query_rules: ( ) scheduler ( ) mysql_replication_hostgroups ( )3启动 proxysqlproxysql -c /home/mysql/proxysql.cnf3. 配置一台 ProxySQL1登录 ProxySQL 管理端mysql -u admin -p123456 -h 127.0.0.1 -P6032 --promptAdmin 2配置 ProxySQL-- 创建 MySQL 服务器组 INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,172.20.1.23,18251); INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,172.20.1.16,18251); INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,172.20.1.48,18251); LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; select * from mysql_servers; select * from runtime_mysql_servers; -- 配置监控用户 UPDATE global_variables SET variable_valuemonitor WHERE variable_namemysql-monitor_username; UPDATE global_variables SET variable_valuerSeMskx2H696Kx2nR9us WHERE variable_namemysql-monitor_password; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK; select * from global_variables where variable_name in (mysql-monitor_username,mysql-monitor_password); select * from runtime_global_variables where variable_name in (mysql-monitor_username,mysql-monitor_password); -- 配置复制主机组 INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,cluster1); LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; select * from mysql_replication_hostgroups; select * from runtime_mysql_replication_hostgroups; -- 添加 MySQL 用户 INSERT INTO mysql_users(username,password,default_hostgroup) VALUES (vv_app,*73D193E322F67A70A9C5A7C89F009E3D63ED6C33,1); INSERT INTO mysql_users(username,password,default_hostgroup) VALUES (read_only_user,*73D193E322F67A70A9C5A7C89F009E3D63ED6C33,2); LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK; select username, password, active, default_hostgroup, backend, frontend from mysql_users; select username, password, active, default_hostgroup, backend, frontend from runtime_mysql_users; -- 配置参数 -- ProxySQL 2.6.5 生产参数优化脚本 -- 适用架构一主两从 OLTP在线业务双账号读写分离依靠default_hostgroup分流、不使用query_rules -- 优化目标1.故障快速感知、快速失败防雪崩 2.连接池稳定无会话污染 3.限制长事务保护MySQL -- 注意带横杠 mysql-xxx 类监控/后端连接参数仅支持UPDATE global_variables不支持SET语法 -- -- 1. 连接重试 后端TCP连接超时优化故障场景快速失败避免工作线程堆积 UPDATE global_variables SET variable_value3 WHERE variable_namemysql-connect_retries_on_failure; -- 后端MySQL连接失败最大重试3次防止无限重试压垮故障节点 UPDATE global_variables SET variable_value100 WHERE variable_namemysql-connect_retries_delay; -- 每次连接失败重试间隔100ms简单退避平滑瞬时冲击 UPDATE global_variables SET variable_value1000 WHERE variable_namemysql-connect_timeout_server; -- 建立TCP连接后端MySQL超时1000ms(1秒)卡死节点快速抛错给应用不阻塞线程 UPDATE global_variables SET variable_value60000 WHERE variable_namemysql-ping_interval_server_msec; -- 后端空闲连接60秒发送一次SELECT 1保活规避内网防火墙长时间空闲断链 -- 2. 后端节点健康监控探测仅3台实例3秒探测兼顾速度与数据库负载 UPDATE global_variables SET variable_value3000 WHERE variable_namemysql-monitor_connect_interval; -- 每3秒使用monitor账号重连所有节点快速识别网络中断、节点宕机 UPDATE global_variables SET variable_value3000 WHERE variable_namemysql-monitor_ping_interval; -- 每3秒执行ping(SELECT 1)检测节点卡死、SQL执行阻塞 UPDATE global_variables SET variable_value1500 WHERE variable_namemysql-monitor_read_only_interval; -- 每1.5秒查询read_only/super_read_only极速识别主从切换标准黄金平衡值 -- 3. 多路复用连接池稳定优化解决会话污染、长期连接内存碎片问题 UPDATE global_variables SET variable_valuetrue WHERE variable_namemysql-autocommit_false_not_reusable; -- 未提交事务的连接禁止放回连接池复用避免事务隔离级别、会话变量串数据 UPDATE global_variables SET variable_value200 WHERE variable_namemysql-max_stmts_per_connection; -- 单条后端连接最多复用200次SQL后自动重置平衡复用性能与会话干净度 UPDATE global_variables SET variable_value3600000 WHERE variable_namemysql-connection_max_age_ms; -- 后端连接最长生命周期1小时到期强制销毁重建清理残留状态与内存碎片 -- 4. 长事务强制拦截防止锁表、阻塞主从复制、长期占用连接 UPDATE global_variables SET variable_value600000 WHERE variable_namemysql-max_transaction_idle_time; -- 事务开启后空闲超过10分钟自动kill释放连接与行锁 UPDATE global_variables SET variable_value1800000 WHERE variable_namemysql-max_transaction_time; -- 单个事务总执行时长超过30分钟强制断开拦截超大慢事务 -- 5. OLTP故障节点隔离自愈策略 UPDATE global_variables SET variable_value10 WHERE variable_namemysql-shun_recovery_time_sec; -- 连续失败被屏蔽的节点10秒后自动解除隔离兼顾防震荡与快速自愈 -- 将配置加载到运行内存即时生效 LOAD MYSQL VARIABLES TO RUNTIME; -- 持久化至本地sqlite磁盘文件ProxySQL重启配置不丢失 SAVE MYSQL VARIABLES TO DISK; -- 参数校验SQL执行后核对是否全部生效 SELECT variable_name, variable_value FROM global_variables WHERE variable_name IN ( mysql-connect_retries_on_failure, mysql-connect_retries_delay, mysql-connect_timeout_server, mysql-ping_interval_server_msec, mysql-monitor_connect_interval, mysql-monitor_ping_interval, mysql-monitor_read_only_interval, mysql-autocommit_false_not_reusable, mysql-max_stmts_per_connection, mysql-connection_max_age_ms, mysql-max_transaction_idle_time, mysql-max_transaction_time, mysql-shun_recovery_time_sec ); SELECT variable_name, variable_value FROM runtime_global_variables WHERE variable_name IN ( mysql-connect_retries_on_failure, mysql-connect_retries_delay, mysql-connect_timeout_server, mysql-ping_interval_server_msec, mysql-monitor_connect_interval, mysql-monitor_ping_interval, mysql-monitor_read_only_interval, mysql-autocommit_false_not_reusable, mysql-max_stmts_per_connection, mysql-connection_max_age_ms, mysql-max_transaction_idle_time, mysql-max_transaction_time, mysql-shun_recovery_time_sec );4. 配置 ProxySQL 集群1配置集群认证三台节点逐一执行mysql -u admin -p123456 -h 127.0.0.1 -P 6032 -- 设置集群通信用户名和密码三台必须完全一致 SET admin-cluster_usernamecluster_sync; SET admin-cluster_passwordSyncPass2024; -- 将集群用户添加到管理凭证列表保留原有 admin SET admin-admin_credentialsadmin:123456;cluster_sync:SyncPass2024; -- 加载并持久化 LOAD ADMIN VARIABLES TO RUNTIME; SAVE ADMIN VARIABLES TO DISK;2配置同步参数三台节点逐一执行-- 检查间隔毫秒 SET admin-cluster_check_interval_ms1000; -- 详细状态检查频率 SET admin-cluster_check_status_frequency10; -- 同步阈值实验环境设为1生产建议3 SET admin-cluster_mysql_servers_diffs_before_sync1; SET admin-cluster_mysql_users_diffs_before_sync1; SET admin-cluster_mysql_query_rules_diffs_before_sync1; SET admin-cluster_proxysql_servers_diffs_before_sync1; -- 启用自动持久化 SET admin-cluster_mysql_servers_save_to_disktrue; SET admin-cluster_mysql_users_save_to_disktrue; SET admin-cluster_mysql_query_rules_save_to_disktrue; SET admin-cluster_proxysql_servers_save_to_disktrue; LOAD ADMIN VARIABLES TO RUNTIME; SAVE ADMIN VARIABLES TO DISK;3配置集群节点列表三台节点逐一执行-- 每台机器都执行 DELETE FROM proxysql_servers; INSERT INTO proxysql_servers (hostname, port) VALUES (172.20.1.23, 6032), (172.20.1.16, 6032), (172.20.1.48, 6032); LOAD PROXYSQL SERVERS TO RUNTIME; SAVE PROXYSQL SERVERS TO DISK;4等待业务配置自动同步完成前三步后等待 30-60 秒。业务配置mysql_servers、mysql_users、mysql_query_rules 等会自动从老节点同步到其他节点。5验证集群状态-- 验证1节点列表应有3行 SELECT * FROM proxysql_servers; -- 验证2同步状态应有21行 7模块 × 3节点 SELECT hostname, name, version, checksum, diff_check FROM stats_proxysql_servers_checksums ORDER BY hostname, name; -- 验证3业务配置数量应与老节点一致 SELECT COUNT(*) FROM mysql_servers; SELECT COUNT(*) FROM mysql_users; SELECT COUNT(*) FROM mysql_query_rules; -- 验证4节点间连接状态 SELECT hostname, port, response_time_ms, Uptime_s, Queries FROM stats_proxysql_servers_metrics; -- 验证5客户端连接状态 SELECT uuid, hostname, port, admin_mysql_ifaces FROM stats_proxysql_servers_clients_status;预期结果验证项预期结果验证13 行验证221 行所有diff_check 0验证3三台节点数量完全一致验证4所有节点都有正常的Uptime_s和响应时间验证5每个节点都能看到其他节点五、高可用故障模拟测试测试前置说明业务入口ProxySQL 业务端口 6033。读流量账号read_only_user路由 HG2 从库。写流量账号vv_app路由 HG1 主库。Orchestrator 屏蔽大数据从 172.18.4.88故障切换不会选为新主。1. 单业务从库故障读高可用验证1持续读流量while true; do mysql -h172.20.1.23 -P6033 -uread_only_user -p对应密码 -e SELECT hostname, server_id, NOW(); 21 sleep 1 done2模拟从库宕机# 在另一个终端杀掉一个从库同时强杀 mysqld 与 orchestrator pkill -9 mysqld || true pkill -9 orchestrator || true echo 已强制终止 mysqld、orchestrator 进程3观察业务读流量读查询会短暂失败约 1-3 秒。然后自动切换到另一个从库。业务侧无需任何操作。4恢复故障从库mysqld_safe --defaults-file/home/mysql/my_18251.cnf --skip-slave-start mysql -uroot -p123456 -S /data/18251/mysqldata/mysql.sock reset master; reset slave all; change master to master_host主库IP, master_port18251, master_userrepl, master_password123456, MASTER_AUTO_POSITION1; start slave; show slave status\G5观察从库自动加回读组ProxySQL 检测到从库恢复约 3 秒。自动加回 HG2读组。读流量重新分布。2. 主库宕机自动故障切换核心高可用验证1持续写流量while true; do mysql -h172.20.1.23 -P6033 -uvv_app -p业务密码 -e CREATE DATABASE IF NOT EXISTS test_failover_$(date %s); DROP DATABASE test_failover_$(date %s); 21 echo $(date %H:%M:%S) sleep 1 done2模拟主库完全宕机# 同时强制杀死 mysqld 与 orchestrator pkill -9 mysqld || true pkill -9 orchestrator || true echo 已强制终止 mysqld、orchestrator 进程3观察业务写流量写操作会失败约 1-5 秒。Orchestrator 自动切换主库。. 新主库接管写入。. 剩余从库自动指向新主库。故障切换观测流程Orchestrator Raft 集群探测主库失联等待故障仲裁冷却 5s。自动选举剩余业务从库中最优节点升为新主库关闭 read_onlyOFF。另一台存活从库自动 CHANGE MASTER 指向新主GTID 自动同步。ProxySQL monitor 探测新主 read_only 状态自动将新主划入 HG1 写组旧从库划入 HG2 读组。业务写流量短暂 1~5s 失败自动恢复写入新主。4验证新主状态mysql -h172.20.1.16 -P6033 -uvv_app -p业务密码 -e SELECT hostname, read_only;read_only0 代表当前节点为新主库。5恢复旧主库为指向新主库的从库启动旧主 mysqldOrchestrator 自动将旧主配置为新主的从库。ProxySQL 探测旧主 read_onlyON自动加入 HG2 读组。完整读写分离架构自动复原无需人工修改 ProxySQL 配置。参考ProxySQL一—— 实现 MySQL 读写分离、读负载均衡ProxySQL六—— 搭建 ProxySQL 集群