About --apply-log

MySQL & MariaDB Percona XtraBackup
1

Hi all,

I have a question about the --apply-log option.

  1. It is mandatory ? necesserally ? What the risks of do not use this option after my backup.

  2. If is mandatory, why this option is not included by default in innobackupex ? By example with the stream option, i make my backup, and my backup is compressed ok ?

innobackupex --stream=tar ./ | gzip - > /home/backup/backup.tar.gz

I must unzip backup.tar.gz for to apply the --apply-log option (C.f documentation), it’s crasy not ?

  1. If i do not my --apply-log. I have my backup. I wait one or two weeks. I want to restore my backup, can i to do the --apply-log empty risks ? and if i lost my server (crash?)

Thank you for your replies.

++

2

Hi,

  1. It is mandatory ? necesserally ? What the risks of do not use this option after my backup.

Well, first I’m going to explain how xtrabackup works. It copies the ibd files without locking the tables, that means that while you are copying a table it can be modified by your users, therefore you are making a inconsistent backup. While those ibd files are copied, there is another thread that checks the changes to the innodb log files and store them on another file.

–apply-log read that file with the changes from innodb log files and applied them to the tablespace. So, --apply-log makes your backup consistent and should be run before restoring the backup.

  1. If is mandatory, why this option is not included by default in innobackupex ? By example with the stream option, i make my backup, and my backup is compressed ok ?

Because not everybody wants to apply logs just after getting a backup. There can be lot of different reasons, I’m going to give you two examples where automatically applying the backup is not good idea:

  • apply-log is a time/resources consuming process so run it just after the backup is not always a good idea. Sometimes is better to move the backup to a server with spare power capacity to run the apply-log there.
  • if you want to get incremental backups, then you shouldn’t apply the base backup
  1. If i do not my --apply-log. I have my backup. I wait one or two weeks. I want to restore my backup, can i to do the --apply-log empty risks ? and if i lost my server (crash?)

You can do the --apply-log whenever you want, it doesn’t need a running mysql. It will just read a file from the backup with the changes it needs to apply, and will apply them. Here you have more information about what that --apply-log means:

http://www.percona.com/doc/percona-xtrabackup/innobackupex/p reparing_a_backup_ibk.html

Regards.

3

Thank’s.

But, i think that we will not use Xtrabackup in the future. Endeed, after several attempts we saw useless performances.

EG (full backup):

Our datadirectory : 32 Giga, multiple databases; MyIsam and InnoDB
Server : Core i7, 12 Go ram

mysqldump run at 5am; finish 5:22 am
xtrabackup run at 5:40am, finish 6:27am (oO)

  1. Time of backup with mysqldump (and compress to tar.gz included) : 22 min et 53 s

1 ) The time of backup with xtrabackup 47 min et 42 s
options in innobackupex :

–compress-threads=5 --defaults-file=/etc/my.cnf --stream=tar /home/backup/ful/backup_directory/ 2> /home/backup/full/log_innobackupex | gzip > /home/backup/full/FULL_backup_$DATE.tar.gz

4

hi ,
i had a question ,
what if some one delete records or tables after the innobackupex start and in between the backup process deletion commits ,so at the time of preparation it will apply transaction log and we wont get that data back then whats the use of backup?

5

With xtrabackup you will get a snapshot of the data exactly the same as it was in the same moment the backup finished. So, if during the backup you remove rows then those rows won’t be on the backup.