Restoration of MySQL InnoDB table structure

What is the restoration method for MySQL InnoDB table structure? The following describes in detail the restoration steps of the MySQL InnoDB table structure. If you are interested in this, take a look.

MySQL InnoDB Table Structure Restoration:

Assume that the MYSQL database has crashed. Currently, only frm files of the corresponding table are available. As we all know, frm files cannot be viewed in the text editor. If they are not restored, they are basically useless to us. In this example, we assume that the file is test_innodb.frm.

The table creation script is as follows:

 
 

  
  
mysql> create table test_innodb  
  
  
 
  
  
    -> (A int(11) default NULL,  
  
  
    -> B varchar(30) default NULL,  
  
  
    -> C date default NULL) engine=innodb;  
  
  
Query OK, 0 rows affected (0.05 sec)  
  
  
 
 
 

Recovery Method Introduction (process ):

1. Create a database in a new normal MYSQL environment, such as aa.

2. Create a data table test_innodb with the same name under the aa database. The table structure is random. Here there is only one id field. The operation procedure is as follows:

 
 

  
  
mysql> create table test_innodb (id bigint not null)engine=InnoDB;  
  
  
Query OK, 0 rows affected (0.09 sec)  
  
  
 
  
  
mysql> show tables;  
  
  
+--------------+  
  
  
| Tables_in_aa |  
  
  
+--------------+  
  
  
| test_innodb |  
  
  
+--------------+  
  
  
2 rows in set (0.00 sec)  
  
  
 
  
  
mysql> desc test_innodb;  
  
  
+-------+------------+------+-----+---------+-------+  
  
  
| Field | Type       | Null | Key | Default | Extra |  
  
  
+-------+------------+------+-----+---------+-------+  
  
  
| id    | bigint(20) | NO   |     | NULL    |       |  
  
  
+-------+------------+------+-----+---------+-------+  
  
  
1 row in set (0.00 sec)  
  
  
 
 
 

3. Stop the mysql server, copy the test_innodb.frm file left after the system crash to the data directory aa of the new normal database, and overwrite the frm file with the same name on the lower side:

4. Restart the MYSQL service.

5. test whether the restoration is successful. log on to the aa database and run the desc command to test the restoration:

 
 

  
  
mysql> desc test_innodb;  
  
  
+-------+-------------+------+-----+---------+-------+  
  
  
| Field | Type        | Null | Key | Default | Extra |  
  
  
+-------+-------------+------+-----+---------+-------+  
  
  
| A     | int(11)     | YES |     | NULL    |       |  
  
  
| B     | varchar(30) | YES |     | NULL    |       |  
  
  
| C     | date        | YES |     | NULL    |       |  
  
  
+-------+-------------+------+-----+---------+-------+  
  
  
3 rows in set (0.01 sec)  
  
  
 
 
 

OK. The table structure has been restored.

MySQL multi-Table Union query syntax example

Alternative usage of MySQL table alias

MySQL left join query Experience Summary

How to display MYSQL table information

Three Common MySQL table creation statements