Can I restore incremental backups to a new server?

I am trying to design a backup strategy, with one weekly, full backup and six daily, incremental backups. However, when reading through Percona’s incremental backup page, it looks like I have to run the --apply-log step on the system, where the backed-up database lives, in order to apply the logs.

Does this mean that I can’t use the incremental backups to restore a database to a new, blank server?

1 Like

Hi, the --apply-log can be run on a different server. Just make sure you copy the base backup and the incremental.

1 Like

I tried that after I asked this question - I keep getting a lot of errors when I run --apply-log on the full backup:

InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-re
covery.html for information about forcing recovery.
InnoDB: Page [page id: space=1179, page number=0] log sequence number 100016388047 is in the future! Current system log sequence number 40077642257.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-re
covery.html for information about forcing recovery.

And when I run it on the incremental backup, it takes incredibly long - after more than 1 hour it was still working on ibdata1:

Applying /x/jan/laptop/backup/1//ibdata1.delta to ./ibdata1...

Should I be worried?

1 Like

Can you post here the exact steps you are following?
Basically to restore with incrementals you should be applying with --redo-only to roll forward the base snapshot, then apply the incrementals, and finally a --apply-log as follows:

innobackupex --apply-log --redo-only BASE-DIR
innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1
innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2
innobackupex --apply-log BASE-DIR

1 Like

Yes, of course - and thank you for taking the time. This is in a test environment, so the database is relatively small, and there isn’t a lot of activity:

rgs-mi-db-3 root = mysql -V
mysql  Ver 14.14 Distrib 5.6.51-91.0, for debian-linux-gnu (x86_64) using  7.0
rgs-mi-db-3 root = mysqld -V
mysqld  Ver 5.6.51-91.0 for debian-linux-gnu on x86_64 (Percona Server (GPL), Release 91.0, Revision b59139e)

Basically, I followed the standard procedure:

  1. Take a full backup: innobackupex --user=myuser --password=mypwd /backupfs/0
  2. Incremental backup: innobackupex --user=myuser --password=mypwd --incremental /backupfs/1 --incremental-basedir=/backupfs/0 --no-timestamp

These were taken only a short while apart, maybe 1 hour, since this is just for testing. The incremental backup turned out to be surprisingly big - the full backup was some 24GB, the incremental 18GB.

I copied the backups to a newly installed system with no mysql server, only percona’s client and tools:

$ innobackupex --version
xtrabackup: recognized server arguments:
innobackupex version 2.4.23 Linux (x86_64) (revision id: 3320f39)
$ mysql -V
mysql  Ver 8.0.20 for Linux on x86_64 (MySQL Community Server - GPL)

The, I ran the --apply-log:

xtrabackup --prepare --apply-log-only --target-dir=/backupfs/0
...
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-re
covery.html for information about forcing recovery.
InnoDB: Page [page id: space=1179, page number=0] log sequence number 100016388047 is in the future! Current system log sequence number 40077642257.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-re
covery.html for information about forcing recovery.
...

Apart from a large number of those, it finished without other error messages.

1 Like

Are you sure you recursively copied all the files in the backup dir? seems some kind of mismatch between ibdata1 and the table space files. Perhaps you had some leftover files from a previous test? make sure you start from an empty dir just in case

1 Like

Yes - I tar’ed up the backup directories:

# tar jcvf 0.tar.bz 0
# tar jcvf 1.tar.bz 1

Then copied them to a new system and unpacked them:

$ ll
total 7068220
drwxr-x--- 10 jan.andersen jan.andersen       4096 Jul  8 14:56 0/
-rw-r--r--  1 jan.andersen jan.andersen 4120477406 Jun 28 15:28 0.tar.bz
-rw-rw-r--  1 jan.andersen jan.andersen    2728482 Jul  8 14:56 0.trc
drwxr-x--- 10 jan.andersen jan.andersen       4096 Jul  8 15:32 1/
-rw-r--r--  1 jan.andersen jan.andersen 3114628632 Jun 28 16:14 1.tar.bz
$
$ # There is no /var/lib/mysql, so no leftover files:
$ ll /var/lib/mysql
ls: cannot access '/var/lib/mysql': No such file or directory

I ran the --apply-log under strace - hence the .trc file; I don’t know if this is interesting:

$ grep ibdata *trc
16694 stat("./ibdata1", {st_mode=S_IFREG|0640, st_size=2965372928, ...}) = 0
16694 openat(AT_FDCWD, "./ibdata1", O_RDWR) = 3
16694 openat(AT_FDCWD, "./ibdata1", O_RDWR) = 3
16694 stat("./ibdata1", {st_mode=S_IFREG|0640, st_size=2965372928, ...}) = 0
16694 openat(AT_FDCWD, "./ibdata1", O_RDONLY) = 3
16694 openat(AT_FDCWD, "./ibdata1", O_RDWR) = 3
16694 stat("./ibdata1", {st_mode=S_IFREG|0640, st_size=2965372928, ...}) = 0
16694 stat("./ibdata1", {st_mode=S_IFREG|0640, st_size=2965372928, ...}) = 0
16694 stat("./ibdata1", {st_mode=S_IFREG|0640, st_size=2965372928, ...}) = 0
1 Like

igroene was right - the problems I had seem to have been caused by using innobackupex instead of xtrabackup. Once I switched to using xtrabackup throughtout, everything worked without any problems.

1 Like