In MySQL when we delete some data it stays in the database as overhead until we optimize the database or write sth. into it. Is there any way to recover the overhead data in the tables? I can't access the direct database files, so I have to do this with sql queries if there is any.

tried looking it up, but I get the impression that the data is deleted but the other data around it isn't moved around to fill the void until you optimize.

Just a piece of a data from a different file of the table (with a little size) is deleted and the main data remains in its place without change. But as the other data which references it is deleted MySQL doesn't see it as data. I mean it stays in its place. When we optimize MySQL rewrites the file without overhead data. I need a query to get this overhead data but I don't think there is any too.

there's no simple way to recover data from MySQL.
Give the MySQL forums a search and you'll find problems like:

A typing error made me delete entries from the wrong database and I lost all my entries from one table. How is it possible to recover it? I need this information I've lost because my backup server got down too.

leading to responses like:

I'm terribly sorry, but it's probably impossible to recover the deleted rows.

If it's really, really, really important (huge financial loss from the destroyed data) you should block all write access to that harddrive immediately (but that's maybe too late already) and contact MySQL support and specialized data recovery companies. There could be a (very) slight chance to recover at least part of the data from the harddrive directly: But that would probably be very expensive to do...

That comes straight from the horses mouth.

the only viable option is to pre-emt problems by making regular backups of your most important data. phpmyadmin does this well enough, or you could create a PHP script and cron it every night.

case in point, we have a Harry potter fansite. we'd worked really hard and made it the biggest and most popular fansite in the UK.
Our host has a HD problem, repairs bad sectors and our entire site, database and all reverts back 8 months.

We didn't have any database backup and only some file backups. we had to work very hard for the next week or so to get it anything like where we were, yet still we were nearly 20,000 users down from what we had.

that's 20,000 email addresses for our newsletter, 20,000 potential ad clicks etc, it was a real big deal.

And ya know what, we never recovered from that.

so every site we have gets backed up. When we move our main business onto it's own server in a few months (servers ready, site isn't) Ive built a croned script that'll backup the main DB tables every hour to my email address and 2 seperate remote servers.

if you've lost data, then I really feel for you, but I'm afraid there's no way of getting it back. Just make sure you have backups to hand for next time.

Fortunately I haven't lost any data, I just wanted to learn if its possible. In fact if the server was at my home I could save the lost data by opening the database files with an editor such as notepad.
eXcuse me for the delayed replay.