分析MySQL复制以及调优原理和方法-6ren

分析MySQL复制以及调优原理和方法

转载作者：qq735679552 更新时间：2022-09-29 22:32:09

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章分析MySQL复制以及调优原理和方法由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

一. 简介

。

MySQL自带复制方案，带来好处有:

数据备份.

负载均衡.

分布式数据.

概念介绍:

主机（master）：被复制的数据库.

从机（slave）：复制主机数据的数据库.

复制步骤： (1). master记录更改的明细，存入到二进制日志（binary log）。 (2). master发送同步消息给slave。 (3). slave收到消息后，将master的二进制日志复制到本地的中继日志（relay log）。 (4). slave重现中继日志中的消息，从而改变数据库的数据.

下面放一张经典的图片来说明这一过程:

分析MySQL复制以及调优原理和方法

二. 实现复制

。

实现复制有以下步骤:

1.设置MySQL主库的二进制日志以及server-id 。

MySQL配置文件一般存放在/etc/my.cnf 。

 
    ? 
   
         # 在[mysqld]下面添加配置选项 
        
         [mysqld] 
        
         server-id=1 
        
         log-bin=mysql-bin.log

server-id是数据库在整个数据库集群中的唯一标示，必须保持唯一。重启MySQL.

注：如果MySQL配置文件中已经配置过此文件，则可以跳过此步.

2.新建复制账号。

在主库里面新建用于从库复制主库数据的账号，并授予复制权限.

 
    ? 
   
         mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO user_name@'host' IDENTIFIED BY 'password';

3.设置MySQL主库server-id 。

和第二步配置一样，要注意的地方有两点:

如果不需要从库作为别的从库的主库的话，则不需要配置二进制日志。很多时候复制并不需要复制主库的全部数据库（特别是mysql的信息配置库）。因此可以配置replicate_do_db来指定复制的数据库 4.从库初始化主库的数据。

如果数据量不算大的情况下，可以使用mysqldump工具导出主库数据，然后导入到从库里面.

 
    ? 
   
         mysqldump --single-transaction --triggers --master-data databasename > data.sql

如果数据量大的情况下应该使用Xtrabackup去进行数据库的导出，此处不做介绍。可能会有同学问，为什么不直接使用二进制日志进行初始化呢?

如果我们主库运行了比较长的一段时间，并不太适合使用从库根据二进制日志进行复制数据，直接使用二进制日志去初始化从库会比较耗费时间和性能。更多的情况下，主库的二进制日志的配置项没有打开，因此也就不存在以前操作的二进制日志。 5.开启复制。

从库执行下面命令。

 
    ? 
   
         mysql> CHANGE MASTER TO MASTER_HOST='host', 
        
         -> MASTER_USER='user', 
        
         -> MASTER_PASSWORD='password', 
        
         -> MASTER_LOG_FILE='mysql-bin.000001', 
        
         -> MASTER_LOG_POS=0;

注意最后的两个命令：MASTER_LOG_FILE和MASTER_LOG_POS，表示从库的从哪个二进制文件开始读取，偏移量从那里开始，这两个参数可以从我们导入的SQL里面找到.

分析MySQL复制以及调优原理和方法

开启复制。

 
    ? 
   
         start slave;

这时候就完成了复制，在主库更新一个数据或者新增数据在从库都可以查询到结果.

分析MySQL复制以及调优原理和方法

在主库上也可以查询的到复制线程的状态.

分析MySQL复制以及调优原理和方法

三. 复制的日志格式

。

MySQL复制的日志格式有三种，根据主库存放数据的方式不同有以下三种:

。

复制方式	特点	优点	缺点
row	基于行的格式复制，记录需要修改的每行的数据信息。如果一个SQL修改了2w行的数据，那么就会记录2w行的日志格式	保证了数据的强一致性，且由于记录的是执行后的结果，在从库上执行还原也会比较快	日志记录数量很多，主从之间的传输需要更多的时间。
statement	基于段的日志格式复制，也就是记录下更改的SQL记录，而不是更改的行的记录。	日志记录量最小。	对于一些输出结果不确定的函数，在从库上执行一遍很可能会出现问题，如uuid，从库根据日志还原主库数据的时候需要执行一遍SQL，时间相对较慢。
mixed	混合上面两种日志格式记录记录日志，至于什么时候使用哪种日志方式由MySQL本身决定。	可以平衡上面两种日志格式的优缺点。

