We all know that when we set up a data table (InnoDB or MyISAM), we generate the corresponding file (e.g., myd,myi,frm)
Here, we explore the use of frm file to restore the InnoDB and MyISAM type table structure, but because of their storage engine characteristics, so the recovery method is not the same, the following is a detailed recovery process.
Myisamchk "Xxx.frm" Myisamchk can be tried out, the library is not MyISAM type
1: Restore the InnoDB type data table structure
Let's first copy a innodb.frm file from the test data directory to another library (INNODB)
Copy Code code as follows:
mysql> use InnoDB;
Mysql> DATABASE changed
Mysql> show CREATE TABLE InnoDB;
ERROR 1146 (42S02): TABLE ' innodb.innodb ' doesn ' t exist
Note that the copied file is not directly available, and then we create another library (TMP) and create a InnoDB type table in this library.
Copy Code code as follows:
mysql> CREATE DATABASE tmp;
Mysql> CREATE TABLE innodb (' id ' int (one) not NULL) Engine=innodb
DEFAULT Charset=utf8;
We then copy InnoDB under INNODB.FRM to the TMP data directory and overwrite the INNODB.FRM in the TMP directory
Here we restart MySQL try
Copy Code code as follows:
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 the data structure is visible, but you can't query, OK, that's the use of. frm recovery InnoDB type of table structure
2: Restore the MyISAM type data table structure
Restoring the type of MyISAM is much simpler, and I look at the following steps
First or above, copy a TEST.FRM data directory from the test data directory to the TMP library
Copy Code code as follows:
mysql> use TMP;
Mysql> show CREATE TABLE test;
ERROR 1017 (HY000): Can ' t find file: ' Test ' (errno:2)
Hint that the file cannot be found, let's handle the error and create test in the TMP data directory. Myi and Temp. MyD file, and then we use the MySQL self-brought fix table command
Copy Code code as follows:
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
+------+
1 row in SET (0.00 sec)
Well, this table structure is also seen
