Use mysqlfrm to restore the frm table structure and mysqlfrmfrm table structure

Use mysqlfrm to restore the frm table structure and mysqlfrmfrm table structure

1. Install mysqlfrm

-----------------------------

Because mysqlfrm is part of the mysql-utilities tool, we can install mysql-utilities, download the corresponding source code package, compile and install it.

Shell> tar-xvzf mysql-utilities-1.6.4.tar.gz
Shell> cd mysql-utilities-1.6.4
Shell> python./setup. py build
Shell> python./setup. py install

After the installation is complete, execute files such as mysqlfrm in the corresponding python execution directory.

2. About mysqlfrm Parameters

-------------------------------

-- Basedir: for example -- basedir =/usr/local/percona-5.6.21

-- Server: for example -- server = user: password@192.168.1.100: 3306

-- Diagnostic: Enable the byte mode to restore the frm structure.

-- User: Start the MySQL user

3. Use mysqlfrm

-------------------------

Restore in -- basedir mode:

[16:35:29-root @ br3cy1sw :~ ] # Mysqlfrm -- basedir =/usr/local/percona-5.6.21 // root/t1.frm -- port = 3434 -- user = mysql -- diagnostic
# WARNING The -- port option is not used in the -- diagnostic mode.
# WARNING: The -- user option is only used for the default mode.
# WARNING: Cannot generate character set or collation names without the -- server option.
# CAUTION: The diagnostic mode is a best-effort parse of. frm file. as such, it may not identify all of the components of the table correctly. this is especially true for damaged files. it will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading. frm file for/root/t1.frm:
# The. frm file is a TABLE.
# Create table Statement:

Create table 'root'. 't1 '(
'A' int (11) not null,
'B' int (11) DEFAULT NULL,
'C' int (11) default null,
'D' varchar (600) default null,
Primary key 'primary' ('A '),
KEY 'idx _ t1_bc '(' B ', 'C ')
) ENGINE = InnoDB;

#... Done.

Restore using -- server:

[16:35:10-root @ br3cy1sw :~ ] # Mysqlfrm -- server = user: password@192.168.1.100: 3306/root/t1.frm -- port = 3434 -- user = mysql -- diagnostic
WARNING: Using a password on the command line interface can be insecure.
# WARNING The -- port option is not used in the -- diagnostic mode.
# WARNING: The -- user option is only used for the default mode.
# Source on 192.168.1.100:... connected.
# CAUTION: The diagnostic mode is a best-effort parse of. frm file. as such, it may not identify all of the components of the table correctly. this is especially true for damaged files. it will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading. frm file for/root/t1.frm:
# The. frm file is a TABLE.
# Create table Statement:

Create table 'root'. 't1 '(
'A' int (11) not null,
'B' int (11) DEFAULT NULL,
'C' int (11) default null,
'D 'varchar (200) collate' utf8 _ general_ci 'default null,
Primary key 'primary' ('A '),
KEY 'idx _ t1_bc '(' B ', 'C ')
) ENGINE = InnoDB default charset = utf8;

#... Done.

Note that the varchar restored by using -- basedir is three times that of the -- server mode. This is because mysqlfrm cannot perform character encoding verification when using the basedir mode.

I read the -- server File again: (focus on the text marked in red and bold). We recommend that you:If you can use the -- server mode, try to use -- server while ensuring that the mysqld environment is consistent with the original production environment.

-- Server = server
Connection information for a server. Use this option or -- basedir for the default mode.If provided with the diagnostic mode, the storage engine and character set information are validated against this server.

4. References:

Mysqlfrm official documentation
Https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqlfrm.html