I have some problems concerning replication (not exactly master-slave).
We use MySql server v5.5.30.
Replication is enabled with this :
This seems to work perfectly.
A script is executed hourly.
- Do a flush-log to rotate binary logs.
- Uses mysqlbinlog to split the (previous) hourly binary log file per database
mysqlbinlog --short-form --database db_A previous_bin_log_file --result-file=db_A.sql
mysqlbinlog --short-form --database db_B previous_bin_log_file --result-file=db_B.sql
mysqlbinlog --short-form --database db_C previous_bin_log_file --result-file=db_C.sql
- Transfer these sql file to a remote server.
On the remote server, another script
- Check if md5sum is correct
- Inject each sql file in a local copy of the databases.
grep -v 'SET \@\@session.pseudo_thread_id' db_A.sql | mysql -h localhost -D db_A -f -uuser -ppassword
After a while it seems the database are desynchronized, there are records missing on the remote host database copy.
On the source server, no errors are detected (also not for the mysqlbinlog).
When injecting the sql files on the remote host, no errors are sent to stderr nor terminal and md5checksum is correct.
We use a similar method on an older version of mysql with update_log and it works perfectly. We now test binary logs to replace update_log (deprecated).
Can anyone help me to find out why I loose records and solving this issue ?
Thank you for your help.