Using Replication for Scale-Out

Question

Having read Using Replication for Scale-Out, how can I route different queries to different servers, for example, SELECTI want to route to slaves and NON-SELECT to master. I assume as loadbalancer I can use haproxy, but I didn't find it's possible to distict between queries on the level of haproxy? In addition, let's say someone have reached master directly, how can master identify that this is SELECT query and show be sent to slave, or to loadbalancer.

Answer 1

There is a project called MySQL Proxy that is attempting to handle read/write splitting in the proxy layer, but it is not production-ready. You can read more about the issues on this page, paying attention to the known issues section.

For now, as others have noted, you have to handle the routing through your application.

Answer 2

Its the job your web client to determine if a transaction it is about to send to the database is read-only or read-write and choose its designated slave or the master accordingly.

Answer 3

I'm not aware of any proxy software to do this for you. haproxy is an HTTP level proxy, this will not have any impact on your database. You will need to implement this as part of the business logic of your web application.

At the heart of your persistance layer, you should be able to intercept these calls and "route" them to the appropriate database based on the type of transaction being executed. For example, SELECT queries could be sent to your read-only servers, but UPDATE/INSRET/DELETE queries would be sent ot the master server.