数据库主从复制原理与主从延时问题优化

https://www.cnblogs.com/syncnavigator/p/10189644.html
https://blog.csdn.net/cwb521sxm/article/details/97303289
https://blog.csdn.net/zhanghan18333611647/article/details/91638443

一、MySQL的数据库主从复制原理
MySQL的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高。Slave的IO Thread线程从主库中binlog中读取取日志。Slave的SQLThread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随即的，不是顺序的，成本高很多。由于SQL Thread也是单线程的，如果slave上的其他查询产生lock争用，又或者一个DML语句（大事务、大查询）执行了几分钟，那么所有之后的DML会等待这个DML执行完才会继续执行，这就导致了延时。

主从同步延迟的引发原因

1、Master负载

2、Slave负载

3、网络延迟

4、机器配置（cpu、内存、硬盘）

总之，当主库的并发较高时，产生的DML数量超过slave的SQL Thread所能处理的速度，或者当slave中有大型query语句产生了锁等待那么延时就产生了。

同步延迟的解决方案

最简单的减少slave同步延时，尽量让主库的DDL快速执行。还有就是主库是写，对数据安全性较高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog也可以设置为0来提高sql的执行效率，从库关闭binlog记录。另外就是使用比主库更好的硬件设备作为slave。
在业务架构上将不同的业务分布到不同的物理数据库上，同一个业务分库架构，在业务层与数据库之间增加分布式缓存以便减少主库的压力。
在mysql 5.7版本后开始支持多线程并行复制技术enhanced multi-threaded slave（简称MTS）。
MySQL并行同步机制

并行同步在MySQL5.6就开始支持，只不过在5.6时是基于schema的，也就是基于库的，相比于5.7的机制要逊色的多。MySQL 5.7才可称为真正的并行复制，这其中最为主要的原因就是slave服务器的回放与主机是一致的。即master服务器上是怎么并行执行的slave上就怎样进行并行回放。不再有库的并行复制限制。

从MySQL官方来看，其并行复制的原本计划是支持表级的并行复制和行级的并行复制，行级的并行复制通过解析ROW格式的二进制日志的方式来完成。但是最终出现给小伙伴的确是在开发计划中称为：MTS: Prepared transactions slave parallel applier。该并行复制的思想最早是由MariaDB的Kristain提出，并已在MariaDB 10中出现，相信很多选择MariaDB的小伙伴最为看重的功能之一就是并行复制。

MySQL 5.7并行复制的思想简单易懂，一言以蔽之：一个组提交的事务都是可以并行回放，因为这些事务都已进入到事务的prepare阶段，则说明事务之间没有任何冲突（否则就不可能提交）。为了兼容MySQL 5.6基于库的并行复制，5.7引入了新的变量slave-parallel-type，其可以配置的值有：

• DATABASE：默认值，基于库的并行复制方式

• LOGICAL_CLOCK：基于组提交的并行复制方式

MySQL在binlog中定义了两个Logical_clock的变量：

• max_c ommitted_transaction：记录上次组提交时的logical_clock，代表上述mysqlbinlog中的last_committed

• transaction_counter：记录当前组提交中各事务的logcial_clock，代表上述mysqlbinlog中的sequence_number

通过工具检查binlog文件，可以发现较之原来的二进制日志内容多了last_committed和sequence_number，last_committed表示事务提交的时候，上次事务提交的编号，如果事务具有相同的last_committed，表示这些事务都在一组内，可以进行并行的回放。例如上述last_committed为0的事务有6个，表示组提交时提交了6个事务，而这6个事务在从机是可以进行并行回放的。

提升同步的策略中，有很多实现，包括MySQL各版本的实现及MariaDB等分支的实现上，按按粒度区分的三个策略，粒度从粗到细是按库、按表、按行，而MySQL5.7按照commit_id的策略适用范围更广，改进策略并发性更优。

MySQL主从复制实际上基于二进制日志，原理可以用一张图来表示：

MySQL数据库主从同步延迟分析及解决方案

分为四步走：

1. 主库对所有DDL和DML产生的日志写进binlog；

2. 主库生成一个 log dump 线程，用来给从库I/O线程读取binlog；

3. 从库的I/O Thread去请求主库的binlog，并将得到的binlog日志写到relay log文件中；

4. 从库的SQL Thread会读取relay log文件中的日志解析成具体操作，将主库的DDL和DML操作事件重放。

关于DDL和DML

SQL语言共分为四大类：查询语言DQL，控制语言DCL，操纵语言DML，定义语言DDL。

DQL：可以简单理解为SELECT语句；

DCL：GRANT、ROLLBACK和COMMIT一类语句；

DML：数据库操作语言：SQL中处理数据库中的数据

其主要命令有INSERT,UPDATE,DELETE等，

DDL：数据库定义语言：定义数据库的结构。

其主要命令有CREATE，ALTER，DROP

二、主从复制存在的问题

1. 主库宕机后，数据可能丢失；

2. 主从同步延迟。

三、MySQL数据库主从同步延迟产生原因
原因分析

MySQL的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高。Slave的SQL Thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随即的，不是顺序的，成本高很多。另一方面，由于SQL Thread也是单线程的，当主库的并发较高时，产生的DML数量超过slave的SQL Thread所能处理的速度，或者当slave中有大型query语句产生了锁等待那么延时就产生了。

常见原因：Master负载过高、Slave负载过高、网络延迟、机器性能太低、MySQL配置不合理。

四、主从延时排查方法
通过监控 show slave status 命令输出的Seconds_Behind_Master参数的值来判断：

NULL，表示io_thread或是sql_thread有任何一个发生故障；

0，该值为零，表示主从复制良好；

正值，表示主从已经出现延时，数字越大表示从库延迟越严重。

五、解决方案
解决数据丢失的问题：

1. 半同步复制

从MySQL5.5开始，MySQL已经支持半同步复制了，半同步复制介于异步复制和同步复制之间，主库在执行完事务后不立刻返回结果给客户端，需要等待至少一个从库接收到并写到relay log中才返回结果给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一个TCP/IP往返耗时的延迟。

2. 主库配置sync_binlog=1，innodb_flush_log_at_trx_commit=1

sync_binlog的默认值是0，MySQL不会将binlog同步到磁盘，其值表示每写多少binlog同步一次磁盘。

innodb_flush_log_at_trx_commit为1表示每一次事务提交或事务外的指令都需要把日志flush到磁盘。

注意:将以上两个值同时设置为1时，写入性能会受到一定限制，只有对数据安全性要求很高的场景才建议使用，比如涉及到钱的订单支付业务，而且系统I/O能力必须可以支撑！

解决从库复制延迟的问题：

1. 优化网络

2. 升级Slave硬件配置

3. Slave调整参数，关闭binlog，修改innodb_flush_log_at_trx_commit参数值

4. 升级MySQL版本到5.7，使用并行复制

5. 1 如何写频繁，水平拆分，减少单片写数量
   7 避免复杂DML操作