主从复制
提升数据库并发能力
1、一般应用对数据库都是读多写少,即对数据库读取数据的压力比较大
2、考虑次序
(1)优化 SQL 和索引,简单有效
(2)采用缓存 ,如使用 Redis 将热点数据保存在内存数据库中,提升读取的效率
(3)对数据库采用主从架构,进行读写分离
主从复制的作用
1、提高数据库的吞吐量
2、读写分离
(1)其中一个是 Master 主库,负责写入数据,称为:写库;其它都是 Slave 从库,负责读取数据,称为:读库
(2)当主库进行更新时,会自动将数据复制到从库中,而在客户端读取数据时,会从从库中进行读取
(3)减少锁表的影响,实现更高的并发访问,还能对从服务器进行负载均衡,让不同的读请求按照策略均匀地分发到不同的从服务器上
3、数据备份
(1)通过主从复制,将主库上的数据,复制到从库
(2)热备份机制,即在主库正常运行的情况下进行备份,不会影响到服务
4、高可用性
(1)数据备份实际上是一种冗余的机制,通过这种冗余的方式可以换取数据库的高可用性,当服务器出现故障 / 宕机,可以切换到从服务器上,保证服务的正常运行
(2)高可用性的指标衡量 = 正常可用时间 / 全年时间
三个线程
1、主从同步原理;基于 binlog 进行数据同步
2、在主从复制过程中,基于3 个线程来操作,一个主库线程,两个从库线程
(1)主库线程:二进制日志转储线程(Binlog dump thread),当从库线程连接时,主库可以将二进制日志发送给从库,当主库读取事件(Event)时,会在 binlog 上加锁,读取完成后,再释放锁
(2)从库 I/O 线程:连接到主库,向主库发送请求更新 binlog,读取到主库的二进制日志转储线程发送的 binlog 更新部分,并且拷贝到本地的中继日志(relaylog)
(3)从库 SQL 线程:读取从库的中继日志,并且执行日志中的事件,将从库中的数据与主库保持同步
3、复制三步骤
(1)Master 将写操作记录到二进制日志(binlog),这些记录叫做二进制日志事件(binary log events)
(2)Slave 将 Master 的 binary log events 拷贝到它的中继日志(relay log)
(3)Slave 重做中继日志中的事件,将改变应用到自己的数据库中,MySQL 复制是异步的且串行化的,而且重启后从接入点开始复制
4、复制的最大问题:延时
5、注意
(1)不是所有版本 MySQL 都默认开启服务器的二进制日志,在进行主从同步时,需要先检查服务器是否已经开启二进制日志
(2)除非特殊指定,默认情况下从服务器会执行所有主服务器中保存的事件,可以通过配置,使从服务器执行特定的事件
6、复制的基本原则
(1)每个 Slave 只有一个 Master
(2)每个 Slave 只能有一个唯一服务器 ID
(3)每个 Master 可以有多个 Slave
一主一从架构搭建
1、架构图
2、主机配置文件
(1)建议 mysql 版本一致,且后台以服务运行,主从所有配置项都配置在 [mysqld] 节点下,且都是小写字母
(2)必选参数配置
#[必须]主服务器唯一ID
server-id=指定ID
#[必须]启用二进制日志
log-bin=二进制日志路径和文件名
(3)可选参数配置
#[可选] 0(默认)表示读写(主机),1表示只读(从机)
read-only=0
#设置日志文件保留的时长,单位是秒
binlog_expire_logs_seconds=6000
#控制单个二进制日志大小,此参数的最大和默认值是1GB
max_binlog_size=200M
#[可选] 设置不要复制的数据库
binlog-ignore-db=数据库名
#[可选] 设置需要复制的数据库,默认全部记录
binlog-do-db=需要复制的主数据库名字
#[可选] 设置binlog格式,默认STATEMENT
binlog_format=STATEMENT
(4)重启后台 mysql 服务,使配置生效
(5)先搭建完主从复制,再创建数据库
(6)MySQL 主从复制起始时,从机不继承主机数据
STATEMENT 模式
1、基于 SQL 语句的复制:statement-based replication,SBR
2、binlog 默认格式:每一条会修改数据的 SQL 语句会记录到 binlog 中
3、SBR 优点
(1)技术成熟
(2)不需要记录每一行的变化,减少 binlog 日志量,文件较小
(3)binlog 中包含所有数据库更改信息,可以据此来审核数据库的安全等情况
(4)binlog 不仅用于复制,还用于实时还原
(5)主从版本可以不一样,从服务器版本可以比主服务器版本高
4、SBR 缺点
(1)不是所有 UPDATE 语句都能被复制,尤其是包含不确定操作时
(2)使用以下函数的语句无法被复制:LOAD_FILE()、UUID()、USER()、FOUND_ROWS()、SYSDATE(),除非启动时启用 --sysdate-is-now 选项
(3)INSERT …… SELECT 会产生比 RBR 更多的行级锁
(4)复制需要进行全表扫描(WHERE 中没有使用到索引)的 UPDATE 时,需要比 RBR 请求更多的行级锁
(5)对于有 AUTO_INCREMENT 字段的 InnoDB 表而言,INSERT 语句会阻塞其他 INSERT 语句
(6)对于一些复杂的语句,在从服务器上的耗资源情况会更严重,而 RBR 模式下,只会对那个发生变化的记录产生影响
(7)如果执行复杂语句出错,会消耗更多资源
(8)数据表必须几乎和主服务器保持一致才行,否则可能会导致复制出错
ROW 模式
1、基于行的复制,row-based replication,RBR
2、MySQL 5.1.5 版本开始支持,不记录每条 SQL 语句的上下文信息,仅记录数据的修改情况
3、RBR 优点
(1)任何情况都可以被复制,最安全可靠,比如:不会出现某些特定情况下的存储过程、function、trigger 的调用和触发无法被正确复制的问题
(2)多数情况下,从服务器上的表如果有主键的话,复制效率提升
(3)复制以下几种语句时的行锁更少:INSERT …… SELECT、包含 AUTO_INCREMENT 字段的 INSERT、没有附带条件或没有修改很多记录的 UPDATE 或 DELETE 语句
(4)执行 INSERT,UPDATE,DELETE 语句时锁更少
(5)从服务器上采用多线程来执行复制成为可能
4、RBR 缺点
(1)binlog 占用更多空间
(2)复杂的回滚时,binlog 中会包含大量的数据
(3)主服务器上执行 UPDATE 语句时,所有发生变化的记录都会写到 binlog 中,而 SBR 只会写一次,这会导致频繁发生 binlog 的并发写问题
(4)无法从 binlog 中查看都复制的语句
MIXED 模式
1、混合模式复制,mixed-based replication,MBR
2、MySQL 5.1.8 版本开始支持,实际是 Statement 与 Row 结合
3、在 Mixed 模式下
(1)一般的语句修改使用 statment 格式保存 binlog
(2)statement 无法完成主从复制的操作(某些函数),则采用 row 格式保存 binlog
4、MySQL 会根据执行的每一条具体的 SQL 语句,来区分对待记录的日志形式,即在 Statement 和 Row 之间选择一种
从机配置文件
1、要求主从所有配置项都配置在 my.cnf 的 [mysqld] 组下,且都是小写字母
2、必选配置项
#[必须]从服务器唯一ID
server-id=指定ID
3、可选配置项
#[可选]启用中继日志
relay-log=mysql-relay
4、重启后台 mysql 服务,使配置生效
主机:建立账户并授权
1、MySQL 5.5、5.7 版本,在主机 MySQL 执行授权主从复制的命令
GRANT REPLICATION SLAVE ON *.* TO '设置从数据库账户名'@'从机器数据库IP' IDENTIFIED BY '设置该账户的密码';
2、MySQL 8 建立账户,并授权
CREATE USER '设置从数据库账户名'@'从机器数据库IP' IDENTIFIED BY '设置该账户的密码';
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';
#此语句必须执行,否则报错
ALTER USER '从数据库账户名'@'从机器数据库IP' IDENTIFIED WITH mysql_native_password BY '该账户密码';
flush privileges;
3、事项
(1)*.* 表示任何库下的任何表
(2)REPLICATION 表示主从复制
4、查询 Master 状态,并记录 File 和 Position 值,即 binlog 的文件名、位置(以数值方式表示)
SHOW MASTER STATUS;
从机:配置需要复制的主机
1、从机上复制主机的命令
CHANGE MASTER TO
MASTER_HOST='主机的IP地址',
MASTER_USER='主机用户名',
MASTER_PASSWORD='主机用户名的密码',
MASTER_LOG_FILE='mysql-bin.具体数字',
MASTER_LOG_POS=主机Position具体值;
2、启动 slave 同步
START SLAVE;
3、若从机已经同步,需要停止同步,否则报错
STOP SLAVE;
4、删除 SLAVE 数据库的 relaylog,并重新启用新的 relaylog 文件,然后重新执行 CHANGE MASTER TO……语句即可
RESET SLAVE;
5、查看同步状态
SHOW SLAVE STATUS \G;
(1)Slave_IO_Running、Slave_SQL_Running 都为 Yes,说明同步配置成功
(2)若同步配置失败,可能原因:网络不通、账户密码错误、防火墙、mysql 配置文件问题、连接服务器时语法错误、主服务器 mysql 权限
停止主从同步
STOP SLAVE;
1、如果停止从服务器复制功能,再使用需要重新配置主从,否则报错
2、重新配置主从,需要在从机上执行
STOP SLAVE;
#删除Master中所有binglog,并将日志索引文件清空,重新开始所有新的日志文件
RESET MASTER;
主从延迟 -> 主从同步中的数据不一致性问题
1、进行主从同步的内容是二进制日志,它是一个文件,在进行网络传输的过程中,就一定会存在主从延迟,造成用户在从库上读取的数据不是最新的数据
2、主从同步的要求
(1)读库和写库的数据,最终必须一致
(2)写数据必须写到写库
(3)一般情况下,读数据必须到读库
2、例:主库 A 执行完成一个事务,写入 binlog,记为 T1 时刻;之后 binlog 传给从库 B,从库 B 接收完 binlog 时刻记为 T2;从库 B 执行完成这个事务,记为 T3 时刻
(1)在网络正常时,日志从主库传给从库所需的时间很短,即 T2 - T1 值是非常小
(2)即网络正常情况下,主从延迟的主要来源,从库接收完 binlog 和执行完这个事务之间的时间差,即 T3 - T2
3、最直接表现:从库消费中继日志(relaylog)的速度,比主库生产 binlog 的速度要慢
主从延迟
1、原因
(1)从库的机器性能比主库要差
(2)从库的压力大
(3)大事务的执行
2、减少主从延迟
(1)降低多线程大事务并发的概率,优化业务逻辑
(2)优化 SQL,避免慢 SQL,减少批量操作,建议写脚本以 update-sleep 形式完成
(3)提高从库机器的配置,避免主库写 binlog 和从库读 binlog 效率差
(4)尽量采用短的链路,即主库和从库服务器的距离尽量要短,提升端口带宽,减少 binlog 传输的网络延时
(5)实时性要求的业务读,强制走主库,从库只做灾备,备份
解决主从一致性问题
1、读写分离情况下,就是解决主从之间数据复制方式的问题
2、划分方式:数据一致性从弱到强;并发性从强到弱,有 3 种复制方式
3、方法一:异步复制
(1)异步模式就是客户端提交 COMMIT 之后,不需要等从库返回任何结果,而是直接将结果返回给客户端
(2)优点:不会影响主库写的效率
(3)可能会存在主库宕机,而 binlog 还没有同步到从库的情况,即主库和从库数据不一致
(4)解决:此时选择一个从库作为新主库,则新主库则可能缺少原主服务器中已提交的事务
(5)这种复制模式下的数据一致性是最弱的
4、方法二:半同步复制
(1)MySQL 5.5 版本之后,开始支持半同步复制方式
(2)原理:在客户端提交 COMMIT 之后,不直接将结果返回给客户端,而是等待至少有一个从库接收到 binlog,并且写入到中继日志中,再返回给客户端
(3)优点:提高数据的一致性
(4)缺点:相比异步复制,至少多增加一个网络连接的延迟,降低主库写的效率、并发能力
(5)在 MySQL 5.7 版本中,还增加一个 rpl_semi_sync_master_wait_for_slave_count 参数,可对应答的从库数量进行设置,默认为 1,即只要有 1 个从库进行响应,就可以返回给客户端,若该参数调大,可以提升数据一致性的强度,但会增加主库等待从库响应的时间
5、方法三:组复制
(1)异步复制和半同步复制都无法最终保证数据的一致性问题,MGR 弥补两种复制模式的不足
(2)组复制技术:MGR,MySQL Group Replication
(3)MySQL 5.7.17 版本支持,基于 Paxos 协议的状态机复制
(4)工作原理:将多个节点共同组成一个复制组
(5)在执行读写(RW)事务时,需要通过一致性协议层(Consensus 层)同意,即读写事务想要进行提交,必须要经过组里大多数 Node 节点的同意,大多数指同意的节点数量需要大于 N / 2 + 1
(6)针对只读(RO)事务,则不需要经过组内同意,直接 COMMIT 即可
(7)Paxos 算法作为:分布式一致性算法被广泛应用
第三方的中间件
1、Cobar:阿里开发,已经停止维护
2、Mycat:开源社区在 Cobar 基础上进行二次开发
3、OneProxy:基于 MySQL 官方 proxy 思想,利用 C 语言进行开发,商业收费,专注在性能和稳定性
4、kingshard:GO 语言开发,需要发展完善
5、Vitess:Youtube 使用,架构复杂,不支持 MySQL 原生协议,使用需要大量改造成本
6、Atlas:360 基于 mysql proxy 改写,功能还需完善,高并发下不稳定
7、MaxScale:根据 mariadb(MySQL 原作者维护的一个版本) 研发的中间件
8、MySQLRoute:是 MySQL 官方 Oracle 公司发布的中间件
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战