likes
comments
collection
share

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

作者站长头像
站长
· 阅读数 28

🍳引言

复制,cv怪,已经成为了程序员的标签hhh,但实际上复制是有很大作用的,特别是在提升系统高可用性方面,MySQL和redis中都充分运用了复制这一原理,并且扩展出了各种复制模型,由此又引申出了读写分离,进一步分摊压力,提高系统整体的高可用性。

本篇就来聊聊,主从复制的原理,模式,方式,实现,以及读写分离的原理和实现方式,最后还有经典的【手撕面答】环节

🎏本篇速览脑图

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

什么是复制?

复制是指将主数据库的DDL 和 DML 操作通过二进制日志传到从库服务器中,然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制, 从库同时也可以作为其他从服务器的主库,实现链状复制。

🎯复制的原理是什么?

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

MySQL 的主从复制流程如下:

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

图中涉及到了三个线程,一个运行在主节点(log dump thread)--图中binlog 到 slave线程就是通过binlog dump线程来推送,其余两个(I/O thread, SQL thread)运行在从节点。

主节点 log dump 线程

当从节点连接主节点时,主节点会为其创建一个log dump 线程,用于发送和读取binlog的内容。在读取binlog中的操作时,log dump线程会对主节点上的bin-log加锁,当读取完成,在发送给从节点之前,锁会被释放。主节点会为自己的每一个从节点创建一个log dump 线程

从节点I/O thread

当从节点上执行start slave命令之后,从节点会创建一个I/O线程,跟主库建立一个普通的客户端连接,请求主库中更新的binlog。然后在主库上启动一个特殊的二进制转储进程,读取主库上二进制日志中的事件,把 binlog 信息写入 relay log 的中继日志里

从节点SQL thread

该线程负责重做中继日志 relay log,从中读取事件并在备库执行。

🎈复制都有哪些方式?

基于语句的复制

Statement-base Replication (SBR)

主库会记录那些造成数据更改的SQL语句,备库重做时,其实是把这些SQL语句再重新执行一遍。

缺点

  • 可能存在一些无法被正确复制的SQL语句,比如使用了CURRENT_USER()函数的SQL语句。

基于行的复制

Row-based Relication(RBR)

主库记录的是实际的数据,而不是SQL语句,备库重做时,无需再执行SQL语句,可以更高效的复制数据。

缺点

  • 比如全表更新:update table set xxx = 0,如果基于语句复制,只需要存储这一句,而如果是基于行的复制,则每一行数据都需要记录到二进制日志中,开销很大。

Mix混合模式

Mixed-format Replication(MBR)

所以,没有哪一种复制模式是完美的,实际上MySQL能在两种复制模式中动态切换,默认情况下使用的是基于语句的复制方式,如果无法被正确复制,则会切换为基于行的复制

🎈复制的各种模式

总结

异步与同步的区别在于,返回客户端之前,是否需要去跟从库做操作,

MySQL 主从复制默认是异步的模式。MySQL增删改操作会全部记录在bin-log(binary log)中,当slave节点连接master时,会主动从master处获取最新的bin-log文件。并把bin-log存储到本地的relay-log中,然后去执行relay-log的更新内容。

异步模式

从上边可以看出,是从节点主动从主节点获取最新的bin-log文件,主节点并不会主动推送bin-log到从节点,主节点执行完客户端提交的事务后,立即将结果返回给客户端,而不去关心从库到底有没有复制成功。(这就是所谓异步的体现,无需等待从库复制完才继续自己的操作)

优点

  • 性能高,延迟低(相比其他两种模式)

缺点

  • 数据一致性,完整性欠缺:比如某一天主库执行完客户端提交的事务后,突然宕机了,而此时从库还没有复制完呢,这是我们第一反应如果是:把从库提升为主库,那此时可能数据就会出现不一致。

半同步模式

顾名思义,介于异步和全同步之间,“半”体现在哪里呢,主库执行完客户端提交的事务后,会等待至少一个从库接收并写到relay-log中后,才将结果返回给客户端。

优点

  • 一定程度上保证了数据一致性。

缺点

  • 相比异步模式,多造成了一定程度的延迟。

全同步模式

主库执行完客户端提交的事务后,需要等待所有从库都复制之后,才返回结果给客户端,性能方面的缺陷是显 而易见的。

🎯复制的架构都有哪些?

单向主从

一个主人,一个仆人

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现


一主多从

一个主人,多个仆人

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现


互为主从

两台机器都可以写入数据,两台机器既是主人,也是仆人

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现


多主多从

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

级联双主复制

级联复制模式下,部分slave的数据同步不连接主节点,而是连接从节点。因为如果主节点有太多的从节点,就会损耗一部分性能用于replication(复制),那么我们可以让3~5个从节点连接主节点,其它从节点作为二级或者三级与从节点连接,这样不仅可以缓解主节点的压力,并且对数据一致性没有负面影响。

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

🍙复制有什么优势?

MySQL 复制的要点主要包含以下三个方面:

  • 主库出现问题,可以快速切换到从库提供服务。
  • 可以在从库上执行查询操作,从主库中更新,实现读写分离,降低主库的访问压力。
  • 🎈🎈可以在从库中执行备份,以避免备份期间影响主库的服务。

🍙解决了什么问题?

  • 数据分布 (Data distribution )
  • 负载平衡(load balancing)
  • 数据备份(Backups) ,保证数据安全
  • 高可用性和容错行(High availability and failover)
  • 实现读写分离,缓解数据库压力

🍙从库是不是越多越好?

不是的。

因为从库数量增加,从库连接上来的 I/O 线程也比较多,主库也要创建同样多的 log dump 线程来处理复制的请求,对主库资源消耗比较高,同时还受限于主库的网络带宽。 所以在实际使用中,一个主库一般跟 2~3 个从库(1 套数据库,1 主 2 从 1 备主),这就是一主多从的 MySQL 集群结构。

🍙主从同步延迟怎么处理?

主从同步延迟的原因

一个服务器开放N个链接给客户端来连接的,这样有会有大并发的更新操作, 但是从服务器里面读取 binlog 的线程仅有一个,当某个 SQL 在从服务器上执行的时间稍长 或者由于某个 SQL 要进行锁表就会导致:主服务器的 SQL 大量积压,未被同步到从服务器里。这就导致了主从不一致, 也就是主从延迟

主从同步延迟的解决办法

解决主从复制延迟有几种常见的方法:

  1. 写操作后的读操作指定发给数据库主服务器

比如我们刚生成100个订单【从服务器复制需要一定时间】,后续去显示已生成订单的时候,这个读操作也发给主服务器。

这种方式和业务强耦合,对业务的侵入和影响较大,不利于扩展和维护

  1. 读从机失败后再读一次主机

这就是通常所说的 "二次读取" ,二次读取和业务无绑定,只需要对底层数据库访问的 API 进行封装即可,实现代价较小,不足之处在于如果有很多二次读取,将大大增加主机的读操作压力。例如,黑客暴力破解账号,会导致大量的二次读取操作,主机可能顶不住读操作的压力从而崩溃。

  1. 关键业务读写操作全部指向主机,非关键业务采用读写分离

像这种刚支付完,要看到自己支付的订单,这种属于关键业务,不容许太多读写不一致的情况,所以这些关键事务可以都指向主服务器,而其他比如点赞数量,非关键的业务就还是去读写分离。

🎯主从复制搭建步骤

master

1) 在master 的配置文件(/etc/my.cnf)中,配置如下内容:

如果你的my.cnf不在此目录,可以用如下命令查找: find / -name my.cnf -print

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

#mysql 服务ID,保证整个集群环境中唯一
server-id=1

#mysql binlog 日志的存储路径和文件名
log-bin=mysql-bin

#是否只读,1 代表只读, 0 代表读写
read-only=0

#忽略的数据, 指不需要同步的数据库
binlog-ignore-db=mysql

#指定同步的数据库
#binlog-do-db=db01

2) 执行完毕之后,需要重启Mysql:

service mysql restart ;

🧨授权

3) 创建一个专门用来同步数据的账户,并且进行授权操作:

这里的意思是,将所有库,所有表的主从复制权限赋予用户名为melo,密码为melo,并且ip为192.168.192.131的远程mysql

grant replication slave on *.* to 'melo'@'192.168.192.131' identified by 'melo';

flush privileges;

扩展:

create user 'ua'@'%' identified by 'pa';

这条语句的逻辑是创建一个用户’ua’@’%’,密码是pa。注意,在MySQL里面,**用户名(user)+地址(host)**才表示一个用户,因此 ua@ip1ua@ip2代表的是两个不同的用户。

