database data lost / mysql

Question

I reformatted my pc and reinstalled my xampp installation but never overwritten the mysql folder (option in xampp install) and did not back up my database table thinking this is safe! (what was i thinking?)

Now, Myphpmyadmin shows there are tables exists-mydatabase(294)! but when clicking on shows mydatabase(0) tables - no tables found!!! running my drupal site gives me :

DOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mydrupaldatabase.semaphore' doesn't exist: SELECT expire, value FROM {semaphore} WHERE name = :name; Array ( [:name] => variable_init ) in lock_may_be_available() (line 167 of F:\xampp\htdocs\mydrupalsite\includes\lock.inc)....

inside my mysql/data/mydrupaldatabase/ folder i can see there are all the *.frm files for my database. But I'm not sure if these are the actual data because there is only about 2mb worth of it when in fact i remember there should be around 4 mb of my data.

I strongly think my data is still there but phpmyadmin is probably just confused how to restore it.

Any help greatly appreciated!

Answer 1

First, verify that you have the correct datadir by using:

SHOW VARIABLES LIKE 'datadir';

This should give you a directory that contains more directories based on the database names. Inside each of these, you should see three types of files:

• .frm contains the table definition, but no actual data.
• .myd contains the raw table data. (MyISAM)
• .myi contains any table indices that you have defined. (MyISAM)
• .ibd contains the indices and data in a combined format. (InnoDB)

Here's where it gets tricky. InnoDB (which you mentioned int he comments is what you're using) has the notion of tablespaces, much like Oracle. That is, it separates the files out on the system into different namespaces if you've configured it to do; otherwise, it stores everything in the system tablespace. Its typically in a file called ibdata#, where number can change. If that's gone, then your data is gone. Its very likely that your reinstall of MySQL backed up the file and put in the standard system file--but you'll need to look and see.

For future reference, you can create these on a per-file basis, which allows you to separate out the tablespaces into different files by using the innodb_file_per_table config option.