do we have apply-incremental-backup like parameter in Percona Xtrabackup

[abhinay....@citrix.com]

Hi I am an Oracle DBA and new to MYSQL world.

I am currently working on evaluating Percona Xtrabackup for replacing our existing MYSQL Enterprise Backup solution.

Currently our MYSQL backups patter is as below:

1. We maintain one full (base) backup.

2. On daily basis, we take a nightly incremental backup with the option (apply-incremental-backup), which merges the incremental backups with full (base) backup.

3. Once the incremental backups are merged, we drop that days incremental backup and this happens everyday.

I am trying to do similar thing with Percona Xtrabackup.

I have tested few scenarios which simulates same thing by below steps:

1. Take a full (base) backup with Percona Xtrabackup.

2. Take an incremental backup with Percona.

3. Only way to merge the incremental backups with full backup is by using option --prepare and --apply-log-lonly (which help is merging the incremental with Full backups).

Only problem i see in this process it, i am seeing some message like "Inno DB Shutdown complete" while i use above mentioned options (--prepare & --apply-log-only).

My questions are:

1. Does Shutdown Inno DB means literally shutting down Inno DB Engine and can that be considered as downtime? Since that is not desirable for our DB either in Master (or) Slave environments, since our slaves are used actively by application folks as read-only DB's.

2. Is there any similar parameter in Percona Xtrabackup that it has in MEB backups "apply-incremental-backup" which doesn't shuts down the InnoDB, but merges the incremental backups with Full backup.

[shahriya...@percona.com]

Hi,
1. "InnoDB Shutdown" message is not related to the MySQL Server. If it is possible to say in that way -> there is an "embedded InnoDB" within Xtrabackup.
The prepare step uses this “embedded InnoDB” to perform crash recovery on the copied datafiles, using the copied log file
During the prepare operation, xtrabackup boots up a kind of modified InnoDB that’s embedded inside it (the libraries it was linked against). So there is no downtime for MySQL Server.
You see these messages because of crash recovery operation.


2. You scenario for XB is pretty good. Say you have full + 5 incremental backups and you are going to take more incrementals. You need to keep in mind that, for final prepare you should run "--prepare" with last incremental backup, after that, your backup will be ready to recover from. In other words:
full -> --prepare --apply-log-only
inc1 -> --prepare --apply-log-only
inc2 -> --prepare --apply-log-only
inc3 -> --prepare --apply-log-only
inc4 -> --prepare --apply-log-only
...
inc20 -> --prepare (If you are going to recover)

[abhinay....@citrix.com]

Hi shahriya, thank you so much for your reply this is what i was looking for.

Regards

Abhinay

[abhinay....@citrix.com]

Hey shahriya, just another question related to same topic.

When we use the option --apply-log-only, it says that Xtrabackup will perform a rollback on uncommitted transactions, does that mean it performs rollback on uncommitted transactions on the entire MYSQL database (or) it just rollbacks the uncommitted tx's from the files it has saved during the backup?

I may sound stupid, but just wanted a clarification, since i know never an un-committed tx's comes to a file (not a persistent change), but i am trying to understand what percona is saying.

On Tuesday, June 14, 2016 at 10:20:41 PM UTC-7, shahriya...@percona.com wrote:

[Sergei Glushchenko]

Hi,

I double checked the DOC and it is saying:

The --prepare step for incremental backups is not the same as for normal backups. In normal backups, two types of operations are performed to make the database consistent: committed transactions are replayed from the log file against the data files, and uncommitted transactions are rolled back. You must skip the rollback of uncommitted transactions when preparing a backup, because transactions that were uncommitted at the time of your backup may be in progress, and it’s likely that they will be committed in the next incremental backup. You should use the --apply-log-only option to prevent the rollback phase.