生猛干货

带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试

官方文档

https://dev.mysql.com/doc/

如果英文不好的话，可以参考 searchdoc 翻译的中文版本

http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.htm

为什么要用主从方案

可以从以下的几个方面来考虑

如果主服务器出现问题，可以快速切换到从服务器提供的服务。（如果做了读写分离的话，主库挂了，起码还能提供查询服务。如果又做了高可用的话，从服务可以提升为主节点。）
可以在从服务器上执行查询操作，降低主服务器的访问压力
可以在从服务器上执行备份，以避免备份期间影响主服务器的服务

常见的主从方案

每个方案都有其适用的场景，没有通用的放之四海而皆准的方案。根据自己的业务选择合理的方案。

一主一从 M-S

最简单的主从方案

一主多从 M-S-S-S

一主一从和一主多从是最常见的主从架构，实施起来简有效，不仅可以实现HA，而且还能读写分离，进而提升集群的并发能力。

多主一从 M-M-M-S (5.7开始支持)

多主一从可以将多个mysql数据库备份到一台存储性能比较好的服务器上。

使用场景

举个例子 master节点是各个分公司的库， slave节点是集团公司的库。数据同步至集团slave节点。

集团公司要动态的掌握子公司的财务状况，集团每个月要进行汇总，这个时候各个子公司（master节点）把数据汇总到集团公司（slave节点），这样是不是方便来集团公司汇总查看了？

一主多级从

级联复制模式下，部分slave的数据同步不连接主节点，而是连接从节点。

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

双主 M-M

双主结构，相互读写复制。

双主复制，也就是互做主从复制，每个master既是master，又是另外一台服务器的slave。这样任何一方所做的变更，都会通过复制应用到另外一方的数据库中。

我们常用的M-M-M 高可用的方案就是基于这个来做的。

环形主从(复制)S-S-S-S

使用场景

比较特殊的一种场景

举个例子：各个分公司之间有些数据是共享的，任何一个分公司的数据的变化都要通知到其他分公司，这个时候使用这种闭环的方案比较合适。

MySQL安装

MySQL-CentOS7通过YUM安装MySQL5.7.29

主从复制的概念

MYSQL的主从复制主要是说数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。

MySQL 默认采用异步复制方式。

这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

主从复制的两种方案

目前来说，MYSQL提供了两种方式来实现主从复制

基于bin-log ,传统的方式，MySQL1.5版本就有了。

基于事务(GTID) global transaction identifieds ，MySQL5.7版本开始提供，其底层也是基于binlog .

我们这里重点来讨论基于bin log的主从复制

主从架构的架构图解析

MySQL主从复制涉及到三个线程

主节点（log dump thread)
其余两个(I/O thread, SQL thread)运行在从节点

主节点 binary log dump 线程

当从节点连接主节点时，主节点会创建一个binlog dump 线程，用于发送bin-log的内容。

从节点-I/O线程

当从节点上执行start slave命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log。I/O线程接收到主节点binlog dump 进程发来的更新之后，保存在本地relay-log中。

从节点-SQL线程

SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

MySQL 主从复制的过程

对于每一个主从连接，都需要三个线程来完成。

当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个binary log dump 进程，而每个从节点都有自己的I/O进程，SQL进程。

从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时I/O进程可以很快从主节点获取更新，尽管SQL进程还没有执行。如果在SQL进程执行之前从节点服务停止，至少I/O进程已经从主节点拉取到了最新的变更并且保存在本地relay日志中，当服务再次起来之后，就可以完成数据的同步。

要实施复制，首先必须打开Master 端的binary log（bin-log）功能，否则无法实现。因为整个复制过程实际上就是Slave 从Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。

从节点(Slave )上的I/O 进程连接主节点(Master)，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；

主节点(Master)接收到来自从节点(Slave )的I/O请求后，通过负责复制的I/O进程根据请求信息读取指定日志指定位置之后的日志信息，返回给从节点。返回信息中除了日志所包含的信息之外，还包括本次返回的信息的bin-log file 的以及bin-log position；从节点的I/O进程接收到内容后，将接收到的日志内容更新到本机的relay log中，并将读取到的binary log文件名和位置保存到master-info 文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log 的哪个位置开始往后的日志内容，请发给我”；

