search

ShahriyarR / MySQL-AutoXtraBackup

MySQL-AutoXtraBackup commandline tool written in Python 3 based on Percona XtraBackup
https://autoxtrabackup.azepug.az/
MIT License
140 stars 79 forks source link

Full-backup preferred run time #350

Closed gitmstoute closed 3 years ago

gitmstoute commented 5 years ago

I'm running autoxtrabackup on a cron every two hours, with a 1 day full_backup_interval.

Because I initialized the cron job during business hours, my "daily" full backup occurs at 2pm. It would be nice to control this so that it can run the full backup during off hours, and thus only incrementals during business hours (e.g. midnight, or 4am). This would also make the archives align with days; so each archive represents 1 date of activity (it doesn't span from 2pm monday to 1:59pm tuesday) in my example.

Hopefully this makes sense, but I'm not sure how it would look for other intervals (e.g. weekly).

ShahriyarR commented 5 years ago

@gitmstoute Hi, So what is the issue here? :) Did not get the point)

gitmstoute commented 5 years ago

It would be more of a feature request, nothing is broken.

ShahriyarR commented 5 years ago

Yeah, but the same thing I did not get the feature request :dancer:

gitmstoute commented 5 years ago

For example, if I schedule autoxtrabackup to run every 6 hours (6am, noon, 6pm, midnight). Currently it goes like:

day1: Jan17 noon-full, jan17 6pm-inc, jan18 midnight-inc, jan18 6am-inc. ... Archive day 1 -> Jan17.tar.gz day2: Jan18 noon-full, jan18 6pm-inc, jan19 midnight-inc, jan19 6am-inc. ... Archive day 1 -> Jan18.tar.gz

If I wanted to restore some table to Jan18 6am, I would need to know that the closest incremental backup is actually stored in the Jan17 archive.

My proposal is an option to make it have a preferred full-backup time (in this example, midnight):

day1: Jan17 noon-full, jan17 6pm-inc Archive day 1 -> Jan17.tar.gz day2: Jan18 midnight-full, Jan18 6am-inc, Jan18 noon-inc, Jan18 6pm-inc Archive day 2 -> Jan18.tar.gz

In this case, it does the Jan 18 full backup at midnight, because I configured that the full backup should occur at midnight. Now, each Archive (e.g. Jan17) only contains the full+incremental backups from that day.

Not sure if there's too much use for it but it was something I thought would be nice to have.

ShahriyarR commented 5 years ago

I can convert it to feature request and wait for your contribution for sure :)

gitmstoute commented 5 years ago

For more context, the slave I run autoxtrabackup falls behind master while doing some of the decompress > prepare > compress & archive stages; So with this feature, those stages would also occur overnight when there is less load on the server.