MySQL Replication issues on server placed in different continents I am running 2 cpanel servers with MySQL Replication set on them,  one in france the other in US. Running a database hungary site. Replication was setup to load balance as well as for failover reason with mysql master being in US.

The issue is slave sometimes lag behind and leaves some rows which creates difference in dbs.

I want to know what could be the possible reason behind this.

Latency between servers is 250ms which some times rises to 400ms or more but sometimes.
Nodes are 32 GB Octa core, tables with around 400k rows and total database size below 5GB and table count around 700. And connectivity at around 100 Mbps.
I know there is always room for optimizations though databases have been optimised.
Our Application receives 10 -15 hits per second in peak time and every hit creates 2/3 database queries.

What could be a possible solution to this problem? What are standard practices followed in the industry for similar situations.
MySQL's replication is based on binary log shipping.

Binary log are flushed whenever a transaction commits. Master server sends logs to slave servers, then slaves replays received logs. Moreover, logs are basically replayed in order, in 5.6 or earlier.
So, when there are a huge bunch  of logs, the lags  may occur because the slave has to replay those logs, as well as those logs have to be send to the slave.

Simply put:


Master                   Slave
begin;
queries;
commit;
flushing binary log
                lag1:sending binary log

                         lag2:replaying binary log


Since MySQL 5.7's parallel replication feature has improved, to use the 5.7 might be the answer you want. See document: slave-parallel-workers and slave-parallel-type.

In addition, if slave's workload is very heavy, lags may also occur since slave is prevented to replay logs.

These are just my experiences. I don't know whether they can be applied to your environment.

Addition: compressing binary log, which other people suggests, is also a good solution, i think.