XtraBackup Restore Issue – Database Not Recognized but mysqldump Works

1

Subject: XtraBackup Restore Issue – Database Not Recognized but mysqldump Works

Environment:

  • Backup Server:

    • OS: Ubuntu 20.04.6 LTS

    • MySQL Version: 8.0.42

    • XtraBackup Version: 8.0.35-32

    • Datadir: /data/mysql

  • Restore Server:

    • OS: Ubuntu 22.04.5 LTS

    • MySQL Version: 8.0.45

    • XtraBackup Version: 8.0.35-31

    • Datadir: /media/vdata/mysql

Backup Command Used:
xtrabackup --backup --target-dir=/backup/Mysqlbackup --parallel=4 --user=root --password=XXXX

Restore Steps:

  1. xtrabackup --prepare --target-dir=/backup/Mysqlbackup

  2. systemctl stop mysql

  3. rm -rf /media/vdata/mysql/*

  4. xtrabackup --copy-back --target-dir=/backup/Mysqlbackup

  5. chown -R mysql:mysql /media/vdata/mysql

  6. rm -f /media/vdata/mysql/auto.cnf

  7. systemctl start mysql

Observed Behavior:

  • Restore completes without error

  • MySQL starts successfully

  • Database folder (centraldb) exists in datadir

  • However, database is not properly recognized in some restore attempts

Validation:

  • Both source and restore servers show:
    SHOW DATABASES → centraldb only

  • Data consistency verified (row count matches)

  • mysqldump backup/restore works perfectly

Key Checks Already Done:

  • innodb_file_per_table = ON (both servers)

  • lower_case_table_names = 0 (both servers)

  • Datadir correctly configured

  • Backup contents verified (centraldb present)

  • Only one MySQL instance running

  • Permissions set correctly

Error Log Snippet:

  • “No existing UUID has been found… generating new UUID”

  • Server upgrade from 8.0.42 to 8.0.45 observed

  • No major InnoDB corruption errors seen

Concern:
It appears that MySQL initializes a new InnoDB dictionary (possibly due to early startup or redo log mismatch), causing XtraBackup restored data to not be properly recognized, while mysqldump works fine.

Request:

  • Guidance on ensuring proper InnoDB dictionary recognition after restore

  • Best practice to avoid UUID / initialization conflicts

  • Any known compatibility issues between MySQL 8.0.42 → 8.0.45 with XtraBackup 8.0.35

Additional Info (if needed):
Can provide full error logs, backup directory structure, and configuration files.

Thanks in advance for your support.

2

Hi @viswanatha. Could you share the backup, prepare and error log?

3

image
image1411×254 110 KB
Please find the error log.

4

Hi viswanatha,

When restoring try to use same or higher xtrabackup to avoid any incompatibilities or unknown bug.

This time you are backing up with -32 but restoring with -31 (older)

Regards

6

In ubuntu 22 its already the newest version.XtraBackup Version: 8.0.35-31

7

Could you try updating the package list?

sudo apt update

I can still see newer versions in Ubuntu 22.04.5 LTS:

# apt list -a percona-xtrabackup-80
Listing... Done
percona-xtrabackup-80/unknown 8.0.35-35-1.jammy amd64
percona-xtrabackup-80/unknown 8.0.35-34-1.jammy amd64
percona-xtrabackup-80/unknown 8.0.35-33-1.jammy amd64
percona-xtrabackup-80/unknown 8.0.35-32-1.jammy amd64
percona-xtrabackup-80/unknown 8.0.35-31-1.jammy amd64
percona-xtrabackup-80/unknown 8.0.35-30-1.jammy amd64
percona-xtrabackup-80/unknown 8.0.34-29-1.jammy amd64
percona-xtrabackup-80/unknown 8.0.33-28-1.jammy amd64
percona-xtrabackup-80/unknown 8.0.33-27-1.jammy amd64
percona-xtrabackup-80/unknown 8.0.32-26-1.jammy amd64
percona-xtrabackup-80/unknown 8.0.32-25-1.jammy amd64
percona-xtrabackup-80/unknown 8.0.31-24-1.jammy amd64
percona-xtrabackup-80/unknown 8.0.30-23-1.jammy amd64
8

image
image599×169 31.6 KB

9

Hi again!

Try with percona-release Configure Percona repositories - Percona Software Repositories

You can also downgrade the PXB package on the source to match PXB on the destination server. Or you can also download the tarballs from percona.com/downloads to match versions

Regards

10

Version Changed still the same issue DB not recognised.

image
image1762×265 124 KB

11

Hi again!

Reasons can be many.

Please start by checking that the backup completed successfully. The last line in the log should say “Backup completed successfully”.

Another possibility is wrong permissions. Check that file ownership of the files is for the “mysql” user and not root

Regards

12

Backup completed successfully, & permission is also ok.

image
image783×385 13.8 KB

13

Mysqldump and rsync are working fine, but xtrabackup fails. During backup/restore, MySQL starts successfully, but in the final stage it throws a “database not available” error.

image
image1823×273 125 KB

14

Checked with both Backup & Restore xtrabackup version 8.0.35-35 the same issue persists.

15

It’d be great if you can share the mysql error log. The current log that you shared seems to be application log.

16

image
image1416×388 204 KB

17

Also tried in another system, still the same issue, DB not available.

18

Hi again!

Can you please mention the steps you are doing to restore the backup?

Also, have you successfully used xtrabackup in the past for some other backup/server ? or is this your first time restoring with xtrabackup?

Regards

19

Please find the backup & restore procedure

Backup server:-

1)Insatall Percona software
2)Disable Crontab (crontab -u mysq1 -e)
3)create directory using mkdir Mysqlbackup command
4)xtrabackup --backup --target-dir=/backup/Mysqlbackup --parallel=4 --user=root --password=123456
5. Check /backup is mounted in new DB

Restore SERVER

1)Insatall Percona software
2)before restore once again prepar the backup
xtrabackup --prepare --target-dir=/backup/Mysqlbackup (xtrabackup --prepare --target-dir=/backup/Mysqlbackup)
3)sudo systemctl stop mysql
4)sudo rm -rf /media/vdata/mysql/*
5)sudo xtrabackup --copy-back --target-dir=/backup/Mysqlbackup --datadir=/media/vdata/mysql
6)chown -R mysql:mysql /media/vdata/mysql
7)sudo systemctl start mysql

Yes, we have done several backups & restores previously, but in this system it’s not working. For this we have formatted the operating system & checked the result being the same.

20

can you take remote & suggest what needs to be done

21

Any more suggestion from where i can pin point the exact issue