MYSQL主从复制与读写分离

已于 2022-11-06 22:44:25 修改
阅读量337 收藏 3
点赞数 1
文章标签： mysql 数据库 java
于 2022-11-06 22:44:08 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_64413763/article/details/127614239
版权

一、MYSQL主从复制

1、MySQL 支持的复制类型

  • 基于语句的复制（STATEMENT）：在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制，效率比较高。但在高并发高负载的情况下可能会出现复制误差。
  • 基于行的复制（ROW）：基于行的记录复制，把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
  • 混合类型的复制（MIXED）：默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。在高并发高负载时无法精确复制时会采用基于行的记录复制。
     

2、MySQL主从复制的工作过程

6、主从复制的工作过程

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

注:
中继日志通常会位于os 缓存中，所以中继日志的开销很小。
复制过程有一个很重要的限制，即复制在―Slave F.是串行化的，也就是说Master 上的并行更新操作不能在 slave 上:并行操作。

主从复制中分为「主服务器（master）「和」从服务器（slave）」，「主服务器负责写，而从服务器负责读」，Mysql的主从复制的过程是一个「异步的过程」。

mysql主服务器数据发生更新，会以二进制日志写入binary log里，从服务器会使用I/O线程向主库请求二进制事件，主服务器开启dump线程进行二进制日志发送到从库，然后写入到relay log中继日志中，然后从服务会开启SQL线程自动从中继日志中读取二进制事件，解析成SQL语句在本地重放，使得从库数据和主库数据保持一致。

二、MySQL读写分离

1、什么是读写分离?

读写分离，基本的原理是让主数据库处理事务性增、改、删操作(INSBRI、UPOATE、DBL.TE)，而从数据库处理sLECr查询操作。数据南复制被用米把事务性操作导致的变更同步到集群中的从数据库。

2、为什么要读写分离呢?

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

读写分离的作用：

可以分担数据库压力，提高性能，避免数据库的写入影响查询的效率。

3、什么时候要读写分离?

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

4、主从复制与读写分离

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

在这里插入图片描述

  • 数据库每天都要承担的网站的数据更新，所以大量的读写任务都在一个机器上压力非常大。使用读写分离可以有效的缓解数据库压力。

5、MySQL 读写分离原理

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

6、读写分离方案

  • 基于程序代码内部实现
    在代码中根据select. insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。优点是性能较好，因为在程序代码中实现，不需要增加额外的设备作为硬件开支:缺点是需要开发人员来实现，运维人员无从下手。
  • 基于中间代理层实现
    代理一般位于客户端和服务器之间，代理服务器接到客户端的请求后通过判断后转发到后端数据库，有两个代表性程序。
    (1) MySQL-Proxy。MySQL- -Proxy为MySQL开源项目，通过其自带的lua脚本进行SQL判断,虽然是MySQL官方产品，但是MySQL官方并不建议将MySQL- -Proxy 用到生产环境。
    (2) Amoeba (变形虫)。由陈思儒开发，其曾就职于阿里巴巴。该程序用Java语言进行开发,阿里巴巴将其用于生产环境。它不支持事务和存储过程。
  • 经过上述简单的比较,通过程序代码实现MySQL读写分离自然是一个不错的选择, 但是并不是所有的应用都适合在程序代码中实现读写分离，例如一些大型复杂的Java应用，如果在程序代码中实现读写分离对代码改动就较大。所以,像这种大型复杂的应用一般会考虑使用代理层来实现。

MySQL主从复制和读写分离实验示例

在这里插入图片描述

1、实验思路

1、客户端访问代理服务器
2、代理服务器写入到主服务器
3、主服务器将增删改写入自己二进制日志
4、从服务器将主服务器的二进制日志同步至自己中继日志
5、从服务器重放中继日志到数据库中
6、客户端读，则代理服务器直接访问从服务器
7、降低负载，起到负载均衡作用

2、环境配置

安装mysql
jdk-6u14-linux-x64.bin、
amoeba-mysql-binary-2.2.0.tar.gz

