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

Hi all!

After eventually managing to upgrade my debian virtual server from etch to lenny. (Read here for the full story: http://serverfault.com/questions/168808/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

From serverfault yas4891

• 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

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

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

  yas4891 : @James: THANKS THANKS THANKS THANKS!

  James Lawrie : 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
  From James Lawrie

• Hello,

  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.

  yas4891 : thanks for the reply.
  From Janne Pikkarainen

• Well, a couple of things:

  First, backup the files that you currently have. Some of the operations may make modifications to them, so you don't want to be worse off than you are right now.

  I'll assume that your current data directory has a mysql/user.frm file. If that's the case, the problem is that the new version of MySQL is looking in the wrong place for its data. Check out the documentation on my.cnf, specifically the part about DATADIR. You can change the default datadir to point to your old fileset. Once you do that, you'll want to run the command mysql_upgrade to check your tables and upgrade any of the table structure. See the documentation.

  If there is no mysql/user.frm file in your data directory, then something went wrong. You'll need to manually complete the installation again (not re-install, just the post-installation steps). To do this, run the program mysql_install_db. That should restore the user tables. Note that you will lose all authentication information (seeing as it's already gone since the user.frm doesn't exist).

  I'll leave it at that for now considering that until you pick one of those routes, I can't really help further. Let me know which you pick, and I'll try to continue helping the best I can.

  From ircmaxell

• this is index files they can be generated mysql repair

  Backup mysql files and in phpmyadmin enter query:

  REPAIR TABLE tbl_name [, tbl_name] USE_FRM;
  

  Mysql will repair it.

  From Liutas