mysql5.7以前默认使用statement格式.

设置方式，可以在配置文件设置（首选）:

 
    ? 
   
         binlog_format=ROW

或临时设置全局变量（当前mysql连接有效）:

 
    ? 
   
         查看日志格式 
        
         mysql > show variables like 'binlog_format'; 
        
         设置日志格式 
        
         mysql > set binlog_format='row';

由于两个主从服务器一般都会放在同一个机房里面，两者之间同步的速度会会比较快，为保证强一致性，应该首选行的日志格式记录（row），保证传输素速度可以选择混合方式（mixed）。而行的日志格式有下面三种记录方式:

。

记录方式	特点
minimal	只记录被修改列的数据
full	记录被修改的行的全部列的数据
noblob	特点同上，只是如果没有修改blob和text类型的列的情况下，不会记录这些列的数据（也就是大数据列）

。

mysql默认是full，最好修改成minimal.

 
    ? 
   
         binlog_row_image=minimal

四. 主从复制延迟

。

由于主库和从库之间不在同一个主机上，数据同步之间不可以避免地具有延迟，解决的方法有添加缓存，业务层的跳转等待，如果非得从数据库层面去减缓延迟问题，可以从复制时候的三大步骤（主库产生日志，主从传输日志，从库还原日志内容）入手:

1.主库写入到日志的速度。

控制主库的事务大小，分割大事务为多个小事务.

如插入20w的数据，改成插入多次5000行（可以利用分页的思路）。

2.二进制日志在主从之间传输时间。

主从之间尽量在同一个机房或地域.

日志格式改用MIXED，且设置行的日志格式未minimal，原理详见上面的日志格式介绍.

3.减少从库还原日志的时间。

在MySQL5.7版本后可以利用逻辑时钟方式分配SQL多线程.

设置逻辑时钟：slave_parallel_type=‘logical_clock',

设置复制线程个数：slave_parallel_workers=4,

五. 需要注意的地方

。

重启MySQL最好切换未MySQL用户再进行操作，不然文件启动后会有权限问题。搭建好MySQL的环境后就设置好配置里的log-bin选项，这样以后如果数据库需要从库的复制，就不需要重启数据库，打断业务的进行。需要打开主库的防火墙的对应的mysql端口。由于从库同步主库的方式，监听主库发送的信息，而不是轮询，因此如果出现通信出现了故障，重新连接后如果主库没有进行数据更改的操作，从库不会同步数据，因此可以通过插入空事务的方式同步数据.

以上就是小编本次整理的全部内容，感谢你对我的支持.

原文链接：https://www.cnblogs.com/s-b-b/p/8359355.html 。

最后此篇关于分析MySQL复制以及调优原理和方法的文章就讲到这里了,如果你想了解更多关于分析MySQL复制以及调优原理和方法的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！。

文章推荐： Linux中安装MySql 5.7.21的详细操作步骤

文章推荐：建网站一年经验分享:做网站前的准备和推广经验

文章推荐：安全联盟的认证提示和风险提示对百度排名的影响

文章推荐： mysql按照天统计报表当天没有数据填0的实现代码

