Mysql之主从同步

1.BinLog同步机制

Mysql要去保证高可用，或者去分担请求压力，一般会去主从部署，读写分离。写库只负责写，而读库更多的去承担读的请求，从库不写数据，数据从主库同步，那么到底是怎么同步的呢？

同步，一定十把数据同步给从，它得有个载体，比如redis里面有rdb文件，在Mysql里面充当这个载体得就是BinLog.

BinLog又称二进制文件，属于Mysql Server层去记录得，所以，不管是什么存储引擎进行的数据存储，如果binLog开启，都会进行记录。

二进制日志包含描述数据库更改 ( 如表创建操作或表数据更改 ) 的 “ 事件 ” 。它还包含可能发生更改的语句的事件( 例如，不匹配任何行的 DELETE) ，二进制日志还包含关于每条语句花费更新数据的时间的信息。

只有完整的事件或事务才会被记录或回读。

但是，BinLog不用在非修改语句，比如SELECT、SHOW，如果需要查询这些日志，可以参考通用日志: MySQL :: MySQL 8.0 Reference Manual :: 7.4.3 The General Query Log

1.1 BinLog用途

a.主从复制

b.数据恢复

redoLog 跟 BinLog 都是去保证数据一致性的，但是 RedoLog 是 innoDB 层面，并且是覆盖写的，所以不能保证全数据备份。
还有因为备份的方式不一样，一个是二进制日志， server 层去做的，一种是物理日志，InnoDB 做的，所以 BinLog 也替代不了 RedoLog 。

 1.2 BinLog配置

BinLog会有很多相关的配置，这些配置决定了要不要开启、以什么方式存储、存储在哪里等等!

show variables like '%log_bin%'; -- log_bin相关配置
log_bin -- 默认on 开启 可以对binlog进行关闭
log_bin_basename -- bin文件前缀 默认
/var/lib/mysql/mysql-bin
log_bin_index -- bin文件索引 /var/lib/mysql/mysql-bin.index

binlog_cache_size -- binlog日志 事务缓存大小
binlog_encryption -- 内容是否加密 我们的内容为了安全性可能需要加密
binlog_format -- binlog格式
binlog_expire_logs_seconds -- 多久后binlog删除 默认2592000s 也就是30天

1.3 BinLog格式

STATEMENT

基于语句记录，记录的是语句，后续去执行binLog的执行语句

比如:

update table set time=now() where id=1;

binlog就会记录这条语句，然后拿这条语句去执行。

问题: 有些场景，比如获取当前系统时间就会导致根据Binlog同步、恢复的数据跟之前的数据不一致。  因此，又有了Row的格式。

ROW

基于行格式记录，binlog记录的是单个表行是如何更改的

以刚才那个例子为例，会直接记录1这条数据改成了什么时间，并且能确定是哪条数据

update table set time=1675778373 where @1=1 and @2=.. and @3=...;

但是row格式的缺点是更加复杂，占用的空间比较大，恢复起来也相对来讲比较慢。

所以又有了一个折中的混合模式。混合模式就是去看下STATEMENT格式下会不会导致一致性问题，如果会，就用row,如果不会，就用STATEMENT

MIXED

混合模式，默认是语句，在下列场景下会切换成行模式

MySQL :: MySQL 8.0 Reference Manual :: 7.4.4.3 Mixed Binary Logging Format

1.4 查询BinLog

show master status; // 当前在写哪个binlog

知道了正在写哪个BinLog后，我们想去看下之前binLog的日志内容

[root@localhost mysql]# mysqlbinlog -vv --base64-output=decode-rows --database=zsc_edu --start-datetime='2023-02-05 00:00:00' mysql-bin.000003

指定编码格式，库名，开始时间和要查询的binlog文件名

1.5 BinLog同步机制 

binLog记录完整的日志，在开启事务后，事务语句中的二进制日志先放入内存缓存，这个内存缓存就是存储我事务没有提交的数据。具体缓存大小由binlog_cache_size设置(如果超过这个值，就会暂存到磁盘)。

