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