Docker通信全视角：原理、实践与技术洞察
本文全面深入地探讨了Docker容器通信技术，从基础概念、网络模型、核心组件到实战应用。详细介绍了不同网络模式及其实现，提供了容器通信的技术细节和实用案例，旨在为专业从业者提供深入的技术洞见和实
Dubbo快速上手 -- 带你了解Dubbo使用、原理
📒博客首页：崇尚学技术的科班人 🍣今天给大家带来的文章是《Dubbo快速上手 -- 带你了解Dubbo使用、原理》🍣 🍣希望各位小伙伴们能够耐心的读完这篇文章🍣 🙏博主也在学习阶段，如若发
npm install 原理
一、写在前面我们经常使用npm install ，但是你是否思考过它内部的原理是什么？ 1、执行npm install 它背后帮助我们完成了什么操作？ 2、我们会发现还有一个成为package-lo
一篇带你了解 Base64 原理
Base64 Base64 是什么?是将字节流转换成可打印字符、将可打印字符转换为字节流的一种算法。Base64 使用 64 个可打印字符来表示转换后的数据。准确的来说，Base64 不算
详细解读tornado协程(coroutine)原理
目录协程定义生成器和yield语义 Future类 IOLoop类 coroutine函数装饰器总结 tornado中的
golang常用手册之切片(Slice)原理
切片，这是一个在go语言中引入的新的理念。它有一些特征如下：对数组抽象数组长度不固定可追加元素切片容量可增大容量大小成片增加我们先把上面的理念整理在这
100 行代码透彻解析 RPC 原理
文章来源：https://sourl.cn/HpZHvy 引言本文主要论述的是“RPC 实现原理”，那么首先明确一个问题什么是 RPC 呢？RPC 是 Remote Procedure Call
原理：WebMvcConfigurer 与 WebMvcConfigurationSupport避坑指南
源码地址（包含所有与springmvc相关的，静态文件路径设置，request请求入参接受，返回值处理converter设置等等）： spring-framework/WebMvcConfigurat
java - 关于简单java类中的依赖注入(inject)原理
请通过简单的java类向我展示一个依赖注入(inject)原理的小例子虽然我已经了解了spring，但是如果我需要用简单的java类术语来解释它，那么你能通过一个简单的例子向我展示一下吗？提前致谢。
简单聊聊内网穿透（背景-概念-原理-工具对比）
1、背景我们平常使用手机和电脑上网，需要访问公网上的网络资源，如逛淘宝和刷视频，那么手机和电脑是怎么知道去哪里去拿到这个网络资源来下载到本地的呢？就比如我去食堂拿吃的，我需要
来，今天飞哥带你理解 Iptables 原理！
大家好，我是飞哥! 现在 iptables 这个工具的应用似乎是越来越广了。不仅仅是在传统的防火墙、NAT 等功能出现，在今天流行的的 Docker、Kubernets、Istio 项目中也经
CesiumJS 2022^ 原理[5] - 着色器相关的封装设计
本篇涉及到的所有接口在公开文档中均无，需要下载 GitHub 上的源码，自己创建私有类的文档。 npm run generateDocumentation -- --private yarn gene
solid-principles - SOLID 原理，以及类内的硬编码配置
我最近在很多代码中注意到人们将硬编码的配置(如端口号等)值放在类/方法的深处，使其难以找到，也无法配置。这是否违反了 SOLID 原则？如果不是，我是否可以向我的团队成员引用另一个“原则”来说明为什
mvvm - 命令和 MVVM 原理 - RelayCommands
我是 C#、WPF 和 MVVM 模式的新手。很抱歉这篇很长的帖子，我试图设定我所有的理解点(或不理解点)。在研究了很多关于 WPF 提供的命令机制和 MVVM 模式的文本之后，我在弄清楚如何使用这
javascript - Angularjs $http.post 原理
可比较的 jQuery 函数 $.post("/example/handler", {foo: 1, bar: 2}); 将创建一个带有 post 参数 foo=1&bar=2 的请求。鉴于 $htt
Django "lazy query execution"原理
如果Django不使用“延迟查询执行”原则，主要问题是什么？ q = Entry.objects.filter(headline__startswith="What") q = q.filter(
c# - .NET 中的 BODMAS 原理
我今天发现.NET框架在做计算时遵循BODMAS操作顺序。即计算按以下顺序进行: 括号订单部门乘法添加减法但是我四处搜索并找不到任何文档确认 .NET 绝对遵循此原则，是否有此类文档？如
c++ - 库方便使用 "design by contract"原理
已结束。此问题不符合 Stack Overflow guidelines .它目前不接受答案。我们不允许提出有关书籍、工具、软件库等方面的建议的问题。您可以编辑问题，以便用事实和引用来回答它。关闭
CesiumJS 2022^ 原理[4] - 最复杂的地球皮肤影像与地形的渲染与下载过程
API 回顾在创建 Viewer 时可以直接指定影像供给器（ImageryProvider），官方提供了一个非常简单的例子，即离屏例子（搜 offline）： new Cesium.Viewer(
compiler-construction - self 学习编译器的最佳方法:原理，技术和工具
As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be

qq735679552

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城