In my recent work, I have a requirement to increase the throughput of MySQL which do heavy READ operation and light WRITE operation. We all know that single-node MySQL will defiantly not able to achieve it and running it in cluster mode(Master-Slave) would probably could help, as we could spread out the workload by scaling up the number of slave instance.
The Problem
I could get a pretty good result by scaling up the slave instance.
However, I and the team bump into a situation that
we have to ensure whatever we INSERT into MySQL would be found in the subsequent SELECT statement. The data replication in
MySQL slave is asynchronous, and the “lagging” in between caused data inaccuracy.
Stale reads - read operation that fetches an incorrect value from a source that has not synchronized an update operation to the value
The Solution
We have to sacrifice some throughput to ensure the result accuracy.
There are 2 builtin method by MySQL that could help us to solve this issue:
SHOW MASTER STATUS - provide the binlog file name and data position.
MASTER_POS_WAIT(log_name, log_position) - block until the replica has read and applied all the updates to the specified position
Steps:
INSERTdata to Master NodeSHOW MASTER STATUSto query out the Master Node’s binlog and positionSELECT MASTER_POS_WAITon the target slave node.SELECTdata from slave node.