主机操作系统IP地址所需工具/软件/安装包
AmoebaCentOS7192.168.10.16jdk-6u14-linux-x64.bin、amoeba-mysql-binary-2.2.0.tar.gz
MasterCentOS7192.168.10.13ntp 、 mysql-boost-5.7.20.tar.gz
Slave1CentOS7192.168.10.14ntp 、ntpdate 、 mysql-boost-5.7.20.tar.gz
Slave2CentOS7192.168.10.15ntp 、ntpdate 、 mysql-boost-5.7.20.tar.gz
客户端CentOS7192.168.10.17

三、搭建 MySQL 主从复制

1、Mysql主从服务器时间同步

主服务器设置（192.168.10.13）
yum -y install ntp

vim /etc/ntp.conf

25行左右添加
server 127.127.10.0							#设置本地是时钟源，注意修改网段
fudge 127.127.10.0 stratum 8					#设置时间层级为8（限制在15内）

service ntpd start


从服务器设置（192.168.10.14）、（192.168.10.15）

yum -y install ntp ntpdate

service ntpd start
/usr/sbin/ntpdate 192.168.10.13			#进行时间同步，指向Master服务器IP

crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.10.13

2、主服务器的mysql配置

vim /etc/my.cnf
server-id = 1
log-bin=master-bin							#添加，主服务器开启二进制日志
log-slave-updates=true						#添加，允许从服务器更新二进制日志
binlog_format = MIXED                       #设置二进制日志格式
expire_logs_days = 10                       #二进制日志保存时间
max_binlog_size = 500M                      #一个二进制日志大小

systemctl restart mysqld

mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.10.%' IDENTIFIED BY 'root';			#给从服务器授权
FLUSH PRIVILEGES;

show master status;

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

3、从服务器的mysql配置

vim /etc/my.cnf
server-id = 2						#修改，注意id与Master的不同，两个Slave的id也要不同
relay-log=relay-log-bin						#添加，开启中继日志，从主服务器上同步日志文件记录到本地
relay-log-index=slave-relay-bin.index		#添加，定义中继日志文件的位置和名称
relay_log_recovery = 1                      #优化设置
systemctl restart mysqld



两台从服务器配置相同，唯一不同的是id

 

mysql -u root -p
change master to master_host='192.168.10.13' , master_user='myslave',master_password='root',master_log_file='master-bin.000001',master_log_pos=603;
				#配置同步，注意 master_log_file 和 master_log_pos 的值要与Master查询的一致，这里的是例子，每个人的都不一样

start slave;						#启动同步，如有报错执行 reset slave;
show slave status\G					#查看 Slave 状态
//确保 IO 和 SQL 线程都是 Yes，代表同步正常。
Slave_IO_Running: Yes				#负责与主机的io通信
Slave_SQL_Running: Yes				#负责自己的slave mysql进程

4、验证主从复制效果

主服务器上进入执行 create database dc_test1;

 

四、搭建 MySQL 读写分离

1、Amoeba服务器配置

Amoeba服务器：192.168.10.16
将jdk-6u14-linux-x64.bin 和 amoeba-mysql-binary-2.2.0.tar.gz.0 上传到/opt目录下。
cd /opt/
cp jdk-6u14-linux-x64.bin /usr/local/

cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin
./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=$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
java -version

2、安装 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/
source /etc/profile
/usr/local/amoeba/bin/amoeba
//如显示amoeba start|stop 说明安装成功

 

3、在主从服务器的mysql上授权

先在Master、Slave1、Slave2 的mysql上开放权限给 Amoeba 访问

grant all on *.* to test@'192.168.10.%' identified by 'root';

一台主两台从都需要开放权限给 Amoeba 访问这里就不一一设置了

