Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

What if I had three master MySQL servers and wanted to use mysql-proxy to "dispatch" connections from my nineteen app servers to the appropriate MySQL server. That way all the app servers would be pointed at a single hostname or IP and that single mysql-proxy server would redirect the requests to whichever MySQL machine contained the database it needed. Each app server has multiple JBoss instances running on it and each JBoss instance has a single database located on a particular MySQL server.

Has anyone ever done this? I don't know what to call it. It's not load balancing and not clustering either. It would be a way to obviate the need to change config files on each app server when moving databases to different servers.

share|improve this question
    
Hello Janne. Thanks very much. My boss asked me to look into sharding and now I have a better idea of what he meant. Unfortunately our app does use stored procedures so sharding sounds like risky business. –  Matt Herzog Sep 8 '10 at 12:49
add comment

1 Answer

What you are looking after is called sharding. In theory MySQL Proxy can do what you want, but in practise if you are using stored procedures, triggers, or other fancier stuff, then MySQL Proxy might fail in the most marvelous ways you have ever seen, so be careful!

Basically you just need to use connect_server() piece of MySQL Proxy to alter the database connection settings according to your needs.

Depending on your needs, a modified version of MySQL Proxy called Spock Proxy can also do what you need, but it has several limitations (all listed on their web page). There's also MySQL Proxy based HScale, but it doesn't seem to be very active and I don't know how well it suits you. Take a look.

share|improve this answer
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.