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.

I have copied the /var/lib/mysql/mydbname db to a new machine from a crashed server (booted using a live cd) but now I cannot access the tables within the db...

All the tables only have a .frm extension, so I am assuming they are InnoDB.

They appear in a SHOW TABLES statement, but I cannot SELECT from them.

REPAIR / OPTIMIZE / ANALYZE all give;

+---------------+-------+----------+-------------------------------------+
| Table         | Op    | Msg_type | Msg_text                            |
+---------------+-------+----------+-------------------------------------+
| mydbname.xyz  | check | Error    | Table 'mydbname.xyz' doesn't exist  |
| mydbname.xyz  | check | error    | Corrupt                             |
+---------------+-------+----------+-------------------------------------+

myisamchk wont touch them (obviously)...

I am feeling slightly optionless atm.

Thanks in advance.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

If they're InnoDB, then you needed to copy the ibdata* and ib_logfile* files across from the crashed server, but you'll pick up other databases' table data then, and it'll all just be too painful. Restore from backup, instead.

share|improve this answer
    
Great, that will work a treat as all the other databases will eventually be migrated anyway. Thanks, will report how it goes. :) –  Christian Jun 15 '09 at 4:13
    
Worked beautifully - thank you! –  Christian Jun 15 '09 at 6:13
    
Consider using file_per_table to avoid the problem of maintaining a monolithic ibdata file which contains all your tables. –  Dave Cheney Jun 15 '09 at 6:56
    
Only problem with the file_per_table option is that innodb does not return diskspace back to the OS. If you add a lot of data to a table and then remove it again, that diskspace can't be used by another table. –  David Pashley Jun 15 '09 at 9:07

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.