1.简介

mysql从3.23版本开始提供复制功能，复制是将主库的DDL（就是改变结构的语句create table，drop table等）和DML（数据库操作语句增删改查）操作通过二进制日志传递到复制服务器（从库）上，然后从库对这些日志重新执行（重做），从而使得主库和从库保持数据一致。

2.为什么要做主从复制？

  1. 在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
  2. 做数据的热备，主库宕机后能够及时替换主库，保证业务可用性。
  3. 架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

3 .mysql复制的优点

  1. 如果主库出现问题，可以快速切换到从库提供服务
  2. 可以在从库执行查询操作，降低主库的访问压力。
  3. 可以在从库进行备份，以免备份期间影响主库的服务。

4.Mysql复制解决的问题

  • 数据分布 (Data distribution )
  • 负载平衡(load balancing)
  • 数据备份(Backups) ，保证数据安全
  • 高可用性和容错行(High availability and failover)
  • 实现读写分离，缓解数据库压力

注意：由于 mysql 实现的异步复制，所以主库和从库数据之间存在一定的差异，在从库执行查询操作需要考虑这些数据的差异，一般只有更新不频繁和对实时性要求不高的数据可以通过从库查询，实行要求高的仍要从主库查询。

5.MySQL 主从复制的主要用途

  1. 读写分离
    在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
  2. 数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换(主从切换)
    提高数据安全-因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据；
  3. 高可用（HA）
    1）因为数据库服务器中的数据都是相同的，当Master挂掉后，可以指定一台Slave充当Master继续保证服务的运行，因为数据是一致性的（如果当插入时Master就挂掉，可能不一致，因为同步也需要时间）当然这种配置不是简单的把一台Slave充当Master，毕竟还要考虑后续的Slave的数据同步到Master
    2）在主服务器上执行写入和更新，在从服务器上向外提供读功能，达到读写分离的效果，也可以动态地调整从服务器的数量，从而调整整个数据库的性能。
    3）在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。
  4. 架构扩展
    随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。

6.MySQL 主从形式

1）一主多从，提高系统的读性能


mysql主从异步复制 mysql主从复制作用_数据库

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

mysql主从异步复制 mysql主从复制作用_mysql_02


       

数据库主主

：两台都是主数据库，同时对外提供读写操作。客户端访问任意一台。数据存在双向同步，

库主从

：一台是主数据库，同时对外提供读写操作。一台是

从数据库，对外提供读的操作。

数据从主库同步到从库。

mysql主从异步复制 mysql主从复制作用_mysql主从异步复制_03

 从库和备库，就是slave库功能不同因此叫法不一样而已。一般slave库都会对外提供读的功能的，因此，大家日常听得最多的就是

主从


2）多主一从 （从5.7开始支持）


mysql主从异步复制 mysql主从复制作用_数据_04


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

3）双主复制

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

4）级联复制


mysql主从异步复制 mysql主从复制作用_数据库_05


       级联复制模式下，部分slave的数据同步不连接主节点，而是连接从节点。因为如果主节点有太多的从节点，就会损耗一部分性能用于replication（复制），那么我们可以让3~5个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。级联复制下从节点也要开启binary log（bin-log）功能。

7.MySQL 主从复制的原理

MySQL主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个(I/O thread, SQL thread)运行在从节点，如下图所示:

mysql主从异步复制 mysql主从复制作用_数据库_06

1）主节点 log dump 线程

     当从节点连接主节点时，主节点会为其创建一个log dump 线程，用于发送和读取bin-log的内容。在读取bin-log中的操作时，log dump线程会对主节点上的bin-log加锁，当读取完成，在发送给从节点之前，锁会被释放。主节点会为自己的每一个从节点创建一个log dump 线程。

2）从节点 I/O线程

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

3）从节点 SQL线程

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

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

要实施复制，首先必须打开Master 端的binary log（bin-log）功能，否则无法实现。

