I’m trying to set up MySQL replication where I can merge databases from separate source servers into a single destination server. Here’s my current setup:
Source servers:
Machine X has database called sales_db
Machine Y has database called inventory_db
Goal:
I want both sales_db and inventory_db to be replicated into Machine Z as one MySQL instance running both databases together.
I don’t want to run multiple MySQL instances on the target server. Instead, I need one MySQL service managing both replicated databases. Is it possible to configure master-slave replication this way? What would be the best approach to achieve this kind of database consolidation using MySQL replication features?
I did this exact thing six months ago when we consolidated our regional databases. Multi-source replication is definitely the way to go, but there’s stuff the other answers missed. Your target server needs serious resources - it’s handling writes from multiple sources at once. I hit performance issues right away because I underestimated the combined load. Use ROW format for your binlog, not STATEMENT. ROW worked way better with multiple sources. The monitoring gets tricky fast. You’ve got to track replication health for each channel separately, so you’ll need to update your monitoring scripts. Setting it up is easy with CHANGE MASTER TO and the FOR CHANNEL clause, but expect to do some tuning once you see your real traffic patterns.
Heads up - GTID conflicts will mess you up if they’re enabled on your sources. Almost destroyed my setup attempting this. I had to turn off GTIDs and switch to file positions instead. Pain in the ass, but ran smoothly after that.
Yeah, this is totally doable with MySQL’s multi-source replication. I set up something similar last year and it worked great. You’ll configure Machine Z as a replica with two separate channels - one for each source server. The trick is using distinct channel names when you run CHANGE MASTER TO for each source. Since you’re using different database names (sales_db and inventory_db), you won’t have any conflicts on the target. Enable log-slave-updates on Machine Z if you want to replicate from it later. You’ll need MySQL 5.7+ for this to work properly. Just keep an eye on replication lag for both channels separately - they can drift under different loads.
for sure, you can do that! Just go with multi-source replication on machine Z, make it a slave for both machines. since the db names are unique, there won’t be any clash. i’ve had the same setup for a while and it works just fine!