That post is not really a question, but I think stackexchange is a good place to keep record of a head scratching try, to get a GTID based replication slave working. Just in case someone hit's the same problem.
Machine 1,2,3 debian8 mysql5.7
replication M1 > M2 > M3 All machines use GTID Replication is setup to use CHANNELS M1 has db1.1 and db1.2 M2 has db2.3
replicating db1.1 and db1.2 from M1 > M2 replicating db2.3 from M2 > M3
replication M1 > M2 works for a long time w/o any problems
Now I tried additional replicating db2.3 from M2 to M3 db2.3 is a 168GB beast growing nearly 2GB each 2days and the server on M2 is really busy on transactions.
So I used used percona's
innobackupex
innobackupex --apply-log
to backup databases on M2 and ssh'd it to M3
Started mysql on M3
SET @@GLOBAL.GTID_PURGED='as in xtrabackup_binlog_info';
CHANGE MASTER TO MASTER_HOST="X.X.X.X", master_user="myuser", master_password="mypass", MASTER_AUTO_POSITION = 1 FOR CHANNEL 'c2';
I needed to provide the channel here because I setup MASTER MASTER replication on M1 and M2 and innobackupex copied also the replication channel data from M1 > M2.
START SLAVE FOR CHANNEL 'c2';
And got
Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
First, in that scenery gtid_executed looks a bit different than in the manuals
6e3e5e02-a1d5-11e6-941e-002590771320:1-913775, 85a95426-71b4-11e6-9328-002590a49aea:363280-365686
That's because we have already a replication running and so, two set's of GTID's. The server UUID for
M1 : 85a95426-71b4-11e6-9328-002590a49aea
M2 : 6e3e5e02-a1d5-11e6-941e-002590771320
I didn't have any idea how to set that on the new slave. I tried every possible combination and still got into the error above.
I guessed, because I don't replicate from M1 > M3 I don't need to set the values for M1 on M3.
SET @@GLOBAL.GTID_PURGED='6e3e5e02-a1d5-11e6-941e-002590771320:1-913775'; START SLAVE FOR CHANNEL 'c2';
but without any luck.
Then I read somewhere that mysql is able to catch up GTID_PURGED by itself.
so I:
STOP SLAVE FOR CHANNEL 'c1';
RESET SLAVE ALL FOR CHANNEL 'c1';
CHANGE MASTER TO MASTER_HOST="X.X.X.X", master_user="myuser", master_password="mypass" FOR CHANNEL 'c2';
NOTE: this time only providing credentials and don't give MASTER_AUTO_POSITION = 1 START SLAVE FOR CHANNEL 'c2';
First try, I got an Error
Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;
OK, that I now how to deal with. In essential, it just says that the slave is trying to apply a statement, but cannot because of some missing data or data that is already there.
So now, let's set GTID another time, as it was on Master
SET @@GLOBAL.GTID_PURGED='6e3e5e02-a1d5-11e6-941e-002590771320:1-913775'; START SLAVE FOR CHANNEL 'c2';
And it worked, slave is trying to catch up with master now.
