1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.