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
Best Answer
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.--copy-back
it). You can delete the saved transaction log as it is not needed any more.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.