MySQL Forums
Forum List  »  Backup

Backup mehod - for intervals and possible restore points
Posted by: Rob Martin
Date: May 07, 2015 07:47AM

Currently I have a nightly full backup that happens at 2am and then I take incremental backups every 2 hours after that.

Backup directory:
/backups/development/mysql/mys01ld/20150506

Sub directories:
drwxr-xr-x+ 4 mysql mysql 7 May 6 04:02 0400
drwxr-xr-x+ 4 mysql mysql 7 May 6 06:02 0600
drwxr-xr-x+ 4 mysql mysql 7 May 6 08:01 0800
drwxr-xr-x+ 2 mysql mysql 2 May 6 10:00 1000
drwxr-xr-x+ 4 mysql mysql 7 May 6 10:38 full
drwxr-xr-x+ 4 mysql mysql 5 May 6 12:00 1200
drwxr-xr-x+ 4 mysql mysql 5 May 6 14:00 1400
drwxr-xr-x+ 4 mysql mysql 5 May 6 16:00 1600
drwxr-xr-x+ 4 mysql mysql 5 May 6 18:00 1800
drwxr-xr-x+ 4 mysql mysql 5 May 6 20:00 2000
drwxr-xr-x+ 4 mysql mysql 5 May 6 22:00 2200

Here is the methods I use for my backup:

if [ "$CURRTIME" -ge 200 -a "$CURRTIME" -lt 300 ]
then
mkdir -p ${BACKUPDIR}/${DATE}/full
mysqlbackup --defaults-file=/usr/my.cnf -u $USERNAME -p$PASSWORD --backup-dir=$BACKUPDIR/$DATE/full backup ( ## line 1 ## )
mysqlbackup --defaults-file=/usr/my.cnf -u $USERNAME -p$PASSWORD --backup-dir=$BACKUPDIR/$DATE/full apply-log ( ## line 2 ## )
else
mkdir -p ${BACKUPDIR}/${DATE}/${CURRTIME}
mysqlbackup --defaults-file=/usr/my.cnf -u $USERNAME -p$PASSWORD --incremental --incremental-backup-dir=$BACKUPDIR/$DATE/$CURRTIME --incremental-base=dir:$BACKUPDIR/$DATE/full backup ( ## line 3 ## )
mysqlbackup --defaults-file=/usr/my.cnf -u $USERNAME -p$PASSWORD --backup-dir=$BACKUPDIR/$DATE/full --incremental-backup-dir=$BACKUPDIR/$DATE/$CURRTIME apply-incremental-backup ( ## line 4 ## )
fi

So my question is - when I run the full backup, lines 1 and 2 are executed and the /backups/development/mysql/mys01ld/20150506/full directory is updated
and can be used for restore back to the 2am time when the full backup was taken. However, when I run the incrementals every two hours after that, lines 3
and 4. Does the step in line 4 ("apply-incremental-backup") take the updates taken in line 3 from the time specific directory and apply those changes
captured to the full backup directory? If so, does that invalidate my ability to do a restore back to backup-0 when the initial backup was taken?

What I want to do is have a way to where I have a 2 hour roll back window every night in case needed - and possibly I can change that time
interval to every 10 minutes or as needed based upon the need of the business?

Options: ReplyQuote


Subject
Views
Written By
Posted
Backup mehod - for intervals and possible restore points
2153
May 07, 2015 07:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.