MySQL主从复制和读写分离的具体搭建过程,MyCat的实现
🍳引言
复制,cv怪,已经成为了程序员的标签hhh,但实际上复制是有很大作用的,特别是在提升系统高可用性方面,MySQL和redis中都充分运用了复制这一原理,并且扩展出了各种复制模型,由此又引申出了读写分离,进一步分摊压力,提高系统整体的高可用性。
🎏本篇速览脑图
什么是复制?
复制是指将主数据库的DDL 和 DML 操作通过二进制日志传到从库服务器中,然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步。
MySQL支持一台主库同时向多台从库进行复制, 从库同时也可以作为其他从服务器的主库,实现链状复制。
🎯复制的原理是什么?
MySQL 的主从复制流程如下:
图中涉及到了三个线程,一个运行在主节点(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中后,才将结果返回给客户端。
优点
- 一定程度上保证了数据一致性。
缺点
- 相比异步模式,多造成了一定程度的延迟。
全同步模式
主库执行完客户端提交的事务后,需要等待所有从库都复制之后,才返回结果给客户端,性能方面的缺陷是显 而易见的。
🎯复制的架构都有哪些?
单向主从
一个主人,一个仆人
一主多从
一个主人,多个仆人
互为主从
两台机器都可以写入数据,两台机器既是主人,也是仆人
多主多从
级联双主复制
级联复制模式下,部分slave
的数据同步不连接主节点,而是连接从节点。因为如果主节点有太多的从节点,就会损耗一部分性能用于replication
(复制),那么我们可以让3~5
个从节点连接主节点,其它从节点作为二级或者三级与从节点连接,这样不仅可以缓解主节点的压力,并且对数据一致性没有负面影响。
🍙复制有什么优势?
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 大量积压,未被同步到从服务器里。这就导致了主从不一致, 也就是主从延迟。
主从同步延迟的解决办法
解决主从复制延迟有几种常见的方法:
- 写操作后的读操作指定发给数据库主服务器
比如我们刚生成100个订单【从服务器复制需要一定时间】,后续去显示已生成订单的时候,这个读操作也发给主服务器。
这种方式和业务强耦合,对业务的侵入和影响较大,不利于扩展和维护
- 读从机失败后再读一次主机
这就是通常所说的 "二次读取" ,二次读取和业务无绑定,只需要对底层数据库访问的 API 进行封装即可,实现代价较小,不足之处在于如果有很多二次读取,将大大增加主机的读操作压力。例如,黑客暴力破解账号,会导致大量的二次读取操作,主机可能顶不住读操作的压力从而崩溃。
- 关键业务读写操作全部指向主机,非关键业务采用读写分离
像这种刚支付完,要看到自己支付的订单,这种属于关键业务,不容许太多读写不一致的情况,所以这些关键事务可以都指向主服务器,而其他比如点赞数量,非关键的业务就还是去读写分离。
🎯主从复制搭建步骤
master
1) 在master 的配置文件(/etc/my.cnf)中,配置如下内容:
如果你的my.cnf不在此目录,可以用如下命令查找: find / -name my.cnf -print
#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@ip1 和 ua@ip2代表的是两个不同的用户。
4) 查看master状态:
show master status;
字段含义:
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即可查阅。
指定当前从库对应的主库的IP地址,用户名,密码,从哪个日志文件开始的那个位置开始同步推送日志。
4) 开启同步操作
start slave;
show slave status;
只有这两个都Yes之后,才算搭建成功,如果出错的话,可以查看mysql的错误日志,在my.cnf中有指定错误日志的位置,若无指定一般为datadir所在的位置。
验证同步操作
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) 在从库中查询数据,进行验证 :
在从库中,可以查看到刚才创建的数据库:
在该数据库中,查询user表中的数据:
重新分配主从
stop slave; reset master;
🎨故障问题
drop user相关
比如主机原本有一个用户mycat,而从机是没有的,主机drop user mycat后,改动了mysql表,而从机中没有相应的mycat用户,导致没办法同步 如何预防这种错误发生呢?
- 在从机的/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.%
- 重启mysql服务
service mysql restart;
- 重新连上mysql
stop slave;
start slave;
show slave status\G;
出现这样就表示配置成功了: 当然,我们这里只是防止了以后不会出现用户相关故障,亡羊补牢罢了,但目前从机还是卡在这一栏过不去,怎么办呢?
出现错误后,如何恢复?
比如出现这个错误之后,我再新建了一个test表,之后的同步,Slave_SQL_Running进程都直接显示No了,从机不会去重做中继日志的。
- show slave status\G 来定位出错的sql:(补充自己的案例吧,制造一个错误)
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,因此我们可以在从库上执行:
如果这几条语句不是很重要的话,或者:
- 直接skip跳过这几行语句的执行
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 3;
start slave;
- 如果这个错误不影响我们的业务,可以采用以下方式,直接跳过不影响业务的错误号
在从库配置文件(默认在/etc/my.cnf)中修改:
slave-skip-errors = 1032,1062,1007(此处是具体的业务号,根据Error_code)
🎯读写分离
读写分离的基本原理是将数据库读写操作分散到不同的节点上,下面是基本架构图: 读写分离的基本实现是:
- 数据库服务器搭建主从集群,一主一从、一主多从都可以。
- 数据库主机负责读写操作,从机只负责读操作。
- 数据库主机通过复制将数据同步到从机,每台数据库服务器都存储了所有的业务数据。
- 业务服务器将写操作发给数据库主机,将读操作发给数据库从机。
读写分离的分配怎么实现呢?
将读写操作区分开来,然后访问不同的数据库服务器,一般有两种方式:程序代码封装和中间件封装。
程序代码封装
程序代码封装指在代码中抽象一个数据访问层 ,实现读写操作分离和数据库服务器连接的管理。例如,基于 Hibernate 进行简单封装 感兴趣的同学,可以了解下sharding-jdbc
中间件封装
中间件封装指的是独立一套系统出来,实现读写操作分离和数据库服务器连接的管理。中间件对业务服务器提供 SQL 兼容的协议,业务服务器无须自己进行读写分离。 对于业务服务器来说,访问中间件和访问数据库没有区别,事实上在业务服务器看来,中间件就是一个数据库服务器。 主流的有MyCat,其基本架构是:
🎮MyCat实现读写分离
安装
属于解压后即可使用的类型,非常简单!!!
- 到github下载安装包,推荐 1.6-Release
- 上传 解压
tar -zxvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
-
重要的三个配置文件 (不同版本的位置可能不一样,建议用 find / -name schema.xml -print 查看所在位置)
-
schema.xml:定义逻辑库,表,分片节点等内容
-
rule.xml:定义分片规则。
-
server.xml:定义用户以及系统相关变量,如端口等。
启动
- 修改配置文件 这里首先你得有两台 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">-->
<!--<!– 我的是读,从机 –>-->
<!-- <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种:
-
balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的 writehost 上。
-
balance="1",全部的 readhost 与 standby wtirehost 参与select语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与M2互为主备),正常情况下,M2,S1,S2 都参与 select 语句的负载均衡。
-
balance="2",所以读操作都随机的在 writehost、readhost 上分发。
-
balance="3",所有读请求随机的分发到 writehost 对应的 readhost 执行,writehost不负担读压力,注意 balance=3 只有1.4及其以后版本有,1.3没有。
接下来就可以启动了,有两种方式:
①控制台启动 :去 mycat/bin 目录下执行 ./mycat console ②后台启动 :去 mycat/bin 目录下 ./mycat start
如果要第一时间看到启动日志,方便定位问题,可以选择①控制台启动,否则采用第二种后台启动方式。
登录
在安装好mycat的服务器上的MySQL中,添加我们刚才server.xml中定义的用户:
- 先用原来的用户连接上MySQL后,执行如下语句
grant all privileges on*.*to'mycat'@'%' identified by '密码' with grant option;
flush privileges;
- exit退出
- 用刚才创建的mycat用户登录 MyCat , 像登录 MySql 一样登录
数据窗口 注意 -h 要用127.0.0.1 不要用localhost
mysql -umycat -p密码 -P 8066 -h ip地址
**维护窗口 **
mysql -umycat -p密码 -P 9066 -h ip地址
- 测试
mysql> show databases;
----------
| DATABASE |
----------
| MyCat |
----------
验证是否成功
1. 日志法
- conf文件夹中打开log4j2.xml,配置日志级别为debug
- 用mycat用户连接上mycat中间件,执行写入操作,比如创建一个表
- 看mycat的logs文件夹中的mycat.log,若如下图所示,则说明成功了!!
2. 9066管理端法
show @@datasource;
再次执行读操作或写操作,看哪个的execute发生了变化就说明走了哪个数据库
验证故障切换
我现在配置的负载均衡策略是:写主机不承担读的压力,也就是正常情况下,读操作都是不会走hostM1的 此时我把hostS3搞废了,看我读操作会发生什么问题?
- 实验证明,hostS3没有变化,但hostM1发生了变化,说明hostS3废掉之后,mycat会自动把读操作转移到hostM1,即使我们的负载均衡策略本意不是如此。
🍿🍿🍿手撕面答环节 -- 这是一条分割线
🍔说说主从复制的原理?
实际上是有三个线程,主节点一个dump线程,从节点一个IO和SQL线程
- dump线程负责将主库中的binlog,传递给从库
- 从节点的IO线程负责读取主库的binlog,并转换为relaylog
- 从节点的SQL线程负责重做这个replaylog,实现同步
🍔复制有什么好处呢?
- 可以把数据分摊到从库,这样后续也可以扩展读写分离,读都去读从库,写操作落在主库上
- 提供系统高可用性,不至于一台MySQL宕机后,整体服务都不可用
- 🎈🎈可以在从库中执行备份,以避免备份期间影响主库的服务。
🍔有哪些复制方式?
基于语句的复制
主库会记录那些造成数据更改的SQL语句,备库重做时,其实是把这些SQL语句再重新执行一遍。
缺点
- 可能存在一些无法被正确复制的SQL语句,比如使用了CURRENT_USER()函数的SQL语句。
基于行的复制
主库记录的是实际的数据,而不是SQL语句,备库重做时,无需再执行SQL语句,可以更高效的复制数据。
缺点
- 比如全表更新:update table set xxx = 0,如果基于语句复制,只需要存储这一句,而如果是基于行的复制,则每一行数据都需要记录到二进制日志中,开销很大。
Mix混合模式
上边两者的折中方案,如果语句无法被正确复制,则会切换为基于行的复制
🍔有哪些复制模式?
全同步复制
主库更新后,此时发送同步请求到从库去进行复制,需要等待从库确认复制成功了,才返回响应给客户端
问题
耗时过长,对客户端不友好
异步复制
主库更新后,发送异步请求到从库去复制,无需等待从库响应,直接返回响应给客户端
问题
可能存在复制丢失的情况
半同步复制
主库更新后,发送请求到从库,只需要部分从库确认复制成功即可,无需等待所有从库都复制成功,才返回响应给客户端
优点
算是一个折中的方式,不至于全丢失,也不至于耗时过长
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /boutique/detail/taneiaa
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
Android 11 保存文件到外部存储,并分享文件
Luke 10-12 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
excel下划线不显示怎么办
PHP中文网 06-23 -
怎样阻止微信小程序自动打开
PHP中文网 06-13 -
excel打印预览压线压字怎么办
PHP中文网 06-22 -
photoshop蒙版画笔没反应怎么办
PHP中文网 06-24