I want to create an hourly backup of our MySQL database to a staging system using Percona XtraBackup.
Is it possible to prepare the $BACKUPBASE and then use this prepared backupbase as base for the following incremental backup (using the incremental-lsn option)?
My plans are:
- take a full backup once into $BACKUPBASE
-
then every hour:
- take an incremental backup on top of $BACKUPBASE
-
integrate the incremental backup into $BACKUPBASE using
innobackupex --apply-log $BACKUPBASE --incremental-dir=$INCREMENTALDIR innobackupex --apply-log $BACKUPBASE
-
remove $INCREMENTALDIR
To be more specific:
- what about the –redo-log option. When do i have to specify this option in this scenario?
- can you apply a incremental backup to an prepared $BACKUPBASE
Best Answer
No because the above mentioned --apply-log won't update LSN in xtrabackup_checkpoints, thus every next incremental backup will copy pages modified since the last full backup. That's not what you want to achieve
UPD
To implement your scenario you need:
Take full backup
Save the last LSN
Apply xtrabackup REDO log
Take incremental backup
Save last LSN
Apply incremental changes and REDO log
Remove directory with incremental backup
Repeat 4-7 as many as you need. /path/full will contain the last version of your database.
When you want to restore the database
Finish applying logs(= create REDO log):
Copy the backup copy to datadir
Fix permissions (Check that options in /path/full/backup-my.cnf are the same as in /etc/my.cnf (/etc/mysql/my.cnf for Debian))
Start MySQL