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 master.mydb.com, but under the hood I want to be able to access databases on slave1.mydb.com or slave2.mydb.com.

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.

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 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 🙂

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

Leave a Reply