4) 查看master状态:

show master status;

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

字段含义:

File : 从哪个日志文件开始推送日志文件 
Position : 从哪个位置开始推送日志
Binlog-Do-DB:指定需要同步的数据库
Binlog_Ignore_DB : 指定不需要同步的数据库

slave

1) 在 slave 端配置文件(同样是my.cnf)中,配置如下内容:

#mysql服务端ID,唯一
server-id=2

#指定binlog日志
log-bin=mysql-bin

2) 执行完毕之后,需要重启Mysql:

service mysql restart;

🎪配置主机

3) 重启后登录上mysql,执行如下指令 : change master to master_host= '192.168.192.130', master_user='melo', master_password='melo', master_log_file='mysqlbin.000001', master_log_pos=413;

其中:

  • master_host是主库的ip地址,
  • master_user是刚才的用户名,
  • master_password是刚才创建的密码
  • master_log_file,master_log_pos:在主库中show master status即可查阅。

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

指定当前从库对应的主库的IP地址,用户名,密码,从哪个日志文件开始的那个位置开始同步推送日志。

4) 开启同步操作

start slave;

show slave status;

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

只有这两个都Yes之后,才算搭建成功,如果出错的话,可以查看mysql的错误日志,在my.cnf中有指定错误日志的位置,若无指定一般为datadir所在的位置。 「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

验证同步操作

1) 在主库中创建数据库,创建表,并插入数据 :

create database db01;

user db01;

create table user(
id int(11) not null auto_increment,
name varchar(50) not null,
sex varchar(1),
primary key (id)
)engine=innodb default charset=utf8;

insert into user(id,name,sex) values(null,'Tom','1');
insert into user(id,name,sex) values(null,'Trigger','0');
insert into user(id,name,sex) values(null,'Dawn','1');

2) 在从库中查询数据,进行验证 :

在从库中,可以查看到刚才创建的数据库:

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

在该数据库中,查询user表中的数据:

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

重新分配主从

stop slave; reset master;

🎨故障问题

drop user相关

比如主机原本有一个用户mycat,而从机是没有的,主机drop user mycat后,改动了mysql表,而从机中没有相应的mycat用户,导致没办法同步 「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现 如何预防这种错误发生呢?

  1. 从机的/etc/my.cnf中加上这几栏,表示从机不会去重做这几个MySQL系统表相关的变更:
replicate_wild_ignore_table =mysql.%

replicate_wild_ignore_table =test.%

replicate_wild_ignore_table =information_schema.%

replicate_wild_ignore_table =performance_schema.%
  1. 重启mysql服务
service mysql restart;
  1. 重新连上mysql
stop slave;
start slave;
show slave status\G;

出现这样就表示配置成功了: 「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现 当然,我们这里只是防止了以后不会出现用户相关故障,亡羊补牢罢了,但目前从机还是卡在这一栏过不去,怎么办呢?

出现错误后,如何恢复?

比如出现这个错误之后,我再新建了一个test表,之后的同步,Slave_SQL_Running进程都直接显示No了,从机不会去重做中继日志的。

  1. show slave status\G 来定位出错的sql:(补充自己的案例吧,制造一个错误)

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

Last_SQL_Error : 代表最后一个执行的sql出错的原因。定位到了错误的原因,我们需要根据错误原因,解决问题。这里我们需要关注三个返回值:

Relay_Master_Log_File: mysql-bin.000001 Skip_Counter: 0 Exec_Master_Log_Pos: 1543

Relay_Master_Log_File:主库哪个日志文件出现问题 Exec_Master_Log_Pos:偏移量

根据以上提示的信息,在主库执行:

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000001' from 1543;

可以看到出错的sql语句,MySQL的binary log每个SQL执行实际上是由一些event组成的,我们这里要设置的就是要跳过的event的个数。本例中就是BEGIN、具体SQL语句和COMMIT这3个event,因此我们可以在从库上执行:

如果这几条语句不是很重要的话,或者:

  1. 直接skip跳过这几行语句的执行
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 3;
start slave;
  1. 如果这个错误不影响我们的业务,可以采用以下方式,直接跳过不影响业务的错误号

在从库配置文件(默认在/etc/my.cnf)中修改:

slave-skip-errors = 1032,1062,1007(此处是具体的业务号,根据Error_code)

🎯读写分离

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

读写分离的基本原理是将数据库读写操作分散到不同的节点上,下面是基本架构图: 「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现 读写分离的基本实现是:

  • 数据库服务器搭建主从集群,一主一从、一主多从都可以。
  • 数据库主机负责读写操作,从机只负责读操作。
  • 数据库主机通过复制将数据同步到从机,每台数据库服务器都存储了所有的业务数据。
  • 业务服务器将写操作发给数据库主机,将读操作发给数据库从机。

读写分离的分配怎么实现呢?

将读写操作区分开来,然后访问不同的数据库服务器,一般有两种方式:程序代码封装中间件封装

程序代码封装

程序代码封装指在代码中抽象一个数据访问层 ,实现读写操作分离和数据库服务器连接的管理。例如,基于 Hibernate 进行简单封装 「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现 感兴趣的同学,可以了解下sharding-jdbc

中间件封装

中间件封装指的是独立一套系统出来,实现读写操作分离和数据库服务器连接的管理。中间件对业务服务器提供 SQL 兼容的协议,业务服务器无须自己进行读写分离。 对于业务服务器来说,访问中间件和访问数据库没有区别,事实上在业务服务器看来,中间件就是一个数据库服务器。 主流的有MyCat,其基本架构是: 「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

🎮MyCat实现读写分离

安装

属于解压后即可使用的类型,非常简单!!!

  1. 到github下载安装包,推荐 1.6-Release

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

github.com/MyCATApache…

  1. 上传 解压
tar -zxvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
  1. 重要的三个配置文件 (不同版本的位置可能不一样,建议用 find / -name schema.xml -print 查看所在位置)

  2. schema.xml:定义逻辑库,表,分片节点等内容

  3. rule.xml:定义分片规则。

  4. server.xml:定义用户以及系统相关变量,如端口等。

启动

  1. 修改配置文件 这里首先你得有两台 mysql

server.xml

点进去会发现有一大堆配置,我们只需要找到user标签,修改这一项即可

<user name="mycat" defaultAccount="true">
		<property name="password">#Hjj93290</property>
  <!--		schema中配置的name-->
		<property name="schemas">MyCat</property>
		<property name="defaultSchema">MyCat</property>
		
		<!-- 表级 DML 权限设置 -->
		<!-- 		
		<privileges check="false">
			<schema name="TESTDB" dml="0110" >
				<table name="tb01" dml="0000"></table>
				<table name="tb02" dml="1111"></table>
			</schema>
		</privileges>		
		 -->
	</user>

	<user name="user">
		<property name="password">user</property>
		<property name="schemas">MyCat</property>
		<property name="readOnly">true</property>
	</user>

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="MyCat" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
		
	</schema>

	<!--  dataHost对应下边的name,database则是实际要代理的物理库	-->
	<dataNode name="dn1" dataHost="host1" database="myrdc" />

	<!--  name对应上边的dataHost	-->
	<dataHost name="host1" maxCon="1000" minCon="10" balance="2"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<!--		心跳检测-->
		<heartbeat>select user();</heartbeat>
		<!-- can have multi write hosts -->

		<!--		<writeHost host="hostS2" url="localhost:3306" user="root" password="Hjj93290">-->
		<!--&lt;!&ndash;			我的是读,从机 &ndash;&gt;-->
		<!--			<readHost host="hostM1" url="47.98.190.152:3306" user="root"-->
		<!--					  password="rdcyyds"/>-->
		<!--		</writeHost>-->

		<writeHost host="hostM1" url="jdbc:mysql://47.98.190.152:3306" user="root" password="rdcyyds">
			<!--			我的是读,从机 -->
			<readHost host="hostS2" url="jdbc:mysql://localhost:3306" user="root"
					  password="Hjj93290"/>
		</writeHost>

	</dataHost>
  
</mycat:schema>
balance负载均衡

负载均衡类型,目前的取值有4种:

  1. balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的 writehost 上。

  2. balance="1",全部的 readhost 与 standby wtirehost 参与select语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与M2互为主备),正常情况下,M2,S1,S2 都参与 select 语句的负载均衡。

  3. balance="2",所以读操作都随机的在 writehost、readhost 上分发。

  4. balance="3",所有读请求随机的分发到 writehost 对应的 readhost 执行,writehost不负担读压力,注意 balance=3 只有1.4及其以后版本有,1.3没有。

接下来就可以启动了,有两种方式:

