MySQL replication - how to read from slaves? manually?

Question

MySQL Manual - Replication:

"Scale-out solutions - spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves. "

I see how to setup replication which looks relatively simple, but I haven't seen how an application should communicate to slaves.

I assume the application would have to determine which slave to read from? The application will also have to know which server is doing writes? Or is it possible to have the application send all queries to the Master and have reads proxied to slaves?

Answer 1

I am adding an additional answer because Giovanni's one is completely right, but it only defines why scaling, how to do HA on the master and on the slaves, and how to split read queries among the slaves from an architectural point of view. I think OP question was more about how to do the read-write split itself.

Then answer depends on the software stack used, but many MySQL connectors/ORMs/frameworks, etc. allows you to define several connections as read-write or read-only. For example, the PHP connector for MySQL has a plugin for read-write splitting that can send read-only statements or those marked so to a different connection.

If you connector does not support it, you can always use a fabric-aware connector, a framework that can handle HA and read-write split for you, among other things.

Of course, you can always program it manually just by opening two connections, and use one for writing and another for reading, but managing connections at low level (reopening them if they fail, retrying statements, etc.) can be tricky.

The main problem here is that doing read-write split transparently can lead to many problems because the replication protocol is not synchronous. That means that if you perform a read just after a write, it may not have been replicated to the slave yet. So, for reads that have to be up-to-date, you will have to send those to the master. You may also want to monitor your replication lag so that it never goes over the maximum limit that your application requires. Or, in some cases (think, for example, banking) you may want to avoid asynchronous replication completely and use other protocols.

Think that there may be many processes that are heavy, such as reports or analytics that may not be affected by the replication lag, and that can safely be offloaded to the slaves. You code should be able to be "intelligent" enough to decide which service to use: in most cases you cannot do it transparently.

Answer 2

A good solution is to define two datasources for the applications. One datasource pointing to master is for writing. One datasouce pointing to slave node is for reading. The applications must be aware that data can be delayed with respect to the master. There are applications that could tolerate a slight delay on data, and other applications that must be sure that read data are fresh. For example data that are updated infrequently can be always read from slave like bills of the previous month. Data that have just been inserted must be read from the master.

A Virtual IP is assigned to the master. Another vip is assigned to the slave. The applications connect to the master and slave using vip. So if master node changes, the vip can be assigned to the new master, and the application don't have to be reconfigured. Using vips is usefull if slave is promoted to master, and master demoted to slave.

If you have more than one slave, a load balancer (aka haproxy) could be put before slaves. The vip for the slave can be assigned to the load balancer. When a connection toward slave is opened the load balancer hijacks the connection toward one of slaves.