MySQL Replication - multiple masters (different d/bs) replicated to same slave server

2
votes

I have a MySQL Replication setup up and running:

Database A (Server 1 as master) --> Database A (Server Z, acting as the slave)

I now want to use the same Slave Server (Z) to be a slave of two other databases; totally different databases (e.g. B and C), not other copies if Database A.

I've followed this simple guide: https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql

But when it comes to adding the second database to the slave server in /etc/my.cnf, I don't see how it's possible to have more than one profile.

Basically I am thinking I need both of these blocks in my.cnf on the slave server but it won't be that simple, right?

log_error="/var/log/mysql/error.log"
server-id=2
log_bin="/var/log/mysql/mysql-bin.log"
binlog_do_db=database_a

log_error="/var/log/mysql/error.log"
server-id=3
log_bin="/var/log/mysql/mysql-bin.log"
binlog_do_db=database_b

Is it possible, or do I literally need a new slave server for each unique database I want to replicate?

Is it possible to, on the slave server, have specific conf files for each database, e.g. /etc/databasea.cnf and /etc/databaseb.cnf so I can specify different settings for each database?

Thanks in advance

1
mysqldatabasereplicationdatabase-replicationmaster-slave

1 Answers

2
votes

Multi-source replication is only available in MySQL starting with 5.7.6 or MariaDB starting with 10.0.1.

Multi-Source Replication enables a replication slave to receive transactions from multiple sources simultaneously. Multi-source replication can be used to back up multiple servers to a single server, to merge table shards, and consolidate data from multiple servers to a single server. Multi-source replication does not implement any conflict detection or resolution when applying the transactions, and those tasks are left to the application if required. In a multi-source replication topology, a slave creates a replication channel for each master that it should receive transactions from.

Source: