Problem
Error on slave node replication :
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 '00019832-1111-1111-1111-111111111111:1,
00019832-1111-1111-2222-111111111111:1', and the missing transactions are '00019832-1111-1111-2222-111111111111:10'.'
Once we try to start the replication on slave node we receive the above error. Below is the complete show slave status output.
slave1 [localhost:19833] {msandbox} ((none)) > show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 127.0.0.1
Master_User: rsandbox
Master_Port: 19832
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 154
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
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 '00019832-1111-1111-1111-111111111111:1,
00019832-1111-1111-2222-111111111111:1', and the missing transactions are '00019832-1111-1111-2222-111111111111:10'.'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 19832
Master_UUID: 00019832-1111-1111-1111-111111111111
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 210423 05:28:31
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 00019832-1111-1111-1111-111111111111:1,
00019832-1111-1111-2222-111111111111:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
Here we can see that slave node is trying to fetch the GTID from the master node which has already been purged on the master node. To fix this issue, see the solution steps.
Solution
Here based on the show slave status output, we can say the Master_UUID is “00019832-1111-1111-1111-111111111111″ .
Master_UUID: 00019832-1111-1111-1111-111111111111
Executed_Gtid_Set: 00019832-1111-1111-1111-111111111111:1,
00019832-1111-1111-2222-111111111111:1
So we need to make sure that, the GTID for all the UUID which are present on the “Executed_Gtid_Set” are available on the master node.
I.e. The GTIDs for all below UUID :
Executed_Gtid_Set: 00019832-1111-1111-1111-111111111111:1,
00019832-1111-1111-2222-111111111111:1
Once we connected to the master and saw the “show master status” like below, we found that on Master node, GTID is still available for slave’s master UUID “00019832-1111-1111-1111-111111111111:1-2″ i.e. GTID is not purged yet.
master [localhost:19832] {msandbox} ((none)) > select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 00019832-1111-1111-1111-111111111111 |
+--------------------------------------+
1 row in set (0.00 sec)
master [localhost:19832] {msandbox} ((none)) > show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 307
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 00019832-1111-1111-1111-111111111111:1-2,
00019832-1111-1111-2222-111111111111:10
1 row in set (0.00 sec)
But above on master node, we can notice that there is one another UUID’s (00019832-1111-1111-2222-111111111111) which slave is also trying to fetch GTID but has already been purged upto “00019832-1111-1111-2222-111111111111:10“, so due to this reason slave is throwing the error as it can’t find the GTID from “00019832-1111-1111-2222-111111111111:2“.
Once we reviewed about this UUID, found this is errant GTID which is not related to any of the replication nodes UUID.
master [localhost:19832] {msandbox} ((none)) > select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 00019832-1111-1111-1111-111111111111 |
+--------------------------------------+
1 row in set (0.00 sec)
slave1 [localhost:19833] {msandbox} ((none)) > select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 00019833-2222-2222-2222-222222222222 |
+--------------------------------------+
1 row in set (0.00 sec)
To solve this, we will have to tell the slave node that this extra errant unknown UUID’s GTID (00019832-1111-1111-2222-111111111111:10) has already been executed on slave node.
slave1 [localhost:19833] {msandbox} ((none)) > stop slave;
Query OK, 0 rows affected (0.01 sec)
slave1 [localhost:19833] {msandbox} ((none)) > reset slave;
Query OK, 0 rows affected (0.00 sec)
slave1 [localhost:19833] {msandbox} ((none)) > reset master;
Query OK, 0 rows affected (0.01 sec)
slave1 [localhost:19833] {msandbox} ((none)) > set global gtid_purged='00019832-1111-1111-1111-111111111111:1,00019832-1111-1111-2222-111111111111:10';
Query OK, 0 rows affected (0.00 sec)
slave1 [localhost:19833] {msandbox} ((none)) > start slave;
Query OK, 0 rows affected (0.01 sec)
slave1 [localhost:19833] {msandbox} ((none)) > show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rsandbox
Master_Port: 19832
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 307
Relay_Log_File: mysql-relay.000003
Relay_Log_Pos: 520
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 307
Relay_Log_Space: 723
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 19832
Master_UUID: 00019832-1111-1111-1111-111111111111
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 00019832-1111-1111-1111-111111111111:2
Executed_Gtid_Set: 00019832-1111-1111-1111-111111111111:1-2,
00019832-1111-1111-2222-111111111111:10
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)