Mysql主从复制和读写分离

Mysql主从复制与读写分离
最新推荐文章于 2025-09-16 20:28:15 发布
原创 最新推荐文章于 2025-09-16 20:28:15 发布 · 244 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库 #sql

本文介绍Mysql主从复制的概念、原理及其实现步骤，同时讲解了读写分离的应用场景与实现方式，并提供了详细的实验部署教程。

文章目录

一，Mysql主从复制和读写分离

1.1相关概述
在实际生产环境中，对数据库的读写都在同一个数据库服务器中，不能满足需求，因此通过主从复制的方式来同步数据，在通过读写分离来提升数据库的并发能力，类似于rsync，不同的是rsync是对磁盘文件做备份，mysql主从复制是对数据库中的数据，语句做备份。

1.2读写分离：
数据库的写入速度操作比较耗时
但是读取速度非常快
所以读写分离，解决的是数据库的写入，会影响查询的效率

1.3什么时候用读写分离呢？
数据库不一定要读写分离，如果程序使用数据库较多时，更新数据较少，查询多的情况下会考虑使用。利用数据库主从同步，解决的是，数据库的写入，影响了查询的效率。

1.4读写分离的原理
读写分离的基本原理是让主数据库处理事务性增、改、删操作
让从数据库处理select查询操作。
数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

1.5mysql支持的复制类型
STATEMENT：基于语句的复制。在服务器上执行sgl语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制，执行效率高。
ROW：基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
MIXED：混合类型的复制。默认采用基于语句的复制，一旦发信息基于语句无法精确复制时，就会采用基于行的复制。

1.6主从复制的工作过程
在每个事务更新数据完成之前，Master在二进制日志(Binary log)记录这些改变。写入二进制日志完成后，Master通知存储引擎提交事务。
Slave将Master复制到其中继日志(Relay log) 。首先slave开始一个工作线程(I/O)，I/0线程在Master上打开一个普通的连接，然后开始Binlog dump process。Binlogdumpprocess从Master的二进制日志中读取事件，如果已经跟上Master,它会睡眠并等待Master产生新的事件，I/O线程将这些事件写入中继日志（缓存中）。
SQL slave thread (SQL从线程)处理该过程的最后一步，SQL线程从中继日志读取事件，并重放其中的事件而更新Slave数据，使其与Master 中的数据一致，只要该线程与I/O 线程保持一致，中继日志通常会位于OS（系统）缓存中，所以中继日志的开销很小。复制过程有一个很重要的限制，即复制在 Slave 上 是串行化的，也就是说Master 上的并行更新操作不能在Slave上并行操作。

1.7Mysql主从复制延迟
形成原因：master服务器高并发，形成大量事务
网络延迟
主从硬件设备导致 cpu主频、内存io、硬盘io
本来就不是同步复制、而是异步复制
从库优化Mysql参数。比如增大innodb_buffer_pool_size，让更多操作在Mysgl内存中完成，减少磁盘操作。从库使用高性能主机。包括cpu强悍、内存加大。避免使用虚拟云主机，使用物理主机，这样提升了i/o方面性。从库使用SSD磁盘
网络优化，避免跨机房实现同步

1.8目前较常见的mysql读写分离
读写分离分为以下两种∶
（1）基于程序代码内部实现
在代码中根据 select、insert，进行路由分类，这类方法也是目前生产环境应用最广泛的。
优点是性能较好，因为在程序代码中实现，不需要增加额外的设备为硬件开支;
缺点是需要开发人员来实现，运维人员无从下手。
但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的Java应用，如果在程序代码中实现读写分离对代码改动就较大。

（2）基于中间代理层实现
代理一般位于客户端和服务器之间， 代理服务器接到客户端请求后通过判断后转发到后端数据库， 有以下代表性程序。

①MySQL-Proxy。 MySQL-Proxy 为 MySOL开源项目， 通过其自带的 lua 脚本进行SOL 判断。
②Atlas。是由奇虎360的Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy0.8.2版本的基础上，对其进行了优化， 增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以 及存储过程。
③Amoeba。 由陈思儒开发，作者曾就职于阿里巴巴。该程序由Jaya语言进行开发，阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。