Slave 的 SQL线程检测到relay-log 中新增加了内容后，会将relay-log的内容解析成在主节点上实际执行过的操作，并在本数据库中执行。

MySQL 主从复制模式

MySQL 主从复制默认是异步的模式。MySQL增删改操作会全部记录在binary log中，当slave节点连接master时，会主动从master处获取最新的bin log文件

异步模式（mysql async-mode）

主节点不会主动push bin log到从节点，这样有可能导致failover的情况下，也许从节点没有即时地将最新的bin log同步到本地,但效率高。

半同步模式(mysql semi-sync)

主节点只需要接收到其中一台从节点的返回信息，就会commit；否则需要等待直到超时时间然后切换成异步模式再提交；这样做的目的可以使主从数据库的数据延迟缩小，可以提高数据安全性，确保了事务提交后，binlog至少传输到了一个从节点上，不能保证从节点将此事务更新到db中。性能上会有一定的降低，响应时间会变长.

半同步模式不是mysql内置的，从mysql 5.5开始集成，需要master 和slave 安装插件开启半同步模式。

全同步模式(mysql fully synchronized)

主节点和从节点全部执行了commit并确认才会向客户端返回成功

binlog记录格式

MySQL 主从复制有三种方式：

基于SQL语句的复制（statement-based replication，SBR），

基于行的复制（row-based replication，RBR)，

混合模式复制（mixed-based replication,MBR)。

对应的binlog文件的格式也有三种：STATEMENT,ROW,MIXED。

Statement-base Replication (SBR)就是记录sql语句在bin log中，Mysql 5.1.4

及之前的版本都是使用的这种复制格式。优点是只需要记录会修改数据的sql语句到binlog中，减少了binlog日质量，节约I/O，提高性能。缺点是在某些情况下，会导致主从节点中数据不一致（比如sleep(),now()等）。

Row-based Relication(RBR)是mysql master将SQL语句分解为基于Row更改的语句并记录在bin log中，也就是只记录哪条数据被修改了，修改成什么样。优点是不会出现某些特定情况下的存储过程、或者函数、或者trigger的调用或者触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是修改table的时候会让日志暴增,同时增加bin log同步时间。也不能通过bin log解析获取执行过的sql语句，只能看到发生的data变更。

Mixed-format Replication(MBR)，MySQL NDB cluster 7.3 和7.4 使用的MBR。是以上两种模式的混合，对于一般的复制使用STATEMENT模式保存到binlog，对于STATEMENT模式无法复制的操作则使用ROW模式来保存，MySQL会根据执行的SQL语句选择日志保存方式。

MySQL 5.7.6之前默认为STATEMENT模式。MySQL 5.7.7之后默认为ROW模式. 这个参数主要影响主从复制。

GTID复制模式

GTID底层也是基于bin log 。

1、全局事物标识：global transaction identifieds。

2、GTID事物是全局唯一性的，且一个事务对应一个GTID。

3、一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。

4、GTID用来代替classic的复制方法，不在使用binlog+pos开启复制。而是使用master_auto_postion=1的方式自动匹配GTID断点进行复制。

5、MySQL-5.6.5开始支持的，MySQL-5.6.10后开始完善。

6、在传统的slave端，binlog是不用开启的，但是在GTID中，slave端的binlog是必须开启的，目的是记录执行过的GTID（强制）

基于GTID复制实现的工作原理

主节点更新数据时，会在事务前产生GTID，一起记录到binlog日志中。

从节点的I/O线程将变更的bin log，写入到本地的relay log中。

SQL线程从relay log中获取GTID，然后对比本地binlog是否有记录（所以MySQL从节点必须要开启binary log）。

如果有记录，说明该GTID的事务已经执行，从节点会忽略。

如果没有记录，从节点就会从relay log中执行该GTID的事务，并记录到bin log。

在解析过程中会判断是否有主键，如果没有就用二级索引，如果有就用全部扫描。

