当前位置： 首页 > news >正文

MySql 主从复制+读写分离

news 2026/6/28 22:51:53

先把 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;
http://www.jsqmd.com/news/1086024/

相关文章：

  • CoppeliaSim/V-REP全版本软件安装包：从官网到国内网盘的一站式获取指南
  • ncmdumpGUI终极教程：3分钟掌握网易云音乐NCM文件转换技巧
  • 从零到一：在Windows系统上部署gprMax3.0并完成首个B-scan仿真
  • Python 推导式全景解析：从语法核心到高性能实战
  • Ubuntu 22.04 触屏干扰排查指南：精准识别与禁用输入设备
  • 终极指南：如何在Windows/Linux上轻松下载官方macOS系统镜像
  • 从CSS Hack到优雅降级：Flex Gap Polyfill如何重塑前端布局兼容性策略
  • WooCommerce商城的安全性一定要重视起来
  • 口碑好的瓷砖供应商
  • UT61E通信协议解析与数据包解码实战
  • 【实践解析】DDRNet：面向实时道路场景解析的双分辨率网络架构与实现
  • DataGrip实战MongoDB：从连接配置到高效CRUD的避坑指南
  • RA8T2 EtherCAT从站核心寄存器实战：看门狗、EEPROM与同步管理器配置详解
  • 瓶装水生产线控制系统中PLC双通道以太网通讯架构设计
  • 终极泰拉瑞亚模组管理工具tModLoader完全指南：5分钟快速入门教程
  • 从MATLAB实践出发：功率谱(PS)与功率谱密度(PSD)的数值差异与物理内涵
  • Allegro高效设计：从零构建你的专属快捷键体系
  • Ubuntu启动卡在/dev/sda4: clean？别慌，这是磁盘空间告急的信号
  • Windows热键侦探：3步快速找出谁偷了你的快捷键
  • 【RoCE】从ECN标记到DC-QCN响应：构建无损数据中心网络的拥塞控制闭环
  • LinkSwift：8大网盘直链下载助手终极指南
  • Fay数字人框架终极指南：5步实现智能代理的自主决策与主动交互
  • AI专著生成新利器！4款AI工具实测，高效完成20万字专著写作！
  • WELearn网课助手：告别熬夜刷题的3个实用技巧
  • 【RuoYi-Vue-Plus】性能调优实践：从Druid迁移至HikariCP数据源
  • CVE-2024-2879漏洞复现：LayerSlider插件SQL注入深度剖析与实战
  • PlayCover跨平台架构解析：iOS应用原生运行与数据同步引擎技术实现
  • 从特征工程到模型融合：Kaggle植物幼苗分类竞赛的机器学习实战解析
  • TVA 赋能智慧工厂的十大核心优势（4）
  • LeetDown：让老款iPhone重获新生的终极降级指南