All we need is an easy explanation of the problem, so here it is.
I am looking to have one URL like
master.mydb.com, but under the hood I want to be able to access databases on
The reason for this is we have a massive number of near identical databases on each server, but very different data on each database. kinda like this:
slave1.mydb.com has databases p1 p2 p3 slave2.mydb.com has databases p4 p5 p6
all of those databases will have the exact same tables in them, but when my server accesses the data I just want to be able to connect to the cluster, and not have to worry about figuring out where the database p4 is stored, and making a connection slave2-mydb.com. In production we will probably have something more like slave[1-20].mydb.com with
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Of course internally you could have some DNS based mechanism like
p5.mydb.com to indicate these server has p5 and use this in the application’s connection to the database.
If you have same databases in all slaves then you need a load balancer, which you can achieve with haproxy, ngnix. If you want to split read write between slave and master then you can look into proxySQL. If you have different databases then you need to manage that in the connection strings.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