Connect to mysql server with one url but have multiple masters

All we need is an easy explanation of the problem, so here it is.

I am looking to have one URL like, but under the hood I want to be able to access databases on or

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: has databases
p3 has databases

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 In production we will probably have something more like slave[1-20] 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.

Method 1

This isn’t a replication problem to solve. Something like spider, max scale or vitess. Also, see the Percona toolkit for comparing tables.

Of course internally you could have some DNS based mechanism like 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 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply