You could try to use MySQL's CHECK TABLE and REPAIR TABLE functions (but make sure you grab a copy of the table first):
mysql -uroot -p database1
CHECK TABLE table1
If that doesn't work, try using:
CHECK TABLE table1 EXTENDED
According to the CHECK TABLE documentation:
EXTENDED is to be used only after you have run a normal check but still get strange errors from a table when MySQL tries to update a row or find a row by key. This is very unlikely if a normal check has succeeded.
If any errors are reported by either of the above, try:
REPAIR TABLE table1
If that doesn't work, you can try:
REPAIR TABLE table1 EXTENDED
Finally, if that still doesn't work, you may be able to use myisamchk while MySQL is offline - according to the documentation:
REPAIR TABLE does not implement all the options of myisamchk
MyISAM
For a MyISAM table mydb.mytable, you should have three files
\bin\mysql\mysql5.6.12\data\mydb\mytable.frm\bin\mysql\mysql5.6.12\data\mydb\mytable.MYD\bin\mysql\mysql5.6.12\data\mydb\mytable.MYI
They should already be accessible as a table since each file contains needed data, metadata, and index info. Collectively, they form the table. There are no external storage engine mecahnisms to access.
InnoDB
Take a look at this Pictorial Representation of InnoDB
The only thing that attaches ibdata1 to the .ibd files is the data dictionary.
Your mission, should you decide to accept it, is to create each table and swap in the .ibd
Before you do anything, make a full copy of "\bin\mysql\mysql5.6.12\data" to another
Here is a sample
Suppose you have a database mydb with the table mytable. This means
- You have the folder
\bin\mysql\mysql5.6.12\data\mydb
- Inside that folder, you have
You need the .frm. If you look at my post How can extract the table schema from just the .frm file?, you can download a MySQL utility that can generate the SQL needed to create the table.
You should now do the following
- Move
mytable.ibd to \bin\mysql\mysql5.6.12\data
- Run the SQL to create the InnoDB table
- Login to mysql and run
ALTER TABLE mydb.mytable DISCARD TABLESPACE; (This will delete \bin\mysql\mysql5.6.12\data\mydb\mytable.ibd)
- Copy
\bin\mysql\mysql5.6.12\data\mytable.ibd into \bin\mysql\mysql5.6.12\data\mydb
- Login to mysql and run
ALTER TABLE mydb.mytable IMPORT TABLESPACE; (This will register \bin\mysql\mysql5.6.12\data\mydb\mytable.ibd into the data dictionary)
After this, the table mydb.mytable should be fully accessible. You can test that accessibility by simply running:
SELECT * FROM mydb.mytable LIMIT 10;
Give it a Try !!!
DRINK (Data Recovery Incorporates Necessary Knowledge) Responsibly
Best Answer
if your data is mission critical and you really cannot move forward without it there are some very specialist tools on the market for recovery. There are only small amount of experts that know how to do this properly. I spent some years working at a consultancy that could do this with varying degrees of success and it was very expensive in terms of $ and time. Take a look on https://recovery.twindb.com/ to see if there's anything there that can be useful.
Don't forget to set your backups up to avoid this situation again in the future. There are plenty of tools out there that make this simple for you.
https://www.percona.com/downloads/XtraBackup/LATEST/
https://www.binlogic.io/