4

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?

Improve this question

2 Answers 2

Reset to default
1

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:

  • All databases are identically named on all Masters
  • Replication Filters are global in scope. It would be very convenient if a filter could be scoped toward each MASTER_BIND interface 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

Improve this answer
1
  • Thank you for the answer. Yes, I found that the "for channel='x'" restriction cannot be added to the CHANGE REPLICATION FILTER, which is what would need to work in order for it to work per source. I did consider multiple single instances as a solution but was hoping there was a better way. We have 10 source instances now and will continue to grow, and managing that many (even with mysql_multi) is not something I look forward to :)
    – Alex
    Apr 3, 2014 at 14:05
1

Unfortunately, as for the current version (MySQL 5.7.16), replication filters per channel is not implemented yet!

I've filed a feature request "MySQL Bugs: #80843" and wrote a blog post "Can we set Replication Filters per channel in Multi-Source Replication?" for alternative solutions in such cases. Check it out for more details!

In brief: This feature is already implemented in MariaDB 10.x, so alternatively, you can switch to MariaDB or just use a MariaDB slave replicating from multiple MySQL masters if you are not enabling GTIDs (as GTIDs are not compatible between MySQL and MariaDB) where you can specify which DB should be replicated/ignored/rewrote from which master.

UPDATE: Replication filters per channel is now implemented starting from MySQL 8.0.1. Check out my post Replication filter per channel is now available in MySQL! for more details

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.