I want to set up a one-master-one-slave replication. But I have a question. Suppose the read-load is distributed between the master and the slave. I first write to the master and then read from the slave. At this time the information has not reached the slave. So my read will fail. But I have written it! How is such a problem solved or avoided in practice?
2 Answers
For some use-cases, the (usually) small delay before the data appears on the slave isn't a problem.
For "critical" reads, you could send them to the master only.
Another solution would be to use so-called synchronous replication, though actual synchronous replication doesn't really exist. The technology used in Galera Cluster (used by MariaDB Cluster and Percona XtraDB Cluster) is virtually synchronous. The same is true for MySQL's InnoDB Cluster.
semi synchronous replication can solve the issue (depends on how large transactions will hit to master, if its heavy yes even semi sync will turnout into asynchronous replication)