二，主从复制实验

准备三台服务器
主服务器：192.168.100.254
从服务器1：192.168.100.10
从服务器2：192.168.100.11
（全部关闭防火墙）
主服务器配置：

vim /etc/ntp.conf  #配置主从服务器时间同步设置

末行添加

server 127.127.100.0  #设置本地是时钟源，注意修改网段
fudge 127.127.100.0 stratum 8 #设置时间层级为8（限制在15以内）

从服务器配置：

yum -y install ntp ntpdate
service ntpd start
crontab -e #创建自动化任务
*/30 * * * * /usr/sbin/ntpdate 192.168.100.254

vim /etc/my.cnf
##添加以下配置
server-id = 11 #修改服务器id为11
log-bin=master-bin #添加，主服务器开启二进制日志
binlog_format = MIXED #添加，mysql支持的复制类型为MIXED
log-slave-updates=true #添加，允许从服务器更新二进制日志

systemctl restart mysqld.service #重启服务器

登录mysql
GRANT REPLICATION SLAVE ON . TO ‘myslave’@‘192.168.100.%’ IDENTIFIED BY ‘123456’;添加授权

在这里插入图片描述
SHOW master status; #查看主服务状态，如下代表正确
在这里插入图片描述
#file显示日志名，position显示偏移量

从服务器mysql设置：
在这里插入图片描述
重启mysql

配置同步：
注意master_log_file和master_log_pos的值要与Master查询的一致
change master to master_host=‘192.168.100.254’ , master_user=‘myslave’,master_password=‘123456’,master_log_file=‘master-bin.000001’,master_log_pos=452;

在这里插入图片描述
start slave；启动服务
show slave status\G #查看Slave状态
在这里插入图片描述
Slave_IO_Running: Yes #负责与主机的io通信
Slave_SQL_Running: Yes #负责自己的slave mysql进程

#一-般Slave_IO_Running: No的可能性:
1、网络不通
2、my. cnf配置有问题
3、密码、file文件名、pos偏移量不对
4、防火墙没有关闭

三，读写分离实验

master服务器 192.168.100.254
slave1服务器192.168.100.10
slave2服务器192.168.100.11
amoeba服务器192.168.100.12
客户端192.168.100.13

amoeba服务器：
设置java，我用的rpm安装
cd /opt/
rpm -qpl jdk-8u201-linux-x64.rpm
rpm -ivh jdk-8u201-linux-x64.rpm
两条命令安装rpm
查看原来的java：
在这里插入图片描述
改成刚刚安装的java的话需要设置jdk环境变量：
vim /etc/profile.d/java.sh
在这里插入图片描述
在这里插入图片描述
运行脚本
在这里插入图片描述
再次查看，变成了我刚刚安装的201版本

vim /etc/profile
末行添加：

export JAVA_HOME=/usr/java/jdk1.8.0_201-amd64
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin

source /etc/profile

补充：
CLASSPATH：编译、运行Java程序时，JRE会去该变量指定的路径中搜索所需的类（.class）文件。
dt.jar：是关于运行环境的类库，主要是 swing 的包。
tools.jar：主要是一些jdk工具的类库，包括javac,java,javap，javadoc等。
JDK ：java development kit （java开发工具）
JVM ：java virtuak machine （java虚拟机），使java程序可以在多种平台上运行class文件。

然后安装amoeba
mkdir /usr/local/amoeba
tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod -R 755 /usr/local/amoeba/
/usr/local/amoeba/bin/amoeba
显示：amoeba start|stop 说明安装成功

如果显示：
在这里插入图片描述

查看文件
在这里插入图片描述
在这里插入图片描述
在运行一次
在这里插入图片描述
ok，amoeba安装完成

配置amoeba读写分离，两个slave读均衡负载

在master和两台slave上给amoeba权限访问

mysql -uroot -pabc123
grant all on *.* to test@'192.168.100.%' identified by '123456';

然后再amoeba服务器上配置

cd /usr/local/amoeba/conf/
 
