# About xtrabackup prepare step and its logic with binlog **URL:** \ **Category:** Percona XtraBackup\ **Created:** [February 17, 2022, 2:57am UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445 "2022-02-17T02:57:19Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![lcheng](https://avatars.discourse-cdn.com/v4/letter/l/9fc348/32.png) [@lcheng](https://forums.percona.com/u/lcheng)\ **Post date:** [February 17, 2022, 2:57am UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445/1 "2022-02-17T02:57:19Z")
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 ---
**Author:** ![Rahul\_Malik](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/rahul_malik/32/3580_2.png) [@Rahul\_Malik](https://forums.percona.com/u/Rahul_Malik)\ **Post date:** [February 17, 2022, 5:47am UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445/2 "2022-02-17T05:47:26Z")
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 ```auto 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 ---
**Author:** ![lcheng](https://avatars.discourse-cdn.com/v4/letter/l/9fc348/32.png) [@lcheng](https://forums.percona.com/u/lcheng)\ **Post date:** [February 17, 2022, 6:34am UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445/3 "2022-02-17T06:34:28Z")
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 ---
**Author:** ![Rahul\_Malik](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/rahul_malik/32/3580_2.png) [@Rahul\_Malik](https://forums.percona.com/u/Rahul_Malik)\ **Post date:** [February 17, 2022, 6:47am UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445/4 "2022-02-17T06:47:14Z")
> [@lcheng](#): > > 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? 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. > [@lcheng](#): > > 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. 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. ---
**Author:** ![lcheng](https://avatars.discourse-cdn.com/v4/letter/l/9fc348/32.png) [@lcheng](https://forums.percona.com/u/lcheng)\ **Post date:** [February 17, 2022, 6:57am UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445/5 "2022-02-17T06:57:29Z")
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. ---
**Author:** ![Rahul\_Malik](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/rahul_malik/32/3580_2.png) [@Rahul\_Malik](https://forums.percona.com/u/Rahul_Malik)\ **Post date:** [February 17, 2022, 7:03am UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445/6 "2022-02-17T07:03:49Z")
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](https://jira.percona.com/browse/PXB-2506). ---
**Author:** ![lcheng](https://avatars.discourse-cdn.com/v4/letter/l/9fc348/32.png) [@lcheng](https://forums.percona.com/u/lcheng)\ **Post date:** [February 17, 2022, 7:23am UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445/7 "2022-02-17T07:23:46Z")
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 ---
**Author:** ![Rahul\_Malik](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/rahul_malik/32/3580_2.png) [@Rahul\_Malik](https://forums.percona.com/u/Rahul_Malik)\ **Post date:** [February 17, 2022, 7:28am UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445/8 "2022-02-17T07:28:36Z")
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. ---
**Author:** ![lcheng](https://avatars.discourse-cdn.com/v4/letter/l/9fc348/32.png) [@lcheng](https://forums.percona.com/u/lcheng)\ **Post date:** [February 17, 2022, 7:32am UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445/9 "2022-02-17T07:32:30Z")
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 ---
**Author:** ![Rahul\_Malik](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/rahul_malik/32/3580_2.png) [@Rahul\_Malik](https://forums.percona.com/u/Rahul_Malik)\ **Post date:** [February 17, 2022, 7:38am UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445/10 "2022-02-17T07:38:20Z")
no it will not block. you can run first prepare with --apply-log-only and then without it. ---
**Author:** ![lcheng](https://avatars.discourse-cdn.com/v4/letter/l/9fc348/32.png) [@lcheng](https://forums.percona.com/u/lcheng)\ **Post date:** [February 17, 2022, 11:55am UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445/11 "2022-02-17T11:55:16Z")
Thanks for the information. ---
**Author:** ![korjjj](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/korjjj/32/6999_2.png) [@korjjj](https://forums.percona.com/u/korjjj)\ **Post date:** [August 14, 2023, 12:31pm UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445/12 "2023-08-14T12:31:36Z")
Hi. I think this [[PXB-2506] Error out if backup is not prepared during copy-back - Percona JIRA](https://jira.percona.com/browse/PXB-2506) should have been made a default but overridable behaviour. If I’m trying to restore from incremental base - it adds an unnecessary, time and disk consuming step. ---
**Author:** ![Marcelo\_Altmann](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/marcelo_altmann/32/11299_2.png) [@Marcelo\_Altmann](https://forums.percona.com/u/Marcelo_Altmann)\ **Post date:** [August 14, 2023, 1:32pm UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445/13 "2023-08-14T13:32:20Z")
Hi korjjj, 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. Waiting on your clarifications. ---
**Author:** ![korjjj](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/korjjj/32/6999_2.png) [@korjjj](https://forums.percona.com/u/korjjj)\ **Post date:** [August 14, 2023, 4:07pm UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445/14 "2023-08-14T16:07:13Z")
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. ---
**Author:** ![Marcelo\_Altmann](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/marcelo_altmann/32/11299_2.png) [@Marcelo\_Altmann](https://forums.percona.com/u/Marcelo_Altmann)\ **Post date:** [August 14, 2023, 7:21pm UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445/15 "2023-08-14T19:21:10Z")
@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. ---
**Author:** ![korjjj](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/korjjj/32/6999_2.png) [@korjjj](https://forums.percona.com/u/korjjj)\ **Post date:** [August 14, 2023, 11:27pm UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445/16 "2023-08-14T23:27:53Z")
From xtrabackup docs I get the impression that mysqld (percona server) would perform rollback on its own. > Even if the `--apply-log-only` was used on the last step, backup would still be consistent but in that case server would perform the rollback phase. ---
**Author:** ![korjjj](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/korjjj/32/6999_2.png) [@korjjj](https://forums.percona.com/u/korjjj)\ **Post date:** [August 21, 2023, 9:48am UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445/17 "2023-08-21T09:48:45Z")
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? ---
**Author:** ![Marcelo\_Altmann](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/marcelo_altmann/32/11299_2.png) [@Marcelo\_Altmann](https://forums.percona.com/u/Marcelo_Altmann)\ **Post date:** [August 22, 2023, 4:15pm UTC](https://forums.percona.com/t/about-xtrabackup-prepare-step-and-its-logic-with-binlog/14445/18 "2023-08-22T16:15:29Z")
Hi @korjjj , 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.