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.

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.