retrieve MySQL table schema from .frm files to create SQL script

Question

I recently received a MySQL database from my vendor (whose contract is now closed) and followed their instructions to set up PHP and MySQL on localhost, however using the phpMyAdmin interface I could only see a few of the tables that were visible in the DB directory.

I noticed that those tables that I was able to see (and query) in phpMyAdmin actually had all 3 files types (.frm .MYD .MYI) but the 'missing' ones had only one type (.frm).

How do I use the .frm file (which is schema) to create SQL scripts that will allow me to re-generate the tables in full?

Answer 1

You actually can´t restore data from your .frm files, however its no problem to get the table defintion out of this files. Do the following:

1. Create a new table with the same name as the .frm file (so e.g. yada.frm will become yada table)
2. stop your mysql daemon
3. copy over the old old .frm file to the location where the new .frm is stored
4. restart your mysql deamon
5. you should now be able to get the table defintion by SHOW CREATE TABLE yada