MySQL主从复制与读写分离

最新推荐文章于 2024-07-19 00:12:38 发布
最新推荐文章于 2024-07-19 00:12:38 发布
阅读量179 收藏
点赞数
分类专栏： Mysql 文章标签： 数据库 linux mysql 运维
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/IHBOS/article/details/116012488
版权

MySQL主从复制与读写分离

一、MySQL主从复制原理

在实际的生产中，为了解决Mysql的单点故障已经提高MySQL的整体服务性能，一般都会采用「主从复制」。

比如：在复杂的业务系统中，有一句sql执行后导致锁表，并且这条sql的的执行时间有比较长，那么此sql执行的期间导致服务不可用，这样就会严重影响用户的体验度。

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

这样读写分离的过程能够是整体的服务性能提高，即使写操作时间比较长，也不影响读操作的进行。

1、MySQL 支持的复制类型

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

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

在这里插入图片描述

  • 在从节点上执行sart slave命令开启主从复制开关，开始进行主从复制。从节点上的I/O 进程连接主节点，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；
  • 主节点接收到来自从节点的I/O请求后，通过负责复制的I/O进程（log dump 线程）根据请求信息读取指定日志指定位置之后的日志信息，返回给从节点。返回信息中除了日志所包含的信息之外，还包括本次返回的信息的bin-log file 的以及bin-log position（bin-log中的下一个指定更新位置）；
  • 从节点的I/O进程接收到主节点发送过来的日志内容、日志文件及位置点后，将接收到的日志内容更新到本机的relay-log（中继日志）的文件（Mysql-relay-bin.xxx）的最末端，并将读取到的binary log（bin-log）文件名和位置保存到master-info 文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log 的哪个位置开始往后的日志内容，请发给我”；
  • Slave 的 SQL线程检测到relay-log 中新增加了内容后，会将relay-log的内容解析成在主节点上实际执行过SQL语句，然后在本数据库中按照解析出来的顺序执行，并在relay-log.info中记录当前应用中继日志的文件名和位置点。

二、MySQL读写分离

在这里插入图片描述

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

1、MySQL 读写分离原理

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

2、读写分离方案

  • 基于程序代码内部实现
    在代码中根据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.172.40jdk-6u14-linux-x64.bin、amoeba-mysql-binary-2.2.0.tar.gz
MasterCentOS7192.168.172.10ntp 、 mysql-boost-5.7.20.tar.gz
Slave1CentOS7192.168.172.20ntp 、ntpdate 、 mysql-boost-5.7.20.tar.gz
Slave2CentOS7192.168.172.30ntp 、ntpdate 、mysql-boost-5.7.20.tar.gz
客户端CentOS7192.168.172.50

关闭所有主机的防火墙和安全防护机制

三、搭建 MySQL 主从复制

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

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

vim /etc/ntp.conf

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

service ntpd start

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

从服务器设置（192.168.172.20）、（192.168.172.30）

yum -y install ntp ntpdate

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

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

在这里插入图片描述
slave2和slave1操作一致就不演示了

2、主服务器的mysql配置

vim /etc/my.cnf
server-id = 1
log-bin=master-bin							#添加，主服务器开启二进制日志
log-slave-updates=true						#添加，允许从服务器更新二进制日志

systemctl restart mysqld

mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.172.%' IDENTIFIED BY '35123512';			#给从服务器授权
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		#添加，定义中继日志文件的位置和名称

systemctl restart mysqld

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

mysql -u root -p
change master to master_host='192.168.172.10' , master_user='myslave',master_password='35123512',master_log_file='master-bin.000001',master_log_pos=604;
				#配置同步，注意 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 test;
    在这里插入图片描述在这里插入图片描述
    在这里插入图片描述

四、搭建 MySQL 读写分离

1、Amoeba服务器配置

Amoeba服务器：192.168.172.40
将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/
/usr/local/amoeba/bin/amoeba
//如显示amoeba start|stop 说明安装成功

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

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

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

grant all on *.* to test@'192.168.172.%' identified by '35123512';

