Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "mysql from the frm file to restore the table table structure of what methods", the content is easy to understand, well-organized, hope to help you solve your doubts, the following let the editor lead you to study and learn about "mysql from the frm file to restore the table table structure of what methods there are" this article.

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

But sometimes when mysql fails, this approach is no longer feasible.

When a failure occurs, a new mysql instance is usually used to recover the current data.

Table building is a very important step, and we have to find other ways to find the structure of table.

Where is the definition of table structure

The underlying user data, which is usually concerned about, is actually stored in the mysql data directory.

Other metadata is no exception, such as the definition of table table structure.

The structure of mysql data directory file is very clear.

Directory corresponding database

The frm file stores the definition of the table structure

The ibdata file stores mysql metadata and other data.

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

When a frm file exists, it is relatively easy to restore the table structure

However, if the drop table is executed and the frm file is deleted, the method provided in this article is not helpful.

In this case, you can try to restore the table structure from ibdata, which is beyond the scope of the following discussion.

Analysis of table structure

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

Mysqlfrm

Mysqlfrm is one of the mysql utilities toolsets

Used to analyze frm files to generate create table commands.

It is no longer updated, and some features have been incorporated into the new version of mysql shell (version 8 and later).

Mysql utilities requires a python2 environment and is very easy to install.

$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 types of modes to interpret frm:

Direct analysis

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

In this mode, the-- diagnostic parameter is required.

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

Analysis with the help of mysql examples

This mode, with the help of new mysql instances, completes the analysis of frm.

You can specify how to open a new mysql instance in two ways.

First, spawn from the current mysql service and specify the mysql service using-- server

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

Second, start a new mysql instance and specify the mysql program path using-- basedir

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

-- port assigns a port to the new instance to avoid conflicts with the current port 3306.

Dbsake

This is a tool found by accident, which describes itself like this in the document:

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

The author must be a very knowledgeable person on mysql, tools from download, installation to use, simple and neat.

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

Online service

There are some online services that are also concerned about such issues.

The experience of using twindb online is very good, and the related toolset is also great.

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

The above is all the contents of the article "what are the ways for mysql to recover the structure of table tables from frm files?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.