百度360必应搜狗淘宝本站头条
python判断字典是否为空crontab每周一执行aes和des区别安装指定版本npmadb pull
当前位置：网站首页 > IT知识 > 正文

MySQL 主从复制、读写分离理论分析+实战演示

liuian 2025-03-20 16:23 7 浏览

引言

在企业应用中，成熟的业务通常数据量都比较庞大，如果对 MySQL 数据库的读和写都在一台数据库服务器上操作，无论是在安全性、高可用性，还是高并发等各个方面都是不能满足实际需求的。因此，一般来说都是通过主从复制（Master-Slave）的方式来同步数据，再通过读写分离来提升数据库的并发负载能力这样的方案进行部署与实施。

一、主从复制

1. 原理

MySQL 的主从复制和 MySQL 的读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离。

  • 主从复制的核心：2个日志、3个线程

master 线程：bin log 二进制日志、dump 线程

master 上的 dump 线程会监控 bin log 二进制日志的更新，若有更新会通知 slave的 I/O 线程

slave：relay log 中继日志、I/O 线程、SQL 线程

线程1：

slave上的 I/O 线程会向 master 申请同步 bin log 二进制日志的更新内容，slave的 I/O 线程把更新内容写入自己的中继日志

线程2:

slave 的 SQL 线程把日志中的更新语句同步执行到内容，以达到和 master 数据库趋于一致

2. 支持的复制类型

STATEMENT：基于语句的复制，在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句；MySQL 默认采用基于语句的复制，效率比较高

ROW：基于行的复制，把改变的内容复制过去，而不是把命令在从服务器上执行一遍

MIXED：混合类型的复制，默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制

3. 复制的工作过程

① 在每个事务更新数据完成之前，Master 都会在二进制日志记录这些改变；写入二进制日志完成后，Master 通知存储引擎提交事务；

② Slave 将 Master 的 Binary log 复制到其中继日志

首先，Slave 开始一个工作线程——I/O 线程，I/O 线程在 Master 上打开一个普通的连接，然后开始 Binlog dump process；Binlog dump process 从 Master 的二进制日志中读取时间，如果已经跟上 Master，它会睡眠并等待Master 产生新的时间；I/O 线程将做这些事件写入中继日志；

③ SQL slave thread（SQL 从线程）处理该过程的最后一步

SQL 线程从中继日志读取事件，并重放其中的事件而更新 Slave 的数据，使其与 Master 中的数据一致；只要该线程与 I/O 线程保持一致，中继日志通常会位于 OS 的缓存中，所以中继日志的开销很小；

④ 复制过程有一个很重要的限制，即复制在 Slave 上是串行化的，也就是说 Master 上的并行更新操作不能在 Slave 上并行操作。

4. MySQL主从复制延迟

（1）master 服务器高并发，形成大量事务

（2）网络延迟

（3）主从硬件设备导致：cpu主频、内存io、硬盘io

（4）本来就不是同步复制、而是异步复制

（5）从库优化 Mysql 参数，比如增大 innodb_buffer_pool_size，让更多操作在 Mysql 内存中完成，减少磁盘操作。

（6）从库使用高性能主机，包括 cpu 强悍、内存加大。避免使用虚拟云主机，使用物理主机，这样提升了I/O方面性能。

（7）从库使用SSD磁盘

（8）网络优化，避免跨机房实现同步

二、读写分离

1. 原理

  • 读写分离就是只在主服务器上写，只在从服务器上读
  • 基本的原理是让主数据库处理事务性查询，而从数据库处理 select 查询。数据库复制被用来把主数据库上事务性查询导致的变更同步到集群中的从数据库

为什么要读写分离呢？

因为数据库写 10000 条数据可能要 3 分钟，操作比较耗时，但是数据库读 10000 条数据可能只要5秒钟，所以读写分离，解决的是，数据库的写入，影响了查询的效率。

读写分离的过程如下图所示

2. 读写分离作用

  • 数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用。
  • 利用数据库主从同步，再通过读写分离可以分担数据库压力，提高性能

3. 实现方式

  • 读写分离分为以下两种∶

3.1 基于程序代码内部实现

在代码中根据 select、insert，进行路由分类，这类方法也是目前生产环境应用最广泛的。

优点是性能较好，因为在程序代码中实现，不需要增加额外的设备为硬件开支; 缺点是需要开发人员来实现，运维人员无从下手。

但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的 Java 应用，如果在程序代码中实现读写分离对代码改动就较大。

3.2 基于中间代理层实现

代理一般位于客户端和服务器之间， 代理服务器接到客户端请求后通过判断后转发到后端数据库， 有以下代表性程序：

（1） MySQL-Proxy ：MySQL-Proxy 为 MySOL开源项目， 通过其自带的 lua 脚本进行SOL 判断。

（2） Atlas ：是由奇虎360的Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy0.8.2版本的基础上，对其进行了优化， 增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以 及存储过程。

（3） Amoeba ：由陈思儒开发，作者曾就职于阿里巴巴。该程序由Jaya语言进行开发，阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。

由于使用 MySQL Proxy 需要写大量的 Lua 脚本，这些 Lua 并不是现成的，而是需要自己去写。这对于并不熟悉 MySQL Proxy 内置变量和 MySQL Protocol 的人来说是非常困难的。

Amoeba是一个非常容易使用、可移植性非常强的软件。因此它在生产环境中被广泛应用于数据库的代理层。

三、MySQL 主从复制架构搭建

1. 服务器配置

主机名

操作系统

IP地址

所需软件

Master

CentOS 7

192.168.10.14

mysql-5.7

Amoeba

CentOS 7

192.168.10.13

jdk1.6、Amoeba

Slave1

CentOS 7

192.168.10.16

mysql-5.7

Slave2

CentOS 7

192.168.10.19

mysql-5.7

client端

CentOS 7

192.168.10.26

mysql5.7

修改主机名

2. 环境准备

#关闭防火墙与安全增强系统

systemctl stop firewalld && systemctl disable firewalld

setenforce 0

setenforce: SELinux is disabled

3. mysql 主从服务器时间同步

主服务器：192.168.10.14

[root@localhost ~]#yum install -y ntp
[root@localhost ~]#vim /etc/ntp.conf 							#尾行加入下面内容

server 127.127.10.0											#设置本地是时钟源，注意修改自己的网段
fudge 127.127.10.0 stratum 8									#设置时间层级为 8（限制在 15 内）
[root@master ~]#systemctl start ntpd

slave1 ：192.168.10.16

[root@ky19 ~]#yum install -y ntp ntpdate
[root@ky19 ~]#systemctl start ntpd 
[root@ky19 ~]# /usr/sbin/ntpdate 192.168.10.14              #时间同步
27 Jun 20:45:40 ntpdate[20801]: the NTP socket is in use, exiting

[root@ky19 ~]#crontab -e									#设置计划任务

*/30 * * * * /usr/sbin/ntpdate 192.168.10.14

slave2 ：192.168.10.26

4. 主服务器的 mysql 配置

[root@localhost ~]#vim /etc/my.cnf

#添加下面配置

server-id = 1 #定义 server-id，每台主机不可相同

log-bin=master-bin #主服务器开启二进制日志

binlog_format = MIXED #本次使用 MIXED 模式

log-slave-updates=true #允许从服务器更新二进制日志

#注意：default-character-set=utf8 这个需要注释掉，不然会报错

systemctl restart mysqld.service #重启服务

#设置从服务器账号并授权

mysql -uroot -p123456

#给从服务器授权

mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.10.%' IDENTIFIED BY '123456';

mysql> flush privileges;

mysql> use mysql;

mysql> show master status;

#File 列显示日志名，Position 列显示偏移量

5. 从服务器的 mysql 配置

5.1 slave1 配置(192.168.10.16)

vim /etc/my.cnf

server-id = 2 # 注意 id 与其他主机都不能相同

