先把 MySQL 主从复制搭建好让数据能自动同步再用 ProxySQL 做读写分离才有意义。一 主从复制的原理主库 (二进制 会记录增删改)创建授权账号,并且开启binlog日志,告知从机的二进制位置节点从库IO线程 --- 主库的二进制日志start/stop 开机关闭 slave二 Gtid方式实现主从复制GTID工作原理:主库更新数据时生成GTID记录到binlog从库I/O线程将变更写入relay log从库SQL线程获取GTID检查本地binlog记录如有记录则忽略无记录则执行并记录到binlogGTID是复制协议让主从同步更可靠、更容易定位同步位置、故障切换更方便GTID 模式下从库严禁写入否则会造成 GTID 不连续、复制中断。环境准备:删除主机 mysql 的uid号:rm -rf /var/lib/mysql/auto.cnf做主机名、IP、主机名解析:hostnamectl set-hostname mysql-master在/etc/hosts 做ip域名解析关闭防火墙和SELinuxsystemctl disable --now firewalld \ setenforce 0 \ sed -ri /^SELINUX/cSELINUXdisabled /etc/selinux/config确保时钟同步dnf -y install chronyvim /etc/chrony.conf注释默认的 pool 服务器添加阿里云 NTP 服务器server ntp.aliyun.com iburstsystemctl start chronyd \ systemctl enable chronyd三 主服务器配置 - MySql-master配置文件 - my.cnf启用Gtid模式:添加以下配置 server-id1 # 服务器唯一标识主从不能重复 log-binmysql-bin # 开启二进制日志主库必须开启【可选】 gtid_modeON # 启用GTID模式 enforce_gtid_consistencyON # 强制GTID一致性 #mysql_native_passwordON # 8.4版本重启MySQL# 重启 systemctl restart mysqld进入mysql操作:创建用于复制的专用用户 (可以自己指定用户名和密码) -- 建议%改为从服务器的IPCREATE USER repl% IDENTIFIED WITH mysql_native_password by Repl123;授权repl%GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl%;刷新权限FLUSH PRIVILEGES;备份主库数据并同步到从库如果从库加入时主库已运行一段时间已有业务数据则需要做以下操作主库备份 (模版)将备份文件拷贝到从库服务器scp master_full_backup_*.sql rootmysql-slave1:/root/四 从服务器配置 - MySql-slave1从库导入初始数据mysql -uroot -pFeige2026 master_full_backup_20260106.sql配置文件 - my.cnf /重启mysql添加以下配置 [mysqld] server-id2 # 从服务器ID必须唯一 gtid_modeON # 启用GTID模式 enforce_gtid_consistencyON # 强制GTID一致性 master-info-repositoryTABLE # 主库信息存储到表 8.4不支持 relay-log-info-repositoryTABLE # 中继日志信息存储到表 8.4不支持 read_onlyON # 设置从库为只读模式防止误写 影响范围普通用户账户 super_read_onlyON # 超级只读模式 影响范围所有用户包括 SUPER 权限用户 #mysqlnativepasswordON # 8.4版本配置解析[mysqld] 从库唯一ID必须和主库(1)、其他从库不重复 server-id2 【修复1】GTID模式正确写法加下划线和主库同阶段 gtidmodeOFFPERMISSIVE enforcegtidconsistencyON 【修复2】删除8.4不支持的两个参数默认已启用 master-info-repositoryTABLE # 8.4已移除直接删掉 relay-log-info-repositoryTABLE # 8.4已移除直接删掉 从库只读配置普通用户只读超级用户可写防误操作 read_onlyON 超级只读所有用户都只读包括SUPER权限启动成功后再开 superreadonlyON 【补充】从库必须开启中继日志漏写会导致主从复制失败 relay-logrelay-bin 【补充】从库建议开启logslaveupdates级联复制需要主从架构建议开启 logslaveupdatesON配置主从连接msyql 8.0版本之前-- 配置主从复制连接【5.7】 mysql CHANGE MASTER TO MASTER_HOSTmysql-master, -- 主库IP地址 MASTER_USERrepl, -- 复制用户名 MASTER_PASSWORDRepl123, -- 复制用户密码 MASTER_AUTO_POSITION1; -- 启用GTID自动定位start slaveshow slave status \G;mysql8.0版本之后#-- 配置主从复制连接【8.0】 CHANGE REPLICATION SOURCE TO SOURCE_HOSTmysql-master, SOURCE_USERjack, SOURCE_PASSWORDJack123, SOURCE_PORT3306, SOURCE_AUTO_POSITION1start replicaSHOW REPLICA STATUS\G扩容从机 还是一样的操作,先导入master的数据保持数据一致,然后配置Gtid的配置信息, 进入mysql配置主从复制连接五 ProxySQL读写分离 (独立一台节点)原理:ProxySQL 原生就能实现读写分离核心是靠 SQL 语法匹配规则ProxySQL 本身不做数据同步而是在已经通过 GTID 实现主从数据一致的 MySQL 集群之上实现读写分离、负载均衡和高可用路由。读写分离软件 : proxySQLProxySQL 是一个高性能、高可用性、基于 MySQL 协议的开源数据库中间件。它核心的功能包括安装 proxySQLproxySQl的依赖以及proxySQldnf install -y wget gnupg2dnf -y install https://repo.proxysql.com/ProxySQL/proxysql-3.0.x/centos/9/proxysql-3.0.4-1-centos9.x86_64.rpm启动 ProxySQL 服务systemctl enable --now proxysql \ systemctl status proxysql查看进程ss -tnlp| grep proxySQl开放 ProxySQL 的管理端口6032和代理端口6033sudo firewall-cmd --permanent --add-port6032/tcp \ sudo firewall-cmd --permanent --add-port6033/tcp \ sudo firewall-cmd --reload两个端口6033业务端口应用连这里proxysql的端口6032管理端口管理员配规则、看状态配置读写分离ProxySQL 操作登录ProxySQL管理界面mysql -u admin -padmin -h 127.0.0.1 -P 6032 --promptProxySQLAdmin --promptProxySQLAdmin 自定义 命令行提示符清空现有配置如果是新安装可跳过DELETE FROM mysql_servers;mysql_servers; 记录这所有的登录主机添加 MySQL 节点添加主库到 hostgroup 10注意改IPINSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections)VALUES (10, 192.168.xx.xx, 3306, 1000, 1000);解析hostgroup_id10 10 你可以自己定义比如 10 写组主库 20 读组从库 hostname192.168.91.200 MySQL 的 IP 地址 就是你后端数据库真实的 IP 大白话要转发的那台数据库在哪 port3306 MySQL 端口 默认就是 3306 大白话数据库的门牌号 weight1000 权重 1000 读请求负载均衡用的 数字越大分到的读请求越多 max_connections1000 ProxySQL 给这台 MySQL 最多开 1000 个连接 连接池限制 超过 1000 个连接就排队 作用保护 MySQL 不被连接冲爆 大白话最多允许同时用 1000 个连接添加从库到 hostgroup 20注意IPINSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections)VALUES (20, 192.168.xx.xx, 3306, 1000, 1000);删除命令 (从指定的分组删除)delete from mysql_servers where hostgroup_id 10 and hostname 192.168.xx.xx ;查看select hostgroup_id, hostname, port, weight, max_connections from mysql_servers;将配置加载到运行时内存生效load mysql servers to runtime ;将配置持久化到磁盘重启后仍有效save MYSQL SERVERS to disk;监控与应用在 MySQL Master 上执行创建监控用户【用于proxy_sql检测后端服务器的健康状态】--在master上创建slave上也会同步后端 MySQL服务器创建用户要注意账号后的主机ipCREATE USER proxysql_monitor192.168.72.% IDENTIFIED BY Feige123;权限GRANT USAGE, REPLICATION CLIENT ON *.* TO proxysql_monitor192.168.72.%;刷新FLUSH PRIVILEGES;配置监控用户 (在proxysql配置)清空现有用户如果是新安装可跳过DELETE FROM mysql_users;设置监控用户名你执行的语句update global_variables set variable_valueproxysql_monitor where variable_namemysql-monitor_username;解析global_variables ProxySQL 的 “全局设置清单” SET variable_valueproxysql_monitor 把监控用户名设置为proxysql_monitor WHERE variable_namemysql-monitor_username 这个配置项的名字叫mysql 监控的用户名设置监控用户密码update global_variables set variable_valueFeige123 where variable_namemysql-monitor_password;查看账号select * from global_variables where variable_nameadmin-admin_credentials or variable_namemysql-monitor_username or variable_namemysql-monitor_password;加载到运行时runtimeLOAD MYSQL VARIABLES TO RUNTIME;SAVE MYSQL VARIABLES TO DISK;检查监控状态SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 5;示例:调优,针对账号安全检测UPDATE global_variables SET variable_value2000 WHERE variable_namemysql-monitor_connect_interval;设置 ProxySQL 尝试连接 MySQL 的间隔时间单位是毫秒 (ms)。2000ms 2 秒意思是如果 MySQL 连不上ProxySQL 每隔 2 秒就重试一次连接。mysql-monitor_connect_interval2 秒重试一次连接UPDATE global_variables SET variable_value2000 WHERE variable_namemysql-monitor_ping_interval;mysql-monitor_ping_interval2 秒发一次心跳设置 ProxySQL 给 MySQL 发心跳 ping 的间隔时间单位是毫秒 (ms)意思是ProxySQL 每隔 2 秒给所有后端 MySQL 发一次 你还活着吗 的心跳包检查节点是否存活。UPDATE global_variables SET variable_value3 WHERE variable_namemysql-shun_on_failures;mysql-shun_on_failures3 次失败拉黑设置 连续失败多少次就把 MySQL 节点拉黑剔除集群意思是不是一次失败就踢掉而是连续失败 3 次才拉黑避免网络抖动误判。UPDATE global_variables SET variable_value10 WHERE variable_namemysql-shun_recovery_time_sec;mysql-shun_recovery_time_sec10 秒自动恢复检查设置 被拉黑的 MySQL 节点多久后自动尝试恢复重新加入集群单位是秒 (s)就像电梯坏了被停用物业每隔 10 秒检查一次修好了没修好了立刻恢复使用不用人工干预在 MySQL Master 上执行创建应用程序用户这【根据需要后期创建】 做实验使用创建赋予权限全局CREATE USER client192.168.72.% IDENTIFIED BY Feige!123; #权限 GRANT ALL PRIVILEGES ON * . * TO client192.168.72.%; #刷新 FLUSH PRIVILEGES;应用库1CREATE USER blog192.168.72.% IDENTIFIED BY Feige123!; GRANT ALL PRIVILEGES ON blog.* TO blog192.168.72.%; FLUSH PRIVILEGES;应用库2CREATE USER shop192.168.72.% IDENTIFIED BY Feige123; GRANT ALL PRIVILEGES ON shop.* TO shop192.168.72.%; FLUSH PRIVILEGES;六 配置读写分离规则配置读写分离规则清空现有规则如果是新安装可跳过DELETE FROM mysql_query_rules;在mysql操作1. 捕获 SELECT ... FOR UPDATE发往写组 (10)INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES (1, 1, ^SELECT.*FOR UPDATE, 10, 0);2. 捕获所有其他 SELECT发往读组 (20)并停止匹配INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES (2, 1, ^SELECT, 20, 1);3. 默认规则将所有未匹配的语句发往写组 (10)并停止匹配INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES (3, 1, .*, 10, 1);mysql_query_rules配置表 配置完规则在这里查看SELECT rule_id, active, match_digest, match_pattern, destination_hostgroup, apply, comment FROM mysql_query_rules;使规则生效load mysql query rules to runtime ;save mysql query rules to disk ;监控和调试登录mysql -uadmin -padmin -h 127.0.0.1 -P 6032 (还是上面的管理端口)查看查询统计 操作命令SELECT hostgroup, count_star, digest_text FROM stats.stats_mysql_query_digest ORDER BY count_star DESC LIMIT 10;hostgroup 主机组 ID 10 写库、20 读库 count_star 这条 SQL 总共执行了多少次 数值越大 访问越频繁 digest_text SQL 语句模板抽象后的 SQL desc 降序查看连接池状态SELECT * FROM stats_mysql_connection_pool;查看当前连接数SELECT * FROM stats_mysql_global;重置统计用于重新测试SELECT * FROM stats.stats_mysql_query_digest_reset;