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
Last activity: May 12, 2025, 03:36 PM