Mysql backup strategy using Innobackupex/Xtrabackup :
If we are planning to automate backup of our Mysql production instance, we can think of two approaches :
- A full backup followed by cumulative backups i.e All incremental backups with last full backup as base directory.
- A full backup followed by differential backups i.e All incremental backups with last incremental/Full backup as base directory.
Both approaches have their own benefits and drawbacks.
The first approach would need little bit more backup storage since we would always perform incremental backup from the last full backup checkpoint. Also the time required to perform incremental backup would be more compared to the second approach.
The main drawback of having the second approach is that we would be spending more time during backup restore process, mainly we need to to prepare the full backup by applying all the subsequent incrementals to it.
A better approach would be definitely the first one since it saves our time while we need to restore the database to a particular point, we just need to apply required incremental to the base backup.
Below are few innobackupex examples :
Full Backup :
/usr/bin/innobackupex --slave-info --user=${User} --password=${Password} --defaults-file=${DefaultsFile} --compress /u01/mysql/backup/fullbackup/
We are using compress option which would compress our backups and save us atleast 2x times of backup storage. slave-info useful while backing up the slave server, generates xtrabackup_slave_info file with master information.
Incremental Backups : Read More