I need to configure MySQL replication where multiple source servers send data to one replica server. The tricky part is that all my source servers have databases with identical names like main_database. However, I want these to end up with unique names on the destination server such as main_database_server1, main_database_server2, and so on.
Here’s my current setup:
-- Source Server A has database: company_data
-- Source Server B has database: company_data
-- Source Server C has database: company_data
-- I want replica to have:
-- company_data_A (from Server A)
-- company_data_B (from Server B)
-- company_data_C (from Server C)
I’m wondering if MySQL provides any built-in functionality to rename databases during the replication process. Has anyone managed to set this up successfully? What configuration options should I look into?
MySQL’s multi-source replication doesn’t provide built-in functionality for automatic database renaming. However, you can achieve this by using the replicate-rewrite-db option in your my.cnf file. This allows you to map source database names to unique names on the replica, for example, using replicate-rewrite-db=main_database->main_database_server1. Make sure to set up separate replication channels for each source server and use appropriate CHANGE MASTER TO instructions with channel identifiers. It’s crucial to carefully plan your database schema and foreign key relationships, and I recommend testing the setup in a development environment before going live, as changing rewrite rules post-setup can complicate matters.
yeah, totally doable but a bit tricky. You’ll need to set up each replication channel separately using the replicate-rewrite-db parameter. for channel one, do something like replicate-rewrite-db=company_data->company_data_A, then repeat for the others. just make sure your channel names are unique when configuring the masters - otherwise things get messy fast.