By Alex


2014-04-02 14:26:06 8 Comments

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?

2 comments

@Moll 2016-10-18 19:42:04

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

@RolandoMySQLDBA 2014-04-02 18:29:54

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

@Alex 2014-04-03 14:05:41

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 :)

Related Questions

Sponsored Content

1 Answered Questions

1 Answered Questions

[SOLVED] Multi-source replication from older masters

1 Answered Questions

1 Answered Questions

Track row origin in MySQL multi source replication

1 Answered Questions

2 Answered Questions

1 Answered Questions

[SOLVED] MySQL master/slave replication... structure not being replicated

  • 2013-01-11 22:57:58
  • BroknDodge
  • 3648 View
  • 2 Score
  • 1 Answer
  • Tags:   mysql replication

Sponsored Content