3 methods to restore table structure from frm file in mysql [recommended]

When mysql is running normally, it is not difficult to view the table structure.

But sometimes mysql fails and this method is no longer feasible.

When a failure occurs, a new MySQL instance is usually used to restore the current data.

Creating a table is a very important step, and we must have other ways to find the table structure.

Where is the table structure defined?

The user data that is usually of interest is actually stored in the MySQL data directory.
Other metadata are no exception, such as the definition of the table structure.

The mysql data directory file structure is very clear.

• Catalog Mapping Database
• The frm file stores the definition of the table structure
•ibdata files store mysql metadata and other

The structure defined by the table exists in the frm file, and of course the ibdata that manages metadata will also have records.

When the frm file exists, it is relatively easy to restore the table structure;
However, if drop table is executed, the frm file will be deleted, and the method provided in this article will be of no help.
In this case, you can try to restore the table structure from ibdata, which is not within the scope of the following discussion.

Parsing table structure

The following are three ways to parse the create table command from the frm file.

mysqlfrm

mysqlfrm is one of the mysql utilities tools.
Used to analyze frm files and generate create table commands.
It is no longer updated and some of its features have been incorporated into newer versions of MySQL shell (version 8 and later).

mysql utilities requires python2 environment and the installation is very simple.

$ tar -xvzf mysql-utilities-1.6.5.tar.gz
$ cd mysql-utilities-1.6.5
$ python setup.py build
$ python setup.py install

mysqlfrm supports two modes for interpreting frm:

Direct analysis

This mode is relatively straightforward, analyzing the frm file byte by byte, extracting as much information as possible.

In this mode, the --diagnostic parameter needs to be used.

$ mysqlfrm --diagnostic /data/sakila/actor.frm

MySQL example analysis

This mode uses a new mysql instance to complete the frm analysis work.
There are two ways to specify how to start a new MySQL instance.

1. Spawn from the current MySQL service and use --server to specify the MySQL service

$ mysqlfrm --server=root:pass@localhost:3306 --port=3310 /data/sakila/actor.frm

Second, start a new MySQL instance and use --basedir to specify the MySQL program path

$ mysqlfrm --basedir=/usr/local/bin/mysql --port=3310 /data/sakila/actor.frm

--port specifies the port for the new instance to avoid conflicts with the current port 3306.

dbsake

This is a tool I discovered by chance. It introduces itself in the documentation as follows:

dbsake - a (s)wiss-(a)rmy-(k)nif(e) for MySQL

The author must be someone who is very experienced with MySQL. The tool is simple and neat from downloading, installing to using.

$ curl -s get.dbsake.net > dbsake
$ chmod u+x dbsake
$ ./dbsake frmdump [frm-file-path]

online service

There are some online services that also focus on such issues.
I have used twindb online and the experience is very good. The related tool set is also great.

From the Recover Structure -> from .frm file entry, upload the frm and you will get the create table command.

Final Thoughts

When using it, you can test multiple tools, compare which tool's recovery commands are more complete and desirable, and choose the best one.

refer to:

•mysqlfrm official doc

•dbsake project doc

Summarize

The above are the three methods that I introduced to you to restore the table structure of MySQL from the frm file. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!