Add flag file to control backup finish time.
Description
Environment
Smart Checklist
Activity
Satya Bodapati 5 hours ago
This is only one side of the story. We also want a backup to be fully prepared. So, it's better to use another process/job that looks into full backup and incremental backup folders and prepares them.
So, in case of upgrade failure, only the last few increments should be applied.
Satya Bodapati 5 hours ago
instaead of continuous redo copy, I would propose
Lets assume we target the LSN gap to be less 100MB
LSN_gap=inifinity
while (LSN_gap < 100MB) {
if LSN_gap == infinity
backup_LSN= take full backup
else
backup_LSN = take incremental backup
sleep if required.
Get server flushed_to_disk_LSN
LSN_gap = flushed_to_disk_LSN - backup_LSN
}
Satya Bodapati 5 hours ago
imho, just copying redo logs is a burden. I think this will effective increase the backup preparation time. I don't see the backup prepare steps anywhere mentioned below.
I think, in the above scenario, you can take a full backup and take incremental backups that should significantly reduce the downtime.
Also, if it is a multi engine backup, just copying redo logs wont work.
Julia Vural 7 hours ago
Thank you for the report. There has not been activity on this ticket for a while. We are closing it for housekeeping purposes. If you still believe it is happening on the latest version, feel free to contact us.
Pep Pla September 14, 2021 at 2:15 PMEdited
@Lalit Choudhary yes, this way we can control when the backup effectively ends.
When you are going to perform a database change or upgrade, often a previous backup is required. But, or there is a gap between the backup end and the start of the maintenance operation, or the backup has to be included in the maintenance window total time. The idea is to have a parameter, for example, named --end-backup-flag-file that does the following:
create the named flag file when the backup starts.
once the backup is done, it checks if the file still exists, if it does, it continues copying the logs until the file is removed.
This way we can tell the time that the backup finishes.
Use case:
MySQL 5.7 to 8 upgrade. The maintenance window starts at 01:00 AM
Without flag file:
at 01:01 stop application.
at 01:02 start backup
at 06:00 end backup
at 06:10 start upgrade
at 06:50 completed upgrade
at 07:00 start application
Total downtime: 6 hours
With flag file:
at 18:00 start backup
at 01:01 stop application
at 01:02 remove flag file and end backup
at 01:10 start upgrade
at 01:50 completed upgrade
at 02:00 start application
Total downtime: 1 hour