【MySQL数据库】主从复制与读写分离

已于 2023-06-26 18:20:54 修改
阅读量642 收藏 1
点赞数
分类专栏： mysql数据库 文章标签： 数据库 mysql 服务器
于 2023-06-24 18:42:07 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wang_dian1/article/details/131363985
版权

目录

一、读写分离

1.1概述

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

1.2为什么要读写分离呢？

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

1.3什么时候要读写分离？

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

1.4主从复制与读写分离

  • 在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。

1.5mtsql支持的复制类型

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

1.6主从复制工作流程

  • （1）Master节点将数据的改变记录成二进制日志（bin log），当Master上的数据发生改变时，则将其改变写入二进制日志中。
  • （2）Slave节点会在一定时间间隔内对Master的二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O线程请求 Master的二进制事件。
  • （3）同时Master节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至Slave节点本地的中继日志（Relay log）中，Slave节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，即解析成 sql 语句逐一执行，使得其数据和 Master节点的保持一致，最后I/O线程和SQL线程将进入睡眠状态，等待下一次被唤醒。

在这里插入图片描述

1.7主从复制原理

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

二、主从复制实战

Master 服务器：192.168.243.107		mysql5.7
Slave1 服务器：192.168.243.108		mysql5.7

1、配置时间同步

#主服务器配置
yum install ntp -y
vim /etc/ntp.conf
#第九行添加
restrict default nomodify #限制只有主服务器能修改时间
22-25注释，26行添加
server ntp.aliyun.com #添加阿里云的时间地址，也可以添加其它的或者本机的
 systemctl restart ntpd


#从服务器配置
yum install ntp ntpdate -y
ntpdate 192.168.243.107 #同步主服务器的时间，需要等待会
crontab -e #添加计划性任务，每三十分钟执行一次
*/30 * * * * /usr/sbin/ntpdate 192.168.243.107

mysql配置主服务器配置

#主服务器配置
vim /etc/my.cnf
server-id=11
log-bin=mysql-bin						#添加，主服务器开启二进制日志
binlog_format=mixed

#选配项	
expire_logs_days=7						#设置二进制日志文件过期时间，默认值为0，表示logs不过期
max_binlog_size=500M					#设置二进制日志限制大小，如果超出给定值，日志就会发生滚动，默认值是1GB
skip_slave_start=1						#阻止从库崩溃后自动启动复制，崩溃后再自动复制可能会导致数据不一致的
systemctl restart mysqld
mysql -u root -pabc123
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.80.%' IDENTIFIED BY '123456';			#给从服务器授权
FLUSH PRIVILEGES;
show master status;
#File 列显示日志名，Position 列显示偏移量

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

nysql从服务器配置

vim /etc/my.cnf
server-id = 22								#修改，注意id与Master的不同，两个Slave的id也要不同
relay-log=relay-log-bin						#开启中继日志，从主服务器上同步日志文件记录到本地
relay-log-index=relay-log-bin.index			#定义中继日志文件的位置和名称，一般和relay-log在同一目录

#选配项
innodb_buffer_pool_size=2048M		#用于缓存数据和索引的内存大小，让更多数据读写内存中完成，减少磁盘操作，可设置为服务器总可用内存的 70-80%
sync_binlog=0						#MySQL不做任何强制性的磁盘刷新指令，而是依赖操作系统来刷新数据到磁盘
innodb_flush_log_at_trx_commit=2	#每次事务log buffer会写入log file，但一秒一次刷新到磁盘
log-slave-updates=0					#slave 从 master 复制的数据会写入二进制日志文件里，从库做为其他从库的主库时设置为 1
relay_log_recovery=1				#当 slave 从库宕机后，假如 relay-log 损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的 relay-log， 并且重新从 master 上获取日志，这样就保证了 relay-log 的完整性。默认情况下该功能是关闭的，将 relay_log_recovery 的值设置为 1 时， 可在 slave 从库上开启该功能，建议开启。


systemctl restart mysqld

mysql -u root -pabc123
CHANGE master to master_host='192.168.80.10',master_user='myslave',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=339;					#配置同步，注意 master_log_file 和 master_log_pos 的值要与Master查询的一致

start slave;						#启动同步，如有报错执行 reset slave;
show slave status\G					#查看 Slave 状态

在这里插入图片描述
在这里插入图片描述

三、Mysql主从复制的几个同步模式

3.1模式简介

异步复制（Asynchronous replication）

  • MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。

全同步复制（Fully synchronous replication）

  • 指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

半同步复制（Semisynchronous replication）

  • 介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

3.2半同步配置

主数据库配置

vim /etc/my.cnf				#在 [mysqld] 区域添加下面内容
......
plugin-load=rpl_semi_sync_master=semisync_master.so			#加载mysql半同步复制的插件
rpl_semi_sync_master_enabled=ON								#或者设置为"1"，即开启半同步复制功能
rpl-semi-sync-master-timeout=1000							#超时时间为1000ms，即1s

systemctl restart mysqld

从数据库配置

vim /etc/my.cnf	
......
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=ON

systemctl restart mysqld

查看半同步是否在运行

#主数据库执行
show status like 'Rpl_semi_sync_master_status';
show variables like 'rpl_semi_sync_master_timeout';

#从数据库执行（此时可能还是OFF状态，需要在下一步重启IO线程后，从库半同步状态才会为ON）
show status like 'Rpl_semi_sync_slave_status';

#重启从数据库上的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

#在主库查询半同步状态
show status like '%Rpl_semi%';

参数说明：
Rpl_semi_sync_master_clients #半同步复制客户端的个数
Rpl_semi_sync_master_net_avg_wait_time #平均等待时间（默认毫秒）
Rpl_semi_sync_master_net_wait_time #总共等待时间
Rpl_semi_sync_master_net_waits #等待次数
Rpl_semi_sync_master_no_times #关闭半同步复制的次数
Rpl_semi_sync_master_no_tx #表示没有成功接收slave提交的次数
Rpl_semi_sync_master_status #表示当前是异步模式还是半同步模式，on为半同步
Rpl_semi_sync_master_timefunc_failures #调用时间函数失败的次数
Rpl_semi_sync_master_tx_avg_wait_time #事物的平均传输时间
Rpl_semi_sync_master_tx_wait_time #事物的总共传输时间
Rpl_semi_sync_master_tx_waits #事物等待次数
Rpl_semi_sync_master_wait_pos_backtraverse #可以理解为"后来的先到了，而先来的还没有到的次数"
Rpl_semi_sync_master_wait_sessions #当前有多少个session因为slave的回复而造成等待
Rpl_semi_sync_master_yes_tx #成功接受到slave事物回复的次数

  • 当半同步复制发生超时（由rpl_semi_sync_master_timeout参数控制，默认为10000ms，即10s），会暂时关闭半同步复制，转而使用异步复制，也就是会自动降为异步工作。
    当 master dump 线程发送完一个事务的所有事件之后，如果在 rpl_semi_sync_master_timeout 内，收到了从库的响应， 则主从又重新恢复为半同步复制。

四、读写分离

4.1常见的读写分离方法

基于程序代码内部实现

  • 在代码中更具solect、insert进行路由分类，这类方法最为广泛
  • 优点事性能好，不需要增加额外的设备为硬件开支

基于中间代理层实现

  • 代理一般位于客户端客户端与服务器之间，代理服务器接到客户端请求后通过判断转发到后端数据库中
  • MySOL-Proxy：MySL-Proxy 为 MySOL 开源项目，通过其自带的 lua 脚本进行SOL 判断。
  • Atlas:是由奇虎360的web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxi0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的ysl业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。
  • Amoeba:由陈思儒开发，作者曾就职于阿里巴巴。该程序由Java语言进行开发，阿里巴巴将其用千生产环境。但是它不支持事各和存储过积
  • Mycat:是一款流行的基于Java语言编写的数据库中间件，是一个实现了MSqL协议的服务器，其核心功能是分库分表。配合数据库的主从模式还可以实现读写分离

