MySQL主從同步延遲Seconds_Behind_Master很大
1.mysql> show slave status \G 查看延遲6天多，也是很神奇了
2. 查看 Master_Log_File和Relay_Master_Log_File 對(duì)比，可以說(shuō)明中繼日志差100多位置，所有定位是中繼日志問(wèn)題
3.查看中繼日志文件，密密麻麻的relay-bin文件
4.查看優(yōu)化配置
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;
查看磁盤(pán)讀寫(xiě)速度，有之前的800多K，上升到兩千左右，觀察 Seconds_Behind_Master也在慢慢變小
浙公網(wǎng)安備 33010602011771號(hào)