Mysql – xtrabackup prepare – after dump or before restore

backupMySQLperconapercona-toolsxtrabackup

i have doubts about xtrabackup –apply-log.
is there any difference when use it? should it be done right after dump or before restore (even few months later)? seems stupid, but maybe i missing something.

documentation says this:

Prepare a backup in BACKUP-DIR by applying the transaction log file named xtrabackup_logfile located in the same directory. Also, create new transaction logs. The InnoDB configuration is read from the file backup-my.cnf created by innobackupex when the backup was made.

thanks for help 🙂

Best Answer

PURE OPERATIONAL STANDPOINT

You need to decide which protocol works best for you

PLAN A : AFTER DUMP

Run --apply-log immediately after backup, and you would have the most currently full backup. You would not have to spend your recovery time applying applying logs at the time of a restore (especially at an actual disaster recovery event). Thus, recovery would be only focus at such a dire time.

PLAN B : BEFORE RESTORE

This might work to your advantage if and only if you do not need the transactions that were accumulated after the point-in-time start of the backup. Such a situation would hardly make sense in a write-heavy environment.

Perhaps under these circumstances it would be better

  • Scheduled downtime
  • Small enough dataset
  • Acceptable window of time to backup (short time)
  • Additional backup of binary logs (extra insurance)

EPILOGUE

Whichever way one choose, make sure all the logs are good. If you apply the logs immediately, you'll know right then and there if the transaction logs are corrupt or not.

You also need to take into account how transaction-heavy the production system is. Why?

If the backup is recording incoming transactions as fast or faster than the backup process, then you have no choice but to go with PLAN B.

Related Question