relay-log=relay-log-bin #开启中继日志，从主服务器上同步日志文件记录到本地

relay-log-index=slave-relay-bin.index #定义中继日志文件的位置和名称

relay_log_recovery = 1 #选配项

#当 slave 从库宕机后，假如 relay-log 损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的 relay-log，

并且重新从 master 上获取日志，这样就保证了relay-log 的完整性。

默认情况下该功能是关闭的，将 relay_log_recovery 的值设置为 1 时， 可在 slave 从库上开启该功能，建议开启。

systemctl restart mysqld

mysql -uroot -p123456

#配置同步，注意 master_log_file 和 master_log_pos 的值要与 Master 查询的一致

mysql> CHANGE master to master_host='192.168.10.14',master_user='myslave',master_password='123456',master_log_file='master-bin.000002',master_log_pos=603;

mysql> start slave; #启动同步，如有报错执行 reset slave;

mysql> show slave status\G; #查看 Slave 状态，确保 IO 和 SQL 线程都是 Yes，代表同步正常

5.2 slave2 配置(192.168.10.26)

vim /etc/my.cnf

server-id = 3 #注意不能和 master 、slave1 相同

relay-log=relay-log-bin

relay-log-index=slave-relay-bin.index

relay_log_recovery = 1

systemctl restart mysqld

mysql -uroot -p123456

mysql> CHANGE master to master_host='192.168.10.14',master_user='myslave',master_password='123456',master_log_file='master-bin.000002',master_log_pos=603;

mysql> start slave;

mysql> show slave status\G;

5.3 一般 Slave_IO_Running: No 的可能性

  • 网络不通
  • my.cnf 配置有问题
  • 密码、file 文件名、pos 偏移量不对
  • 防火墙没有关闭

6. 验证主从复制

#主服务器

mysql> show databases;

mysql> create database test;

mysql> show databases;

#slave1(192.168.10.16)

mysql> show databases;

#slave2(192.168.10.26)

mysql> show databases;

四、MySQL 读写分离架构搭建

1. amoeba 服务器安装配置

安装Java 环境

因为 amoeba 是基于 jdk1.5 开发的，所以官方推荐使用 jdk1.5 或 1.6 版本，高版本不建议使用。

[root@localhost ~]#cd /opt/

[root@localhost /opt]#ls

amoeba-mysql-binary-2.2.0.tar.gz jdk-6u14-linux-x64.bin rh

cp jdk-6u14-linux-x64.bin /usr/local/

cd /usr/local/

chmod +x jdk-6u14-linux-x64.bin

./jdk-6u14-linux-x64.bin

#按住Enter，提示输入YES等待完成并回车

mv jdk1.6.0_14/ /usr/local/jdk1.6

#编辑全局配置文件，在最后一行添加以下配置

vim /etc/profile

export JAVA_HOME=/usr/local/jdk1.6

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

#输出定义Java的工作目录

#输出指定的java类型

#将java加入路径环境变量

#输出定义amoeba工作目录

#加入路径环境变量

[root@localhost /usr/local]#source /etc/profile #执行修改后的全局配置文件

[root@localhost /usr/local]#java -version #查看java版本信息以检查是否安装成功

java version "1.6.0_14"

Java(TM) SE Runtime Environment (build 1.6.0_14-b08)

Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)

安装 Amoeba

[root@localhost /usr/local]#mkdir /usr/local/amoeba

[root@localhost /usr/local]#tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/

[root@localhost /usr/local]#chmod -R 755 /usr/local/amoeba/ #为目录赋予权限

[root@localhost /usr/local]#
/usr/local/amoeba/bin/amoeba #开启amoeba

amoeba start|stop #提示stop说明安装成功

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

在master、slave1、slave2 的 mysql上开放权限给 amoeba 访问

mysql> grant all on *.* to test@'192.168.10.%' identified by '123456';
mysql> select user,host,authentication_string from mysql.user;

amoeba 服务器配置 amoeba 服务

#先备份
cd /usr/local/amoeba/conf/
cp amoeba.xml amoeba.xml.bak
cp dbServers.xml dbServers.xml.bak
vim /usr/local/amoeba/conf/amoeba.xml		#修改 amoeba 配置文件
#--30行--
amoeba
#--32行-- 
123456
#--115行--
master
#--117行-120行，取消注释并修改
master
slaves
......
-------------------------------------------------------------------------------------------
vim /usr/local/amoeba/conf/dbServers.xml	#修改数据库配置文件
#--23行--注释掉  作用：默认进入 test 库，以防 mysql 中没有 test 库时会报错

#--26--修改，使用之前创建的授权用户
test
#--29--去掉注释，密码为之前创建的授权用户密码
123456
#--45--修改，设置主服务器的名 master

#--48--修改，设置主服务器的地址
192.168.10.20
#--52--修改，设置从服务器的名 slave1

#--55--修改，设置从服务器1的地址
192.168.10.30
#--58--复制上面 6 行粘贴，设置从服务器 2 的名 slave2 和地址

192.168.10.40
#--65行--修改

#--71行--修改
slave1,slave2
......

/usr/local/amoeba/bin/amoeba start &		#启动 amoeba 软件，按 ctrl+c 返回
netstat -anpt | grep java					#查看 8066 端口是否开启，默认端口为 TCP 8066
tcp6       0      0 127.0.0.1:61721         :::*                    LISTEN      4346/java           
tcp6       0      0 :::8066                 :::*                    LISTEN      4346/java           
tcp6       0      0 192.168.8.17:58290      192.168.8.15:3306       ESTABLISHED 4346/java           
tcp6       0      0 192.168.8.17:58300      192.168.8.14:3306       ESTABLISHED 4346/java           
tcp6       0      0 192.168.8.17:56808      192.168.8.16:3306       ESTABLISHED 4346/java

# 先备份

cd /usr/local/amoeba/conf/

cp amoeba.xml amoeba.xml.bak

cp dbServers.xml dbServers.xml.bak

vim
/usr/local/amoeba/conf/amoeba.xml #修改 amoeba 配置文件

#--30行--

amoeba

#--32行--

123456

#--115行--

master

#--117行-120行，取消注释并修改

master

slaves

vim
/usr/local/amoeba/conf/dbServers.xml #修改数据库配置文件

#--23行--注释掉 作用：默认进入 test 库，以防 mysql 中没有 test 库时会报错

#--26--修改，使用之前创建的授权用户

test

#--29--去掉注释，密码为之前创建的授权用户密码

123456

#--45--修改，设置主服务器的名 master

#--48--修改，设置主服务器的地址

192.168.10.14

#--52--修改，设置从服务器的名 slave1

#--55--修改，设置从服务器1的地址

192.168.10.16

#--58--复制上面 6 行粘贴，设置从服务器 2 的名 slave2 和地址

192.168.10.26

#--65行--修改

#--71行--修改

slave1,slave2


/usr/local/amoeba/bin/amoeba start & #启动 amoeba 软件，按 ctrl+c 返回

netstat -anpt | grep java #查看 8066 端口是否开启，默认端口为 TCP 8066

3. 客户端测试读写分离

client：192.168.10.26 ，已装好 MySQL 服务

客户端（10.26）

[root@client ~]#mysql -uamoeba -p123456 -h 192.168.10.13 -P8066
mysql> show databases;

mysql> use test;
mysql> create table test(id int,name char(20));
mysql> show tables;

master 服务器

mysql> use test;

mysql> show tables;

slave 1 服务器

mysql> show databases;

mysql> use test;
mysql> show tables;

slave 2 服务器

mysql> show databases;

mysql> use test;
mysql> show tables;

可以看到客户端中做出的修改，在所有服务器上都能看到。

4. 关闭 slave 功能后在主从服务器插入数据

slave 1 服务器

mysql> stop slave;
mysql> insert into test values(1,'slave1');
mysql> select * from test;

slave 2 服务器

mysql> stop slave;
mysql> insert into test values(2,'slave2');
mysql> select * from test;

