About xtrabackup prepare step and its logic with binlog

Hi,
In the full backup page, there is one note at prepare step
If you intend the backup to be the basis for further incremental backups, you should use the xtrabackup –apply-log-only option when preparing the backup, or you will not be able to apply incremental backups to it. See the documentation on preparing incremental backups for more details.

What’s difference if with or without -apply-log-only option? Since we have one innodb cluster, and we may use full backup to recover slave node immediately, does it mean we have run prepare without this option?
If yes, from the documentation, this full backup could not support incremental backup again, am i right?

Another question we’d like to know is, from backup command result, I don’t find any binlog, does it mean xtrabackup not handle binlog. If yes, does it mean we have to reset group replication (reset master, reset slave) on nodes after one node is recoveried, since we find binlog sync is error-prone, if master’s and slave 's binlog is not handled carefully with the datafiles.
Could you please share some information about xtrabackup on backup and restore Innodb cluster

Regards,
-Liang

Answer to your first half of question.

apply-log-only will only apply the redo logs and stops. Without the apply-log-only, It will move to the next step of recovery that is undo the transaction that is not commited.

So for example

begin;
insert into t1 values(100);

take full backup

commit;

take incremental backup

if you prepare full backup with apply-log-only and merge the incremental backup you will see transaction 100 in the database.

if you prepare full backup without apply-log-only. then t1 will not have value 100 in database. Since it is rollbacked by PXB. Also now you can not merge it with incremental

1 Like

Thanks for the reply.
If I prepare full backup with without apply-log-only, and restore the full backup on another database, will new database will not have value 100 either? Since we do the hot backup, I guess this thing could not be avoid.
This backup is safe to [restore], even though the operation skipped the rollback phase. If you restore it and start MySQL , InnoDB detects that the rollback phase was not performed, and it will do that in the background.

If we’d like to replicate or store backup (on env1) to other database(s) in different enviornments (env2) later (periodically), can following steps work

  1. On env1 server, when first full backup is created, run prepare with apply-log-only
  2. After every 5 hours, we do incremental backup, and run prepare with apply-log-only as well. So that we have a bit latest backups.
  3. At anytime, we need to restore databases on env2, we
    a. copy the latest backup to env2 machines
    b. Stop databases on env2
    c. Run restore command to restore databases with backup from env1
    d. start databases on env2

Regards,
-Liang

1 Like

when PXB did full backup 100 was not committed. So if you restore the backup. this transaction will be rollback. It will not be present.

Before restore, you have to prepare your backup once without apply-log-only. and the rollback with the part of it that prepare

steps LGTM to me just add before 3.c prepare the backup without --apply-log-only.

1 Like

Thanks.
On env1, once latest incremental backup is done, I will do the prepare with the apply-log-only immediately to save time before restore
When restore such prepared backup to env2, do you mean I still need to run the prepare again, but without apply-log-only, to let it undo uncommit? But from the following note, once database is restored and started with apply-log-only backup, it can detect the rollback phase wat not performance and do it in the background
This backup is safe to [restore], even though the operation skipped the rollback phase. If you restore it and start MySQL , InnoDB detects that the rollback phase was not performed, and it will do that in the background.

1 Like

In 8.0 series you should run --prepare without apply-log-only once before restore. that could work in 5.7 series.
PXB will not allow that in future release. [PXB-2506] Error out if backup is not prepared during copy-back - Percona JIRA.

1 Like

Thanks. Our MySQL version is 5.7. So following steps a~d is ok on xtrbackup2.4 + MySQL5.7?
At anytime, we need to restore databases on env2, we
a. copy the latest backup to env2 machines
b. Stop databases on env2
c. Run restore command to restore databases with backup from env1
d. start databases on env2

Regards,
-Liang

1 Like

I would still suggest to do the final prepare without the --apply-log-only. Let PXB do the rollback for you and few other things. and then start the instance with server binary.

1 Like

OK. let me confirm one thing on other side. I run the prepare with --apply-log-only based on the latest incremental backup env1 first, and will it block run another prepare without --apply-log-only on env2 again?

Regards,
-Liang

1 Like

no it will not block. you can run first prepare with --apply-log-only and then without it.

1 Like

Thanks for the information.

2 Likes