4.1使用Amoeba搭建读写分离

Amoeba 服务器： 192.168.243.102
主服务器：192.168.243.107
从服务器：192.168.243.108
客户端：192.168.243.105

Amoeba 服务器

##安装 Java 环境##
因为 Amoeba 基于是 jdk1.5 开发的，所以官方推荐使用 jdk1.5 或 1.6 版本，高版本不建议使用。
cd /opt/
cp jdk-6u14-linux-x64.bin /usr/local/
cd /usr/local/
chmod +x jdk-6u14-linux-x64
./jdk-6u14-linux-x64.bin
//按yes，按enter
mv jdk1.6.0_14/ /usr/local/jdk1.6

vim /etc/profile
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=.:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin

source /etc/profile
java -version

##安装 Amoeba软件##
mkdir /usr/local/amoeba
tar zxvf 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说明安装成功

##配置 Amoeba读写分离，两个 Slave 读负载均衡##
#先在Master、Slave1、Slave2 的mysql上开放权限给 Amoeba 访问
grant all on *.* to test@'192.168.80.%' identified by '123.com';

#再回到amoeba服务器配置amoeba服务：
cd /usr/local/amoeba/conf/

cp amoeba.xml amoeba.xml.bak
vim amoeba.xml									#修改amoeba配置文件
--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行--注释掉  作用：默认进入test库 以防mysql中没有test库时，会报错
<!-- <property name="schema">test</property> -->
--26--修改
<property name="user">test</property>
--28-30--去掉注释
<property name="password">123.com</property>
--45--修改，设置主服务器的名Master
<dbServer name="master"  parent="abstractServer">
--48--修改，设置主服务器的地址
<property name="ipAddress">192.168.80.10</property>
--52--修改，设置从服务器的名slave1
<dbServer name="slave1"  parent="abstractServer">
--55--修改，设置从服务器1的地址
<property name="ipAddress">192.168.80.11</property>
--58--复制上面6行粘贴，设置从服务器2的名slave2和地址
<dbServer name="slave2"  parent="abstractServer">
<property name="ipAddress">192.168.80.12</property>
--65行--修改
<dbServer name="slaves" virtual="true">
--71行--修改
<property name="poolNames">slave1,slave2</property>


/usr/local/amoeba/bin/amoeba start&					#启动Amoeba软件，按ctrl+c 返回
netstat -anpt | grep java							#查看8066端口是否开启，默认端口为TCP 8066

测试读写分离

yum install -y mariadb-server mariadb #如果有数据库就不用下载mariadb
systemctl start mariadb.service

在客户端服务器上测试：
mysql -u amoeba -p123456 -h 192.168.80.20 -P8066		
//通过amoeba服务器代理访问mysql ，在通过客户端连接mysql后写入的数据只有主服务会记录，然后同步给从--从服务器

在主服务器上：
use db_test;
create table test (id int(10),name varchar(10),address varchar(20));

在两台从服务器上：
stop slave;											#关闭同步
use db_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','wangwu','this_is_master');

//在客户端服务器上：
use db_test;
select * from test;		//客户端会分别向slave1和slave2读取数据，显示的只有在两个从服务器上添加的数据，没有在主服务器上添加的数据

insert into test values('4','qianqi','this_is_client');		//只有主服务器上有此数据

