XtraBackup Restore Issue – Database Not Recognized but mysqldump Works

22

Hi!

Does the prepare ends correctly?

You are backing up onto a sharedfile system. Even though the backup might have ended in the source, it might not have flushed entirely to disk and thus the 2nd server might not be seeing the complete and correct backup. https://www.percona.com/blog/using-xtrabackup-on-nfs-for-mysql-backups/

Regards

23

Just now, I removed the share and performed the backup and restore process as described earlier, but the same issue still persists

24

Any other troubleshooting tips

25

Hi again,

I have taken another look at the logs.

Latest logs from Percona Server says “ready for connection” and no startup error. Previous messages show that the restore was recognized and an upgrade was performed between 8.0.42 and 8.0.45.

The error is only showing on some application logs. Can you please confirm if you can manually (not through application, intermediate layers nor third party tools) connect to MySQL , check schemas and execute some queries?

26

Only once did I try with this, & it worked after that. For recent backup & restore, it’s not working: mysqlcheck -u root -p --all-databases --auto-repair

27

Hi viswanatha,

Can you check if you can manually connect to the database and execute some queries ?

From the DB logs there seems to be no errors

28

Yes, I can manually connect to the database & execute queries.

29

Ok thanks for confirming

Please execute some “select count(*) from tablename” for a couple tables and check that the data is there. Also check the restore log from xtrabackup and logs from DB to see if any errors pops up. If none then the problem is the application and not the DB

regards

30

Data is available, and both application logs are attached. The application only requires DB IP configuration, but it is still unable to connect to the database.

restore_prepare.log (8.4 KB)

xtrabackup_restore.log (94.8 KB)

31

Any other tips to identify the cause .

32

Hi viswanatha,

If the restore process is ok and the data is there then it seems the problem is your application/connector and not the database per se

33

The application only requires the DB IP for connectivity. It works fine with rsync and mysqldump-based setups, but when using XtraBackup, the application is unable to recognize the database, even though the configuration remains unchanged.

34

Hi again,

If you can manually connect to the DB but not through the application then I would suggest you take a look at existing users. Maybe as part of the restore you are modifying those

Consider creating a new user and re configuring that new user from the app side

35

We have created user & the same user also configured in application result is same.

36

Any additional troubleshooting tip

37

Awaiting for response.

38

Hi viswanatha,

Given that the database is up, accessible and the data is there then the problem might be something else related to the application or the way you are connecting from the app server.

I would suggest checking with the application team or testing on a different system, checking firewalls, users or anything else as the database seems to be fine

39

Hi Tutte,

I completely agree with your observation. I checked with the application team, and as per their confirmation, both Rsync and Mysqldump are working fine from the application side, which indicates there is no issue with the application connectivity or configuration.

The issue is occurring specifically after backup and restore using XtraBackup. From the application perspective, the only required change is updating the database IP, and no other application-side modifications are needed.

This suggests the problem is likely related to the XtraBackup restore process or the restored database environment rather than the application itself.

40

Hi viswanatha,

I do not know what else to tell you since the backup finishes successful and the database is up and accessible.

I would suggest you make sure that the user/permission is correctly set and you are not doing anything different. For example users can be overwritten with xtrabackup while mysqldump does not modify the mysql.users

Else I would suggest you make a simpler test case (no data) and compare what is being changed in the database when using xtrabackup vs mysqldump

Regards

41

Could you please support us by taking a remote session? It would help us clearly understand if there are any additional steps required from our side, as we have performed the same procedure on different hardware, and the results were successful there.