先把 MySQL 主从复制搭建好，让数据能自动同步，再用 ProxySQL 做读写分离才有意义。
一 主从复制的原理
主库 (二进制 会记录增删改)
- 创建授权账号,并且开启binlog日志,告知从机的二进制位置节点
从库
- IO线程 ---> 主库的二进制日志
- start/stop 开机关闭 slave
二 Gtid方式实现主从复制
GTID工作原理:
- 主库更新数据时生成GTID，记录到binlog
- 从库I/O线程将变更写入relay log
- 从库SQL线程获取GTID，检查本地binlog记录
- 如有记录则忽略，无记录则执行并记录到binlog
GTID：是复制协议，让主从同步更可靠、更容易定位同步位置、故障切换更方便
GTID 模式下，从库严禁写入，否则会造成 GTID 不连续、复制中断。
环境准备:
删除主机 mysql 的uid号:
rm -rf /var/lib/mysql/auto.cnf
做主机名、IP、主机名解析:
hostnamectl set-hostname mysql-master
在/etc/hosts 做ip域名解析
关闭防火墙和SELinux
systemctl disable --now firewalld \
setenforce 0 \
sed -ri '/^SELINUX=/cSELINUX=disabled' /etc/selinux/config
确保时钟同步
dnf -y install chrony
vim /etc/chrony.conf
注释默认的 pool 服务器，添加阿里云 NTP 服务器
server ntp.aliyun.com iburst
systemctl start chronyd \
systemctl enable chronyd
三 主服务器配置 - MySql-master
配置文件 - my.cnf
启用Gtid模式:
添加以下配置
server-id=1 # 服务器唯一标识，主从不能重复
log-bin=mysql-bin # 开启二进制日志，主库必须开启【可选】
gtid_mode=ON # 启用GTID模式
enforce_gtid_consistency=ON # 强制GTID一致性
#mysql_native_password=ON # 8.4版本
重启MySQL
# 重启
systemctl restart mysqld
进入mysql操作:
创建用于复制的专用用户 (可以自己指定用户名和密码) -- 建议%改为从服务器的IP
CREATE USER repl@'%' IDENTIFIED WITH mysql_native_password by 'Repl@123';
授权repl@'%'
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';
刷新权限
FLUSH PRIVILEGES;
备份主库数据并同步到从库
如果从库加入时，主库已运行一段时间，已有业务数据，则需要做以下操作
主库备份 (模版)
将备份文件拷贝到从库服务器
scp master_full_backup_*.sql root@mysql-slave1:/root/
四 从服务器配置 - MySql-slave1
从库导入初始数据
mysql -uroot -p'Feige@2026' < master_full_backup_20260106.sql
配置文件 - my.cnf /重启mysql
添加以下配置
[mysqld]
server-id=2 # 从服务器ID，必须唯一
gtid_mode=ON # 启用GTID模式
enforce_gtid_consistency=ON # 强制GTID一致性
master-info-repository=TABLE # 主库信息存储到表 8.4不支持
relay-log-info-repository=TABLE # 中继日志信息存储到表 8.4不支持
read_only=ON # 设置从库为只读模式（防止误写） 影响范围：普通用户账户
super_read_only=ON # 超级只读模式 影响范围：所有用户，包括 SUPER 权限用户
#mysqlnativepassword=ON # 8.4版本
配置解析
[mysqld]
从库唯一ID，必须和主库(1)、其他从库不重复
server-id=2
【修复1】GTID模式正确写法（加下划线），和主库同阶段
gtidmode=OFFPERMISSIVE
enforcegtidconsistency=ON
【修复2】删除8.4不支持的两个参数，默认已启用
master-info-repository=TABLE # 8.4已移除，直接删掉
relay-log-info-repository=TABLE # 8.4已移除，直接删掉
从库只读配置（普通用户只读，超级用户可写，防误操作）
read_only=ON
超级只读（所有用户都只读，包括SUPER权限，启动成功后再开）
superreadonly=ON
【补充】从库必须开启中继日志（漏写会导致主从复制失败）
relay-log=relay-bin
【补充】从库建议开启logslaveupdates（级联复制需要，主从架构建议开启）
logslaveupdates=ON
配置主从连接
msyql 8.0版本之前
-- 配置主从复制连接【5.7】
mysql> CHANGE MASTER TO
MASTER_HOST='mysql-master', -- 主库IP地址
MASTER_USER='repl', -- 复制用户名
MASTER_PASSWORD='Repl@123', -- 复制用户密码
MASTER_AUTO_POSITION=1; -- 启用GTID自动定位
start slave
show slave status \G;
mysql8.0版本之后
#-- 配置主从复制连接【8.0】
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='mysql-master',
SOURCE_USER='jack',
SOURCE_PASSWORD='Jack@123',
SOURCE_PORT=3306,
SOURCE_AUTO_POSITION=1
start replica
SHOW REPLICA STATUS\G
扩容从机 还是一样的操作,先导入master的数据保持数据一致,然后配置Gtid的配置信息, 进入mysql配置主从复制连接
五 ProxySQL读写分离 (独立一台节点)
原理:
ProxySQL 原生就能实现读写分离，核心是靠 SQL 语法匹配规则：
ProxySQL 本身不做数据同步，而是在已经通过 GTID 实现主从数据一致的 MySQL 集群之上，实现读写分离、负载均衡和高可用路由。
读写分离软件 : proxySQL
ProxySQL 是一个高性能、高可用性、基于 MySQL 协议的开源数据库中间件。它核心的功能包括：
安装 proxySQL
proxySQl的依赖以及proxySQl
dnf install -y wget gnupg2
dnf -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）和代理端口（6033）
sudo firewall-cmd --permanent --add-port=6032/tcp \
sudo firewall-cmd --permanent --add-port=6033/tcp \
sudo firewall-cmd --reload
两个端口
- 6033：业务端口，应用连这里
- proxysql的端口
- 6032：管理端口，管理员配规则、看状态
配置读写分离
ProxySQL 操作
登录ProxySQL管理界面
mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '
- --prompt='ProxySQLAdmin> ' 自定义 命令行提示符
清空现有配置（如果是新安装可跳过）
DELETE FROM mysql_servers;
- mysql_servers; 记录这所有的登录主机
添加 MySQL 节点
添加主库到 hostgroup 10 注意改IP
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections)VALUES (10, '192.168.xx.xx', 3306, 1000, 1000);
解析
hostgroup_id=10
10 你可以自己定义，比如：
10 = 写组（主库）
20 = 读组（从库）
hostname='192.168.91.200'
MySQL 的 IP 地址
就是你后端数据库真实的 IP
大白话：要转发的那台数据库在哪
port=3306
MySQL 端口
默认就是 3306
大白话：数据库的门牌号
weight=1000
｜ 权重 = 1000
读请求负载均衡用的
数字越大，分到的读请求越多
max_connections=1000
ProxySQL 给这台 MySQL 最多开 1000 个连接
连接池限制
超过 1000 个连接就排队
作用：保护 MySQL 不被连接冲爆
大白话：最多允许同时用 1000 个连接
添加从库到 hostgroup 20 注意IP
INSERT 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服务器创建用户 要注意账号后的主机ip
CREATE USER 'proxysql_monitor'@'192.168.72.%' IDENTIFIED BY 'Feige@123';
权限
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'proxysql_monitor'@'192.168.72.%';
刷新
FLUSH PRIVILEGES;
配置监控用户 (在proxysql配置)：
清空现有用户（如果是新安装可跳过）
DELETE FROM mysql_users;
设置监控用户名（你执行的语句）
update global_variables set variable_value='proxysql_monitor' where variable_name='mysql-monitor_username';
解析
global_variables ==> ProxySQL 的 “全局设置清单”
SET variable_value='proxysql_monitor'
把监控用户名设置为：proxysql_monitor
WHERE variable_name='mysql-monitor_username'
这个配置项的名字叫：mysql 监控的用户名
设置监控用户密码
update global_variables set variable_value='Feige@123' where variable_name='mysql-monitor_password';
查看账号
select * from global_variables where variable_name='admin-admin_credentials' or variable_name='mysql-monitor_username' or variable_name='mysql-monitor_password';
加载到运行时runtime
LOAD 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_value='2000' WHERE variable_name='mysql-monitor_connect_interval';
- 设置 ProxySQL 尝试连接 MySQL 的间隔时间，单位是毫秒 (ms)。
- 2000ms = 2 秒
- 意思是：如果 MySQL 连不上，ProxySQL 每隔 2 秒就重试一次连接。
- mysql-monitor_connect_interval
- 2 秒重试一次连接
UPDATE global_variables SET variable_value='2000' WHERE variable_name='mysql-monitor_ping_interval';
- mysql-monitor_ping_interval
- 2 秒发一次心跳
- 设置 ProxySQL 给 MySQL 发心跳 ping 的间隔时间，单位是毫秒 (ms)
- 意思是：ProxySQL 每隔 2 秒，给所有后端 MySQL 发一次 "你还活着吗？" 的心跳包，检查节点是否存活。
UPDATE global_variables SET variable_value='3' WHERE variable_name='mysql-shun_on_failures';
- mysql-shun_on_failures
- 3 次失败拉黑
- 设置 连续失败多少次，就把 MySQL 节点拉黑（剔除集群）
- 意思是：不是一次失败就踢掉，而是连续失败 3 次才拉黑，避免网络抖动误判。
UPDATE global_variables SET variable_value='10' WHERE variable_name='mysql-shun_recovery_time_sec';
- mysql-shun_recovery_time_sec
- 10 秒自动恢复检查
- 设置 被拉黑的 MySQL 节点，多久后自动尝试恢复（重新加入集群），单位是秒 (s)
- 就像电梯坏了被停用，物业每隔 10 秒检查一次修好了没，修好了立刻恢复使用，不用人工干预
在 MySQL Master 上执行
创建应用程序用户这【根据需要后期创建】 做实验使用
创建赋予权限
全局
CREATE USER 'client'@'192.168.72.%' IDENTIFIED BY 'Feige!123';
#权限
GRANT ALL PRIVILEGES ON * . * TO 'client'@'192.168.72.%';
#刷新
FLUSH PRIVILEGES;
应用库1
CREATE USER 'blog'@'192.168.72.%' IDENTIFIED BY 'Feige123!';
GRANT ALL PRIVILEGES ON blog.* TO 'blog'@'192.168.72.%';
FLUSH PRIVILEGES;
应用库2
CREATE USER 'shop'@'192.168.72.%' IDENTIFIED BY 'Feige123@';
GRANT ALL PRIVILEGES ON shop.* TO 'shop'@'192.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;
扫一扫
4094
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
