how to: rescue Data from MySQL (only files on HDD left)

Question

After eventually managing to upgrade my debian virtual server from etch to lenny. (Read here for the full story: debian: upgrade from etch to lenny fails)

I now lost the data (including all tables) from my MySQL DB. The files are still present on the hard drive and phpMyAdmin (further referenced to as PMA) still shows me all the database names but without any tables. 'mysql' and ''information_schema' databases still have their tables. But accessing e.g. 'mysql.user' gives me the following error:

#1017 - Can't find file: './mysql/user.frm' (errno: 13) 

Of course (as any 1337 PEBKAC does) I don't have a mySQL dump file. (using the F-word multiple times)

Does anyone know of a tool that could restore the lost data from the file structure?

Thanks in advance for any help granted to that layer-8 problem a.k.a. me

Solution 1

IF you have the files you should be able to view the data (except innodb tables). This error is commonly given when you have a permissions issue - try changing the /var/lib/mysql files to mysql:mysql and see if it fixes it.
chown -R mysql:mysql /var/lib/mysql

Solution 2

First things first: don't panic. Most likely everything is not lost, you just need to figure out what happened.

• Did the MySQL data directory path change during the upgrade? (I don't thinks so - if my memory serves me, both Etch and Lenny serve stuff out from /var/lib/mysql by default. But if you previously changed the path by hand, then something might have happened)

• If you cd to your /var/lib/mysql, can you find the directory mysql and the file user.frm under it? If you can not, does find / -iname 'user.frm' return something?

• Is there worth noting under /var/log, especially under /var/log/mysql?

If everything else fails, setup another Debian installation to some other computer (or a virtual machine), install a Debian Etch there, copy your MySQL data directory there and see if that works. Then run a proper mysqldump and restore that to your brand new Lenny.

EDIT: Ok, this was just a permission issue. I'll leave my reply here anyway in case it helps someone else.

Comments

[yas4891]

Ha ha... that was easy now - wasn't it?

[yas4891]

man... keep me away from administrator duties. For the sake of mankind...

[James L]

No problem :) did I mention the next step is to backup? ;)

[squillman]

@yas4891: If this solved your problem you should click the Checkmark next to the answer to accept it as the solution to your question.

[yas4891]

@squillman: Right. Nearly forgot it