show variables like '%binlog_cache_size%'; -- 事务期间用于保存二进制日志更改的内存缓冲区的大小。

commit的时候，会同步到文件系统缓存。那么为例性能与数据一致性方面的考虑，也会有不同的同步策略来让文件系统缓存同步到磁盘

mysql> show variables like '%sync_binlog%' ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 1 |
+---------------+-------+
1 row in set ( 0.00 sec)

sync_binlog配置选项

 1.sync_binlog=0，不同步刷新到磁盘，交给操作系统去操作，断电或者操作 系统异常，可能导致数据丢失

2.sync_binlog=1 ，能保证数据的一致性，每次提交都必须同步到磁盘，但是对性能有影响
3. sync_binlog=N,N 默认是 1 ，最大 4294967295 ，代表我达到 N 条 binLog 后， 再同步到磁盘，能够灵活的来设置数据的一致性与性能之间的平衡

官网给的建议是,为了数据的一致性，来保证持久性跟一致性请设置 

sync_binlog= 1
innodb_flush_log_at_trx_commit= 1

 2. BinLog、RedoLog、UndoLog

2.1 BinLog跟RedoLog的二阶段机制

binLog是Mysql服务所提供的日志，并且是二进制日志，也就是说我不管你是什么存储引擎，只要你开启了，我都会记录。

那么既然有了binLog，为什么还要有RedoLog呢?

1. 首先 BinLog 只记录提交的完整的事务日志，而 RedoLog 一直在执行同步。
2.BinLog 是追加的二进制日志，不知道里面哪些已经持久化哪些没有持久化，而RedoLog 已经持久化的记录会从 RedoLog 删除。
3. 记录方式不一样， BinLog 是二进制日志，而 RedoLog 是物理日志，所以恢复的方式也会不一样。

当然。RedoLog也替代不了BinLog,因为RedoLog是InnoDB独有的，用其他存储引擎的时候，如果没有BinLog，同步跟恢复都没法完成。

所以，虽然BinLog跟RedoLog虽然作用稍微有些重合，但是缺一不可。

Mysql里面就采用了二阶段提交的形式，来保证这两个事务都是成功的，也尽可能保证我们的表数据跟日志数据一致(如果不一致，恢复的数据，以及主从同步的数据都会不一样)

所谓二阶段提交，就是我们的提交分2次进行，目的: 保证数据的一致性。

 Mysql里面保证BinLog跟RedoLog的一致性，就用了二阶段提交方案，如图:

1.在更新数据的时候，还没有提交事务的时候，提交的RedoLog为prepare 状态

2. 当 commit 事务后，会将 BinLog Cache 缓存的 bin 日志，同步到磁盘。
3. 将 RedoLog 状态更改成 Commit 状态，整个流程结束。

 那么如果发生异常，怎么保证数据的一致性

1. 如果操作①失败，数据回滚， RedoLog 跟 binlog 都不会有
2. 如果②失败，有 RedoLog 的 prepare 状态，但是没有 binlog 落盘，数据回滚，操作失败
3. 当③失败，这个时候，有 RedoLog 并且有 binlog ，数据都会有，并且数据是一致的，成功。

 2.2 三大日志的区别

两个事务日志 

a. RedoLog 重做日志，覆盖写的方式，保证内存缓存与数据库表中的一致性

b. undoLog  回滚日志，在事务里面，如果发生异常的，记录的是回滚的值， 在mvcc中也有应用

c. Binlog MysqlServer层的，二进制日志，主要两个作用 主从同步，数据恢复，binlog找到之前的数据。

3. 主从同步机制

3.1 主从安装流程

a. 创建负责同步数据给slave的用户

