MySQL 主从复制与读写分离

已于 2025-05-15 11:51:57 修改
阅读量1.5k 收藏 16
点赞数 41
文章标签： mysql 数据库 运维 Linux linux
于 2025-05-13 12:28:21 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2301_80839375/article/details/147918585
版权

目录

一、MySQL 主从复制

（0）概述

（1）MySQL 支持的复制类型

（2）复制的工作原理过程​编辑

二、MySQL 读写分离

（0）概述

（1）MySQL 读写分离类型

1. 基于程序代码内部实现

2. 基于中间代理实现

（2）工作原理过程​编辑

三、主从复制与读写分离的关系

四、主从复制配置 

（1）主从复制搭建

（2）主主复制搭建

五、读写分离搭建

一、MySQL 主从复制

（0）概述

MySQL 主从复制是一种数据同步机制，允许数据从一个主数据库（Master）复制到一个或多个从数据库（Slave）。其主要用途包括：

  • 数据冗余与灾备：通过从库提供数据备份，增强容灾能力。

  • 负载均衡：将读操作分散到多个从库，减轻主库压力。

  • 高可用性：主库故障时，从库可快速切换为主库，减少停机时间。

（1）MySQL 支持的复制类型

  1. 基于语句的复制（Statement-Based Replication, SBR）

    • 主库记录执行的 SQL 语句到二进制日志（binlog），从库重放这些语句。

    • 优点：日志量小，节省存储和传输时间。

    • 缺点：非确定性语句（如 NOW()）可能导致数据不一致。

  2. 基于行的复制（Row-Based Replication, RBR）

    • 主库记录每行数据的变更细节到 binlog。

    • 优点：数据一致性高，适合复杂操作（如含变量的语句）。

    • 缺点：日志量大，可能影响性能。

  3. 混合模式复制（Mixed-Based Replication, MBR）

    • 根据操作类型自动选择 SBR 或 RBR，平衡安全性与效率。

（2）复制的工作原理过程

  1. 主库写入数据：主库将数据变更记录到 binlog。

  2. 从库 IO 线程拉取日志：从库的 IO 线程连接主库，读取 binlog 并写入本地中继日志（relay log）。

  3. 从库 SQL 线程重放日志：从库的 SQL 线程解析 relay log，重放 SQL 或行变更操作。

  4. 复制模式：

    • 异步复制（默认）：主库不等待从库确认，性能高但可能丢数据。

    • 半同步复制：主库至少等待一个从库接收数据后才提交事务，平衡性能与安全性。

    • 全同步复制（需第三方插件）：主库等待所有从库提交事务，保证强一致性但延迟高。

二、MySQL 读写分离

（0）概述

MySQL 读写分离是一种通过分离读操作（SELECT）与写操作（INSERT/UPDATE/DELETE）的数据库架构设计，将写操作集中在主库（Master），读操作分散到从库（Slave），以提升系统性能和并发处理能力。

核心作用：

  • 降低主库压力：读操作占数据库负载的 70%~90%，读写分离可将读请求分流到从库。

  • 提高扩展性：通过增加从库水平扩展读能力。

  • 提升可用性：从库故障时，读请求可路由到其他从库或主库

（1）MySQL 读写分离类型

1. 基于程序代码内部实现

原理：在应用程序代码中显式指定读/写操作的数据源，例如：

  • 使用 多数据源配置（如 Spring Boot 中配置 masterDataSource 和 slaveDataSource）。

  • 通过 AOP（面向切面编程） 或 注解 动态切换数据源（如 @ReadOnly 注解标记读操作）。

优点：

  • 灵活控制：可根据业务逻辑定制路由规则（如特定查询强制走主库）。

  • 低依赖：无需额外中间件，减少运维复杂度。

缺点：

  • 代码侵入性：需修改业务代码，增加维护成本。

  • 扩展性受限：新增从库需修改配置并重启应用。

2. 基于中间代理实现

