Using XtraBackup to consolidate

[marcoshaw]

I don’t have the versions of XtraBackup handy (if it might matter).

I’m thinking about consolidating a Percona 5.5 cluster with a newer Percona 5.7.

I want do take the 5.5 cluster offline (or quiesce it by offlining all clients), do an XtraBackup backup, then restore that in my 5.7 (then repoint my clients to the new DB cluster).

The thing perhaps is that this is a consolidation meaning I have DBs A/B/C on 5.5 and X/Y/Z on 5.7. Excluding the users that I need to manually copy over, is this something XtraBackup can do, without blowing away my existing X/Y/Z DBs on 5.7?

[Marcelo_Altmann]

Hi Marcosshaw.

It’s doable with a few caveats:

1. In order to restore only A/B/C you will require to use selective restore approach of xtrabackup https://www.percona.com/blog/2020/04/10/percona-xtrabackup-backup-and-restore-of-a-single-table-or-database/ . However, this requires --file-per-table which is not default in 5.5

2. The restore approach will require you to either perform it in parallel on all 5.7 nodes or leave only a single node in the cluster and force SST once to other nodes once you have finished.

3. You need to disable pxc_strict_mode .

If your 5.5 database is not too big, perhaps a logical backup will make more sense.

[marcoshaw]

My 5.5 database is 1.5TB. It takes 3 hours to backup.

In general… Might it take roughly the same amount of time to complete a backup as it would be to complete a restore?

[Marcelo_Altmann]

I forgot a very important point on your scenario. You should never do a binary backup upgrade skipping a version, so the correct path in case you want to use xtrabackup would be 5.6 first then 5.7. In that case I would advise you to first upgrade to PXC 5.6, validate, upgrade to pxc 5.7, validate then backup and consolidate.

Talking about xtrabackup it all depends on the amount of transaction it has to replay on the restore.

[marcoshaw]

If I follow… You’re saying you do not recommend that I do my backup on 5.5 using XtraBackup, then do my restore on 5.7 using XtraBackup (different versions and all)?

You’re saying the backup/restore should not be done if I’m using different XtraBackup versions (and different PXC versions).

So… I guess it’s a “logical backup” after all.