Description:
When trying to set up multi-source replication, it became apparent that mysqldump doesn't have an option to specify the channel to use, thus making it very difficult to set up.
If you look at the output for MySQL dump it contains a CHANGE MASTER STATEMENT, but there doesn't appear to be a way to make mysqldump include a CHANGE MASTER ... FOR CHANNEL 'channelName'
The only workarounds require twice as much disk space and twice the amount of time, so you can save it as a file, then manually edit the file before importing it, or importing one, then breaking replication, importing the second one, then starting replication on both. Neither of which is a particularly good solution.
How to repeat:
Set up two separate databases.
DB1 with the schema test1
DB2 with the schema test2
Set up a third database, DB3, to use as the multi-source SLAVE for DB1 and DB2 masters.
Run mysqldump on DB3 to get the test1 schema from DB1
mysqldump -h1.2.3.4 -P3306 -umysqldumpuser -ppassword --dump-slave --apply-slave-statements --include-master-host-port --single-transaction --databases test1 -E -R -q -c --max_allowed_packet=2G | mysql -S/var/run/mysqld/mysqld.sock -umysqldumpuser -ppassword
You now have test1 on DB3, replicating from DB1. However, it is using the "" channel, so you need to change this manually.
RESET SLAVE ALL FOR CHANNEL "";
CHANGE MASTER TO
. . .
FOR CHANNEL '<channel_name>;
If I now try and load test2 from DB2:
mysqldump -h1.2.3.5 -P3306 -umysqldumpuser -ppassword --dump-slave --apply-slave-statements --include-master-host-port --single-transaction --databases test2 -E -R -q -c --max_allowed_packet=2G | mysql -S/var/run/mysqld/mysqld.sock -umysqldumpuser -ppassword
I get an error:
"ERROR 3079 (HY000) at line 28: Multiple channels exist on the slave. Please provide channel name as an argument."
But there is (as far as I can tell) no option to specify the channel name to use.
Suggested fix:
Add an option to mysqldump so you can specify the channel name to use. Then this could be appended to the `CHANGE MASTER` statement inside the mysqldump output.