Mysql主从延迟解决办法

1.MIXED模式无索引或SQL慢

在从库上pager grep -v Sleep;show processlist 查看到正在执行的SQL。

解决方法:

a. SQL比较简单, 则检查是否缺少索引，并添加索引。比如update操作where条件没有索引。

b. 另一类是 insert into select from的语句，如果select 里包含group by，多表关联，可能效率会比较低。这类可以到主库把binlog_format改成row。

2. 主库上有大事务，导致从库延时 现象解析binlog 发现类似于下图的情况看

解决方法：

与开发沟通,增加缓存,异步写入数据库,减少直接对db的大量写入。

3. 主库写入频繁,从库压力跟不上导致延时

此类原因的主要现象是数据库的IUD（插改删） 操作非常多，slave由于sql_thread单线程的原因追不上主库。 解决方法：

a. 升级从库的硬件配置，比如ssd、fio

b. 设置日志刷新频率：

set global innodb_flush_log_at_trx_commit=0;

setglobal sync_binlog=0;

• innodb_flush_log_at_trx_commit=0每秒将log_buffer刷新到logfile，并且将日志同步刷新到磁盘。该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。
• innodb_flush_log_at_trx_commit=1每次commit将log_buffer刷新到logfile，并且将日志同步刷新到磁盘
• innodb_flush_log_at_trx_commit=2 每次commit将log_buffer刷新到logfile，但是flush(刷到磁盘)操作并不会同时进行，每秒将日志刷新到磁盘。

4. 数据库中存在大量myisam表，在备份的时候导致slave延迟

由于xtrabackup工具备份到最后会执行flash tables with read lock，对数据库进行锁表以便进行一致性备份，然后对于myisam表锁，会阻碍salve_sql_thread停滞运行进而导致hang。

该问题目前的比较好的解决方式是修改表结构为innodb存储引擎的表。