Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

4 Answers

up vote 1 down vote accepted

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

share|improve this answer
    
Ha ha... that was easy now - wasn't it? –  yas4891 Aug 12 '10 at 12:27
    
man... keep me away from administrator duties. For the sake of mankind... –  yas4891 Aug 12 '10 at 12:28
    
@James: THANKS THANKS THANKS THANKS! –  yas4891 Aug 12 '10 at 12:28
3  
No problem :) did I mention the next step is to backup? ;) –  James Lawrie Aug 12 '10 at 12:37
    
@yas4891: If this solved your problem you should click the Checkmark next to the answer to accept it as the solution to your question. –  squillman Aug 12 '10 at 13:22
show 1 more comment

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.

share|improve this answer
    
thanks for the reply. –  yas4891 Aug 12 '10 at 12:34
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.