mysql> CREATE USER 'repl' @'%.mysql.slave' IDENTIFIED BY 'password' ;
mysql> GRANT REPLICATION SLAVE ON *.* TO   'repl' @'%.mysql.slave' ;
mysql> flush privileges ; -- 刷新权限

 2. 查看master信息

show master status \G -- 查看 master 信息

 3. 配置从库节点 如果不希望从库自己写数据，可以更改为只读

SHOW VARIABLES LIKE '%read_only%' ;
SET GLOBAL super_read_only= 1 ; -- super 账号也只读
SET GLOBAL read_only= 1 ; -- 只读

 4. 创建主从关系

mysql> CHANGE MASTER TO
-> MASTER_HOST= 'source_host_name' ,
-> MASTER_USER= 'replication_user_name' ,
-> MASTER_PASSWORD= 'replication_password' ,
-> MASTER_LOG_FILE= 'recorded_log_file_name' ,
-> MASTER_LOG_POS=recorded_log_position; -- 我要
从 binlog 的哪个位置开始同步
这个为8.0.23 的版本
Or from MySQL 8.0.23 :
mysql> CHANGE REPLICATION SOURCE TO
-> SOURCE_HOST= 'source_host_name' ,
-> SOURCE_USER= 'replication_user_name' ,
-> SOURCE_PASSWORD= 'replication_password' ,
-> SOURCE_LOG_FILE= 'recorded_log_file_name' ,
-> SOURCE_LOG_POS=recorded_log_position; --
我要从 binlog 的哪个位置开始同步

 5. 开启主从同步

mysql> start replica; -- 开启主从同步

 6.查看从库信息 show replica status

mysql> show replica status \G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.8.127
Source_User: replica
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin .000004
Read_Source_Log_Pos: 9285
Relay_Log_File: localhost-relay-bin .000003
Relay_Log_Pos: 1334
Relay_Source_Log_File: mysql-bin .000004
Replica_IO_Running: Yes //IO线程
Replica_SQL_Running: Yes //sql执行线程
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0 //跳过的事务数，还有这个数值的事务不会执行
Exec_Source_Log_Pos: 9285
Relay_Log_Space: 2002
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0 //主从延迟时间
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 129
Source_UUID: 978861 d8- 232 d- 11 ed-bc6f- 000 c2928fa99
Source_Info_File: mysql .slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all
relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set ( 0.01 sec)

Replica_IO_Running 线程以及 Replica_SQL_Running线程都为yes代表跟主建立关系，并且能进行主从同步。

如果从库同步报错，数据又不是特别重要，可以跳过事务

set GLOBAL SQL_replica_SKIP_COUNTER=100000; 跳过多少事务，设置后，后续的100000个事务讲不会执行
我们可以在主从中查看 Skip_Counter 还有多少事务是跳过的 跳过的事务从不会去执行

3.2 建立主从的必要条件

我们知道主从是怎么同步数据的了，那我怎么给2个服务建立主从关系。

我们来看两个必要的条件

a. 确保有唯一的server_id，server_id不能重复，如果实例ID都一样了，那就没法区分实例的唯一性了。

从文件中配置或SQL配置

-- vim /etc/my.cnf
server-id=128

或者

SET GLOBAL server_id = 128; -- 更改server_id

查询一下

mysql> SHOW GLOBAL VARIABLES like '%server_id%' ;
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| server_id | 128 |
| server_id_bits | 32 |
+----------------+-------+
2 rows in set ( 0.01 sec)

b.  数据源必须开启bin_log

因为主从复制是基于binLog去做的，所以如果想要把数据源的数据同步给副本，那么必须开启binLog。

但是副本不需要开启BinLog，除非这个副本想成为另外一个实例的数据源，也就是A->B->C的架构，A同步给B。B同步给C

同时，如果副本想成为别的实例数据源，还必须开启

SHOW GLOBAL VARIABLES like '%log_replica_updates%'; -- 版本8.0.6之后
SHOW GLOBAL VARIABLES like '%log_slave_updates%'; -- 版本8.0.6之前