master 服务器

mysql> show tables;
mysql> insert into test values(3,'master');
mysql> select * from test;

client 服务器

mysql -uamoeba -p123456 -h 192.168.10.13 -P8066

通过amoeba服务器代理访问mysql，在客户端连接mysql后写入的数据只有主服务会记录，然后同步给从--从服务器

use test;
mysql> select * from test;
mysql> select * from test;
mysql> select * from test;
mysql> select * from test;

上面输出结果中：客户端在 slave1、slave2 中轮询查看数据。由于从服务器都关闭了 slave 功能，因此无法查看 master 服务器中的修改。

5. 关闭 slave 功能后，客户端服务器写入数据

客户端服务器

mysql> insert into test values(4,'client1');
mysql> select * from test;
mysql> select * from test;

master 服务器

mysql> select * from test;

slave 1 服务器

mysql> select * from test;

slave 2 服务器

mysql> select * from test;

结果：

客户端服务器的修改操作是对于 master 服务器进行的，因此 master 服务器上可以看到客户端服务器所做的 insert 操作。由于客户端服务器的 select 操作是对从服务器进行的，而从服务器关闭了 slave 功能，无法获取更新，因此客户端服务器本身以及从服务器都无法查看更新操作。

6. 从服务器开启 slave 服务

slave 1 服务器

mysql> start slave;
mysql> select * from test;

slave 2 服务器

mysql> start slave;
mysql> select * from test;

master 服务器

mysql> select * from test;

客户端服务器

mysql> select * from test;
mysql> select * from test;

结果：

从服务器开启 slave 服务后可以从 master 服务器上获取更新，但是从服务器上的更新不会被 master 服务器看到。

7. 客户端服务器再次插入数据

客户端服务器

mysql> insert into test values(5,'client2');
mysql> select * from test;

master 服务器

mysql> select * from test;

slave1 服务器

mysql> select * from test;

slave2 服务器

mysql> select * from test;

结果：

客户端服务器上的数据修改，会同步到所有服务器中。

总结

1、MySQL 主从复制原理：

（1）MySQL主服务器在用户增删改查操作，提交事务的以后，会按顺序写入到binlog日志中，从服务器节点会在一定时间间隔内对主服务器的二进制日志进行探测，观察是否发生数据改变，若发生改变则开启一个I/O线程请求主服务器的二进制事件。

（2）同时主服务器会为每个I/O线程开启dump线程，用于向其发送二进制事件，并保存到从服务器的中继日志中，从服务器会开启SQL线程从中继日志中读取二进制日志，在本地重放，也就是说把二进制日志解析成SQL语句逐一执行，使其数据与主服务器保持一致，最后I/O线程和SQL线程会进入休眠状态，等待下一次被唤醒。

2、当 MySQL 主从复制在 show slave status\G 时出现 Slave_IO_Running 或 Slave_SQL_Running 的值不为YES时，需要首先通过 stop slave 来停止从服务器，然后再执行一次上一步操作即可恢复。

3、但如果想尽可能的同步更多的数据，可以在 Slave 上将 master_log_pos 节点的值在之前同步失效的值的基础上增大一些，然后反复测试，直到同步OK。

4、因为MySQL主从复制的原理其实就是从服务器读取主服务器的 binlog，然后根据 binlog 的记录来更新数据库。

相关推荐

GANs为何引爆机器学习?这篇基于TensorFlow的实例教程为你解惑!

「机器人圈导览」：生成对抗网络无疑是机器学习领域近三年来最火爆的研究领域，相关论文层出不求，各种领域的应用层出不穷。那么，GAN到底如何实践？本文编译自Medium，该文作者以一朵玫瑰花为例，详细阐...

高丽大学等机构联合发布StarGAN:可自定义表情和面部特征

原文来源：arXiv、GitHub作者：YunjeyChoi、MinjeChoi、MunyoungKim、Jung-WooHa、SungKim、JaegulChoo「雷克世界」编译：嗯~...

