MySQL主从同步延迟原因与解决⽅案
⼀、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配置不合理。
关于DDL和DML
SQL语⾔共分为以下⼏⼤类：查询语⾔DQL，控制语⾔DCL，操纵语⾔DML，定义语⾔DDL。事务控制TCL.
DQL（Data QUERY Languages）语句：即数据库定义语句，⽤来查询SELECT⼦句，FROM⼦句，WHERE⼦句组成的查询块，⽐如：select–from–where–gr ouop by–having–order by–limit
DDL（Data Definition Languages）语句：即数据库定义语句，⽤来创建数据库中的表、索引、视图、存储过程、触发器等，常⽤的语句关键字有：CREATE,A LTER,DROP,TRUNCATE,COMMENT,RENAME。增删改表的结构
DML（Data Manipulation Language）语句：即数据操纵语句，⽤来查询、添加、更新、删除等，常⽤的语句关键字有：SELECT,INSERT,UPDATE,DELETE, MERGE,CALL,EXPLAIN PLAN,LOCK TABLE，包括通⽤性的增删改查。增删改表的数据
DCL（Data Control Language）语句：即数据控制语句，⽤于授权/撤销数据库及其字段的权限（DCL is short name of Data Control Language which include s commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.）。常⽤的语句关键字有：GRANT, REVOKE。
TCL（Transaction Control Language）语句：事务控制语句，⽤于控制事务，常⽤的语句关键字有：COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION 。
⼆、主从延时排查⽅法
通过监控 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.解决从库复制延迟的问题：
1)、架构⽅⾯
1.业务的持久化层的实现采⽤分库架构，mysql服务可平⾏扩展，分散压⼒。
2.单个库读写分离，⼀主多从，主写从读，分散压⼒。这样从库压⼒⽐主库⾼，保护主库。
3.服务的基础架构在业务和mysql之间加⼊memcache或者redis的cache层。降低mysql的读压⼒。
4.不同业务的mysql物理上放在不同机器，分散压⼒。
5.使⽤⽐主库更好的硬件设备作为slave，mysql压⼒⼩，延迟⾃然会变⼩。
2)、硬件⽅⾯
1.采⽤好服务器，⽐如4u⽐2u性能明显好，2u⽐1u性能明显好。
2.存储⽤ssd或者盘阵或者san，提升随机写的性能。
3.主从间保证处在同⼀个交换机下⾯，并且是万兆环境。
总结，硬件强劲，延迟⾃然会变⼩。⼀句话，缩⼩延迟的解决⽅案就是花钱和花时间。
3)、mysql主从同步加速
1、sync_binlog在slave端设置为0
2、–logs-slave-updates 从服务器从主服务器接收到的更新不记⼊它的⼆进制⽇志。
3、直接禁⽤slave端的binlog
4、slave端，如果使⽤的存储引擎是innodb，innodb_flush_log_at_trx_commit =2
mysql视图和存储过程4)、从⽂件系统本⾝属性⾓度优化
master端修改linux、Unix⽂件系统中⽂件的etime属性， 由于每当读⽂件时OS都会将读取操作发⽣的时间回写到磁盘上，对于读操作频繁的数据库⽂件来说这是没必要的，只会增加磁盘系统的负担影响I/O性能。可以通过设置⽂件系统的mount属性，组织操作系统写atime 信息，在linux上的操作为：打开/etc/fstab，加上noatime参数/dev/sdb1 /data reiserfs noatime 1 2然后重新mount⽂件系统
#mount -oremount /data
5)、同步参数调整主库是写，对数据安全性较⾼，⽐如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置是需要的⽽slave则不需要这么⾼的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog也可以设置为0来提⾼sql的执⾏效率
2.MySql数据库从库同步其他问题及解决⽅案
1)、mysql主从复制存在的问题：
● 主库宕机后，数据可能丢失
● 从库只有⼀个sql Thread，主库写压⼒⼤，复制很可能延时
2)、解决⽅法：
● 半同步复制—解决数据丢失的问题
● 并⾏复制----解决从库复制延迟的问题
3)、半同步复制mysql semi-sync（半同步复制）半同步复制：
● 5.5集成到mysql，以插件的形式存在，需要单独安装
● 确保事务提交后binlog⾄少传输到⼀个从库
● 不保证从库应⽤完这个事务的binlog
● 性能有⼀定的降低，响应时间会更长
● ⽹络异常或从库宕机，卡主主库，直到超时或从库恢复
4)、主从复制–异步复制原理、半同步复制和并⾏复制原理⽐较

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。