在这里插入图片描述
一台主两台从都需要开放权限给 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">35123512</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.172.10</property>
#---------52修改，设置从服务器的名slave1-----------------
<dbServer name="slave1"  parent="abstractServer">
#---------55修改，设置从服务器1的地址---------------------
<property name="ipAddress">192.168.172.20</property>
#---------58复制上面6行粘贴，设置从服务器2的名slave2和地址---
<dbServer name="slave2"  parent="abstractServer">
<property name="ipAddress">192.168.172.30</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 -p35123512 -h 192.168.172.40 -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访问数据库集群）

IHBOS
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql主从复制与读写分离
weixin_48861962的博客
11-01 792
一、MySQL主从复制与读写分离 1.1 主从分离和读写分离的相关概述 1）主从复制与读写分离、 在实质际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实院需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。 2）为什么要读写分离 因为数据库的"写"（
mysql主从复制与读写分离
gcc001224的博客
06-28 555
在实际的生产环境中，如果对MySQL数据库的读和写都在一台数据库服务中操作，无论在安全性、高可用性，还是高并发性等各个方面都是完全不能满足实际需求的，一般来说都是通过主从复制（Master-Slave)的方式来同步数据，再通过读写分离来提升数据库的并发负载能力这样的方案进行部署与实施 两日志、三线程 主库将更新写入Binlog日志文件后，不需要等待数据更新是否已经复制到从库中，就可以继续处理更多的请求。Master将事件写入binlog，但并不知道Slave是否或何时已经接收且已处理。在异步复制的机制的情况
MySQL 主从复制与读写分离
octoben的博客
06-16 1554
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索引的创建和使用
IHBOS的博客
04-12 6691
这里写目录标题Mysql索引Mysql索引的概念Mysql索引的作用Mysql索引的副作用创建索引的原则依据索引的分类以及创建索引创建的方法普通索引直接创建索引和查询索引的方式修改表方式创建创建表的时候指定索引唯一索引直接创建唯一索引修改表方式创建创建表的时候指定主键索引创建表的时候指定修改表方式创建组合索引（单列索引与多列索引）全文索引直接创建索引修改表方式创建创建表的时候指定索引使用全文索引查询查看索引删除索引直接删除索引修改表方式删除索引删除主键索引 Mysql索引 Mysql索引的概念 1、索引是
MySQL——高阶语句（上）
IHBOS的博客
04-17 672
这里写目录标题MySQL高级语句ORDER BY----按关键字排序单字段排序多字段排序OR/AND----或/且DISTINCT----查询不重复记录GROUP BY----对结果进行分组LIMIT----限制结果条目设置别名通配符子查询子查询----别名as子查询----EXISTS视图创建视图查看视图修改原表数据查看视图 实验准备创建一张表 如下: 数据库有一张test表，记录了学生的id，姓名，分数，地址和爱好 create table test (id int(10) primary key no
MySQL-日志、完全备份恢复和增量备份恢复
IHBOS的博客
04-15 584
这里写目录标题一、MySQL日志管理1、MySQL日志文件错误日志二进制日志中继日志慢查询日志二、查看日志状态命令三、备份的重要性1、造成数据丢失的原因有如下几种四、备份类型1、从物理与逻辑的角度分类2、从数据库的备份策略角度分类3、备份方法五、数据库冷备份与恢复及完全备份与恢复的基本命令1、物理冷备份与恢复2、mysqldump 备份与恢复完全备份一个或多个完整的库（包括其中所有的表）完全备份 MySQL 服务器中所有的库完全备份指定库中的部分表3、查看备份文件 一、MySQL日志管理 1、MySQL日志
MySQL——高阶语句（中）
IHBOS的博客
04-20 446
这里写目录标题一、正则表达式二、运算符三、比较运算符四、逻辑运算符位运算符连接查询内连接左连接右连接 一、正则表达式 MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串。 MySQL 的正则表达式使用REGEXP这个关键字来指定正则表达式的匹配模式， REGEXP 操作符所支持的匹配模式如下: 匹配模式 描述 实例 ^ 匹配文本的开始字符 ‘^bd’ 匹配以 bd 开头的字符串 $ 匹配文本的结束字符
MySQL基本操作命令和数据表高级操作
IHBOS的博客
04-08 436
这里写目录标题常用的数据类型查看数据库、表和表结构查看数据库查看数据库包含的表查看表结构SQL语句创建新的数据库创建新的表删除指定数据表删除指定数据库管理表中的数据记录向数据表中插入新的数据记录查询数据记录修改、更新数据表中的数据记录在数据表中删除指定的数据记录修改表名和表结构修改表名扩展表结构（增加字段）修改字段（列）名，添加唯一键删除字段 常用的数据类型 常用的数据类型作用int整型float单精度浮点-------4字节32位double双精度浮点-------8字节64位char固定长度的字符类型v
MySQL服务MySQL+MHA高可用服务集群搭建
IHBOS的博客
04-24 368
这里写目录标题MySQL MHAMHA概念MHA 的组成MHA 的特点搭建MySQL+MHA1、所有服务器，关闭系统防火墙和安全机制2、修改 master（192.168.172.10）、Slave1（192.168.172.20）、Slave2（192.168.172.30） 节点的主机名3、修改三台MySQL服务器的主配置文件/etc/my.cnf4、配置MySQL一主两从5、主从复制验证6、安装 MHA 软件在 MHA manager 节点上安装 manager 组件7、在所有服务器上配置无密码认证在
Mysql数据库的基本概念
IHBOS的博客
04-06 252
这里写目录标题一、数据库的基本概念1、数据库的组成2、数据库类型关系型数据库非关系型数据库redis 与 Mcmcache对比3、数据库管理系统（DBMS）4、数据库系统数据库系统发展史第一代数据库第二代数据库第三代数据库当今主流数据库关系型数据库基本介绍非关系型数据库MySQL数据库MySQLMySQL商业版与社区版MySQL商业版与社区版两者区别产品阵营日志 一、数据库的基本概念 1、数据库的组成 数据（Data） 描述事物的符号记录 包括数字，文字、图形、图像、声音、档案记录等 以“记录”形式按
MySQL——高阶语句、存储过程（下）
IHBOS的博客
04-20 226
MySQL——高阶语句（下）数据库函数数学函数聚合函数字符串函数 数据库函数 MySQL数据库函数提供了能够实现各种功能的方法，使我们在查询记录时能够更高效的输出。MySQL内建了很多函数，常用的包括数学函数、聚合函数、字符串函数和日期时间函数。 数学函数 数据库内存储的记录，经常要进行一系列的算术操作，所以 MySQ支持很多数学函数。常用的数学函数如表所示 abs (x) 返回x的绝对值 rand () 返回o到1的随机数 mod (x,y)
MySQL事务与存储引擎相关设置
IHBOS的博客
04-13 216
MySQL事务与存储引擎一、事务的ACID特点1、原子性2、一致性3、隔离性4、持久性二、事务之间的互相影响三、事务的四大隔离四、事务控制语句示例1、开启、提交事务2、事务回滚3、多点回滚4、使用set设置控制事务五、MySQL存储引擎1、存储引擎概念介绍2、MyISAMISAM的特点MyIAM特点MyIAM支持的存储格式MyISAM适用的生产场景举例3、InnoDBInnoDB特点InnoDB存储引擎存储成的三个文件4、查看系统支持的存储引擎5、查看表使用的存储引擎方法一方法二6、修改存储引擎方法一：通过
MySQL（事务、索引）&&MyBatis
最新发布
hycccccch的博客
07-19 554
事务指的是一组操作的集合，是一个不可分割的工作单位。事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败默认MySQL的事务是自动提交，即当执行一条DML语句时，MySQL会立即隐式的提交事务开启事务后运行事务不会对数据改变，commit后才会改变数据如果有命令发生错误，需要进行rollback进行回滚、
MySQL主从复制与读写分离实战教程
第八章：mysql主从复制与读写分离.doc” 本文档主要讲述了如何设置MySQL的主从复制和实现读写分离，以提升数据库系统的可用性和性能。以下是详细的知识点说明： 1. **MySQL主从复制**：这是一种数据库高可用性的...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值