MySQL replication not syncing properly

1
votes

I have 2 mysql servers with circular replication set up between them. Doing a slave status check on both servers shows everything is OK. However, when an update on a table on server1 is done, server2 does not sync immediately. Sometimes it will sync after a few minutes. Sometimes it will only sync after mysqld on server2 is restarted.

I have

innodb_flush_log_at_trx_commit = 1

and

sync_binlog = 1

in my my.cnf files.

Any ideas?

1
mysqlreplication

1 Answers

1
votes

sync-binlog may be the prime suspect.

MySQL Documntation on sync-binlog says

A value of 1 is the safest choice because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

I would check your disk controller battery on the Master

I would also recommend using

[mysqld]
innodb_flush_method=O_DIRECT

I wrote about innodb_flush_method in the DBA StackExchange