Issue When restoring a mysql.daily.dump file in plesk, it only recreates the SQL server but without the table data or structure

[thomasjenk]

Server operating system version

Ubuntu 20.04

Plesk version and microupdate number

Plesk Obsidian Web Host Edition Version 18.0.49 Update #2

0
The issue began when I removed a subscription from a plesk server that was a staging site for the main site that was also on the same server. However unaware to me the wp-config for the live site had never been changed to point to a different MySQL database and when I removed the subscription, the MySQL database with all the sites additions since 16th May 2022 [were gone].
Unfortunately, after realising that this SQL server held such important data, I checked for backups however there were only backups of the main site running and no server backups or of the staging site. Looking on the server through SFTP I found both the mysql.daily.dump zip files and the SQL preupgrade backups, I tried running these commands in an SSH terminal

cd /var/lib/psa/dumps
ls -l mysql.daily*
zcat mysql.daily.dump.0.gz | plesk db

This did restore both the deleted subscription and SQL database, but without any of its table structure or table data. I was referred to plesk support through the servers hosting provider support who spent the evening reviewing our case and came back saying that ...it seems that this database dump is not for a single website... (which is correct) ...,but requires privileges necessary to make edits to the entire Plesk database (psa)...
After attempting the recovery myself, but to no luck, I'm now in a very tough spot.
Does anybody have any advice on how I can restore or recover the table data from those files in another way?

 

[Peter Debik]

Have you tried to unpack the file manually and then to look into the dump if the data you would like to restore is actually included in the dump? SQL dumps are simply text files, so you should be able to look at them with "less" or an editor of your choice.

 

[thomasjenk]

Have you tried to unpack the file manually and then to look into the dump if the data you would like to restore is actually included in the dump? SQL dumps are simply text files, so you should be able to look at them with "less" or an editor of your choice.

I have unpacked the file and viewed it in notepad++, this is how I found the mySQL user credentials for the lost database, whats confusing to me is I can also find chunks of the table structure in the text document too
{\"sorted_col\":\"`E8ZmYXcDn_users`.`user_registered` DESC\"}','2022-04-14 14:26:58')
for example, if anything it left me more confused as to why I could see the structure prefix but they hadn't recovered.