MySQL replication - how to read from slaves? manually?

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?

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.

StackExchange Database Administrators Q#93823, answer score: 5