Can set this kindf of replication 1

[JackTheRussel]

Hi.

Is it possible to make replication like this:

I have one machine (master1) where I have one database called db1. And I have different machine (master2) where I have one database called db2.

I also have third machine (slave) where I have two databases:
db1 and db2. Now the slave database db1 would be replicated with master1 database db1 and slave database db2 with master2 db2 ?

Is this possible ?

master1           master2
--------         --------
|      |         |       |
| db1  |         | db2   |
|______|         |_______|

         
         slave
        ---------
        |        |
        | db1    |
        | db2    |
        __________

Any help would be nice.

 

[LucL]

A slave can only have one master, but a master can have many slaves. This is assuming you don't start hacking up the mySQL code yourself.

There is a way around that though using federated tables.

What you do is have the slave replicate DB1, and then create federated DB and federated tables of db2 on the slave (call it fed_db2). Then on the slave create a new database db2 with the same structure of db2 on the master. Then you can use a stored procedure to regularly populate the db2 slave table with data from the fed_db2 table.

It's not true replication, and it does require some changes (such as adding mod_date TIMESTAMP to see which records in the fed_db2 might need to be updated) but if you shard the databases properly or if you're not working with billions of rows then it's a good system.

Also, you could install another copy of mySQL on the slave (if it can handle that), with diferent paths, and slave db2 using normal replication on the secondary mySQL copy.

Good luck
Luc,

 

[LucL]

Also, you could have master1 replicate itself to master2 and master2 replcate itself to master1, so you would have both copies of the DB on each master, then you could just hook the slave up onto one of the masters (which has both dbs) and replicate off of it normally.

 

[JackTheRussel]

Thank you very much Luc!
Now I know how to set replication between databases.