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（Data QUERY Languages）语句：即数据库定义语句，用来查询SELECT子句，FROM子句，WHERE子句组成的查询块，比如：select–from–where–grouop by–having–order by–limit

DDL（Data Definition Languages）语句：即数据库定义语句，用来创建数据库中的表、索引、视图、存储过程、触发器等，常用的语句关键字有：CREATE,ALTER,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 includes 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。

三、主从延时排查方法

主要查看Seconds_Behind_Master参数的值来判断。NULL表示io_thread或是sql_thread有任何一个发生故障；0，该值为零，表示主从复制良好；>0的数字表示主从延时，数字越大表示从库延迟越大

四、解决方案

使用半同步复制，主库配置 （也就是双1操作）    

sync_binlog=1，

innodb_flush_log_at_trx_commit=1

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

架构进行优化

业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。

单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。

服务的基础架构在业务和mysql之间加入redis的cache层。降低mysql的读压力。

不同业务的mysql物理上放在不同机器，分散压力。

使用比主库更好的硬件设备作为slave，mysql压力小，延迟自然会变小。

使用性能好一点的硬件设施

存储用SSD，提升随机写的性能。

mysql主从同步加速

1.sync_binlog在slave端设置为0

2.–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。

3.直接禁用slave端的binlog

4.slave端，如果使用的存储引擎是innodb，innodb_flush_log_at_trx_commit =2

4.2 gtid模式下数据不一致后怎么解决

主库：

mysql>FLUSH TABLES WITH READ LOCK;  

mysql>mysqldump -uroot -p --host=192.168.1.1 --port=3306 --set-gtid-purged=on --single-transaction --all-databases >/root/master-dump.sql

主库备份完成后，将备份文件传至从库。

从库：

mysql>stop slave;

mysql>reset slave all;

mysql>source /root/master-dump.sql

mysql>change master to

master_host='192.168.1.1',

MASTER_PORT=3306,

master_user='repl',

master_password='123123' ,

MASTER_AUTO_POSITION=1;    

# 该参数会自动读取relaylog中的最后一个gtid号,来向主库申请日志, 如未从relaylog中读取到,则从第一个gtid开始同步

# 该参数值代表从哪个gtid号开始请求, 如从库是使用xbk物理恢复的,则此处值需要是恢复文件中最后一个gtid号+1。

也可以在主库执行show global variables like '%gtid%'\G，set @@global.gtid_purged='XXXXX'，

然后执行MASTER_AUTO_POSITION=1。

mysql>start slave;

最后执行查看数据是否同步。

mysql>show slave status