MySQL 主从复制与读写分离 （学习笔记，自用）

原创 于 2026-04-09 16:25:41 发布 · 425 阅读 · 10 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
GEO检测

本次笔记系统的记录了MySQL主从复制与读写分离的核心原理、实操配置流程及MaxScale代理部署。

一、MySQL高可用架构设计总览

1.1架构组成要素

主从辅助、读写分离、负载均衡、高可用（HA）四者一体，构成MySQL集群建设的核心问题域。

1.2关键设计目标

目标实现方式
数据一致性保障通过不同复制类型实现
负载压力分担通过读写分离与多节点扩展
故障自动转移能力任一节点宕机后服务可持续

二、复制类型概念与选型依据

2.1主流复制方式对比

复制类型数据一致性性能配置复杂的
异步复制最弱最高最简单
半同步复制中等中等中等
组复制强一致低复杂
GTID复制中等中等中等

2.2业务场景适配原则

复制类型适用场景
异步复制游戏、读写分离、数据归档等对一致性容忍度高、性能要求极致的场景
半同步复制中等一致性要求、性能可接受折中的通用场景
组复制金融核心系统等强一致性、高容错性场景（如股票交易、数据不一致将引发严重后果）
GTID复制简化运维、支持故障切换，一致性与复杂度均为中等

三、异步复制原理详解

3.1定义与特性

主库执行事务后立即返回客户端，不等待从库确认；数据一致性最弱，但性能最高，配置最简单。

3.2与IO模型类比

模型特点
异步I/O任务发出即返回，无需等待结果
同步必须等待前序任务完成才发起下一任务

3.3位置复制（Position-Based Replication）本质

基于二进制日志文件名（如 mysql-bin.000001 ）与偏移量（ position ）进行数据同步。

四、主从复制底层机制

4.1、主库关键组件

组件说明
二进制日志（binary log）记录所有DML/DDL操作（不含SELECT）

配置项

ini

   log-bin = mysql-bin
   binlog-fornat = ROW  #或 MIXED

日志轮转

每个事务提交后，日志按顺序追加至当前文件，满则轮转新文件（ mysql-bin.000002 等）。

4.2从库关键组件

组件说明
IO线程（ I/O Thread ）连接主库，读取其binary log内容， 写入本地中继日志（ rela log ）
SQL线程（ SQL Thread ）读取relay log, 重放其中语句，使从库数据与主库一致
中继日志（ relay log ）文件名由  relay-log = mysql-relay-bin 指定，内容与主库binary log完全一致

五、主从复制配置全流程

5.1前置环境准备

准备项要求
时间同步

所有节点启用NTP服务，指向权威时间服务器

（如 ntp1.aliyun.com 、cn.ntp.org.cn ）

时区统一设置为 Asia/Shanghai
免密登录通过SSH秘钥实现主从节点间无密码通信

5.2主库配置（my.cnf）

ini

   [mysqld]
   server-id = 128
   log-bin = mysql-bin
   binlog-format = ROW
   expire_logs_days = 7

5.3从库配置（my.cnf）

ini
 
   [mysqld]
   server-id = 129
   relay-log = mysql-relay-bin

5.4用户权限与CHANGE MASTER TO指令

主库创建复制用户

sql

   CREATE USER 'slave'@'192.168.160.%'
   IDENTIFIED WITH mysql_native_password BY '123.com';
    
   GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.160.%';

从库执行复制命令

sql

    CHANGE MASTER TO
    MASTER_HOST='192.168.160.128',
    MASTER_USER='slave',
    MASTER_PASSWORD='123.COM',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=156;

    START SLAVE;

六、典型故障排查与解决

6.1IO线程连接失败（Error 2003）

项目说明
原因网络不通，防火墙拦截（如firewalld未开放3306端口）
验证 ping 测试连通性， telnet 192.168.160.128 3306 检测端口
解决 firewall-cmd --permanent --add-port= 3306/tcp && firewall-cmd --reload

6.2认证插件不兼容（Error 1045）

项目说明
原因MySQL8默认 caching_sha2_password 插件要求TLS安全连接，而实验环境未配置

解决方案

方案一：修改用户认证方式
sql

   ALTER USER 'slave'@'%'
   IDENTIFIED WITH mysql_native_password BY '123.com';
方案二：全局配置
ini 

   [mysqld]
   default_authentication_plugin = mysql_native_password

6.3从库状态异常诊断

关键命令

sql
 
   SHOW SLAVE STATUS\G;

核心字段

字段正常状态

 Slave_IO_Running 

Yes
 Slave_SQL_RunningYes

错误定位

字段说明
 Seconds_Behind_Master延迟秒数

 Last_IO_Erroe

IO线程错误信息
 Last_SQL_ErrorSQL线程错误信息

七、数据一致性增强策略

7.1新节点加入同步集群

问题

后加入从库若直接配置复制，仅同步配置后的新数据，历史数据缺失。

解决方案

步骤1：主库全逻辑备份

hash
    
    mysqldump --all-databases --master-data=2 > full_backup.sql

步骤2：从库导入备份

sql

   mysql < full_backuo.sql

步骤3：执行CHANGE MASTER TO并启动复制

sql
  
   CHANGE MASTER TO ...;
   START SLAVE;

八、复制过滤机制实践

8.1白名单（Reolicate_Do_DB）

配置

ini
  
   [mysqld]
   replicate-do-db = DB2

效果

仅同步指定数据库（如DB2），其他库（DB1、DB3）不复制。

8.2黑名单（Replicate_lgnore_DB）

配置

ini
  
   [mysqld]
   replicate-ignore-db = musql

效果

忽略指定系统库，避免权限表冲突

注意：实际验证中黑名单功能存在兼容性问题，白名单更稳定可靠。

九、读写分离代理：MaxScale部署

9.1工具定位与能力

1.MariaDB官方提供，兼容MySQL协议

2.支持读写分离、负载均衡、查询路由、连接加密、防火墙等功能

9.2部署障碍与绕行方案

环境限制

问题说明
官方RPM包限制仅支持CentOS 7/Ubuntu20.04等
openEuler适配需手动编译或寻找适配包

配置要点

1.后端服务器定义（servers）
ini

   [server1]
   type=server
   address=192.168.160.128
   port=3306
   protocol=MySQLBackend
   
   [server2]
   type=server
   address=192.168.160.129
   port=3306
   protocol=MySQLBackend
2.监控模块（monitor）
ini

   [MySQL Monitor]
   type=monitor
   module=mariadbmon
   servers=server1,server2
   user=monitor
   password=monitor_password
   monitor_interval=2000
3.路由模块（service）
ini
 
   [Read-Write Service]
   type=service
   router=readwritesplit
   servers=server1,server2
   user=maxscale_user
   password=maxscale_password

用户权限要求

用户所需权限
monitorREPLICATION CLIENT,REPLICATION SLAVE
maxscale_userSELECT 权限

9.3配置避坑指南

注释格式

ini

   #正确：注释独占一行
   option = value

   #错误：注释置于配置项末尾（会报错）
   option = value # comment

版本匹配

确保MaxScale版本与后端MySQL/MariaDB版本兼容（如MySQL 8.0对应MaxScale 22.x）。

十、总结与建议

10.1核心原则重申

主从复制是所有高可用架构的前提，必须首选确保数据同步稳定，再叠加读写分离等高级功能。

10.2运维实践提示

二进制日志恢复

支持两种方式：

方式说明
基于时间 --start-datetime / --stop-datetime
基于位置 --start-position / --stop-position 

生产环境规范

禁用 reolicate-ignore-db = mysql 等可能引发权限同步混乱的配置

故障相应

 SHOW SLAVE STATUS  是首要诊断入口，重点关注IO/SQL Running 状态及错误日志字段。

十一、常用命令速查

主库操作

sql

   --查看二进制日志
   SHOW MASTER LOGS;

   --查看当前binlog位置
   SHOW MASTER STATUS;
 
   --创建复制用户
   CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
   FLUSH PRIVILEGES;

从库操作

sql

   --配置主从复制
   CHANGE MASTER TO
   MASTER_HOST='master_ip',
   MASTER_USER='slave',
   MASTER_PASSWORD='password',
   MASTER_LOG_FILE='mysql-bin.000001'，
   MASTER_LOG_POS=154;

   --启动复制
   STARI SLAVE;

   --查看复制状态
   SHOW SLACE STATUS\G;

   --停止复制
   STOP SLAVE;

   --重置复制
   RESET SLAVE ALL;

