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
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
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
On env1 server, when first full backup is created, run prepare with apply-log-only
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.
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
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.
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
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.
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?
Can you please let us know what exactly is the issue you are seeing?
PXB-2506 is about checking if the backup is fully prepared before we allow it to be copied and used as a datadir. Allowing users to skip it doesn’t bring any benefit from our point of view. Having a not prepared backup used as datadir results in error.
Hi Marcelo.
I plan on using incremental backups that are applied to a base backup regularly (and archived aside).
So basically I keep a full backup with applied logs but without rolling back the uncommitted stuff on a remote fs.
If I want to restore/build a replica off of this base backup I have to copy it somewhere first and perform a full prepare for --copy-back to work.
I’d probably just rsync’d the thing, but my setup includes external tablespaces.
@korjj, you need xtrabackup to execute the --prepare once again in order to get the undo(rollback) phase applied otherwise your instance will not be consistent. xtrabackup uses a different redo structure from mysqld server.
I still do not fully understand why blocking the --copy-back will have any implications on your case. This is a safe guard to not let you start the server if a full prepare has not been performed on the target backup dir.
Yes you can manually copy the files, however, your datadir will not be consistent.
Hi Marcelo.
Could You please clear this up for me?
Apart from performing rollback, what else full prepare does (after --apply-log-only) that is required for a consistent backup?
Wouldn’t mysqld perform that step on the first start with restored data?
From the top of my mind, I remember that we also take care of DD updates, which we normally skip if you run --apply-log-only.
In summary, it’s not advised to make part of the crash recovery under PXB and part under the server. This is basically running a kill -9 in the MySQL process while has finished the apply phase of the crash recovery.