MySQL Forums
Forum List  »  Replication

Replication fails with "Got fatal error 1236 from source when reading data from binary log: 'Cannot replicate because the source purged required binary logs"
Posted by: Shobhana Sriram
Date: November 24, 2023 11:00AM

I have 2 master instances (M1 and M2) with 1 replica each (R1 and R2) - all running MySQL 8.0.34 version with GTID enabled in all. All schema names are unique in M1 and M2 and there is no overlap. Also, R1 is configured to ignore replicating one schema (say myExcludedSchemaName).

I wanted to copy a few tables from M2 to M1. So I executed the following mysqldump command in R2:

sudo mysqldump -umyUserName -p myExcludedSchemaName myTableName1 myTableName2 myTableName3 myTableName4 --lock-tables=false > partialDump.sql

There was a warning when this command was executed:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

However, the dump file got generated. So I tried to restore these tables in M1 using the following command:

mysql -umyUserName -p myExcludedSchemaName < partialDump.sql

This failed with the following error:

ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

So I regenerated the dump again by passing another flag: --set-gtid-purged=OFF

There were no warnings when the dump was generated or restored in M1. However, the replica has stopped after this. The I/O thread has stopped with the following error:

Got fatal error 1236 from source when reading data from binary log: 'Cannot replicate because the source purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new replica from backup. Consider increasing the source's binary log expiration period. The GTID sets and the missing purged transactions are too long to print in this message. For more information, please see the source's error log or the manual for GTID_SUBTRACT'

How can I resolve this error?

Options: ReplyQuote


Subject
Views
Written By
Posted
Replication fails with "Got fatal error 1236 from source when reading data from binary log: 'Cannot replicate because the source purged required binary logs"
303
November 24, 2023 11:00AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.