因为整个复制过程实际上就是Slave 从Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。如下图所示：

mysql主从异步复制 mysql主从复制作用_SQL_07

4）复制的基本过程

  1. 在从节点上执行sart slave命令开启主从复制开关，开始进行主从复制。从节点上的I/O 进程连接主节点，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；
  2. 主节点接收到来自从节点的I/O请求后，通过负责复制的I/O进程（log dump 线程）根据请求信息读取指定日志指定位置之后的日志信息，返回给从节点。返回信息中除了日志所包含的信息之外，还包括本次返回的信息的bin-log file 的以及bin-log position（bin-log中的下一个指定更新位置）；
  3. 从节点的I/O进程接收到主节点发送过来的日志内容、日志文件及位置点后，将接收到的日志内容更新到本机的relay-log（中继日志）的文件（Mysql-relay-bin.xxx）的最末端，并将读取到的binary log（bin-log）文件名和位置保存到master-info 文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log 的哪个位置开始往后的日志内容，请发给我”；
  4. Slave 的 SQL线程检测到relay-log 中新增加了内容后，会将relay-log的内容解析成在主节点上实际执行过SQL语句，然后在本数据库中按照解析出来的顺序执行，并在relay-log.info中记录当前应用中继日志的文件名和位置点。

8.MySQL 主从复制的模式

MySQL 主从复制默认是异步的模式。MySQL增删改操作会全部记录在bin-log（binary log）中，当slave节点连接master时，会主动从master处获取最新的bin-log文件。并把bin-log存储到本地的relay-log中，然后去执行relay-log的更新内容。

1）异步模式（mysql async-mode）

       异步模式如下图所示，这种模式下，主节点不会主动推送bin-log到从节点，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主节点如果崩溃掉了，此时主节点上已经提交的事务可能并没有传到从节点上，如果此时，强行将从提升为主，可能导致新主节点上的数据不完整。
        

mysql主从异步复制 mysql主从复制作用_SQL_08

2）半同步模式(mysql semi-sync)

      介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay-log中才返回成功信息给客户端（只能保证主库的bin-log至少传输到了一个从节点上，但并不能保证从节点将此事务执行更新到db中），否则需要等待直到超时时间然后切换成异步模式再提交。相对于异步复制，半同步复制提高了数据的安全性，一定程度的保证了数据能成功备份到从库，同时它也造成了一定程度的延迟，但是比全同步模式延迟要低，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。如下图所示：
        

mysql主从异步复制 mysql主从复制作用_数据_09


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

3）全同步模式

       指当主库执行完一个事务，然后所有的从库都复制了该事务并成功执行完才返回成功信息给客户端。因为需要等待所有从库执行完该事务才能返回成功信息，所以全同步复制的性能必然会收到严重的影响。

4）异步模式，全同步模式，半同步模式的对比图


mysql主从异步复制 mysql主从复制作用_SQL_10

9.GTID复制模式

在传统的复制里面，当发生故障，需要主从切换，需要找到bin-log和pos点（指从库更新到了主库bin-log的哪个位置，这个位置之前都已经更显完毕，这个位置之后未更新），然后将主节点指向新的主节点，相对来说比较麻烦，也容易出错。在MySQL 5.6里面，不用再找bin-log和pos点，我们只需要知道主节点的ip，端口，以及账号密码就行，因为复制是自动的，MySQL会通过内部机制GTID自动找点同步。
基于GTID的复制是MySQL 5.6后新增的复制方式.

GTID (global transaction identifier) 即全局事务ID, 保证了在每个在主库上提交的事务在集群中有一个唯一的ID.

10.GTID复制原理

在原来基于日志的复制中, 从库需要告知主库要从哪个偏移量进行增量同步, 如果指定错误会造成数据的遗漏, 从而造成数据的不一致.

而基于GTID的复制中, 从库会告知主库已经执行的事务的GTID的值, 然后主库会将所有未执行的事务的GTID的列表返回给从库. 并且可以保证同一个事务只在指定的从库执行一次.通过全局的事务ID确定从库要执行的事务的方式代替了以前需要用bin-log和pos点确定从库要执行的事务的方式。

GTID是由server_uuid和事物id组成，格式为：GTID=server_uuid:transaction_id。server_uuid是在数据库启动过程中自动生成，每台机器的server-uuid不一样。uuid存放在数据目录的auto.conf文件中，而transaction_id就是事务提交时系统顺序分配的一个不会重复的序列号。

主节点更新数据时，会在事务前产生GTID，一起记录到bin-log日志中。从节点的I/O线程将变更的bin-log，写入到本地的relay-log中。SQL线程从relay-log中获取GTID，然后对比本地bin-log是否有记录（所以MySQL从节点必须要开启binary-log）。如果有记录，说明该GTID的事务已经执行，从节点会忽略。如果没有记录，从节点就会从relay-log中执行该GTID的事务，并记录到binlog。在解析过程中会判断是否有主键，如果没有就用二级索引，如果有就用全部扫描。

1）GTID的好处

  • GTID使用master_auto_position=1代替了binlog和position号的主从复制搭建方式，相比binlog和position方式更容易搭建主从复制。
  • GTID方便实现主从之间的failover（主从切换），不用一步一步的去查找position和binlog文件。      

2）GTID模式复制局限性

  • 不能使用create table table_name  select * from table_name模式的语句
  •  在一个事务中既包含事务表的操作又包含非事务表
  •  不支持CREATE TEMPORARY TABLE  or DROP TEMPORARY TABLE语句操作
  •  使用GTID复制从库跳过错误时，不支持sql_slave_skip_counter参数的语法

11.多线程复制

多线程复制（基于库），在MySQL 5.6以前的版本，slave的复制是单线程的，而master是并发写入的，所以延时是避免不了的。唯一有效的方法是把多个库放在多台slave，这样又有点浪费服务器。在MySQL 5.6里面，我们可以把多个表放在多个库，这样就可以使用多线程复制。

但 5.6 中的每个线程只能处理一个数据库，所以如果只有一个数据库，或者绝大多数写操作都是集中在某一个数据库的，那么这个“多线程复制”就不能充分发挥作用了。

Mysql 5.7 对 “多线程复制” 进行了改善，可以按照逻辑时钟的方式来分配线程，大大提高了复制性能

12.MySQL主从复制的方式

MySQL 主从复制有三种方式：基于SQL语句的复制（statement-based replication，SBR），基于行的复制（row-based replication，RBR)，混合模式复制（mixed-based replication,MBR)。对应的bin-log文件的格式也有三种：STATEMENT,  ROW,  MIXED。

1）Statement-base Replication (SBR)  

       就是记录sql语句在bin-log中，binlog 记录的是 SQL的原文。如果主库和从库选的索引不一致，可能会导致主库不一致。我们来分析一下。假设主库执行删除这个SQL(其中，a 和 create_time 都会有索引)如下：

delete from t where b '888' and create_time < '2021-05-03' limit 2;

我们知道，数据库选择了 b 索引和选择 create_time 索引，最后 limit b 出来的数据一般是不一样的。所以就会存在这种情况：在 binlog = statement 格式时，主库在执行这条SQL时，使用的是索引b，而从库在执行这条SQL时，使用了索引 create_time。最后主从数据不一致了。如何解决这个问题呢？可以把binlog格式修改为 row。row 格式的 binlog 日志，记录的不是 SQL原文，而是两个 event: Table_map 和 Delete_rows。Table_map event 说明要操作的表，Delete_rows event 用于定义要删除的行为，记录删除的具体行数。row 格式的binlog记录的就是要删除的主键ID信息，因此不会出现主从不一致的问题。但是如果SQL删除10万行数据，使用row格式就会很占空间的，10万条数据都在 binlog 里面，写 binlog 的时候也很耗IO。但是 statement 格式的binlog可能会导致数据不一致，因此设计MySQL的大叔想了一个折中的方案，mixed 格式的 binlog。所谓的 mixed格式其实就是 row 和 statement 格式混合使用，当 MySQL 判断可能数据不一致时，就用 row 格式，否则使用 statement 格式。

 

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

