Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
In a similar vein (but, I believe, is production ready): scalebase.com/solution/architecture –  Nick Chammas Jan 24 '12 at 23:02
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
haproxy is a TCP load balancer and can be used for other protocols, including MySQL, though it may not be the best choice. –  Aaron Brown Jan 25 '12 at 1:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.