Are you running something like selinux or some other similar package? I'd suggest disabling that (or modifying the security policies) to see if something there is preventing MySQL from being able to access the files.
[edit] If so, check syslog to see if selinux is blocking mysql from doing anything. If it is SELinux, I'm told this may disable it so that you might test this theory.
/usr/sbin/setenforce Permissive
Well I tried a bunch of stuff and researched a bunch of commands, switches, and structures (not surprisingly, the MySQL docs were most helpful, if extensive—needle/haystack). Eventually some of my tricks worked (it turns out that others had thought of the same tricks, though I did not see the two major parts—recovering the table structure, and recovering the data—in one place, so I’m posting them together here).
What I had to do was to recreate the IBDATA1 file. Unfortunately while running the daemon detects the databases (the directories), it does not pick up the tables Innodb inside (the IBD/FRM files). So what I did was to:
- Empty out the data directory (or move the original and create an empty one)
- Run the daemon, let it create a new, empty IBDATA1 file
- Import the system tables using the SQL scripts in
…\MySQL\share
- Create a dummy database and table of the same name
- Copy over the original FRM file
- Use either
DESCRIBE or better, SHOW TABLE CREATE to extract the table structure
- Next, I used
DISCARD TABLESPACE on the table
- Copied over the original IBD file
- Then I used
IMPORT TABLESPACE
- Finally, I re-ran the daemon with
innodb-force-recovery=6
- And I ran
mysqldump to extract the structures and data
Of course it was not always smooth. Some tables were fine, but some required dropping the table and database after the SHOW TABLE CREATE, and using that to re-create the table before trying to import the data. Others did not even work that far, and I had to manually get the comments and names of the columns from the FRM file using a hex-editor (though figuring out what the data types and attributes, keys, etc. were was a crap-shoot). Also, there were plenty of—too many—daemon and client restarts.
(I’m still looking for a tool that will directly parse FRM/IBD files (or at least display table structure from an FRM file), but it looks like nobody has bothered to “reverse-engineer” them even though it’s open-source and the file formats are publicly available. It seems that everyone is complacent using the official MySQL tools—thus creating a great opportunity for data-recovery firms and proprietary/commercial tools.)
The key was to always work with the absolute minimum (eg only the MYSQL directory—ie system tables). Unfortunately while it meant that things would be simplified and easier to work with, it also meant recovering one table at a time—which wasn’t a big deal for me, but for some people it could be.
Anyway, out of the many MySQL recovery pages on the Internet that I saw during the past couple of days, a small handful were pretty useful, and I’ll add them once I scour my history to dig them up.
Hope this can help others in a similar situation.
Best Answer
There are two ways to see the storage engine of a table
METHOD #1
Step 01) Double Click on which Connection you want to open (Opens SQL Editor)
Step 02) Click on the Database
Step 03) Right Click on the Table (menu pops up)
Step 04) Click on Send to SQL Editor (menu appears sideways)
Step 05) Click Create Statement
You will see the CREATE TABLE SQL command. At the bottom of the Command is the part that says
ENGINE=.METHOD #2
Another way to display the engine of a table is to query the INFORMATION_SCHEMA database
For a table mydb.mytable, run this to see the engine
Give it a Try !!!