cp amoeba.xml amoeba.xml.bak #备份配置文件
vim amoeba.xml
## 30行修改 ##
<property name="user">amoeba</property>
## 32行修改 ##
<property name="password">123456</property>
## 115行修改 ##
<property name="defaultPool">master</property>
## 117行去掉注释 ##
<property name="writePool">master</property>
<property name="readPool">slaves</property>

cp dbServers.xml dbServers.xml.bak

vim dbServers.xml #修改数据库配置文件
## 23行注释掉 ##
<!-- mysql schema
<property name="schema">test</property>
-->
## 26行修改 ##
<!-- mysql user -->
<property name="user">test</property>
## 28 - 30 行取消注释 ##
<property name="password">123456</property>
## 45行修改 ##
<dbServer name="master" parent="abstractServer">
## 48行修改主服务器id ##
<property name="ipAddress">192.168.100.254</property>
## 52行修改从服务器名slave1 ##
<dbServer name="slave1" parent="abstractServer">
## 55行修改为从服务器slave1的ip ##
<property name="ipAddress">192.168.100.10</property>
## 复制58行上面6行粘贴，设置从服务器2的名slave2和地址
<dbServer name="slave2" parent="abstractServer">
<property name="ipAddress">192.168.100.11</property>
## 65行修改 ##
<dbServer name="slaves" virtual="true">
## 71行修改 ##
<property name="poolNames">slave1,slave2</property>

/usr/local/amoeba/bin/amoeba start& #执行此程序，按Ctrl + c 返回
netstat -anpt | grep java #查看是否开启了8066端口在这里插入图片描述

客户机测试：
systemctl stop firewalld
systemctl disable firewalld
setenforce 0

yum install -y mariadb-server mariadb
mysql -u amoeba -p123456 -h 192.168.100.12 -P8066 #通过amoeba服务器代理访问mysql ，在通过客户端连接mysql后写入的数据只有主服务会记录，然后同步给从服务器

在这里插入图片描述

在这里插入图片描述

主服务器

use TEST;
create table test(id int(10),name varchar(10),address varchar(20));

两台从服务器

stop slave; #关闭同步
USE TEST;

slave1 上

insert into test values(‘1’,‘zhangsan’,‘this_is_slave1’);

slave2 上

insert into test values(‘2’,‘lisi’,‘this_is_slave2’);

主服务器 上

insert into test values(‘3’,‘wanger’,‘this_is_master’);

客户端 上

use TEST;
select * from test; #客户端会分别向slave1和slave2读取数据，显示的只有在两个从服务器上添加的数据，没有在主服务器上添加的数据
insert into test values(‘4’,‘赵六’,‘this_is_client’); ##只有主服务器上有此数据

