How can I set up a multi-source master/slave relation with masters having the same database names?
I am using the MySQL labs 5.7.2 release with multi-source replication. One of the use cases for multi-source replication is centralized backup of many mysql servers (https://dev.mysql.com/worklog/task/?id=1697). I want to set up an environment for this purpose. However, we have multiple database masters that all have the same database names. There doesn't appear to be any way of rewriting the database name on the slave to avoid naming collisions (like prefixing it with host or channel name).
I found the replicate-rewrite-db option (http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_replicate-rewrite-db), which sounded promising but doesn't seem to apply to this case.
Is there any way to accomplish this on the slave side, or is the only option to prefix the names on the master side?
Best Answer
I briefly searched MySQL Documentation
I located the Rewrite DB Option embedded in a new mechanism : CHANGE REPLICATION FILTER
It allows you to manipulate the Rewrite DB stuff without restarting MySQL.
UPDATE 2014-04-02 15:48 EDT
After reading the MySQL 5.7 Docs, I can safely say that CHANGE MASTER TO and CHANGE REPLICATION FILTER still cannot help you. Here is why:
MASTER_BINDinterface name. At this point, MySQL 5.7 does not do that.ALTERNATIVE
Perhaps you should set up multiple MySQL Instances on the Slave Server. Make each MySQL Instance use standard MySQL Replication. You can scale down all the buffer sizes and settings for all the Instances on the Slave.
Then, you can set up a mysqldump from each of the MySQL Instances to different folders.
If you want to learn how to setup multiple MySQL Instances on a single server, please see my old posts
Sep 30, 2011: Running multiple instances on the same hostSep 17, 2012: mysqlservice command syntax