Description:
It is not possible to open multiple named replication channels within a single slave to a single master with selective replication of databases from master.
It is possible to define such configuration, but is does not work due to limitation of master to accept only single connection per slave, master does not distinguish that slave tries to open multiple connections for different named channels.
All tooling support suggests such setup is possible.
Documentation is unclear in this aspect, see https://dev.mysql.com/doc/refman/8.0/en/replication-channels.html. It states:
A replication channel encompasses the path of transactions transmitted from a master to a slave. In multi-source replication a slave opens multiple channels, one per master, and each channel has its own relay log and applier (SQL) threads.
But a few lines bellow:
A replication channel is also associated with a host name and port. You can assign multiple channels to the same combination of host name and port.
How to repeat:
Assume two business systems SystemA and SystemB, fully independent on business level, each of them updates only its own database. There are no cross database transactions, guaranteed on the business level.
1. Setup a replication master with two databaseses DatabaseA, DatabaseB. Configure both databases for binary logging, row based. SystemA uses DatabaseA, SystemB uses DatabaseB exclusively.
2. Setup a slave which is supposed to replicate DatabaseA and DatabaseB from the master. Each database will be replicated over a dedicated channel. DatabseA by ChannlelA, DatabseB by ChannelB as if DatabaseA and DatabaseB were on two different masters. ChannelA and ChannelB are configured with replication filters for selection of relevant updates.
When you start the replication on slave host, one replication slave kicks off the other with error message from master:
Got fatal error 1236 from master when reading data from binary log: 'A slave with the same server_uuid/server_id as this slave has connected to the master; the first event 'mysql-bin.000001' at 155, the last event read from '.\mysql-bin.000001' at 124, the last byte read from '.\mysql-bin.000001' at 155.'
When each of the setup channels is single active it works correctly. As soon as the second one is started, the one that was started first is kicked off by the master with the above error message.
Similar problem addressed partly by https://bugs.mysql.com/bug.php?id=72581 which when fixed, eliminated the problem of slaves kicking each other in infinitely.
Suggested fix:
Allow a single slave to open multiple replication channels to a single master, identify a connected slave in master by server id/uuid + channel name on the slave.
Now it is possible to setup:
a)
-MasterA with DatabaseA,
-MasterB with DatabaseB,
-Salve with ChannelA(MasterA(DatabaseA)->DatabaseA), ChannelB(MasterB(DatabaseB)->DatabaseB).
b)
-Master with DatabaseA and DatabaseB,
-SalveA with Channel(MasterA(DatabaseA)->DatabaseA),
-SlaveB with Channel(MasterB(DatabaseB)->DatabaseB).
It should also be possible to setup:
-Master with DatabaseA and DatabaseB,
-Salve with ChannelA(Master(DatabaseA)->DatabaseA), ChannelB(Master(DatabaseB)->DatabaseB).
