10

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?

Charles
  • 50,943
  • 13
  • 104
  • 142
HyderA
  • 20,651
  • 42
  • 112
  • 180

3 Answers3

4

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

Community
  • 1
  • 1
Yahia
  • 69,653
  • 9
  • 115
  • 144
  • 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. – HyderA Sep 25 '11 at 12:20
3

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/

David Purdue
  • 250
  • 4
  • 9
  • 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. – David Purdue Aug 18 '15 at 07:00
  • Note that if you are planning to backup two servers with different databases by the same name this option is not viable. – Geoffrey Jan 02 '17 at 23:12
  • 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 https://mariadb.com/resources/blog/multisource-replication-how-resolve-schema-name-conflicts – David Purdue Jan 04 '17 at 00:04
  • 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. – Geoffrey Jan 04 '17 at 08:46
  • 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. – David Purdue Jan 05 '17 at 20:43
  • 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. – Geoffrey Jan 06 '17 at 03:34
2

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.

Milind V. Acharya
  • 401
  • 2
  • 6