Setting up multi-source replication (MySQL 8.0) with GTID based replication

Last week I posted a blog on how to setup multi-source for MySQL 5.7 using GTID based replication, in this blog we will do the same using MySQL 8.0.

Most of the procedure are the same, there are only two things that we need to change are;
1) Procedure for creating the replication user.
2) Fetching the GTID value from the MySQL dumpfile.

1) Create replication user for MySQL 8
MySQL 8 uses a new authentication plugin so to create the replication user will differ from MySQL 5.7. The procedure for 5.7 was with:

mysql> CREATE USER 'repl_user'@'slave-host' IDENTIFIED BY 'repl_pass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'slave-host';

With MySQL 8 you need to create the user like:

mysql> CREATE USER 'repl_user'@'slave-host' IDENTIFIED WITH sha256_password BY 'repl_pass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'slave-host';

Another option is to configure MySQL to use old authentication method, then you do not have to modify the CREATE statement above. This can be done by setting configuration variable default_authentication_plugin to "mysql_native_password".

2) Changed format of GTID_PURGED information in dumpfile
The GTID_PURGED information in the dumpfile now include a comment like:

SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'dfa44283-d466-11e9-80ec-ec21e522bf21:1-6';

To fetch the GTID you now have to use below command that will remove also the new comment:

cat dumpM1.sql | grep GTID_PURGED | perl -p0 -e 's#/\*.*?\*/##sg' | cut -f2 -d'=' | cut -f2 -d$'\''

The github page and the scripts to create a test environment have also been updated!