Add flag file to control backup finish time.

Description

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:

  1. at 01:01 stop application.

  2. at 01:02 start backup

  3. at 06:00 end backup

  4. at 06:10 start upgrade

  5. at 06:50 completed upgrade

  6. at 07:00 start application

Total downtime: 6 hours

With flag file:

  1. at 18:00 start backup

  2. at 01:01 stop application

  3. at 01:02 remove flag file and end backup

  4. at 01:10 start upgrade

  5. at 01:50 completed upgrade

  6. at 02:00 start application

Total downtime: 1 hour

Environment

None

Smart Checklist

Activity

Show:

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 PM
Edited

 yes, this way we can control when the backup effectively ends.

 

Won't Do

Details

Assignee

Reporter

Labels

Priority

Smart Checklist

Created August 23, 2021 at 9:36 AM
Updated 5 hours ago
Resolved 7 hours ago

Flag notifications