Sample Header Ad - 728x90

Restoring a primary mysqldump on a R/O replica

1 vote
1 answer
91 views
EDITED: I've encountered the following error message on my R/O replica which supports GTIDs and had not been running for a few weeks: > Last_IO_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 set sent by the replica is '61ab9e32-e5b9-11ed-b4aa-525400003002:1-12069,a4228b68-e5b8-11ed-ac40-525400003001:1-72270', and the missing transactions are 'a4228b68-e5b8-11ed-ac40-525400003001:72271-74629' As a result, in order to "recreate" the replica, I tried to restore a backup made on a primary (which also supports GTIDs) onto the replica. The backup made on the primary:
mysqldump --ssl-ca=letsencrypt.pem --ssl-mode=VERIFY_IDENTITY --user=root \
 --host=primary.example.com --port=3306 '--password=mypassword' --all-databases \
 --events --flush-logs --flush-privileges --opt --routines --single-transaction \ 
 --set-gtid-purged=OFF --source-data=1 --triggers > db_filename
The backup restored on the replica: ```shell mysql --ssl-ca=letsencrypt.pem --ssl-mode=VERIFY_IDENTITY --user=root \ --host=replica.example.com --port=3306 '--password=mypassword' ERROR 1776 (HY000) at line 24: Parameters SOURCE_LOG_FILE, SOURCE_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when SOURCE_AUTO_POSITION is active. EDITED: I have access to the backup of all master binlogs. Is there a proper way to reinject them into the primary in order to be able to recreate the replica?
Asked by jean-christophe manciot (121 rep)
May 7, 2025, 03:35 PM
Last activity: May 12, 2025, 03:36 PM