原理：通过独立的中间件代理层（Proxy）自动路由请求，对应用透明。
常见工具：

  • ProxySQL：高性能开源代理，支持动态路由、连接池管理、故障转移。

  • MyCAT：基于阿里开源的分布式数据库中间件，支持读写分离、分库分表。

  • MySQL Router：官方轻量级中间件，集成于 MySQL InnoDB Cluster。

工作流程：

  1. 应用连接代理：应用将 SQL 请求发送到代理服务器。

  2. 代理解析 SQL：代理解析 SQL 类型（读/写）。

  3. 路由决策：

    • 写操作（INSERT/UPDATE/DELETE） → 转发到主库。

    • 读操作（SELECT） → 按负载均衡策略（轮询、权重、最小连接数）分发到从库。

  4. 结果返回：代理将数据库响应返回给应用。

优点：

  • 对应用透明：无需修改业务代码，降低侵入性。

  • 动态管理：支持在线添加/移除从库，代理自动感知。

  • 高可用：内置健康检查，自动剔除故障节点。

缺点：

  • 性能开销：代理层可能成为性能瓶颈（需选择高性能代理如 ProxySQL）。

  • 运维复杂度：需额外维护代理服务器，增加架构复杂度。

（2）工作原理过程

  1. 请求识别：

    • 中间件或应用层解析 SQL，区分读（SELECT）和写（INSERT/UPDATE/DELETE）。

  2. 路由策略：

    • 写操作：直接转发至主库。

    • 读操作：按负载均衡策略（轮询、权重、随机）分发到从库。

  3. 负载均衡与故障转移：

    • 中间件监控从库状态，自动剔除故障节点。

  4. 数据一致性处理：

    • 强制特定读请求走主库（如通过 /* FORCE_MASTER */ 注释）。

三、主从复制与读写分离的关系

  • 基础与扩展：

    • 主从复制是读写分离的基础，为读写分离提供数据同步支持。

    • 读写分离是主从复制的扩展应用，进一步提升系统性能。

  • 一致性权衡：

    • 主从同步存在延迟（异步复制），读写分离可能导致“读旧数据”。

    • 解决方案：强制特定读请求走主库（如通过 ProxySQL 的 /* FORCE_MASTER */ 注解）。

四、主从复制配置 

（1）主从复制搭建

主服务器配置：
1.关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
setenforce 0

2.修改/etc/my.cnf文件
log-bin=/usr/local/mysql/data/mysql-bin
binlog_format=MIXED
server-id=1  #id号不能相同

3.重启服务
systemctl restart mysqld

4.创建用户给从服务器授权
create user 'myslave'@'%' identified by '123456';
grant replication slave on *.* to 'myslave'@'%';
alter user 'myslave'@'%' identified with mysql_native_password by '123456';

5.刷新权限并查看主数据库状态信息
flush privileges;
show master status;

从服务器配置：
1.修改/etc/my.cnf文件
server-id=2  //添加 唯一服务器标识符。不可以冲突

2.连接主（记得关主防火墙）
change master to master_host='主IP',master_user='主账号名'，master_password='',master_log_file='log名',master_log_pos='position号';
例如：
change master to master_host='192.168.10.101',master_user='myslave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=324;

3.开启从服务，启动同步
start slave；
4.查看状态  （IO ,SQL 为Yes）
show slave status\G
或
mysql -uroot -ppwd123 "show slave status\G" | grep "YES"

验证：

1. 主从 登录MySQL

2. 再主上创建新数据库，在从上查看是否同步

（2）主主复制搭建

#双机互为主从
在主从复制的基础上做（改从的配置）
1.添加配置文件 /etc/my.cof
log-bin=/usr/local/mysql/data/mysql-bin
binlog-format=MIXED
2.创建用户开启权限
create user 'myslave'@'%' identified by 'pwd123';
grant replication slave on *.* to 'myslave'@'%';
alter user 'myslave'@'%' identified with mysql_native_password by 'pwd123';
3.刷新
flush privileges;
show master status;	  #看主状态信息
4.先关闭从同步
stop slave;
5.在主上设置从为它的主
change master to master_host='主IP',master_user='主账号名'，master_password='',master_log_file='log名',master_log_pos=position号；
6.双机开启从同步，查看从状态
start slave;
show master status;

