Implementation of restoring data table structure using. frm in MYSQL _ MySQL

MYSQL uses. frm to restore the data table structure implementation method bitsCN.com. we all know that when we create a data table (innodb or myisam), a corresponding file (such as MYD, MYI, frm) will be generated)
Here, we will discuss how to use the frm file to restore the structure of innodb and myisam tables. However, due to their storage engine features, the recovery methods are different. The following describes the detailed recovery process.

Myisamchk "xxx. frm" myisamchk can be used to test whether the library is of the myisam type.

1: restore the innodb data table structure
We first copy an innodb. frm file from the test data directory to another database (innodb)

Mysql> USE innodb;
Mysql> DATABASE changed
Mysql> show create table innodb;
ERROR 1146 (42S02): TABLE 'innodb. innodb' doesn' t exist

It means that the copied files cannot be directly used. then we create another library (tmp) and create an innodb table in this library.


Mysql> create database tmp;
Mysql> create table innodb ('id' int (11) not null) ENGINE = InnoDB
Default charset = utf8;


Then we copy innodb. frm under innodb to the tmp data directory and overwrite innodb. frm under the tmp directory.
Next 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


Therefore, the data structure can be seen, but cannot be queried. well, this is to use. frm to restore the innodb table structure.
2: restore the structure of a myisam data table
It is much easier to restore the myisam type. I will take the following steps:
First, copy a test. frm file from the test data directory to the data directory of the tmp database.

Mysql> USE tmp;
Mysql> show create table test;
ERROR 1017 (HY000): Can't find file: 'test' (errno: 2)

The system prompts that the file cannot be found. let's handle the error. create the test. MYI and temp. MYD files in the tmp data directory, and then use the command that comes with mysql to fix the table.

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)


Okay, this table structure also shows bitsCN.com