5

I need to replicate different MySQL databases from multiple servers into a single slave server. How can this be done? is there a way to define multiple master hosts?

Improve this question

4 Answers 4

Reset to default
5

As of October 2013 MySQL (> 5.7.6) supports multi-source replication.

MySQL Multi-Source Replication in MySQL Reference Manual

http://www.mysqlperformanceblog.com/2013/10/02/mysql-5-7-multi-source-replication/

http://on-mysql-replication.blogspot.com/2013/09/feature-preview-mysql-multi-source-replication.html

Improve this answer
0
4

I answered this controversial question back on Feb 03, 2012 : One slave, multiple masters MySql

By design, a Slave cannot be a Slave to Multiple Masters. Why? The command CHANGE MASTER TO only allows one MASTER_HOST parameter.

APPROACH #1

If each Master have a distinct Database that is mutually exclusive from other Master, you could have a Slave be a Slave to just one Master. Then, when all changes are posted, you would stop replication, run CHANGE MASTER TO command to point the Slave at a different Master. Then, run START SLAVE. I explained this setup in MySQL in star topology (StackOverflow).

The drawback to this approach would simply be the bookkeeping. This would require

STOP SLAVE;
SHOW SLAVE STATUS\G

and recording Master_Host, Relay_Master_Log_File and Exec_Master_Log_Pos from each Master and simply doing a round-robin from Master to Master. When you reach the next Master, you would take the previously recorded Master_Host, Relay_Master_Log_File and Exec_Master_Log_Pos and running

CHANGE MASTER TO
    MASTER_HOST='...',
    MASTER_LOG_FILE='...',
    MASTER_LOG_POS=...
;
START SLAVE;

APPROACH #2

According to the book

sdocns

Pages 373-375 has two diagrams of three DB Servers (M1, M2, S1)

DIAGRAM #1

  • M1 has the following characteristics
  • M2 has the following characteristics
  • S1 has the following characteristics
    • Slave of M2
    • Database db1 has real data
    • Database db2 has real data

DIAGRAM #2

  • M1 has the Database db1
  • M2 has the following characteristics
  • S1 has the following characteristics
    • Slave of M2
    • Database db1 has real data
    • Database db2 has real data

WARNING : As long as you do not run ALTER TABLE statements against a database that is made up of all BLACKHOLE tables, this approach is the closest you can get to Single Slave/Multiple Master Replication. You will have to live with possible latency issues.

Improve this answer
2
  • 1
    I believe MySql now supports multi-source replication. Someone needs to update this answer.
    – Lamar
    Commented Jul 16, 2019 at 10:34
  • This answer does not contain information about which version it applies to. With 5.7+ the answer seems to be different
    – NiKiZe
    Commented Apr 4, 2021 at 10:24
0

AFAIK, MySQL does not support multisource replication (a replica with more than one master). However, you can emulate this topology by:

  • either changing the replica to point at different master in turn
  • or using master-master replication and the Blackhole storage engine with a replica

Take a look at this for more details.

Improve this answer
0

There is not a native solution in MySQL to support multiple masters, but there are some theoretical alternatives:

Option 1. MariaDB 10, which has everything in MySQL 5.5, some things from MySQL 5.6, and some new things. This version is currently still in Alpha, but supports a single slave replicating continuously from multiple masters using Multi-Source Replication.

Option 2. MyISAM tables can theoretically be shared among multiple MySQL instances on the same machine, so it's possible -- on one machine -- to create an individual MySQL instance for each machine you need to replicate from, and then one more instance that is able to see those tables through creative use of symlinks. This would work even if the tables on the masters aren't MyISAM, as long as you prevent the tables from being altered in a way that would break the setup.

Option 3. Depending on your needs, you could set up multiple slaves on the same machine and expose the tables on "Slave B" to queries on "Slave A" by declaring federated tables on Slave A that connect to Slave B. With the two instances on the same machine, the FEDERATED queries are very responsive. I use this in a setup where I have reports that rely heavily on joining data from two disparate and otherwise-disconnected systems.

The important thing to remember about federated connections is that if you issue a query that requires a full table scan, the entire contents of the federated table will have to be transferred between the two servers for each query, so I typically tell the server where the federated table is defined that every column in the federated table has its own index, in addition to the real indexes existing on the server where the data physically exists. (Federated indexes are only logical, they do not take up space). This almost always prevents the optimizer from choosing a full table scan.

Improve this answer