4、配置 Amoeba读写分离，两个 Slave 读负载均衡

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">root</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库时，会报错
<!-- mysql schema
<property name="schema">test</property>
-->
#---------26修改-----------------------------------------
<!-- mysql user -->
<property name="user">test</property>
#---------28-30去掉注释----------------------------------
<property name="password">35123512</property>
#---------45修改，设置主服务器的名Master------------------
<dbServer name="master"  parent="abstractServer">
#---------48修改，设置主服务器的地址----------------------
<property name="ipAddress">192.168.10.13</property>
#---------52修改，设置从服务器的名slave1-----------------
<dbServer name="slave1"  parent="abstractServer">
#---------55修改，设置从服务器1的地址---------------------
<property name="ipAddress">192.168.10.14</property>
#---------58复制上面6行粘贴，设置从服务器2的名slave2和地址---
<dbServer name="slave2"  parent="abstractServer">
<property name="ipAddress">192.168.10.15</property>
#---------修改后的65或66修改-------------------------------------
<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

5、测试读写分离

客户端：192.168.172.50
使用yum快速安装MySQL虚拟客户端

yum install -y mysql mysql-server
mysql -u amoeba -proot -h 192.168.10.16 -P8066

总结

mysql主从复制原理
slave节点与主节点进行连接，建立主从关系，并把从哪开始同步，及哪个日志文件都一并发送到master
master将修改的数据保存到binlog中
master开启binlog dump线程，将binlog日志推送到连接的slave中
slave接受到推送的binlog，slave开启IO线程将数据写到中继日志(relay log)中
slave同时还会开启一个SQL线程，对比中继日志中新增的内容，并且解析SQL，回放数据到从数据库中
 

面试题：

  • 如何查看主从同步状态是否成功

show slave status

show master status

  • 如果I/O和sql不是yes呢，你是如何排查的

先看last errno有几个错误 有什么错误等等都有信息显示

网络不通

my.cnf配置有问题

密码、file文件名、pos偏移量不对

防火墙没有关闭
 

  • show slave status 能看到哪些信息（比较重要的）

io状态 两个线程的yes

Last_Errno,Last_Error**

slave的SQL线程读取日志参数的的错误数量和错误消息。错误数量为0并且消息为空字符串表示没有错误；

如果Last_Error值不是空值，它也会在从属服务器的错误日志中作为消息显示。
 

  • 主从复制慢（延迟）有哪些可能

从库硬件比主库差，导致复制延迟

主从复制单线程，如果主库写并发太大，来不及传送到从库，就会导致延迟。更高版本的mysql可以支持多线程复制

慢SQL语句过多

网络延迟

master负载：主库读写压力大，导致复制延迟，架构的前端要加buffer及缓存层

slave负载：一般的做法是，使用多台slave来分摊读请求，再从这些slave中取一台专用的服务器，只作为备份用，不进行其他任何操作.
 

  • mysql主从同步读写分离的三个账号
    主从同步（数据库互相授权的账号）
    amoeba（客户端访问amoeba）
    amoeba（amoeba访问数据库集群）

