MySQL主从延迟解决方法的归纳和总结

MySQL主从延迟情况共分为以下四类
一.MIXED模式无索引或SQL慢
在从库上
pager grep -v Sleep;show processlist 查看到正在执行的SQL
解决方法:
1.SQL比较简单，则检查否缺少索引，并添加索引。例如update操作where条件无索引。
2.SQL比较复杂，例如insert into select from语句，如果select里包含group by,多表关联，可能效率会比较低。这类可以到主库把binlog_format改为row.

二.主库上有大事务，导致从库延时
解决方法：
找开发人员沟通，增加缓存，异步写入数据库，减少对DB的大量写入。

三.主库写入频繁，从库压力跟不上主库导致延时
此类原因的主要现象是数据库的IUD（增改删）操作非常多，slave由于sql_thread单线程的原因追不上主库的写操作。
解决方法：
1.升级主库的硬件配置,比如ssd、fio
2.设置日志刷新频率:
set global_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(刷到磁盘)操作并不会同时进行，每秒将日志刷新到磁盘。

四.数据库中存在大量myisam表，在备份的时候导致从库延迟
由于xtrabackup工具备份到最后会执行flush tables with read lock,对数据库进行锁表以便进行一致性备份，然后对于myisam表锁，会阻碍slave sql_thread线程停滞运行而导致hang。
该问题目前比较好的解决方法是修改表结构为innod存储引擎的表。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15498/viewspace-2128217/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15498/viewspace-2128217/