MySQL Replicate Different Databases from DIfferent Masters

Question

I'm relatively new to MySQL replication. In a nutshell I have a MySQL 5.1 server instance on 3 Ubuntu Lucid Lynx servers.

ATM I have Server A (MASTER) which replicates a single database to Server C (SLAVE).

This has been running for a couple months without a problem.

I now want to have Server B (as MASTER) replicate a different single database to Server C (SLAVE).

I was looking into implementing this, but my initial reading seems to indicate that a replication slave server can't have two masters.

My question is, is this the case even when only single or select database are being replicated?

Keep in mind I do not wish to replicate the same database from different masters. I simply wish to replicate multiple separate database on a single server, from separate masters.

Answer 1

You can set a different master per database in MySQL.

The book high performance MySQL has a full chapter on this issue. I recommend getting hold of a copy and using the info in that.

http://oreilly.com/catalog/9780596101718/

You can read a copy of the relevant chapter online here:
http://oreilly.com/catalog/hpmysql/chapter/ch07.pdf

This is from the first edition, the second edition is more up to date, but chapter 8, which deals with replication is not freely available online.

UPDATE
The solution I'm talking about is only in the 2nd edition of the book, see this answer to a simular question: Is it possible to do N-master => 1-slave replication with MySQL?

Answer 2

In general you can't do this. You can't replicate from many-to-one (you can from one-to-many).

Also you can't really just replicate one database - cross-database updates in statement-based mode then become unreplicatable, which means your slave will fail (or become out of sync) as soon as someone does one on the master.

The standard solution is to install multiple mysql instances, which is far from ideal, but works.