2）Row-based Relication(RBR)

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

3）Mixed-format Replication(MBR)

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

13.Mysql主从同步延迟问题及解决方案

主从延迟是怎样定义的呢？与主从数据同步相关的时间点有三个

  1. 主库执行完一个事务，写入binlog，我们把这个时刻记为 T1;
  2. 主库同步数据给从库，从库接受完这个binlog的时刻，记录为 T2；
  3. 从库执行完这个事务，这个时刻记录为 T3。

所谓主从延迟，其实就是指同一个事务，在从库执行完的时间和在主库执行完的时间差值，即 T3-T1。



哪些情况会导致主从延迟呢？

1 如果从库所在的机器比主库的机器性能差，会导致主从延迟，这种情况比较好解决，只需选择主从库一样规格的机器就好。
2 如果从库的压力大，也会导致主从延迟。比如主库直接影响业务，大家可能使用会比较克制，因此一般查询都打到从库了，结果导致从库查询消耗大量CPU，影响同步速度，最后导致主从延迟。这种情况的话，可以搞一主多从架构，即多接几个从库分摊读的压力。另外，还可以把binlog接入到Hadoop这类系统，让它们提供查询的能力。
3 大事务也会导致主从延迟。如果一个事务执行就要10分钟，那么主库执行完后，给到从库执行，最后这个事务可能就会导致从库延迟10分钟啦。日常开发中，我们为什么特别强调，不要一次性delete太多SQL，需要分批进行，其实也是为了避免大事务。另外，大表的DDL语句，也会导致大事务，大家日常开发关注一下哈。
4 网络延迟也会导致主从延迟，这种情况你只能优化你的网络啦，比如带宽20M升级到100M类似意思等。
5 如果从数据库过多也会导致主从延迟，因此要避免复制的从节点数量过多。从库数据一般以3-5个为宜。
6 低版本的MySQL只支持单线程复制，如果主库并发高，来不及传送到从库，就会导致延迟。可以换用更高版本的Mysql，可以支持多线程复制。
 

问题一：主库的从库太多，导致复制延迟

           从库数据以3-5个为宜，要复制的从节点数量过多，会导致复制延迟

问题二：从库硬件比主库差，导致复制延迟

           查看Master和Slave的系统配置，可能会因为机器配置不当，包括磁盘I/O、CPU、内存等各方面因素造成复制的延迟。一般发生在高并发大数据量写入场景中

问题三：慢SQL语句过多

           假如一条SQL语句执行时间是20秒，那么从执行完毕到从库上能查到数据至少需要20秒，这样就延迟20秒了。

          一般要把SQL语句的优化作为常规工作不断地进行监控和优化，如果单个SQL的写入时间长，可以修改后分多次写入。通过查看慢查询日志或show full processlist命令，找出执行时间长的查询语句或大的事务

问题四：主从复制的设计问题

          例如主从复制单线程，如果主库写并发太大，来不及传送到从库，就会导致延迟。更高版本的Mysql可以支持多线程复制，门户网站则会开发自己的多线程同步功能。

问题五：主从库之间的网络延迟

          主从库的网卡、网线、交换机等网络设备都可能成为复制的瓶颈，导致复制延迟。另外，跨公网的主从复制很容易导致主从复制延迟

问题六：主库读写压力大，导致复制延迟

           架构的前端要加buffer及缓存层

