MYSQL restores the data table structure using.frm

We all know that when we create a data table (innodb or myisam), we generate the corresponding files (e.g., MYD,MYI,frm)
Here, we will discuss the structure of the innodb and myisam type tables using frm files, but the recovery method is different due to the characteristics of their storage engine. The following is the detailed recovery process.

myisamchk "xxx. frm" myisamchk can be tested to see if the library is of type myisam

1: restore the innodb type data table structure
We start from the test data directory copy1 innodb.frm file to another library (innodb)

 
mysql> USE innodb; 
mysql> DATABASE changed 
mysql> SHOW CREATE TABLE innodb; 
ERROR 1146 (42S02): TABLE 'innodb.innodb' doesn't exist 

Indicating that the copied files are not directly usable, we then create another library (tmp) and create a table of type innodb in this library

 
mysql> CREATE DATABASE tmp; 
mysql> CREATE TABLE innodb (`id` int(11) NOT NULL) ) ENGINE=InnoDB 
DEFAULT CHARSET=utf8; 

We then go from copy innodb under innodb.frm to tmp data directory and overwrite innodb.frm under tmp
Let's try restart mysql

 
mysql> SHOW CREATE TABLE innodb \G; 
*************************** 1. row ********** 
TABLE: innodb 
CREATE TABLE: CREATE TABLE `innodb` ( 
`dd` varchar(1) NOT NULL, 
`cc` varchar(1) NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
1 row IN SET (0.00 sec) 
ERROR: 
No query specified 
mysql> INSERT INTO innodb (dd,cc) value (1,2); 
mysql> Query OK, 1 row affected (0.00 sec) 
mysql> SELECT * FROM innodb; 
ERROR 2013 (HY000): Lost connection TO MySQL server during query 

So data structures can be seen, but not queried, okay, so that's how you recover the innodb type of table structure using.frm
2: restore the myisam type data table structure
Restoring the myisam type is much easier, as I'll see below
First of all, as in the first example above, copy1 test. frm to tmp library data directory from test data directory copy1 test. frm to tmp library data directory

 
mysql> USE tmp; 
mysql> SHOW CREATE TABLE test; 
ERROR 1017 (HY000): Can't find file: 'test' (errno: 2) 

We can't find the file, so let's deal with the error. Create test.MYI and temp.MYD files in the tmp data directory, and then we'll use the repair table command that comes with mysql

 
mysql> repair TABLE test USE_FRM; 
+------------------+--------+----------+----------+ 
| TABLE | Op | Msg_type | Msg_text | 
+------------------+--------+----------+----------+ 
| test.test_myisam | repair | STATUS | OK | 
+------------------+--------+----------+----------+ 
1 row IN SET (0.00 sec) 

mysql> SHOW CREATE TABLE test \G; 
*************************** 1. row ********** 
TABLE: test 
CREATE TABLE: CREATE TABLE `test` ( 
`dd` varchar(1) NOT NULL, 
`cc` varchar(1) NOT NULL 
) ENGINE=myisam DEFAULT CHARSET=utf8 
1 row IN SET (0.00 sec) 
ERROR: 
No query specified 
mysql> INSERT INTO test (dd,cc) value(1,2); 
Query OK, 1 row affected (0.00 sec) 
mysql> SELECT * FROM test; 
+------+ 
| dd | cc 
+------+ 
| 1 |2 
+------+ 
1 row IN SET (0.00 sec) 

Ok, so you can see the table structure