MySQL 主从复制主要用途

读写分离 : 有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

数据实时备 : 当系统中某个节点发生故障时，可以方便的故障切换

高可用HA

架构扩展: 随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。

MySQL复制性能优化

影响主从延迟的因素

主库写入二进制日志的时间 ------> 控制主库的事务大小，分割大事务
二进制日志传输的时间 ------> 使用mixed日志格式或者使用row,但要set binlog_row_image=minimal;
默认情况下从库只有一个SQL线程，主库上并发修改但到了从库变成了一个线程串行 -----> 使用多线程复制（5.6提供的功能） ,在mysql中可以按照逻辑时钟的方式来分配SQL线程

如何在MySQL5.7中配置多线程复制

在从服务器上配置

[root@artisan binlog]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 51
Server version: 5.7.29-log MySQL Community Server (GPL)
...
....
.....
mysql> 
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> set global slave_parallel_type='logical_clock';
Query OK, 0 rows affected (0.03 sec)
mysql> set global slave_parallel_workers=4;  # 设置4个SQL线程
Query OK, 0 rows affected (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.36 sec)
mysql>

查看

mysql> show processlist \G;
*************************** 1. row ***************************
     Id: 21
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 2. row ***************************
     Id: 22
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 126
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 23
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 126
  State: Slave has read all relay log; waiting for more updates
   Info: NULL
*************************** 4. row ***************************
     Id: 24
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 126
  State: Waiting for an event from Coordinator
   Info: NULL
*************************** 5. row ***************************
     Id: 25
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 126
  State: Waiting for an event from Coordinator
   Info: NULL
*************************** 6. row ***************************
     Id: 26
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 126
  State: Waiting for an event from Coordinator
   Info: NULL
*************************** 7. row ***************************
     Id: 27
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 126
  State: Waiting for an event from Coordinator
   Info: NULL
7 rows in set (0.00 sec)
ERROR: 
No query specified
mysql>

MySQL主从复制无法解决的问题

无法分担主库的写压力 ---->分库分表
无法提供读写分离的功能
无法进行自动故障转移及主从切换—> MMM或者 MHA（推荐）

搞定MySQL

https://artisan.blog.csdn.net/article/details/104086330?spm=1001.2014.3001.5502

MySQL-主从架构探索

生猛干货

官方文档

为什么要用主从方案

常见的主从方案

一主一从 M-S

一主多从 M-S-S-S

多主一从 M-M-M-S (5.7开始支持)

使用场景

一主多级从

双主 M-M

环形主从(复制)S-S-S-S

使用场景

MySQL安装

主从复制的概念

主从复制的两种方案

主从架构的架构图解析

MySQL主从复制涉及到三个线程

主节点 binary log dump 线程

从节点-I/O线程

从节点-SQL线程

MySQL 主从复制的过程

MySQL 主从复制模式

异步模式（mysql async-mode）

半同步模式(mysql semi-sync)

全同步模式(mysql fully synchronized)

binlog记录格式

GTID复制模式

MySQL 主从复制主要用途

MySQL复制性能优化

如何在MySQL5.7中配置多线程复制

MySQL主从复制无法解决的问题

搞定MySQL

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL-主从架构探索

生猛干货

官方文档

为什么要用主从方案

常见的主从方案

一主一从 M-S

一主多从 M-S-S-S

多主一从 M-M-M-S (5.7开始支持)

使用场景

一主多级从

双主 M-M

环形主从(复制)S-S-S-S

使用场景

MySQL安装

主从复制的概念

主从复制的两种方案

主从架构的架构图解析

MySQL主从复制涉及到三个线程

主节点 binary log dump 线程

从节点-I/O线程

从节点-SQL线程

MySQL 主从复制的过程

MySQL 主从复制模式

异步模式（mysql async-mode）

半同步模式(mysql semi-sync)

全同步模式(mysql fully synchronized)

binlog记录格式

GTID复制模式

MySQL 主从复制主要用途

MySQL复制性能优化

如何在MySQL5.7中配置多线程复制

MySQL主从复制无法解决的问题

搞定MySQL

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像