该配置代表，从库能否把从主拿到的binlog事务写入自己的binlog

3.3 主从复制

我们先去看下主从同步用到的线程。官网MySQL :: MySQL 8.0 Reference Manual :: 19.2.3 Replication Threads

官网上提供了3种线程:其中replica 2个  master 1个

slave

a. I/O receiver thread

IO 接收线程，负责从 master 里面获取 binLog 日志，并且将日志加载到replica本地的文件，这个文件也叫作 replica's relay log ，俗称中继日志。
可以在 SHOW SLAVE STATUS 指令中查看 Replica_IO_Running

b.SQL applier thread

 slave接收到binLog日志后，得去执行到replica数据库。就是依靠SQL applier thread线程去执行

可以在 SHOW SLAVE STATUS 指令中查看 Replica_SQL_Running

master

 master收到I/O receiver thread线程发起的同步指令后，master会创建一个Binary log dump thread线程，将binLog内容发送给slave。

可以通过 SHOW PROCESSLIST 查看线程状态

整体的主从复制流程图如下:

 

 3.4 同步方式

我们知道了主从数据是怎么同步的，是由异步线程去进行同步的，那么假如我主成功了，但是主从因为网络断开等异常没有进行同步，不就数据不一致了么?

所以为了主从的数据一致性，同步方式分为异步同步、半同步

MySQL :: MySQL 8.0 Reference Manual :: 20.1.1.1 Source to Replica Replication

异步同步

 利用额外的线程去dump我们的binLog然后传送给slave，并且我们master的用户线程是不会等待同步结果的。所以，默认的同步方式是异步同步。

性能比较高，但是数据一致性低(如果主挂了，没有同步到从，那么这个从就不会有最新的数据),因为会有延迟。

半同步

 由于异步同步会存在一定的数据丢失，并且会有延迟，所以Mysql的主从复制有一个半同步的概念，所谓半同步，就是我的主必须等待数据至少有一个副本(具体数量可以进行配置)，接收并记录了，才会运行提交事务。

半同步不是默认的，如果要开启半同步，必须要安装半同步的插件

插件安装: MySQL :: MySQL 8.0 Reference Manual :: 7.6.1 Installing and Uninstalling Plugins

半同步插件: MySQL :: MySQL 8.0 Reference Manual :: 19.4.10.1 Installing Semisynchronous Replication

3.5 主从数据一致性不同步、或者同步慢的解决思路

网络延迟: 检查网络、优化网络能够让网络能够支撑数据量的传输。可以采用半同步的方式，确保数据不会丢失，或者最少有一个从能同步到数据，但是会牺牲一定的性能。

主库负载很高:  当主库有大量的操作的时候，有大量需要同步给从，也可能会延迟。 可以做负载、缓存减少主的压力
大事务导致:  binlog 太大太多，从库需要执行的时间越久，也会导致可能会延迟，尽量减 少大事务。
从库的机器跟不上: 从库的 cpu 、内存要跟主库能够匹配。不然从的处理性能会跟主不一致

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/647570.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

ant design pro 6.0列表渲实践demo

ant design pro 6.0列表渲实践demo