解决思路：

         如何避免或解决主从延迟？可以用来解决的办法，有如下的:

  • 从库优化Mysql参数。比如增大innodb_buffer_pool_size，让更多操作在Mysql内存中完成，减少磁盘操作。
  • 从库使用高性能主机。包括cpu强悍、内存加大。避免使用虚拟云主机，使用物理主机，这样提升了i/o方面性。
  • 从库使用SSD磁盘。机械硬盘是靠磁头旋转到指定位置来读数据、写数据。转来转去的，我们叫做i/o。磁盘i/o存在速度瓶颈。固态硬盘是一个电子设备，电子设备不需要机械旋转，读写固态硬盘上任意位置的数据，速度都是一样的。
  • 业务代码的妥协。将实时性要求高的某些操作，使用主库做读操作。比如我写了数据到主库了，需要马上展示数据，不要到从库去读数据，因为从库可能还没同步过去呢。直接从主库读数据，保证是最新的数据展示。
  • 从库的线程改为多个同步线程同步数据。mysql-5.6.3为了解决这个问题，从服务器上，每一个库开一个线程来同步。
  •  网络优化。网络堵塞，也会导致同步延迟。跨机房的数据库同步，会存在同步延迟。保证主从在同一个机房里面去。

门户网站的解决方案：

  1. 优酷的解决方案：数据库分片技术，而抛弃了由于数据量的越来越多导致复制延迟的问题。按照user_id进行分片，这样必须有一个全局的表来管理用户与shard的关系,根据user_id可以得到share_id，然后根据share_id去指定的分片查询指定的数据
  2. 淘宝的解决方案：修改源码，对应的机制是Transfer机制，此处通过对Binlog日志重做采用多线程实现，从而提高slave的QPPS

14 聊聊数据的库高可用方案

14.1 双机主备高可用

mysql主从异步复制 mysql主从复制作用_数据_11

  • 架构描述：两台机器A和B，A为主库，负责读写，B为备库，只备份数据。如果A库发生故障，B库成为主库负责读写。修复故障后，A成为备库，主库B同步数据到备库A
  • 优点：一个机器故障了可以自动切换，操作比较简单。
  • 缺点：只有一个库在工作，读写压力大，未能实现读写分离，并发也有一定限制

14.2 一主一从

mysql主从异步复制 mysql主从复制作用_数据库_12

  • 架构描述: 两台机器A和B，A为主库，负责读写，B为从库，负责读数据。如果A库发生故障，B库成为主库负责读写。修复故障后，A成为从库，主库B同步数据到从库A。
  • 优点：从库支持读，分担了主库的压力，提升了并发度。一个机器故障了可以自动切换，操作比较简单。
  • 缺点：一台从库，并发支持还是不够，并且一共两台机器，还是存在同时故障的机率，不够高可用。
     

14.3 一主多从

mysql主从异步复制 mysql主从复制作用_mysql主从异步复制_13

  • 架构描述: 一台主库多台从库，A为主库，负责读写，B、C、D为从库，负责读数据。如果A库发生故障，B库成为主库负责读写，C、D负责读。修复故障后，A也成为从库，主库B同步数据到从库A。
  • 优点：多个从库支持读，分担了主库的压力，明显提升了读的并发度。
  • 缺点：只有台主机写，因此写的并发度不高
     

14.4 MariaDB同步多主机集群

mysql主从异步复制 mysql主从复制作用_mysql主从异步复制_14

  •  架构描述:有代理层实现负载均衡，多个数据库可以同时进行读写操作；各个数据库之间可以通过 Galera Replication 方法进行数据同步，每个库理论上数据是完全一致的。
  • 优点：读写的并发度都明显提升，可以任意节点读写，可以自动剔除故障节点，具有较高的可靠性。
  • 缺点：数据量不支持特别大。要避免大事务卡死，如果集群节点一个变慢，其他节点也会跟着变慢。
     

14.5 数据库中间件

mysql主从异步复制 mysql主从复制作用_SQL_15

  • 架构描述：mycat分片存储，每个分片配置一主多从的集群。
  • 优点：解决高并发高数据量的高可用方案
  • 缺点：维护成本比较大