db1（192.168.56.10）：MySQL 主库 + ProxySQL（作为应用入口，做读写分离）db2（192.168.56.11）：MySQL 从库（只读）- 应用/客户端只连接 ProxySQL（
db1:6033），写走主库，读走从库
0. 准备工作
两台干净的 CentOS 8（root 或 sudo 权限），主机名与 IP：
# db1
hostnamectl set-hostname db1 && exec bash
nmcli -p dev show # 确认网卡与IP
# 假设 db1: 192.168.56.10
# db2
hostnamectl set-hostname db2 && exec bash
# 假设 db2: 192.168.56.11关闭 SELinux（实验方便，生产建议按策略放行）与开放端口：
# 两台都执行
setenforce 0
sed -ri 's/^SELINUX=.*/SELINUX=permissive/' /etc/selinux/config
# 防火墙放行 MySQL(3306)、ProxySQL(6032 管理端口、6033 业务端口，仅 db1 需要)
firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --add-port=6032/tcp --permanent # 仅 db1
firewall-cmd --add-port=6033/tcp --permanent # 仅 db1
firewall-cmd --reload1. 安装 MySQL 8（两台）
# 安装 MySQL 官方 YUM 仓库（版本号可能更新，按需替换）
rpm -Uvh https://repo.mysql.com/mysql80-community-release-el8-4.noarch.rpm
# 安装并启动
yum install -y mysql-server
systemctl enable --now mysqld初始化 root 密码（MySQL 初次启动会生成临时密码）：
# 获取临时密码（两台分别查看）
grep 'temporary password' /var/log/mysqld.log
# 使用临时密码登录并设置新密码（两台各自执行，设置为相同便于实验）
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root@123!';
EXIT;2. 配置主库（db1）与从库（db2）参数（启用 GTID）
**db1（主库）**修改 /etc/my.cnf，在 [mysqld] 段新增/调整：
[mysqld]
server-id=1
log_bin=/var/lib/mysql/mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_row_image=FULL
innodb_flush_log_at_trx_commit=1
sync_binlog=1**db2（从库）**修改 /etc/my.cnf：
[mysqld]
server-id=2
log_bin=/var/lib/mysql/mysql-bin
relay_log=/var/lib/mysql/mysql-relay-bin
relay_log_recovery=ON
read_only=ON
super_read_only=ON
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON重启 MySQL：
# 两台
systemctl restart mysqld3. 主库创建复制与监控用户
在 db1 登录 MySQL：
-- 复制账号（给从库用）
CREATE USER 'repl'@'%' IDENTIFIED BY 'Repl@123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
-- 监控账号（ProxySQL 健康检查用）
CREATE USER 'monitor'@'%' IDENTIFIED BY 'Monit@123!';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
FLUSH PRIVILEGES;4. 主库导出初始化数据（带 GTID），从库导入
为确保一致性，建议主库暂时暂停 DDL/DML。纯 InnoDB 表可用
--single-transaction做热备。
在 db1：
mysqldump -uroot -p --single-transaction --triggers --routines --events \
--all-databases --set-gtid-purged=ON > /tmp/full.sql
scp /tmp/full.sql root@192.168.56.11:/tmp/在 db2 导入：
mysql -uroot -p < /tmp/full.sql如果
SET @@GLOBAL.GTID_PURGED报错，多为从库已有事务历史。确保是全新实例或清空数据目录后重装。
使用命令
RESET MASTER;5. 配置并启动复制（GTID 自动定位）
在 db2（从库）执行：
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.56.10',
SOURCE_PORT=3306,
SOURCE_USER='repl',
SOURCE_PASSWORD='Repl@123!',
SOURCE_AUTO_POSITION=1,
GET_SOURCE_PUBLIC_KEY=1;
START REPLICA;
-- 验证
SHOW REPLICA STATUS\G
-- 关键字段：
-- Replica_IO_Running: Yes
-- Replica_SQL_Running: Yes
-- Seconds_Behind_Source: 0（或很小）MySQL 8 新语法为
SOURCE/REPLICA；旧版命令MASTER/SLAVE在新版本中已替换。
6. 安装 ProxySQL（仅 db1）
# 添加 ProxySQL 仓库（示例，版本号按需）
cat >/etc/yum.repos.d/proxysql.repo <<'EOF'
[proxysql_repo]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/centos/8
gpgcheck=0
enabled=1
EOF
yum install -y proxysql
systemctl enable --now proxysql默认端口：业务 6033、管理 6032。使用 mysql 客户端连管理端口：
mysql -u admin -padmin -h 127.0.0.1 -P 60327. 在两台 MySQL 上为应用创建业务账户
db1 与 db2 都执行（保证账号在两边都能认证）：
db1
INSERT INTO mysql_users(username, password, active, default_hostgroup)
VALUES ('app', 'App@123!', 1, 10);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;db2
CREATE USER 'app'@'%' IDENTIFIED BY 'App@123!';
GRANT ALL PRIVILEGES ON *.* TO 'app'@'%' WITH GRANT OPTION;
-- 为了防误写，从库仍有 super_read_only=ON，写入会被拒绝。
FLUSH PRIVILEGES;实验为简化授权，授予较大权限；生产请按库/表最小化授权。
8. 配置 ProxySQL：后端、用户、读写规则
在 db1 的 ProxySQL 管理端口（6032）执行：
-- 1) 后端实例：HostGroups
-- 10 = writer（主库），20 = readers（从库）
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (10,'192.168.56.10',3306,100);
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (20,'192.168.56.11',3306,100);
-- 2) 监控账号（健康检查）
SET mysql-monitor_username='monitor';
SET mysql-monitor_password='Monit@123!';
-- 3) 业务用户（默认写入走 writer 组 10）
INSERT INTO mysql_users(username,password,default_hostgroup,transaction_persistent) VALUES
('app','App@123!',10,1);
-- 4) 读写分离规则
-- 先确保加粗匹配：SELECT ... FOR UPDATE / LOCK IN SHARE MODE 仍走主库
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply,flagIN) VALUES
(1,1,'^SELECT.*FOR UPDATE',10,1,0),
(2,1,'^SELECT.*LOCK IN SHARE MODE',10,1,0);
-- 普通 SELECT 走从库
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES
(3,1,'^SELECT',20,1);
-- 5) （可选）复制拓扑自动分组：根据 read_only 自动把实例放进写读组
-- 需要表存在时插入；某些版本默认已有，可先 DELETE 再 INSERT
DELETE FROM mysql_replication_hostgroups;
INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,check_type) VALUES (10,20,'read_only');
-- 将配置加载到运行态并落盘
LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;9. 验证：写入主库，读取从库
连接 ProxySQL 业务端口（6033），用 app 账户：
mysql -u app -p'App@123!' -h 192.168.56.10 -P 6033执行以下 SQL（观察执行节点与只读状态）：
-- 看看当前连接到的是谁（不同变量都可）
SELECT @@hostname AS backend_host, @@read_only AS ro, @@server_id AS sid;
-- 建库建表 + 写入（应在主库执行成功）
CREATE DATABASE demo;
USE demo;
CREATE TABLE t(id INT PRIMARY KEY, c VARCHAR(20));
INSERT INTO t VALUES (1,'hello'),(2,'world');
-- 普通 SELECT（应路由到从库，ro=1）
SELECT SLEEP(1); -- 给复制一点时间
SELECT @@hostname AS backend_host, @@read_only AS ro, @@server_id AS sid;
SELECT * FROM demo.t;
-- 带 FOR UPDATE 的读（应回到主库，ro=0）
SELECT @@hostname, @@read_only, @@server_id;
SELECT * FROM demo.t FOR UPDATE;你应看到：
INSERT/DDL在ro=0的主库上执行。- 普通
SELECT在ro=1的从库上执行。 SELECT ... FOR UPDATE强制回主库。
10. 常见问题 & 排错
-
复制不起来 /
Replica_IO_Running=No：- 防火墙是否放行 3306；主库用户/密码/主机 IP 是否正确；
GET_SOURCE_PUBLIC_KEY=1是否设置（MySQL 8）。
- 防火墙是否放行 3306；主库用户/密码/主机 IP 是否正确；
-
SET @@GLOBAL.GTID_PURGED报错：- 确保从库是全新实例（无本地事务历史）。必要时清空数据目录后重建。
-
从库可被写入：
- 确认从库
read_only=ON、super_read_only=ON；ProxySQL 读规则是否过度匹配（检查规则顺序）。
- 确认从库
-
读到旧数据（异步复制延迟）：
- 实验中可
SLEEP(1)或在需要强一致的查询使用FOR UPDATE/走主库；生产可考虑半同步或基于 GTID 的一致性读策略。
- 实验中可
-
ProxySQL 规则未生效：
- 是否
LOAD ... TO RUNTIME&SAVE ... TO DISK；rule_id有无冲突；规则匹配用match_digest的正则是否正确（^SELECT）。
- 是否
1. ProxySQL 是什么
- 它是一个 高性能的 MySQL 中间件（数据库代理层），运行在应用与数据库之间。
- 应用不用直接连接多个 MySQL 节点，而是只连 ProxySQL（一个入口），ProxySQL 再根据规则把 SQL 请求转发给合适的数据库实例。
2. ProxySQL 的主要作用
-
读写分离
- ProxySQL 可以通过规则判断一条 SQL 是“读”还是“写”。
- 写（INSERT/UPDATE/DELETE/DDL） → 转发到主库（writer）。
- 读（SELECT） → 转发到从库（reader）。
- 这样就能让主库专心处理写，从库负责分担读压力。
-
负载均衡
- 如果有多个从库，ProxySQL 可以自动把查询分散到不同的从库，做到负载均衡。
- 可以根据 权重 (weight) 来分配比例。
-
高可用切换
- ProxySQL 可以通过健康检查和监控账号，知道某个节点是否宕机或只读状态。
- 如果主库宕了，可以自动把它从 writer 组里摘掉，避免应用请求失败。
-
SQL 路由/过滤/缓存
- ProxySQL 可以基于正则匹配 SQL，把特定 SQL 路由到指定实例。
- 也可以缓存热点查询结果，减轻数据库压力。
-
连接池
- 应用只需要连 ProxySQL，ProxySQL 内部维护与后端 MySQL 的连接池，大幅减少 MySQL 建连开销。
3. 判定读写的方式
-
ProxySQL 的 查询规则（mysql_query_rules） 表里配置了正则规则，例如：
^SELECT→ 匹配所有以 SELECT 开头的语句 → 路由到从库组。^SELECT.*FOR UPDATE→ 带锁的 SELECT → 强制走主库。- 其他 INSERT/UPDATE/DELETE/DDL 默认走主库。
-
这样 ProxySQL 就能根据 SQL 类型来判定是“读”还是“写”。
4. 最终效果
- 应用只连 ProxySQL，不用关心背后有多少 MySQL 节点。
- ProxySQL 会把写 SQL 发给主库，把读 SQL 分配到从库（甚至多个从库）。
- 实现了 透明的读写分离 + 负载均衡。
注意事项
由于db2只读，过程中如遇需要写入的情况可临时关闭只读模式。
在db2上执行以下命令
mysql -uroot -p登陆后执行：
SET GLOBAL super_read_only=OFF;
SET GLOBAL read_only=OFF;待文件写入后恢复只读模式
SET GLOBAL read_only=ON;
SET GLOBAL super_read_only=ON;