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?
migrated from webmasters.stackexchange.com Apr 18 at 16:25
This question came from our site for pro webmasters.
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.
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
The drawback to this approach would simply be the bookkeeping. This would require
According to the book
Pages 373-375 has two diagrams of three DB Servers (M1, M2, S1)
WARNING : As long as you do not run
AFAIK, MySQL does not support multisource replication (a replica with more than one master). However, you can emulate this topology by:
Take a look at this for more details.
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
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.