0

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.

1 Answer 1

4

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.

3
  • 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, 2009 at 4:13
  • Consider using file_per_table to avoid the problem of maintaining a monolithic ibdata file which contains all your tables. Jun 15, 2009 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. Jun 15, 2009 at 9:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .