Recover MySQL InnoDB lost tables but files exist

I have a MySQL InnoDB which has all the database table files, but MySQL doesn't see them, and isn't loading them.

here is a suggested course of action

For this example, let's try to restore the tags table to the mydb database

STEP #1

Make sure you have backups of those .frm and .ibd files in /tmp/innodb_data

STEP #2

Get the CREATE TABLE tags statement and execute it as CREATE TABLE mydb.tags .... Make sure it is the exact same structure as the original tags.frm




Using dbsake to recover table structure from .frm files

https://www.percona.com/blog/2015/12/16/recovering-table-structure-from-frm-files-using-dbsake/

# curl -s http://get.dbsake.net > dbsake

# chmod u+x dbsake

# ./dbsake --version

dbsake, version 2.1.0 9525896

# ./dbsake frmdump /var/lib/mysql/sakila/staff.frm

# for tbl in `ls -1 /var/lib/mysql/world/*.frm`; do ./dbsake frmdump $tbl | mysql world_recover; done;

STEP #3

Delete the empty tags.ibd using MySQL

ALTER TABLE mydb.tags DISCARD TABLESPACE;

STEP #4

Bring in the backup copy of tags.ibd

cd /var/lib/mysql/mydb
cp /tmp/innodb_data.tags.ibd .
chown mysql:mysql tags.ibd

STEP #5

Add tags table to the InnoDB Data Dictionary

ALTER TABLE mydb.tags IMPORT TABLESPACE;

STEP 6

Test the table's accessibility

SHOW CREATE TABLE mydb.tags\G
SELECT * FROM mydb.tags LIMIT 10;

If you get normal results, congratulations you import an InnoDB table.

STEP 7

In the future, please don't delete ibdata1 and its logs

Give it a Try !!!