Mysql数据库主从延迟如何解决？

目录

1.mysql主从同步原理

2.mysql主从同步延迟是怎么产生的？

3.mysql数据库主从同步延迟解决方案

1）架构方面

2）硬件方面

3）mysql主从同步加速

4.mysql主从同步其它问题及解决方案

---

1.mysql主从同步原理

主库针对写操作，顺序写binlog，从库单线程去读取主库生成的binlog，并在本地原样执行（随机写），来保证主从数据逻辑上一致。

mysql的主从复制都是单线程的操作，从库上面执行DML和DDL的操作是随机的，不是顺序的，成本高很多，还可能slave上的其他查询产生锁争用，由于slave_sql_running是单线程的，所以一个DDL卡住了，需要执行10分钟，那么之后所有的DDL会等待这个DDL执行完才会继续执行，这就导致了延迟。

2.mysql主从同步延迟是怎么产生的？

当主库的TPS并发较高时，产生的DDL数据量超过slave一个sql线程所承受的范围，那么延时就产生了，当然还有可能就是slave的大型query语句产生了锁等待。主要原因：从库在读写上产生的压力太大，CPU计算负荷大，网卡负荷大。硬盘随机IO太高。次要原因：读写binlog带来了性能影响，网络传输延迟。

3.mysql数据库主从同步延迟解决方案

1）架构方面

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

2.单个库读写分离，一主多从，主写从读，分散压力。

3.服务的基础架构在业务和mysql之间加入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从服务器从主服务器上接收到的更新不记入binlog

3.直接禁用slave端的binlog

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

sync_binlog 配置说明：

sync_binlog”：这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。

从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

innodb_flush_log_at_trx_commit 配置说明：

默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电 池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。 

sync_binlog	描述
0	默认值，事务提交之后，将binlog从缓冲区写入磁盘，但是不进行刷新操作方式fsync(),此时只是写入了操作系统缓冲，若操作系统宕机，则会丢失部分二进制日志
1	事务提交之后，将binlog写入磁盘，并立即执行刷新操作，相当于同步写入磁盘，不经过操作系统的缓存
N	每写N次操作系统缓冲，就执行一次刷新操作
innodb_flush_log_at_trx_commit
0	提交事务的时候，不立即吧redo log buffer里面的数据刷入磁盘文件里面去，而是依靠Innodb的主线程，每秒执行一次刷新到磁盘，假如此刻mysql宕机，内存里面的数据则会全部丢失
1	提交事务的时候，必须把redo log从内存刷入到磁盘文件里面去，只要事务提交成功，redo log必然在磁盘里
2	提交事务的时候，把redo log写入到磁盘文件对应的os cache缓存里面去，而不是直接写入磁盘文件，可能1s之后，才会把os cache里的数据写入到磁盘文件里面去。

4.mysql主从同步其它问题及解决方案

1）半同步复制，解决数据丢失问题

事务在主库写完binlog后需要从库返回一个已接受，才放回给客户端

从mysql5.5开始，mysql已经支持半同步复制了，在主库写完binlog并且不立即返回结果给客户端，需要等待至少一个从库接收到binlog并且写到relay log中，才返回结果给客户端。

2）并行复制

社区版5.6中新增

并行是指从库多线程apply binlog 库级别并行应用binlog，同一个库数据更改还是串行的