Percona Server 5.6.11-60.3 has introduced a new feature called Log Archiving for XtraDB. This feature makes copies of the old log files before they are overwritten, thus saving all the redo log for a write workload.

When log archiving is enabled, it duplicates all redo log writes in a separate set of files in addition to normal redo log writing, creating new files as necessary.

Archived log file name format is ib_log_archive_startlsn. The start LSN marks the log sequence number when the archive was started. An example of the archived log files should look like this:

ib_log_archive_00000000010145937920
ib_log_archive_00000000010196267520

In order to enable this feature, variable innodb_log_archive should be set to ON. Once the feature has been enabled, directory specified with innodb_log_arch_dir (MySQL datadir by default) will contain the archived log files.

Creating the Backup

To make incremental backups with Log Archiving for XtraDB, we need to begin with a full backup as usual. The xtrabackup binary writes a file called xtrabackup_checkpoints into the backup’s target directory. This file contains a line showing the to_lsn, which is the database’s LSN at the end of the backup. Create the full backup with a command such as the following:

If you look at the xtrabackup_checkpoints file, you should see some contents similar to the following:

backup_type = full-backuped
from_lsn = 0
to_lsn = 1546908388
last_lsn = 1574827339
compact = 0

Using the Log Archiving to prepare the backup

In order to prepare the backup we need to specify the directory that contains the archived logs with the xtrabackup –innodb-log-arch-dir option:

This command will use archived logs, replay them against the backup folder and bring them up to date with the latest archived log.

Output should look like this:

As you can see in the output Percona XtraBackup is applying the archived logs one by one to the initial backup we’ve taken. After this process is completed successfully backup can be restored.

How much time it takes per-log file to be applied depends on the archived log file size (which is the same size as the innodb_log_file_size) and memory being used (default 100M, more can be allocated with xtrabackup --use-memory option).

You can check the xtrabackup_checkpoints file and see that the backup_type has changed:

backup_type = full-prepared
from_lsn = 0
to_lsn = 1546908388
last_lsn = 1574827339
compact = 0

Archived logs can be applied to the same backup data several times, for example to decrease the time required for preparing the backup. For example if we would run the same command again:

Percona XtraBackup will apply the archived logs that were created after the last prepare statement, to the same backup.

3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Daniël van Eeden

Am I correct if I say that xtrabackup_56 will only backup data from InnoDB/XtraDB and nothing else?

I always use the innobackupex script as it will choose the correct binary and backup everything.

If this is correct then the tables backed up with this tool will lack a corresponding .frm file

I would expect MySQL incremental backups to backup InnoDB data since the last full backup and the complete MyISAM tables, frm files, views, etc.

0
Daniël van Eeden

I’ve setup Percona Server 5.6.14 in a sandbox and tried it myself. It indeed works and it is only the InnoDB data, not the frm files and MyISAM system tables. So for anyone trying this: This is not a complete backup and might only work if you want to restore a table for which the frm hasn’t changed. And as always: test your backup AND the restore.

0
Hrvoje Matijakovic

Daniël,

Yes, you are correct, when using xtrabackup binary for taking backups it will only backup InnoDB/XtraDB data, you need to take care of the rest by yourself. This backup is useful for users that have lots of changes on data, or insert new data but don’t change the table structure. Thank you for the feedback, I’ll make sure to provide additional information in the documentation.

0