Single slave - multiple master MySQL replication

12,382

Solution 1

Best way to achieve that would be a real backup solution... but when you do it the way you describe define one slave instance per master - this way you stay flexible, for example if any change is needed you could even move one or more of the slave instances to another machine without any influence on the other slaves/masters...

EDIT - as per comments:

For a description on how to setup multiple instances of MySQL on the same machine see for example

This keeps you even flexible enough to have different MySQL versions in parallel (identical per slave/master combination)...

Solution 2

Since 2011 the environment has changed a bit. Replication from multiple masters is now supported in MySQL 5.7 and MariaDB 10 - but they use slightly different syntax.

MySQL 5.7: http://www.percona.com/blog/2013/10/02/mysql-5-7-multi-source-replication/

MariaDB 10: https://mariadb.com/kb/en/mariadb/documentation/managing-mariadb/replication/standard-replication/multi-source-replication/

Solution 3

You will have to use multiple instances of mysql. If you are having 6 masters and you are trying to put all the slaves on one physical machine, you will need 6 instances,

Each mysql slave instance will connect to a different master.
Each slave instance will be on a different port
The datadir for each slave instance will also be separate.

Assuming you are using some flavour of unix OS, you could set up a cron job to stop and start each instance to keep the load average to a minimum.

It would be good to let one slave instance run and catch up with its master before doing hot backup. The same steps would apply to next slave and so on. Each time you start up a slave instance you shutdown the other mysql slave instances can be shutdown to keep load avg. to a minimum.

12,382

Author by

HyderA

Updated on July 20, 2022

Comments

HyderA almost 2 years

I need to replicate different MySQL databases from multiple servers into a single slave server. How can this be done? Do I need a separate MySQL instance on the slave for each master server? Or is there a way to define multiple master hosts?

We're basically using the slave as a hot backup for multiple websites. Should we be thinking about clustering instead of replication?
HyderA over 12 years

Good point on the mobility. We currently have 6 masters. What kind of issues would I run into with multiple instances of MySQL, I have never done that before.
David Purdue over 8 years

As a side note, I actually got this going, with replication from several MySQL 5.0 and MySQL 5.5 masters going to a single MariaDB10 slave.
Geoffrey over 7 years

Note that if you are planning to backup two servers with different databases by the same name this option is not viable.
David Purdue over 7 years

Yes it is. MySQL replication has a facility to replicate a database on the master to a database on the slave with a different name, using the --replicate-rewrite-db option. See mariadb.com/resources/blog/…
Geoffrey over 7 years

This does still not make it viable if your intent is to backup a group of servers where someone else controls the database names. For example an administrator of a cPanel server where any user can create a database.
David Purdue over 7 years

Well, yes, you have come up with a narrow enough use case that this solution does not fit. I still reckon I answered the original question, though, and there are many, many use cases where multi-master replication is useful.
Geoffrey over 7 years

My comment was not stating your answer was incorrect, my comment was informative to let others know of the potential name clash issue that can occur. Also this is no narrow use case, I am a consultant for large infrastructure deployments and this question/use is often requested by my clients.