五、读写分离搭建

#MySQL读写分离是建立于MySQL主从的基础上的
搭建一台mycat服务器
1.准备软件包
jdk-8u171-linux-x64.rpm
mycat2-1.21-release-jar-with-dependencies.jar
mycat2-install-template-1.20.zip
mysql-connector-java-8.0.18.jar
2.准备环境并安装mycat
rpm -ivh jdk-8u171-linux-x64.rpm（解决不了依赖关系）
dnf -y install jdk-8u171-linux-x64.rpm（解决依赖关系）
unzip mycat2-install-template-1.20.zip -d /usr/local	（解压到local下）
ln -s /usr/local/mycat/bin/* /usr/local/bin/	（做系统环境）
cp mysql-connector-java-8.0.18.jar mycat2-1.21-release-jar-with-dependencies.jar /usr/local/mycat/lib/		（把架包复制进来）
cd /usr/local/mycat/bin/ 
chmod +x *  
mycat -h	  #测试安装的正确性
3.在主服务器上为mycat创建用户并授权（从会自动同步），使mycat可以连接主服务器
create user 'mycat'@'%' identified by 'pwd123';
grant all on *.* to 'mycat'@'%';
alter user 'mycat'@'%' identified with mysql_native_password by 'pwd123';
flush privileges;
4.mycat配置
vim /usr/local/mycat/conf/datasources/prototypeDs.datasource.json
    "password":"pwd123",	（更改密码）
    "url":"jdbc:mysql://192.168.10.101:3306/mysql?     (更改成主的IP）
    "user":"mycat",		（更改用户名）

mycat start	(启动mycat)
netstat -anpt |grep mycat
netstat -anpt |grep 8066	（查看启动了没有）
systemctl stop firewalld  (关掉防火墙）
5.客户机验证
dnf -y install mysql   （没有mysql命令装）
mysql -uroot -p123456 -P8066 -h192.168.10.104		(这个密码是mycat提供给客户端使用的)
create database aaa;		

注意：mycat 连接的是主，通过mycat创建的库 主从是可以看到的 通过主创建的库 mycat是看不到的。
读写分离配置，有两种方式，一是直接在Mycat的配置目录“/usr/local/mycat/conf”的子目录编辑相关的文本文件；二是登录到mycat交互界面。用特殊的SQL命令配置。
##演示第二种方式

第一步 mycat 添加数据源
   #以管理员的身份登录到mycat （别登录到其他节点了）
# 增加主库master
/*+ mycat:createDataSource{ "name":"master","url":"jdbc:mysql://192.168.10.101:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","instanceType":"WRITE","user":"mycat","password":"pwd123"} */;

#添加从节点的库
/*+ mycat:createDataSource{ "name":"slave1","url":"jdbc:mysql://192.168.10.102:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","instanceType":"READ","user":"mycat","password":"pwd123"} */;

/*+ mycat:createDataSource{ "name":"slave2","url":"jdbc:mysql://192.168.10.103:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","instanceType":"READ","user":"mycat","password":"pwd123"} */;

#查看数据源信息
/*+ mycat:showDataSources{} */\G
注意：如果数据源配置有误可使用“/*+ mycat:resetConfig{} */;” 进行重置
执行前面的SQL语句后，在目录"/usr/local/mycat/conf/data/sources"下会自动生成文本文件。

第二步 创建mycat集群
/*! mycat:createCluster{"name":"cls01","masters":["master"],"replicas":["slave1","slave2"]} */;


可以通过查看mycat集群
ls /usr/local/mycat/conf/datasources

还可以查看负载均衡的算法：看是否为轮询“BalanceRoundRobin”
cat /usr/local/mycat/conf/server.json

重启mycat  
mycat retart

第三步  验证Mycat读写分离效果
1.登录集群
mysql -uroot -p123456 -P8066 -h192.168.10.101	（登录，IP为主服务器）
create database test;    （创建库测试一下）
注意：在mycat 的schemas目录下会有一个该库明明的文件，要修改文件标注的mycat的集群，然后重新启动mycat（pwd--ll--cat test.schema.json）
mycat restart   #重启
create table test.zang(id int(10),name varchar(10),address varchar(20));   #创建表的操作会路由到master主服务器执行，并同步到slave

2.停止主从同步
stop slave；
3.在主和从创建测试数据
在主从分别在zang表里添加数据
如：
主
insert into test.zang values('1','zhangsan','this_is_master');
从
insert into test.zang values('2','zhangsan','this_is_slave1');
#测试读操作
登录mycat集群,查询test.zang的数据
select * from test.zang   #多查几次，结果为从服务器解析
#测试写操作
登录集群   mysql -uroot -p123456 -P8066 -h192.168.10.101
insert into zang values('4','zhang','write_test');

在主上查看(只有主上可以查到)
show databases;
show tables;
select * from zang;

伤不起bb
关注
Mysql主从复制与读写分离
weixin_48861962的博客
11-01 1066
一、MySQL主从复制与读写分离 1.1 主从分离和读写分离的相关概述 1）主从复制与读写分离、 在实质际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实院需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。 2）为什么要读写分离 因为数据库的"写"（
MySQL主从复制与读写分离
可笑
08-01 927
MySQL的主从复制和 MySQL的读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离。(1)MySQL支持的复制类型①基于语句的复制。在主服务器上执行的 SQL语句，在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制，效率比较高。②基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。③混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。
mysql主从复制与读写分离
gcc001224的博客
06-28 661
在实际的生产环境中，如果对MySQL数据库的读和写都在一台数据库服务中操作，无论在安全性、高可用性，还是高并发性等各个方面都是完全不能满足实际需求的，一般来说都是通过主从复制（Master-Slave)的方式来同步数据，再通过读写分离来提升数据库的并发负载能力这样的方案进行部署与实施 两日志、三线程 主库将更新写入Binlog日志文件后，不需要等待数据更新是否已经复制到从库中，就可以继续处理更多的请求。Master将事件写入binlog，但并不知道Slave是否或何时已经接收且已处理。在异步复制的机制的情况
MySQL主从复制和读写分离
2301_81272194的博客
03-28 2498
关于MySQL主从复制和读写分离相关内容
MySQL主从复制与读写分离.docx
04-13
MySQL 主从复制与读写分离 MySQL 主从复制是指将一个 MySQL 服务器的数据实时同步到另一个 MySQL 服务器中，以实现数据的高可用性和读写分离。下面是 MySQL 主从复制与读写分离的详细知识点： MySQL 主从复制 ...
MySQL主从复制与读写分离配置详解
10-31
内容概要：本文详细介绍了如何在两台MySQL服务器上配置主从复制和读写分离，以实现数据同步更新和降低数据库压力。具体步骤包括安装MySQL、配置主服务器和从服务器、导入数据库以及配置MySQL配置文件。通过具体的...
MySQL、FastAPI 和 React 构建一个完整的全栈待办事项应用
No trade is free, but my contents are.
05-18 1013
MySQL 数据库：存储待办事项FastAPI 后端：提供 RESTful APIReact 前端：用户界面。
MySQL——十一、主从复制
2302_80831260的博客
05-16 384
主从复制是指将主数据库的DDL和DML操作通过二进制日志传入从库服务器中，然后在从库上对这些日志重新执行（重做），从而使得从库和主库的数据保持同步。
sparkSQL读入csv文件写入mysql
2301_80936715的博客
05-18 259
mysql的字符集问题。“让字符集认识中文”
MySQL——1、数据库基础
Talon的博客
05-15 1135
本文介绍了MySQL数据库的基础知识，包括安装步骤、数据库概念、使用案例、架构与SQL分类以及存储引擎。首先，详细说明了如何在Linux系统上安装和配置MySQL，包括更新软件包、安装MySQL服务、启动服务以及配置mysqld.cnf文件。接着，解释了数据库的基本概念，如MySQL的客户端和服务端、数据库的存储结构及其与文件系统的关系。文章还通过一个简单的案例展示了如何创建数据库、表以及插入和查询数据。
linux——mysql故障排查与生产环境优化
最新发布
2401_88768957的博客
05-19 931
MySQL 引擎（存储引擎）是数据库管理系统的核心组件，负责数据的存储、检索、索引和事务处理。不同的引擎提供不同的功能特性，用户可以根据业务需求选择最适合的引擎。引擎决定数据在磁盘上的存储格式（如 InnoDB 的聚簇索引、MyISAM 的索引与数据分离）。负责数据文件的组织、缓存和读取（如 InnoDB 的缓冲池缓存热点数据）。事务处理：原子性（Atomicity）：事务中的操作要么全部成功，要么全部失败。一致性（Consistency）：事务执行前后数据保持一致状态。
MySQL Workbench 工具导出与导入数据库：实用指南
C_V_Better的博客
05-17 828
MySQL Workbench 是一款功能强大的数据库管理工具，支持数据库的设计、管理和维护。本文详细介绍了如何使用 MySQL Workbench 进行数据库的导出和导入操作。导出数据库时，用户可以通过图形界面选择数据库、设置导出格式和选项，并执行导出操作，生成 SQL 文件。导入数据库时，需先创建目标数据库，选择导入文件并设置相关选项，最后执行导入操作。文章还强调了备份数据、检查权限、网络问题和文件格式兼容性等注意事项。通过掌握这些操作，用户可以更高效地进行数据库管理，确保数据的安全性和可靠性。
mysql故障排查与环境优化
2401_83683659的博客
05-19 137
核心服务功能（sql接口、缓存的查询、sql的分析和优化以及部分内置函数的执行等。存储引擎层（负责mysql中数据的存储和提取。mysql的运行分为三层。一、mysql运行原理。
Spark，SparkSQL操作Mysql， 创建数据库和表
2401_82393285的博客
05-18 452
下载 mysql-connector-java-x.x.x.jar ，放入 Spark 目录的 jars 文件夹（或提交任务时通过 --jars 参数指定）。.option("url", "jdbc:mysql://localhost:3306/test_db") // 指定数据库。"jdbc:mysql://localhost:3306/", // MySQL 连接地址（不带数据库名）.mode("overwrite") // 覆盖模式（可根据需求改为 append/ignore）
SpringBoot Vue MySQL酒店民宿预订系统源码（支付宝沙箱支付）+代码讲解视频
欧阳小白闯天涯
05-14 804
本文介绍了一个基于SpringBoot、Vue和MySQL的酒店民宿预订系统，集成了支付宝沙箱支付接口，面向管理员、游客（普通用户）和房东三类用户。系统实现了民宿预订的全流程，包括信息管理、支付、沟通、反馈和数据分析等功能。管理员负责平台数据与用户管理，游客可以进行民宿搜索、预订、支付和评价，房东则管理民宿信息和收益。技术栈包括SpringBoot、MyBatis、Vue.js、ElementUI和MySQL，系统采用基于角色的权限控制（RBAC）确保安全性。文章还提供了系统源码和演示视频的获取方式。
MySQL 误删除数据恢复全攻略：基于 Binlog 的实战指南
2302_82041293的博客
05-15 704
在MySQL的世界里，二进制日志（Binlog）就是我们的"时光机"。它默默记录着数据库的每一个重要变更，就像一位忠实的史官，为我们在数据灾难中提供最后的救命稻草。本文将带您深入掌握如何利用这个强大的工具。在数字化办公的日常场景中，因人为操作失误引发的数据危机屡见不鲜。想象一下，在电商大促前夕的紧张部署中，开发人员误将生产环境执行DROP TABLE orders_history，导致五年的交易记录瞬间消失；
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值