mysql主从复制延时问题

      不知道从哪一年搞过数据库主从复制的事情了，为了减轻数据库的瓶颈，采用数据库采用的服务架构是：

主从复制架构图

      当然实际使用时候并没有采用如上一主多从的落地方案，根据实际情况，采用的是一主一从架构，项目实行采用的是动态数据源+AOP自动切换数据库查询，使得实际业务逻辑进行所有的写操作都是在主数据库上执行，读操作都是从数据库进行获取。

      来看一张mysql主从复制内部实现原理图：

mysql主从复制内部原理图

      MySQL之间数据复制的基础是二进制日志文件（binary logfile）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

    原理清楚了，思考一个问题？如果网络、sql执行性能瓶颈等原因造成的数据同步延时问题该如何解决。

例如：当并发量过大的时候，一个tps在主库产生了大量的DDL语句，超过了slave中的一个sql写的范围，从库延时的问题就出来了。master主库可以并发执行DDL语句，而Slave_SQL_Running线程是单线程，却不可以并发执行，并且是读取binary log file安装顺序进行读取的。

当然总结下来就是如下两个原因：

首要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高次要原因：读写binlog带来的性能影响，网络传输延迟。

      回到上面那个问题，造成的数据同步延时问题该如何解决？

1.减缓数据库压力，延时问题自然解决

      在此架构上在加入memcache或者redis的cache层，特殊业务查询时候优先从缓存中进行查询，降低从库数据库服务的压力。

2.硬件设备精选

      a.使用比主库更好的机器用来做slave库 、b.采用配置高的服务器用来做数据库、c.主从保证在一个交换机下面，并且采用万兆环境等

3.主从复制性能调优

     优化之前先了解slave库中的一些知识点，在从服务器上执行show slave status;可以查看到很多同步的参数，我们需要特别注意的参数如下：

slave库参数

从库同步延迟情况出现的

      1、show slave status显示参数Seconds_Behind_Master不为0，这个数值可能会很大

      2、show slave status显示参数Relay_Master_Log_File和Master_Log_File显示bin-log的编号相差很大，说明bin-log在从库上没有及时同步，所以近期执行的bin-log和当前IO线程所读的bin-log相差很大

     3、MySQL的从库数据目录下存在大量mysql-relay-log日志，该日志同步完成之后就会被系统自动删除，存在大量日志，说明主从同步延迟很厉害

通过如上指标可以发觉mysql延时问题比较严重。

优化角度一：采用主从同步加速机制

1、sync_binlog在slave端设置为0

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

3、直接禁用slave端的binlog

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

优化角度二：从文件系统本身进行优化

      在master端修改linux、unix文件系统中文件的etime属性，由于每当读文件时OS都会将读取操作发生的实际回写到磁盘上，对于读操作频繁的数据库文件来说这是没必要的，只会增加磁盘系统的负担影响I/O性能。可以通过设置文件系统的mount属性，组织操作系统写atime信息。

优化角度三：其他方面

      master库是写库，slave是从库，写库对安全性要求特别高，而读库却没有那样的要求。

写库：比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置是需要的

读库：sync_binlog设置为0或者关闭binlog，innodb_flushlog也可以设置为0来提高sql的执行效率