TensorFlow和PyTorch相继发布最新版，有何变化

原文来源：GitHub「机器人圈」编译：嗯~阿童木呀、多啦A亮Tensorflow主要特征和改进在Tensorflow库中添加封装评估量。所添加的评估量列表如下：1.深度神经网络分类器（DNNCl...

「2022 年」崔庆才 Python3 爬虫教程 - 深度学习识别滑动验证码缺口

上一节我们使用OpenCV识别了图形验证码躯壳欧。这时候就有朋友可能会说了，现在深度学习不是对图像识别很准吗？那深度学习可以用在识别滑动验证码缺口位置吗？当然也是可以的，本节我们就来了解下使用深度...

20K star!搞定 LLM 微调的开源利器

LLM（大语言模型）微调一直都是老大难问题，不仅因为微调需要大量的计算资源，而且微调的方法也很多，要去尝试每种方法的效果，需要安装大量的第三方库和依赖，甚至要接入一些框架，可能在还没开始微调就已经因为...

大模型DeepSeek本地部署后如何进行自定义调整?

1.理解模型架构a)查看深度求索官方文档或提供的源代码文件，了解模型的结构、输入输出格式以及支持的功能。模型是否为预训练权重？如果是，可以在预训练的基础上进行微调（Fine-tuning）。是否需要...

因配置不当，约5000个AI模型与数据集在公网暴露

除了可访问机器学习模型外，暴露的数据还可能包括训练数据集、超参数，甚至是用于构建模型的原始数据。前情回顾·人工智能安全动态向ChatGPT植入恶意“长期记忆”，持续窃取用户输入数据多模态大语言模型的致...

基于pytorch的深度学习人员重识别

基于pytorch的深度学习人员重识别Torchreid是一个库。基于pytorch的深度学习人员重识别。特点：支持多GPU训练支持图像的人员重识别与视频的人员重识别端到端的训练与评估简单的re...

DeepSeek本地部署:轻松训练你的AI模型

引言：为什么选择本地部署？在AI技术飞速发展的今天，越来越多的企业和个人希望将AI技术应用于实际场景中。然而，对于一些对数据隐私和计算资源有特殊需求的用户来说，云端部署可能并不是最佳选择。此时，本地部...

谷歌今天又开源了，这次是Sketch-RNN

前不久，谷歌公布了一项最新技术，可以教机器画画。今天，谷歌开源了代码。在我们研究其代码之前，首先先按要求设置Magenta环境。（https://github.com/tensorflow/magen...

Tensorflow 使用预训练模型训练的完整流程

前面已经介绍了深度学习框架Tensorflow的图像的标注和训练数据的准备工作，本文介绍一下使用预训练模型完成训练并导出训练的模型。1.选择预训练模型1.1下载预训练模型首先需要在Tensorf...

30天大模型调优学习计划（30分钟训练大模型）

30天大模型调优学习计划，结合Unsloth和Lora进行大模型微调，掌握大模型基础知识和调优方法，熟练应用。第1周：基础入门目标：了解大模型基础并熟悉Unsloth等工具的基本使用。Day1：大模...

python爬取喜马拉雅音频，json参数解析

一.抓包分析json，获取加密方式1.抓包获取音频界面f12打开抓包工具，播放一个（非vip）视频，点击“媒体”单击打开可以复制URL，发现就是我们要的音频。复制“CKwRIJEEXn-cABa0Tg...

五、JSONPath使用(Python)（json数据python）

1.安装方法pipinstalljsonpath2.jsonpath与Xpath下面表格是jsonpath语法与Xpath的完整概述和比较。Xpathjsonpath概述/$根节点.@当前节点...

Python网络爬虫的时候json=就是让你少写个json.dumps()

大家好，我是皮皮。一、前言前几天在Python白银交流群【空翼】问了一个Python网络爬虫的问题，提问截图如下：登录请求地址是这个：二、实现过程这里【甯同学】给了一个提示，如下所示：估计很多小伙伴和...

一周热门
最近发表
标签列表