备份与恢复

bash

     --全量备份
     mysqldum --all-databases --master-data=2 --single-transaction > 
     backup.sql

     --导入备份
     mysql < backup.sql
 
     --基于时间点回复
     mysqlbinlog --start-datetime="2026-04-09 14:00:00" --stio-
     datetime="2026-04-09 15:00:00" mysql-bin.000001 | mysql -u root -p

主从复制是高可用基础，数据同步稳定后再叠加读写分离；生产环境优先考虑白名单过滤和GTID复制。

标签
#mysql #学习 #笔记
MySQL主从复制与读写分离
2502_91448199的博客
05-14 1664
首先,Slave开始一个工作线程一一I/0线程,I/0线程在Master上打开一个普通的连接,然后开始Binlogdump process。复制过程有一个很重要的限制,即复制在SIave上是串行化的,也就是说Master上的并行更新操作不能在Slave上并行操作。MySQL的主从复制和MySQL的读写分离两者有着紧密联系,首先要部署主从复制,只有主从复制完成了,才能在此基础上进行数据的读写分离。在主服务器上执行的SQL语句,在从最务器上执行同样的语句,MySQL默认采用基于语句的复制,效率比较高。
MySQL主从复制与读写分离（图文详解）
愿你成为自己喜欢的模样，不抱怨，不将就，有自由，有光芒！
02-04 1834
文章目录MySQL主从复制与读写分离一、案例概述二、======MySQL 主从复制原理======1、MySQL 支持的复制类型2、MySQL主从复制的工作过程三、======MySQL读写分离原理======1、读写分离存在意义2、常见的 MySQL 读写分离分为两种四、MySQL主从复制和读写分离实验部署案例拓扑图实验思路环境配置 MySQL主从复制与读写分离 一、案例概述 在实际的生产环境中，如果对数据库的读和写都在同一个数据库服务器中操作，无论是在安全性、高可用性还是高并发等各个方面都是完全不能满
MySQL的主从复制和读写分离
weixin_58376680的博客
03-28 2172
读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
BeikeShop跨境电商独立站系统V1.5.0免授权全开源版
01-31 792
BeikeShop 一款开源好用的跨境电商系统，BeikeShop 是基于 Laravel 开发的一款开源商城系统，主要面向外贸/跨境电商行业提供商品管理、订单管理、会员管理、支付、物流、系统管理等功能。BeikeShop商城 Beike多语言商城 多货币商城B2C商城系统 代码100%开源 PHP商城系统 H5/PC商城 跨境电商系统 跨境商城系统 电商商城系统 Laravel 9 框架插件化开发，MVC架构 支持插件市场。Event 机制实现Hook功能，自定义可视化装修。
MySQL 主从复制与读写分离
最新发布
2301_80839375的博客
05-13 1751
MySQL 主从复制是一种数据同步机制，允许数据从一个主数据库（Master）复制到一个或多个从数据库（Slave）。数据冗余与灾备：通过从库提供数据备份，增强容灾能力。负载均衡：将读操作分散到多个从库，减轻主库压力。高可用性：主库故障时，从库可快速切换为主库，减少停机时间。MySQL 读写分离是一种通过分离读操作（SELECT）与写操作（INSERT/UPDATE/DELETE）的数据库架构设计，将写操作集中在主库（Master），读操作分散到从库（Slave），以提升系统性能和并发处理能力。核心作用。
MySQL主从复制与读写分离（ 主从复制原理 、读写分离原理 、配置 MySQL 主从复制 、配置 MySQL 读写分离）
kimowinter的博客
08-29 5528
文章目录
MySQL主从复制和读写分离
a568911的博客
12-20 2033
文章目录一.MySQL主从复制1.主从复制原理1.mysql支持的复制类型2.mysql主从复制的工作原理3.MySOL主从复制延迟4.复制的作用2.具体操作1.主服务器192.168.68.2002.从服务器192.168.68.303.从服务器192.168.68.1054.测试二.读写分离1.mysql读写分离的概念2.mysql读写分离的原理3. MySQL 读写分离的方式1.基于程序代码内部实现2.基于中间代理层实现4.读写分类的作用5.什么时候要读写分离6.搭建Amoeba 实现读写分离操作1.
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值