CODEWITHSUNDEEP
mysqldatabase-replication
HyderA
HyderA

Reputation: 21371

Single slave - multiple master MySQL replication

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?

Upvotes: 10

Views: 13254

Answers (3)

David Purdue
David Purdue

Reputation: 270

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/

Upvotes: 3

Milind V. Acharya
Milind V. Acharya

Reputation: 411

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.

Upvotes: 2

Yahia
Yahia

Reputation: 70369

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)...

Upvotes: 4

Related Questions