①控制台启动 :去 mycat/bin 目录下执行 ./mycat console ②后台启动 :去 mycat/bin 目录下 ./mycat start

如果要第一时间看到启动日志,方便定位问题,可以选择①控制台启动,否则采用第二种后台启动方式。

登录

在安装好mycat的服务器上的MySQL中,添加我们刚才server.xml中定义的用户:

  1. 先用原来的用户连接上MySQL后,执行如下语句
grant all privileges on*.*to'mycat'@'%' identified by '密码' with grant option;
flush privileges;
  1. exit退出
  2. 用刚才创建的mycat用户登录 MyCat , 像登录 MySql 一样登录

数据窗口 注意 -h 要用127.0.0.1 不要用localhost

mysql -umycat -p密码 -P 8066 -h ip地址

**维护窗口 **

mysql -umycat -p密码 -P 9066 -h ip地址
  1. 测试
mysql> show databases;
+----------+
| DATABASE |
+----------+
| MyCat    |
+----------+

验证是否成功

1. 日志法

blog.csdn.net/qq_41659549…

  1. conf文件夹中打开log4j2.xml,配置日志级别为debug

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

  1. 用mycat用户连接上mycat中间件,执行写入操作,比如创建一个表
  2. 看mycat的logs文件夹中的mycat.log,若如下图所示,则说明成功了!!

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

2. 9066管理端法

show @@datasource;

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现 再次执行读操作或写操作,看哪个的execute发生了变化就说明走了哪个数据库

验证故障切换

我现在配置的负载均衡策略是:写主机不承担读的压力,也就是正常情况下,读操作都是不会走hostM1的 此时我把hostS3搞废了,看我读操作会发生什么问题?

  • 实验证明,hostS3没有变化,但hostM1发生了变化,说明hostS3废掉之后,mycat会自动把读操作转移到hostM1,即使我们的负载均衡策略本意不是如此。

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

🍿🍿🍿手撕面答环节 -- 这是一条分割线

🍔说说主从复制的原理?

实际上是有三个线程,主节点一个dump线程,从节点一个IO和SQL线程

  1. dump线程负责将主库中的binlog,传递给从库
  2. 从节点的IO线程负责读取主库的binlog,并转换为relaylog
  3. 从节点的SQL线程负责重做这个replaylog,实现同步

🍔复制有什么好处呢?

  1. 可以把数据分摊到从库,这样后续也可以扩展读写分离,读都去读从库,写操作落在主库上
  2. 提供系统高可用性,不至于一台MySQL宕机后,整体服务都不可用
  3. 🎈🎈可以在从库中执行备份,以避免备份期间影响主库的服务。

🍔有哪些复制方式?

基于语句的复制

主库会记录那些造成数据更改的SQL语句,备库重做时,其实是把这些SQL语句再重新执行一遍。

缺点

  • 可能存在一些无法被正确复制的SQL语句,比如使用了CURRENT_USER()函数的SQL语句。

基于行的复制

主库记录的是实际的数据,而不是SQL语句,备库重做时,无需再执行SQL语句,可以更高效的复制数据。

缺点

  • 比如全表更新:update table set xxx = 0,如果基于语句复制,只需要存储这一句,而如果是基于行的复制,则每一行数据都需要记录到二进制日志中,开销很大。

Mix混合模式

上边两者的折中方案,如果语句无法被正确复制,则会切换为基于行的复制

🍔有哪些复制模式?

全同步复制

主库更新后,此时发送同步请求到从库去进行复制,需要等待从库确认复制成功了,才返回响应给客户端

问题

耗时过长,对客户端不友好

异步复制

主库更新后,发送异步请求到从库去复制,无需等待从库响应,直接返回响应给客户端

问题

可能存在复制丢失的情况

半同步复制

主库更新后,发送请求到从库,只需要部分从库确认复制成功即可,无需等待所有从库都复制成功,才返回响应给客户端

优点

算是一个折中的方式,不至于全丢失,也不至于耗时过长

「MySQL高级篇」MySQL主从复制 && 读写分离 -- 附具体的搭建过程,MyCat实现

收藏 == 白嫖,点赞+关注才是真爱!!!本篇文章如有不对之处,还请在评论区指出,欢迎添加我的微信一起交流:Melo__Jun

转载自:https://juejin.cn/post/7160580280682545166
评论
请登录