Got fatal error 1236 from master when reading data from binary log

MySQL & MariaDB Percona XtraBackup
1

When restoring a replication slave this error is returned by show slave status\G;

Last_IO_Error: 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. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master’s binary log expiration period. The GTID set sent by the slave is ‘3ccc2392-45ed-11e7-bc98-4061862b8d34:1-35942477’, and the missing transactions are ‘fe51e8df-b7c4-11e9-be21-4061862b8d34:1-23882704’.’

I follow this tutorial

Get the GTID

line=$(head -n 1 "$mysql_restore_dir/xtrabackup_binlog_info")
gtid=$(echo $line | awk -F '[ ,]' '{print $3}')

Restore slave

xtrabackup --prepare --target-dir="$mysql_restore_dir"
xtrabackup --move-back --target-dir="$mysql_restore_dir"
chown -R mysql.mysql $mysql_dir

mysql -u root -p$slave_pass -e "RESET MASTER;\
	SET GLOBAL gtid_purged='$gtid';\
	CHANGE MASTER TO MASTER_HOST='$master_host', MASTER_USER='repl', MASTER_PASSWORD='$master_pass', MASTER_AUTO_POSITION=1;\
	START SLAVE"

The slave is restored from a master backup less than 24 hours old. The binlog on the master expires in 14 days

2 Likes
2

Hi @Clark ,

Your error means that the binlog containing one of the transaction is already purged on your master.
By looking at how you are extracting GTID position after the backup I can see an error:

marcelo@marce-bld:/tmp/backup$ cat xtrabackup_binlog_info
binlog.000005	236	674a625e-976e-11e5-a8fb-125cab082fc3:1,88ae3dea-7042-11eb-a69c-d45d64347a19:1-2
marcelo@marce-bld:/tmp/backup$ line=$(head -n 1 xtrabackup_binlog_info)
marcelo@marce-bld:/tmp/backup$ echo $line
binlog.000005 236 674a625e-976e-11e5-a8fb-125cab082fc3:1,88ae3dea-7042-11eb-a69c-d45d64347a19:1-2
marcelo@marce-bld:/tmp/backup$ gtid=$(echo $line | awk -F '[ ,]' '{print $3}')
marcelo@marce-bld:/tmp/backup$ echo $gtid
674a625e-976e-11e5-a8fb-125cab082fc3:1

Your awk command is getting only one subset from the GTID list. From my example, when the slave tries to connect passing 674a625e-976e-11e5-a8fb-125cab082fc3:1 as gtid_executed, the master will verify that slave has not yet executed subset from 88ae3dea-7042-11eb-a69c-d45d64347a19:1-2 and will have to send those. In case this has already been purged from master you will see the error.

I suggest you to adjust your awk to use the default delimiter (spaces):

marcelo@marce-bld:/tmp/backup$ line=$(head -n 1 xtrabackup_binlog_info)
marcelo@marce-bld:/tmp/backup$ gtid=$(echo $line | awk '{print $3}')
marcelo@marce-bld:/tmp/backup$ echo $gtid
674a625e-976e-11e5-a8fb-125cab082fc3:1,88ae3dea-7042-11eb-a69c-d45d64347a19:1-2
1 Like
3

Ahh, great… But the solution would be (the file has two lines)

line=$(head -n 1 "$mysql_restore_dir/xtrabackup_binlog_info")
gtid="$(echo $line | awk '{print $3}')$(tail -n 1 "$mysql_restore_dir/xtrabackup_binlog_info")"
2 Likes
4

I think the tutorials on Percona should be slightly changed. It doesn’t come clear you need both strings to setup GTID rep

2 Likes