//在两个从服务器上执行 start slave; 即可实现同步在主服务器上添加的数据
start slave;
DDD嘀嘀嘀
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL - 读写分离、一主一从、双主双从
weixin_51351637的博客
06-02 3430
读写分离,简单地说是把对数据库的读和写操作分开,以对应不同的数据库服务器。主数据库提供写操作，从数据库提供读操作，这样能有效地减轻单台数据库的压力。MyCat实现读写分离的话，主要通过两个组件来实现：读写分离它的前提是基于主从复制主从复制的原理是基于MySQL的二进制日志Binlog来实现的如果是读的操作，我们就直接连接从节点，如果是写的操作，直接连主节点应用程序不用直接连接MySQL，否则会很繁琐，因为会有多个服务器数据库。
数据库主从复制，读写分离(1)
h2728677716的博客
08-05 305
mysql主从复制与读写分离 在实际生产环境中，如果对myql数据库的读和写都在一台数据库上，无论是在安全性、高可用性还是高并发性等各个方面都是不能满足实际需求的。 主从复制：一台mysql主服务器带两台mysql从服务器做了数据复制，前端应用在进行写操作时，对主服务器进行操作，在进行服务器读操作时，对两台从服务器进行操作，这样就减轻了对主服务器的压力。 搭建mysql主从复制 准备三台安装mys...
数据库读写分离，主从同步实现方法
热门推荐
starlh35的博客
12-06 8万+
通过实际的例子编码实现数据库读写分离，实现数据库主从同步
数据库集群、主从备份、读写分离
qq_38893580的博客
12-04 929
什么是主从复制、读写分离、为什么要使用 一、什么是主从复制、读写分离、为什么要使用 主从复制：是一种数据备份的方案。 简单来说，是使用两个或两个以上相同的数据库，将一个数据库当做主数据库，而另一个数据库当做从数据库。在主数据库中进行相应操作时，从数据库记录下所有主数据库的操作，使其二者一模一样。 读写分离：是一种让数据库更稳定的的使用数据库的方法。 是在有从数据库的情况下使用，当主数据库进行对数据...
数据库之MySQL主从复制与读写分离
thengsd的博客
02-17 290
目录一、MySQL 主从复制原理1、MySQL 支持的复制类型2、MySQL主从复制的工作过程二、MySQL读写分离1、读写分离的概念2、读写分离存在意义3、什么时候要读写分离4、MySQL 读写分离原理5、常见的 MySQL 读写分离1）基于程序代码内部实现2）基于中间代理层实现三、MySQL主从复制和读写分离实验部署1、搭建 MySQL 主从复制1）Mysql主从服务器时间同步2）主服务器的mysql配置3）从服务器的mysql配置4）验证主从复制效果2、搭建 MySQL 读写分离1）Amoeba服务器
MySQL数据库主从复制与读写分离
Z__Cheng的博客
06-08 384
MySQL数据库主从复制与读写分离
MySQL主从复制与读写分离原理及用法详解
09-08
MySQL主从复制与读写分离是数据库高可用性和负载均衡的重要技术。主从复制允许数据从一个服务器（主服务器）自动同步到其他服务器（从服务器），而读写分离则是将读操作分散到从服务器，以减轻主服务器的压力，提高...
MySQL主从复制与读写分离
07-30
MySQL主从复制与读写分离是数据库架构中的重要策略，旨在提高系统的可用性、扩展性和数据安全性。在大型系统中，随着数据量的增长和访问压力的增加，单一数据库服务器往往无法满足性能需求。主从复制和读写分离是...
MySQL主从复制与读写分离.docx
04-13
MySQL 主从复制与读写分离 ...MySQL 主从复制与读写分离是提高 MySQL 数据库性能和可用性的重要手段。通过配置主从复制和读写分离，可以提高系统的性能和可用性，避免单点故障，并且可以轻松地扩展系统的容量。
MySQL5.6 Replication主从复制（读写分离) 配置完整版
01-19
MySQL5.6主从复制(读写分离)教程 1、MySQL5.6开始主从复制有两种方式： 基于日志（binlog）； 基于GTID（全局事务标示符）。 需要注意的是：GTID方式不支持临时表！所以如果你的业务系统要用到临时表的话就不要考虑...
详解MySQL的主从复制、读写分离、备份恢复
09-09
MySQL的主从复制、读写分离以及备份恢复是数据库管理中的关键概念，它们对于数据库的高可用性、数据安全性以及性能优化至关重要。下面将逐一详细解释这些知识点。 **一、MySQL主从复制** 1. **主从复制的目的**：...
Apollo使用（3）：分布式docker部署
游王子的博客
07-24 586
Apollo 1.7.0版本开始会默认上传Docker镜像到，可以按照如下步骤获取。
图数据库查询与操作指南-以Nebula图数据库为例
DZSpace，专注于计算机科学与技术领域的技术博主，致力于分享实用知识与技巧，帮助读者快速掌握技术要点，共同探索计算机世界的无限可能。欢迎访问DZSpace的博客，一起学习进步！
07-26 288
图数据库查询与操作指南-以Nebula图数据库为例
ODBC连接达梦数据库
qq_55187735的博客
07-24 257
用户执行，因为环境变量配置在。
SQL基础入门：解锁数据库管理的钥匙
最新发布
WayneYalejk的博客
07-26 354
在数字化时代，数据已成为企业最宝贵的资产之一。为了高效地存储、查询和管理这些数据，SQL（Structured Query Language，结构化查询语言）应运而生。SQL不仅是数据库管理员的必备技能，也是数据分析师、开发人员等多个领域从业者的重要工具。本文将带您踏入SQL的世界，从基础概念到实际应用，一步步解锁数据库管理的钥匙。强调SQL在数据处理和分析中的重要性，鼓励读者通过实践不断深化对SQL的理解和应用。
sqlalchemy使用json_unquote函数的mysql like查询
ithongchou的博客
07-23 1306
为您自己的数据库连接信息，并根据实际表结构修改模型类。函数查询MySQL JSON字段可以通过使用。下面是一个示例，假设有一个名为。请确保替换示例代码中的数据库连接字符串(在SQLAlchemy中使用。的表，其中包含一个名为。
CTF-NSSCTF题单[GKCTF2020]
2302_78903258的博客
07-24 1218
在低于7.2.29的PHP版本7.2.x，低于7.3.16的7.3.x和低于7.4.4的7.4.x中，将get_headers（）与用户提供的URL一起使用时，如果URL包含零（\ 0）字符，则URL将被静默地截断。访问/eval的路由，会设置一个delay，和你传入的get参数的delay进行比较，取较大的那个，然后setTimeout是延时函数，delay秒后就会执行第一个参数，即next()，否则就会send出timeout。是假的，但是有线索，提示说真正的flag的在。
通过Docker安装KingbaseES V8并激活开发License
weimeilayer的博客
07-24 172
首先，我们可以去KingbaseES的官网（https://www.kingbase.com.cn/xzzx/index.htm ，在软件版本中，注意下载的镜像为x86版本）下载docker镜像，下载完成后导入即可。简单介绍一下在Docker中安装人大金仓（KingbaseES V9）的过程，以及如何更换默认的License为官方提供的365天有效期的开发License。下面的安装过程都是在WSL2中完成的。我们可以通过下面的命令从宿主来直接查看KingbaseES的版本。启动容器，可以直接下面的脚本。
mysql表设计
wwwwwww31311的博客
07-23 169
status 状态 java代码中用一个枚举类，比如我的请假表还处于草稿状态，可以是新建态，提交后处于待审批状态，审批完成则是成功态，审批拒绝就是拒绝态。start_time 开始时间 dateTime类型。end_time 起始时间 dateTime类型。employee_id 外键，和员工信息表想管理。type 请假类型，对应一个枚举类，病假，事假。create_time 请假记录的创建时间。如果让你设计一个请假表，如何设计？approve_id 外键。
MySQL主从复制与读写分离实战配置
MySQL主从复制与读写分离是一种常见的数据库集群架构，用于提高数据库性能和可用性。在本文中，作者分享了在Linux环境下实现这一功能的具体步骤。首先，环境准备阶段非常重要，包括设置MySQL主服务器（IP: 192.168....
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值