MySQL主从同步延迟Seconds_Behind_Master很大

1.mysql>  show slave status \G    查看延迟6天多，也是很神奇了

2. 查看 Master_Log_File和Relay_Master_Log_File 对比，可以说明中继日志差100多位置，所有定位是中继日志问题

 3.查看中继日志文件，密密麻麻的relay-bin文件

 4.查看优化配置

mysql> show variables like '%sync_binlog%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| sync_binlog   | 0     |

+---------------+-------+

1 row in set (0.01 sec)

 

mysql>

mysql> show variables like '%innodb_flush_log_at_trx_commit%';

+--------------------------------+-------+

| Variable_name                  | Value |

+--------------------------------+-------+

| innodb_flush_log_at_trx_commit | 1     |

+--------------------------------+-------+

1 row in set (0.00 sec)

 

mysql> show variables like '%autocommit%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit    | ON    |

+---------------+-------+

1 row in set (0.00 sec)

修改配置

mysql> set global sync_binlog=20;

mysql> set global innodb_flush_log_at_trx_commit=2;

查看磁盘读写速度，有之前的800多K，上升到两千左右，观察 Seconds_Behind_Master也在慢慢变小