stars293
关注
  • 1
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Mysql主从复制与读写分离
weixin_48861962的博客
11-01 792
一、MySQL主从复制与读写分离 1.1 主从分离和读写分离的相关概述 1）主从复制与读写分离、 在实质际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实院需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。 2）为什么要读写分离 因为数据库的"写"（
MySQL主从复制与读写分离
07-30
MySQL主从复制与读写分离是数据库架构中的重要策略，旨在提高系统的可用性、扩展性和数据安全性。在大型系统中，随着数据量的增长和访问压力的增加，单一数据库服务器往往无法满足性能需求。主从复制和读写分离是...
Mysql 实现读写分离(主从复制)
Erica_java的博客
07-07 1306
mysql实现读写分离(主从复制)，强制走主库方案
MySQL主从复制、读写分离
白豆五的博客
01-24 1627
test
MySQL主从复制和读写分离
a568911的博客
12-20 1875
文章目录一.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.
MySQL数据库主从复制与读写分离（图文详解！）
A1100886的博客
06-26 2312
在实际的生产环境中，如果对mysql数据库的读和写都在一台数据库服务器中操作，无论是在安全性、高可用性，还是高并发等各个方面都是不能满足实际需求的。因此，一般通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。所以主从复制、读写分离就是为了数据库能支持更大的并发。随着业务量的扩展、如果是单机部署的MySQL，会导致I/O频率过高。采用主从复制、读写分离可以提高数据库的可用性。
mysql主从复制与读写分离
gcc001224的博客
06-28 555
在实际的生产环境中，如果对MySQL数据库的读和写都在一台数据库服务中操作，无论在安全性、高可用性，还是高并发性等各个方面都是完全不能满足实际需求的，一般来说都是通过主从复制（Master-Slave)的方式来同步数据，再通过读写分离来提升数据库的并发负载能力这样的方案进行部署与实施 两日志、三线程 主库将更新写入Binlog日志文件后，不需要等待数据更新是否已经复制到从库中，就可以继续处理更多的请求。Master将事件写入binlog，但并不知道Slave是否或何时已经接收且已处理。在异步复制的机制的情况
MySQL 主从复制与读写分离
octoben的博客
06-16 1555
1.1 什么是读写分离? 读写分离，基本的原理是让主数据库处理事务性增、改、删操作( INSERT、UPDATE、DELETE) ，而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库,,,...
MySQL主从复制与读写分离原理及用法详解
09-08
MySQL主从复制与读写分离是数据库高可用性和负载均衡的重要技术。主从复制允许数据从一个服务器（主服务器）自动同步到其他服务器（从服务器），而读写分离则是将读操作分散到从服务器，以减轻主服务器的压力，提高...
MySQL主从复制与读写分离.docx
04-13
MySQL 主从复制与读写分离 MySQL 主从复制是指将一个 MySQL 服务器的数据实时同步到另一个 MySQL 服务器中，以实现数据的高可用性和读写分离。下面是 MySQL 主从复制与读写分离的详细知识点： MySQL 主从复制 ...
CentOS服务器平台搭建mysql主从复制与读写分离的方法
09-09
主要介绍了CentOS服务器平台搭建mysql主从复制与读写分离的方法,结合实例形式较为详细的分析了CentOS平台搭建mysql主从复制与读写分离的步骤、设置方法、相关操作技巧与注意事项,需要的朋友可以参考下
MySQL：SELECT 语句
最新发布
Zachyy的博客
07-20 79
在 MySQL 中，可以使用 SELECT语句来查询数据。查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要的操作。
MySQL表中允许有多少个 TRIGGERS（触发器）？
sheji888的专栏
07-17 436
MySQL的触发器可以分为六种类型：BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER DELETE，每种类型可以针对一个表定义多个触发器，但是每种类型的触发器在单个表上只能有一个与之关联的命名触发器。：虽然技术上讲每种类型的触发器在单个表上只能有一个命名触发器，但你可以通过创建具有不同名称的相同类型的多个触发器来绕过这个限制，只要这些触发器在触发时满足不同的条件（例如，检查不同的列或满足不同的业务逻辑）。
MySQL（事务、索引）&&MyBatis
hycccccch的博客
07-19 560
事务指的是一组操作的集合，是一个不可分割的工作单位。事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败默认MySQL的事务是自动提交，即当执行一条DML语句时，MySQL会立即隐式的提交事务开启事务后运行事务不会对数据改变，commit后才会改变数据如果有命令发生错误，需要进行rollback进行回滚、
使用 Vue3、Node.js、MySQL、Electron 和 Express 实现用户登录、文章管理和截屏功能
liyananweb的博客
07-15 852
通过本文，我们详细介绍了如何使用 Vue3、Node.js、MySQL、Electron 和 Express 实现一个完整的用户登录、文章管理和截屏功能的应用。希望这篇文章能为你提供有价值的参考，帮助你更好地理解和实现前后端分离的应用开发。如果你有任何问题或建议，欢迎在评论区留言讨论。
Mysql----内置函数
m0_74979625的博客
07-18 225
Mysql--内置函数
记录些MySQL题集（8）
安静的软件工程师
07-16 1124
记录些MySQL题集（8）
MySQL主从复制与读写分离实战配置
MySQL主从复制与读写分离是一种常见的数据库集群架构，用于提高数据库性能和可用性。在本文中，作者分享了在Linux环境下实现这一功能的具体步骤。首先，环境准备阶段非常重要，包括设置MySQL主服务器（IP: 192.168....

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值