ant design pro 用户列表渲实践 用户页面： src\pages\Admin\User\index.tsx import { PlusOutlined } from ant-design/icons; import type { ActionType, ProColumns, ProDescriptionsItemProps } from ant-design/pro-components; import {PageContainer,ProDe…
阅读更多...
Pycharm2024搭建QT6开发环境

Pycharm2024搭建QT6开发环境

创建pyqt6虚拟环境 首先，创建一个qt6的虚拟环境： conda create --name pyqt6 python3.11.7激活环境： conda activate pyqt6安装pyqt6 安装pyqt6： pip install pyqt6创建代码目录 创建目录： 使用pycharm打开这个…
阅读更多...
springboot + Vue前后端项目（第十一记）

springboot + Vue前后端项目（第十一记）

项目实战第十一记 1.写在前面2. 文件上传和下载后端2.1 数据库编写2.2 工具类CodeGenerator生成代码2.2.1 FileController2.2.2 application.yml2.2.3 拦截器InterceptorConfig 放行 3 文件上传和下载前端3.1 File.vue页面编写3.2 路由配置3.3 Aside.vue 最终效果图总结写在最后…
阅读更多...
分享活动规划

分享活动规划

前两天去参加菁英学院的一些辅导，是关于苏州久富农业机械的发展，看了他们企业的故事，我觉得我们农机很有前景和发展空间，我希望重新经过一次分享活动来分享我的感触，希望能够再次把我学到的内容传输到其他班的同学们 请…
阅读更多...
主干网络篇 | YOLOv8更换主干网络之MobileNeXt | 新一代移动端模型MobileNeXt来了！

主干网络篇 | YOLOv8更换主干网络之MobileNeXt | 新一代移动端模型MobileNeXt来了！

前言：Hello大家好，我是小哥谈。MobileNeXt是由微软研究院提出的一种高效的卷积神经网络结构，它在保持模型轻量级的同时，能够获得较高的性能。MobileNeXt采用了一种称为Inverted Residuals with Linear Bottlenecks（IRL）的结构，通过深度可分离卷积和快捷连接来减少模型的…
阅读更多...
洗地机十大品牌排名：2024十大值得入手的洗地机盘点

洗地机十大品牌排名：2024十大值得入手的洗地机盘点

随着生活水平的提高，智能清洁家电已经成为日常生活中的必需品。洗地机之所以在家庭清洁中大受欢迎，主要是因为它的多功能特性。传统的清洁方式通常需要扫帚、拖把和吸尘器分别进行操作，而洗地机将这些功能集成在一个设备中，使清洁…
阅读更多...
谷歌Google广告投放优势和注意事项！

谷歌Google广告投放优势和注意事项！

谷歌Google作为全球最大的搜索引擎，谷歌不仅拥有庞大的用户基础，还提供了高度精准的广告投放平台，让广告主能够高效触达目标受众，实现品牌曝光、流量增长乃至销售转化的多重目标，云衔科技以专业服务助力您谷歌Google广…
阅读更多...
【mysql】in和exists的区别，not in、not exists、left join的相互转换

【mysql】in和exists的区别，not in、not exists、left join的相互转换

【mysql】in和exists的区别，not in、not exists、left join的相互转换 【一】in介绍【1】in中数据量的限制【2】null值不参与in或not in，也就是说in and not in 并不是全量值，排除了null值【3】in的执行逻辑 【二】exists介绍【1】exists no…
阅读更多...
-bash: locate: 未找到命令（解决办法）

-bash: locate: 未找到命令（解决办法）

-bash: locate: 未找到命令的解决办法 一、解决办法二、什么是locate三 、locate命令的具体用法 一、解决办法 CentOS7默认没有安装locate命令，安装方式如下： 执行以下命令进行安装： yum install mlocate用 updatedb 指令创建 或更新locate …
阅读更多...
Value-Based Reinforcement Learning（2）

Value-Based Reinforcement Learning（2）

Temporal Difference （TD） Learning 上节已经提到了如果我们有DQN，那么agent就知道每一步动作如何做了，那么DQN如何训练那？这里面使用TD算法。 简略分析： 是的估计 是的估计 所以： Deep Re…
阅读更多...
【论文阅读】Prompt Fuzzing for Fuzz Driver Generation

【论文阅读】Prompt Fuzzing for Fuzz Driver Generation

文章目录 摘要一、介绍二、设计2.1、总览2.2、指导程序生成2.3、错误程序净化2.3.1、执行过程净化2.3.2、模糊净化2.3.3、覆盖净化 2.4、覆盖引导的突变2.4.1、功率调度2.4.2、变异策略 2.5、约束Fuzzer融合2.5.1、论据约束推理2.5.1、模糊驱动融合 三、评估3.1、与Hopper和OSS…
阅读更多...
【真实项目中收获的提升】- 使用MybatisPlus框架 save一条字段中有主键id并且和以前重复会报错吗

【真实项目中收获的提升】- 使用MybatisPlus框架 save一条字段中有主键id并且和以前重复会报错吗

问题描述： save一条数据中有主键id并且和以前重复会报错吗？ 实际场景： 复制一条数据，修改其中一个字段，想让主键自增直接插入进数据库。 解决方案： 会报错， 直接把插入对象的主键id置为空…
阅读更多...
基于Ruoyi-Cloud-Plus重构黑马项目-学成在线

基于Ruoyi-Cloud-Plus重构黑马项目-学成在线

文章目录 一、系统介绍二、系统架构图三、参考教程四、演示图例机构端运营端用户端开发端 一、系统介绍 毕设：基于主流微服务技术栈的在线教育系统的设计与实现 前端仓库：https://github.com/Xiamu-ssr/Dragon-Edu-Vue3 后端仓库：https://g…
阅读更多...
.lib .a .dll库互转

.lib .a .dll库互转

编译 mingw工具，gendef.exe转换dll为a，reimp转换lib为adlltool.exe --dllname python38.dll --def python38.def --output-lib libpython38.adlltool -k -d crypto.lib -l crypto.a 创作不易， 小小的支持一下吧！
阅读更多...
软件web化的趋势

软件web化的趋势

引言 在信息技术飞速发展的今天，软件Web化已成为一个不可忽视的趋势。所谓软件Web化，即将传统的桌面应用软件转变为基于Web的应用程序，使用户能够通过浏览器进行访问和使用。传统软件通常需要在用户的计算机上进行安装和运行，而W…
阅读更多...
一、机器学习概述

一、机器学习概述

1.课程目的 学习机器学习算法、提高算法性能的技巧 2.算法分类 有监督学习supervised learning、无监督学习unsupervised learning (1).有监督学习 在这种学习方式中，算法需要一个带有标签的训练数据集，这些标签通常是每个样本的真实输出或类别。 在有…
阅读更多...
C语言——小知识和小细节19

C语言——小知识和小细节19

一、奇数位与偶数位互换 1、题目介绍 实现一个宏，将一个整数的二进制补码的奇数位与偶数位互换。输出格式依旧是十进制整数。示例： 2、分析 既然想要交换奇数位和偶数位上的数字，那么我们就要先得到奇数位和偶数位上的数字，那么…
阅读更多...
零基础小白可以做抖音电商吗？小白做电商难度大吗？一篇全解！

零基础小白可以做抖音电商吗？小白做电商难度大吗？一篇全解！

大家好，我是电商花花 在直播电商的热度越来越多，更多普通的创业者都对抖音小店电商有了想法，因为很多普通 人都通过抖音小店开店卖货赚到了钱，让更多人对抖店电商产生了兴趣。 于是做抖音小店无货源，开店卖货赚钱成为…
阅读更多...
嵌入式全栈开发学习笔记---C语言笔试复习大全25（实现学生管理系统）

嵌入式全栈开发学习笔记---C语言笔试复习大全25（实现学生管理系统）

目录 实现学生管理系统 第一步：结构体声明 第二步：重命名结构体 第三步：限定可以存储的最大学生数目 第四步：定义结构体指针数组和定义一个整型变量存放当前的人数 第五步：设计欢迎界面 第六步：设计…
阅读更多...
Linux环境下TensorFlow安装教程

Linux环境下TensorFlow安装教程

TensorFlow是学习深度学习时常用的Python神经网络框 下面以Mask R-CNN 的环境配置为例： 首先进入官网：www.tensorflow.org TensorFlow安装的总界面： 新建anaconda虚拟环境： conda create -n envtf2 python3.8 （Pyth…
阅读更多...
最新文章

Copyright @ 2022~2023