MySQL主从复制主从延迟原理与解决方案

一、MySQL主从复制实践

1、MySQL复制技术的有以下特点

数据分布

负载平衡

备份

高可用性和容错性

2、主从复制过程/原理

MySQL的主从复制是一个异步的复制过程（虽然一般情况下感觉是实时的）

(1) master配置用户权限开启bin-log并记录二进制日志

(2) slave执行start slave 命令开启主从复制开关

(3) slave服务器的I/O线程通过master上授权的复制用户权限请求连接master,请求bin-log日志

(4) 当slave服务器的I/O线程获取到master服务器上I/O线程发送的日志内容后，将binlog日志依次写入到slave端自身的relay-log(中继日志)

（5）slave服务器的SQL线程会实时检测本地relay-log中的I/O线程中新增的日志内容，然后把中继日志中的内容解析城SQL语句，并且执行应用这些SQL语句

 

二、MySQL主从延迟

 

1、延迟原理

谈到MySQL数据库主从同步延迟原理，得从mysql的数据库主从复制原理说起，mysql的主从复制都是单线程的操作，主库对所有DDL和 DML产生binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率很比较高，

下一步， 问题来了，slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的，不是顺 序的，成本高很多，还可能可slave上的其他查询产生lock争用，由于Slave_SQL_Running也是单线程的，所以一个DDL卡主了，需要 执行10分钟，那么所有之后的DDL会等待这个DDL执行完才会继续执行，这就导致了延时。有朋友会问：“主库上那个相同的DDL也需要执行10分，为什 么slave会延时？”，答案是master可以并发，Slave_SQL_Running线程却不可以

 

面试问题：

1、主从的好处

2、主从的原理

3、主从延迟怎么解决

https://blog.csdn.net/soar_away/article/details/72615012

 

1、需要同步的从库数据太多

2、从库的硬件资源较差，需要提升

3、网络问题，需要提升网络带宽

4、主库的数据写入量较大，需要优配置和硬件资源

5、sql语句执行过长导致，需要优化

 

主从延迟怎么判断查看

show slave status\G;

Seconds_Behind_Master

0(无视)

NULL(表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No,而非Yes)

 

 

问题一：

一个主库的从库太多，导致复制延迟。

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

 

问题二：

从库硬件比主库差，导致复制延迟

查看master和slave的系统配置，可能会因为机器配置的问题，包括磁盘IO、CPU、内存等各方面因素造成复制的延迟，一般发生在高并发大数据量写入场景。

 

问题三：

慢SQL语句过多

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

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

 

问题四：

主从复制的设计问题

例如，主从复制单线程，因为主库写并发太大，来不及传送到从库就会导致延迟。

更高版本的MySQL可以支持多线程复制，门户网站会开发自己多线程同步功能。

 

问题五：

主从库之间的网络延迟。

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

 

问题六：

主库读写压力大，导致复制延迟

主库硬件要搞好一点，架构的前端要加buffer以及缓存层。

通过read-only参数让从库只读访问

read-only参数选项可以让从服务器只允许来自服务器线程或具有SUPER权限的数据库用户进行更新，可以确保从服务器不接受来自用户端的非法用户更新。

 

4、主挂了怎么办

 

主从故障切换（一主多从）

（1）确保所有的relay-log全部读取完毕

通过show slave status查看

Slave_SQL_Running_State: Slave has read allrelay log; waiting for more updates

（2）选择新的主库

对比选择Relay_Master_Log_File,Exec_Master_Log_Pos最大的作为新的主库，这里我们选择slave1为新的主库

其实，如果两个从IO进程一直都是正常，没有落后于主，且relay log都已经重放完成，两个从是一样的，选择哪个都可以。

（3）进行相应的配置

（4）reset master