First off thanks to percona, ive been very impressed with your webinars, products, customer service, etc. Class Act!
I’ve redoing our current backup strategy and will be using innobackupex, as we have both databases with both InnoDB tables and MyISAM tables.
I’ve read and reread just about every piece of documentation produced by percona about innobackupex and to me it is unclear when to prepare the backup. Immediately after the backup or right before the restore?
It appears to be right before the restore, is this correct?
If this is the case I guess I am missing something, because I am confused how that would work if i tried to restore something that was months old. At that point the binary logs will have been rotated a few times now. Does this even matter?
Preparing the backup comes in play on second stage after taking the backup. Without preparing the backup InnoDB data files will not be in consistent stage. Now for when to prepare the backup either during restore or right after taking the backup i would say both of strategies are right. After taking the backup if you need to restore it on other server you can prepare it on the source server or on the destination after copying the backup directory to destination server and prepare it with innobackupex --prepare.
However, if you prepare the backup right after taking the backup will make your restore faster because during restore you only have to copy the backup directory contents to destination mysql server datadir. You can read more about preparing the backup here
It appears to be right before the restore, is this correct?
Let say your backup is one month old, and you need to restore it after month. Remind you your backup will be restored at one month old position regardless if you prepare the backup at the same time when you took the backup or when you are going to restore it. If you need to restore it to latest, you can take regular backups with full+incremental scheme and on top of that you can take binary log backups for point in time recovery. For more on it you can check documentation here,
NOTE: Incremental backups only works with InnoDB/XtraDB engine tables, for MyiSAM it will be copied entirely each time an incremental backup is performed.
Thank you for the response. Related to the ‘one month old scenario’. The backups dont also back up the the binlogs is this correct? I am not sure if i am getting the 100% correct terminology but with just backups and no binlogs if i wanted to restore to 30 days ago and somepoint within that day i wouldnt be able to do it without binlogs correct?
The scenario in my head is restore to 30 days ago and zip forward 12 hours after that point. So if i had a backup at 30 days ago and 29 days ago, with binlogs I would be able to do this. I guess my question here is then i am not seeing the binlogs in the backup dir that innobackupex creates, am i seeing this correctly or is that info in one of the other files? From what I can gather it pretty closely reflects the log files (minus binlogs) and db source files.
Once you prepare the backup you will have a backup that is up to date as far as the time the backup was initially completed. So if you finished that backup 30 days ago, the backup would be 30 days old. So to get that backup up to current data, you have a few options:
Replication, but then the master would have to have binlogs for the past 30 days, which is unlikely in most cases.
Backup the binlogs separately as you mentioned and apply them as needed.
Do incremental backups like mirfan mentioned to roll your full backup forward by applying all the incremental backups you’ve taken over the past 30 days in this example.
Don’t forget that mysqlbinlog shipped with 5.6 can stream the binary logs from the master so that you can keep the 30 days plus of binary logs with a very simple setup. This could allow you to get a very close point in time recovery.