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 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!

share|improve this question
add comment

2 Answers

I have a similar issue- but I have a copy of my data folder that has all the above that you mention:

.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)

I installed a new copy of mysql. But it set up its own data folder.

How can I move or import or point the data to the new install OR point the new install to this data.

I am using a Macbook Pro.

share|improve this answer
add comment

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.

share|improve this answer
    
even if i am using innodb i should look for .myd files? –  jnroche May 17 '12 at 15:01
    
thanks for clarifying, i guess i have to learn this one the hard way. what i dont get is i never deleted any files during xampp re-install. this is a drupal-created database. but i can't see any more .myd files. –  jnroche May 17 '12 at 15:40
    
The default behavior for InnoDB is to store the files in the system tablespace; I've updated my answer to reflect that. (sorry for the ninja edit, @jnroche)! –  Andrew M. May 17 '12 at 15:40
    
innodb can also be in the "ibdata1" combined file in the datadir. And it is generally not considered a good idea to try and port innodb files between mysql servers unless they are definitely using the exact same version and config of mysql. BTW, with mysql "backup" is pronounced "mysqldump", anything else has gotchas attached. –  rackandboneman May 17 '12 at 15:41
1  
HOME is where ibdata1 is ;) So set your datadir to wherever it is, AFTER making a backup of the files there. –  rackandboneman May 17 '12 at 15:56
show 2 more comments

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.