2

i have been trying to read other questions, but i did not find a answer to suit me. So excuse me if i ask something that has been already asked.

I'm trying to organize my self and backup all my mysql servers with innobackupexec (since i only use Percona Mysql). I have read percona documentation and some other blog/posts but i do not understand if i need to run --apply-log immediately after full backup has been run. If i need to schedule a script as a daily basis backup, should i only script this?

innobackupex /data/backups

and should i only use

innobackupex --use-memory=4G --apply-log /data/backups/2010-03-13_02-42-44/

only when i need to restore the database?

i'm pretty confuse, hope to get here some clarification.

thank you

3

A quick 101 on Percona Xtrabackup:

Basically, what the tool does is copying the data live in an inconsistent way, but making sure it gets all the necessary changes that are happening at copy time, so it can revert it to a proper state. In other words, it does an "unclean full copy" and then a "controlled crash recovery" with a custom InnoDB instance. That is exactly why you need the two phases (the copy first and then the application of the log, where committed changes are reapplied and uncommitted changes are discarded).

That second part can be done on a different machine, without a database connection -that is why it is a separate command.

So you have two options: running the --apply-log as soon as the copy has ended or just before the restore.

  • Advantages of the first case: your copy is 100% ready to be restored in case of an emergency (just --copy-back it). You can delete the saved transaction log as it is not needed any more.
  • Its disadvantages: Once you have deleted the uncommitted changes, you cannot roll forward your full backup with following incrementals. Also, you will not be generating the extra information that may be needed for partial imports.

If you only intend to create in the future full backups and perform full restores, you can safely do it just after the copy finishes. If your apply-log phase is fast (or you do not care about the extra time and space of the transaction log) and you want to have extra flexibility (for partial/incremental backups), just wait until the start of the recovery.

  • Thank you for your precious answer. I have also found on percona's forum that apply log operation can be executed on another server even without mysql installed. This becouse when innobackupexec is issued a file with LSN number is generated and apply log is reading from this files what to update insinde ibdata. – x86fantini Aug 25 '14 at 5:18
  • i've got a question regarding the prepare: is the date/time of the backed up data the time when i started the the backup, or when i ran --apply-log? – Kilian Feb 21 '16 at 20:43
  • The backup time (what it is consistent to, not the autogenerated timestamp of the dir name) is when innobackupex executes FTWRL, the end of the back up process. However, if you do not apply the log, you can roll forward the backup date by creating an incremental backup (that when applied to the original backup will modify the "backup time"). – jynus Feb 25 '16 at 9:28
2

Unless your production database is mission-critical and restore time is of the utmost importance, I would generally suggest that you wait to run the apply-log until you need the backup.

Advantages of running apply-log immediately after taking a backup:

  • Your backup is ready for use immediately (with --move-back / --copy-back), saving you precious time in the event of an emergency

Advantages of not running apply-log until you need the backup:

  • You will be able to do table-level recovery (with the appropriate server versions)
  • On a server without many writes, each backup might be smaller, because the InnoDB log files haven't been created yet
  • You can take and compress a streaming backup to disk or over the network
  • Every time you don't run it, you save CPU cycles and memory

Regardless of which you choose, it might be worth putting a note to yourself somewhere in your backup folder, mentioning what's been done to take the backup and what steps still need to be taken to restore it.

  • Correct. I have done a innobackupexec with stream (xbstream) and compress. Then moved the compressed file to a new dummy server and with only percona-extrabackup installed i have first uncompress files, then apply log, then restored files to my new datadir. And it worked like a charm :). – x86fantini Aug 25 '14 at 5:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.