Mysql – My thesql database crashed, how to recover

Empty frm files won't work because they have a specific syntax which is being checked by mysql.

[root@server test]# touch test_one.frm
[root@server test]# chown mysql test_one.frm
[root@server test]# mysql test

Although it is showing up in show table because it only checks for the existence of the file ...

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
....
| test_one       |
...
+----------------+

... any operations on it will fail.

mysql> show create table test_one;
ERROR 1033 (HY000): Incorrect information in file: './test/test_one.frm'

mysql> drop table test_one;
ERROR 1051 (42S02): Unknown table 'test.test_one'

The documentation says:

Create a matching .frm file in some other database directory and copy it to the database directory where the orphan table is located.

Now, matching (same structure) is actually not mandatory if you only want to drop it but you need to have a valid frm file.

If you do this you should be able to drop the table afterwards:

mysql> create table test_recoverer (id int unsigned primary key);
Query OK, 0 rows affected (0.31 sec)

[root@server test]# cat test_recoverer.frm > test_one.frm

mysql> drop table test_one;
Query OK, 0 rows affected (0.11 sec)

_{Community Wiki answer generated from comments on the question by akuzminsky}

User data are in *.ibd files. So, from just ibdata1 and logfiles - not possible. What you need is take an image from the disk, parse it with stream_parser and if it finds InnoDB pages fetch records from them with c_parser (search for undrop-for-innodb).

For the future, but make sure MySQL runs in durable mode + NTFS + write cache protected by a battery (if enabled) + backups.

Durable mode:

innodb_flush_log_at_trx_commit=1
innodb_doublewrite=1
innodb_checksums=ON
innodb_support_xa=1

Defaults of all of these are set to make InnoDB durable. Just make sure you don't change them.