OSoooo
关注
MySQL主从复制与读写分离
qq1356059950的博客
06-27 1333
在实际生产环境中，面对巨大的并发量和数据读写压力，我们往往需要多台数据库服务器来提共服务，并且将读和写的的压力分担到不同的服务器上，以满足在安全性与高可用性上的需求。MySQL 的主从复制和 MySQL 的读写分离两者有着紧密联系，首先要部署主从复制， 只有主从复制完成了，才能在此基础上进行数据的读写分离。基于语句的复制（STATEMENT）。 在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制，效率比较高。基于行的复制（ROW）。 把改变的内容复制过去，而不是
mysql主从复制 及读写分离
大圣欲何的博客
08-02 799
mysql主从复制 及读写分离 数据库对于任何企业都是至关重要的，数据库的备份冗灾非常重要。 MySQL的主从架构模型可以用来进行备份、故障迁移、或者读写分离来分担负载。 采用读写分离时，在程序中通过封装数据库访问层可以实现写操作连接主库，读操作连接从库。但是当从库较多时，程序中对多个从库做切换比较麻烦。 MySQL官方为我们提供了mysql-proxy这个代理组件，对于读写的sql操作，
Mysql-主从复制与读写分离
最新发布
m0_46589218的博客
09-16 1166
在企业级应用中，随着业务增长，单台 MySQL 数据库在安全性（单点故障风险）、高可用性（故障后服务中断）和高并发（读写请求拥堵）方面逐渐无法满足需求。此时，通过 “主从复制” 实现数据多节点同步，再结合 “读写分离” 分担请求压力，成为解决上述问题的核心方案 —— 主从复制保障数据一致性与高可用，读写分离提升并发处理能力，二者相辅相成，构成企业级 MySQL 架构的基础。1.主从复制通过 “两日志、三线程” 实现数据同步，提供数据冗余与高可用，支持异步、同步、半同步等多种策略，平衡性能与安全性。
企业中最常用的——MySQL主从复制与读写分离
panrenjun的博客
02-28 1396
文章目录MySQL主从复制与读写分离一、案例概述二、MySQL主从复制与读写分离理论1、什么是读写分离2、为什么要读写分离3、什么时候要读写分离4、主从复制与读写分离5、mysql 支持的复制类型6、主从复制的工作过程7、MySQL读写分离原理8、常见的 MySQL 读写分离分为两种三、MySQL主从复制与读写分离实验部署（一）、搭建MySQL主从复制1、MySQL主从服务器时间同步2、主服务器的MySQL配置3、从服务器的MySQL配置 MySQL主从复制与读写分离 一、案例概述 在实际的生产环境中，如果
[MySQL高级](七) MySQL主从复制及读写分离实战
热门推荐
睁眼看世界
05-26 1万+
1. 简介 　　随着技术的发展，在实际的生产环境中，由单台MySQL数据库服务器不能满足实际的需求。此时数据库集群就很好的解决了这个问题。采用MySQL分布式集群，能够搭建一个高并发、负载均衡的集群服务器。在此之前我们必须要保证每台MySQL服务器里的数据同步。数据同步我们可以通过MySQL内部配置就可以轻松完成，主要有主从复制和主主复制。 　　MySQL数据库自身提供的主从复制功能可以方便的...
MySQL 主从复制和读写分离
weixin_68840588的博客
06-24 1188
MySQL 主从复制和读写分离
MySQL 主从复制和读写分离6-7.docx
07-04
通过本文的介绍，我们可以看到MySQL主从复制和读写分离技术在提高数据库性能、确保数据安全性和提高系统可用性方面发挥着重要作用。无论是对于初创公司还是大型企业，掌握这些技术都是至关重要的。希望本文能帮助...
MySQL主从复制和读写分离
weixin_67565536的博客
07-03 2957
前言在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。 MySQL 的主从复制和 MySQL 的读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离。主MySQL服
MySQL主从复制与读写分离原理及用法详解
09-08
MySQL主从复制与读写分离是数据库高可用性和负载均衡的重要技术。主从复制允许数据从一个服务器（主服务器）自动同步到其他服务器（从服务器），而读写分离则是将读操作分散到从服务器，以减轻主服务器的压力，提高...
MYSQL主从复制和读写分离.ppt
10-20
MYSQL主从复制和读写分离.ppt
mysql主从复制与读写分离
m0_71931851的博客
11-07 695
mysql主从复制与读写分离
MySql主从复制与读写分离
m0_50749129的博客
05-30 153
配置NTP服务器 安装NTP [root@centos01 ~]# yum -y install ntp 修改NTP配置文件 [root@centos01 ~]# vim /etc/ntp.conf 启动服务设置开机自动启动 从服务器同步时间 修改从mysql主配置文件 修改从服务器主配置文件 [root@centos02 ~]# vim /etc/my.cnf 重新启动服务 [root@centos02 ~]# systemctl restart mysqld 配置主从复制 创建主从复制
MySQL主从复制及读写分离
m0_67062351的博客
06-20 252
MySQL主从复制及读写分离
Mysql主从复制与读写分离
weixin_67033111的博客
06-19 561
主从复制与读写分离
分布式系统mysql主从复制和读写分离
03-29
<think>嗯，用户想知道MySQL在分布式系统中如何配置主从复制和读写分离，以及它们的原理。首先，我需要回忆主从复制的基本流程。主库记录二进制日志，从库通过IO线程读取这些日志，然后SQL线程重放，对吧？这确保了数据一致性。不过用户可能还需要知道具体的配置步骤，比如修改my.cnf文件，创建复制用户，还有CHANGE MASTER命令这些步骤。 然后是读写分离的部分。主库处理写操作，从库处理读操作，这样可以提升性能。但用户可能关心如何实现自动的读写分离，可能需要中间件或者框架支持，比如MyCat或ShardingSphere。这部分需要解释清楚原理，以及配置方法。 另外，用户可能对主从复制的同步机制有疑问，比如异步、半同步和全同步的区别，以及各自的优缺点。异步可能性能好但有数据丢失风险，半同步则是在两者之间平衡。需要解释这些模式适用场景。 还有，读写分离带来的数据一致性问题。主从同步有延迟的话，读从库可能拿到旧数据。需要讨论如何解决，比如强制读主库或者监控延迟。用户可能在实际应用中遇到这个问题，所以解决方案很重要。 最后，用户可能想知道高可用性的扩展，比如主库故障时的切换，或者如何监控复制状态。这部分可以提到MHA或者Keepalived这样的工具，帮助提升系统的可靠性。 现在要确保回答结构清晰，分步骤讲解配置，再讲原理，最后讨论注意事项和扩展。同时需要引用提供的资料，比如引用[2]提到动态切换分库分表，可能和主从配置有关联。但用户的问题更聚焦在主从复制和读写分离，所以重点应该放在这里。需要检查引用内容是否相关，适当引用。比如引用[3]提到集中式授权，可能和权限配置有关，但可能不太直接相关。可能更多参考引用[2]关于分库分表的动态切换，但主从复制是基础，需要明确区分。 还要生成相关问题，比如主从延迟如何处理，半同步配置，或者读写分离中间件的选择。这些问题能帮助用户深入理解。确保回答中的LaTeX格式正确，数学表达式用$...$，独立公式用$$...$$，并且正确使用引用标记。</think>### MySQL主从复制与读写分离的实现原理与配置 #### 一、主从复制核心原理 1. **二进制日志(Binlog)机制** 主库将所有数据变更操作记录到二进制日志中，格式为$binlog\_format=ROW|STATEMENT|MIXED$[^1]。从库通过I/O线程实时拉取主库的Binlog，并写入本地中继日志(Relay Log)。 2. **数据同步流程** $$ \begin{aligned} &\text{主库写入} \rightarrow binlog \xrightarrow[\text{网络传输}]{\text{I/O线程}} relay\ log \xrightarrow{\text{SQL线程}} \text{从库数据} \\ &\Delta t_{同步} = t_{网络} + t_{SQL解析} + t_{从库写入} \end{aligned} $$ 延迟主要受网络带宽、从库硬件性能和事务复杂度影响[^2]。 #### 二、配置步骤详解 1. **主库配置** (`my.cnf`) ```ini [mysqld] server-id=1 log-bin=mysql-bin binlog_format=ROW ``` 2. **从库配置** (`my.cnf`) ```ini [mysqld] server-id=2 relay-log=mysql-relay ``` 3. **建立复制链路** ```sql -- 主库创建复制账号 CREATE USER 'repl'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; -- 从库执行 CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154; ``` #### 三、读写分离实现方案 1. **中间件方案** 使用MyCat/ProxySQL等中间件，配置规则： ```sql -- 写路由 INSERT/UPDATE/DELETE -> 主库 -- 读路由 SELECT -> 从库（支持负载均衡） ``` 2. **客户端分离方案** 在ORM框架（如MyBatis）中通过注解指定数据源： ```java @DataSource("master") public void insertData(...); @DataSource("slave") public List<Data> queryData(...); ``` #### 四、关键注意事项 1. **复制延迟问题** - 强制读主库：`/* FORCE_MASTER */ SELECT ...` - 并行复制：设置`slave_parallel_workers=8` - 半同步复制：`rpl_semi_sync_master_timeout=1000` 2. **高可用扩展** 采用MHA+Keepalived实现主库故障自动切换，VIP漂移时间可控制在10-30秒[^3]。 ```mermaid graph TD A[应用层] --> B{SQL类型} B -->|写操作| C[主库] B -->|读操作| D[从库集群] C --> E[Binlog] E --> F[从库I/O线程] F --> G[Relay Log] G --> H[从库SQL线程] ```
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值