https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html says:
The .frm file must have the same table schema as the orphan intermediate table (it must have the same columns and indexes) and must be placed in the database directory of the orphan intermediate table.
If the .frm file must have the same columns and indexes, then yes, you must match the right .frm file with the right .ibd file. I don't know how these temporary filenames are generated. There doesn't seem to be any clear correlation, so it's practically impossible to know which one goes with the other.
You can dump the original table structure from a .frm file using the mysqlfrm tool in MySQL Utilities. There's a nice blog showing how to do this here: https://www.percona.com/blog/2014/01/02/recover-table-structure-frm-files-mysql-utilities/
You should download the MySQL Utilities soon, because Oracle seems to have deprecated these tools. Some of the features of these tools is reimplemented as part of MySQL Shell, but I expect any features for .frm files will not be supported, because MySQL 8.0 doesn't use .frm files anymore.
I just tried using mysqlfrm on my Macbook, but I get this error:
ERROR: Cannot find location of mysql_system_tables.sql.
I guess that's because MySQL is installed under /usr/local on my Macbook. Maybe it will be more successful on a Linux server, but I don't have a Linux installation handy that has MySQL Utilities installed.
And that's only half of the story. You would still need to know the table structure of each .ibd file to match it to the right .frm file. You might be able to piece the information together from these INFORMATION_SCHEMA tables:
- INNODB_SYS_TABLES
- INNODB_SYS_COLUMNS
- INNODB_SYS_INDEXES
For example, here's a simple table in my test schema:
CREATE TABLE `A` (
`c1` int(11) NOT NULL DEFAULT '0',
`c2` int(11) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
PRIMARY KEY (`c1`),
KEY `c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
If I query the I_S tables:
mysql> select * from innodb_sys_tables where name = 'test/A';
+----------+--------+------+--------+-------+-------------+------------+---------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
+----------+--------+------+--------+-------+-------------+------------+---------------+
| 2126 | test/a | 1 | 6 | 2106 | Antelope | Compact | 0 |
+----------+--------+------+--------+-------+-------------+------------+---------------+
mysql> select * from innodb_sys_columns where table_id = 2126;
+----------+------+-----+-------+--------+-----+
| TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN |
+----------+------+-----+-------+--------+-----+
| 2126 | c1 | 0 | 6 | 1283 | 4 |
| 2126 | c2 | 1 | 6 | 1027 | 4 |
| 2126 | c3 | 2 | 6 | 1027 | 4 |
+----------+------+-----+-------+--------+-----+
mysql> select * from innodb_sys_indexes where table_id = 2126;
+----------+---------+----------+------+----------+---------+-------+
| INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE |
+----------+---------+----------+------+----------+---------+-------+
| 3875 | PRIMARY | 2126 | 3 | 1 | 3 | 2106 |
| 3876 | c2 | 2126 | 0 | 1 | 4 | 2106 |
+----------+---------+----------+------+----------+---------+-------+
