Restore the FRM table structure using MYSQLFRM

1, MYSQLFRM Installation

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

Since MYSQLFRM is part of the Mysql-utilities tool, we can install mysql-utilities, download the corresponding source package, compile and install.

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, the executable file can be mysqlfrm and so on in the corresponding Python execution directory.

2, MYSQLFRM related parameters Introduction

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

--basedir: such as--basedir=/usr/local/percona-5.6.21

--server: such as--server=user:[email protected]:3306

--diagnostic: Turn on byte mode to restore FRM structure

--user: Start MySQL user via for MySQL

3, Mysqlfrm use

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

Restore using--basedir mode:

[16:35:[email protected]:~]# 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 a 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 the parse of the. frm file. As such, it may not be identify all of the components of the table correctly. This is especially true for damaged files. It would also not read the default values for the columns and the resulting statement is not being syntactically correct.
# Reading. frm file for/root/t1.frm:
# the. frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE ' root '. ' T1 ' (
' A ' int (one) is not NULL,
' B ' int (one) DEFAULT NULL,
' C ' int (one) DEFAULT NULL,
' d ' varchar (+) DEFAULT NULL,
PRIMARY KEY ' PRIMARY ' (' a '),
KEY ' IDX_T1_BC ' (' B ', ' C ')
) Engine=innodb;

#...done.

To recover using--server mode:

[16:35:[email protected]:~] #mysqlfrm--server=user:[email protected]: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 a used in the--diagnostic mode.
# warning:the--user option is a used for the default mode.
# Source on 192.168.1.100: ... connected.
# caution:the Diagnostic mode is a Best-effort the parse of the. frm file. As such, it may not be identify all of the components of the table correctly. This is especially true for damaged files. It would also not read the default values for the columns and the resulting statement is not being syntactically correct.
# Reading. frm file for/root/t1.frm:
# the. frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE ' root '. ' T1 ' (
' A ' int (one) is not NULL,
' B ' int (one) DEFAULT NULL,
' C ' int (one) DEFAULT NULL,
' d ' varchar ($) COLLATE ' utf8_general_ci ' DEFAULT NULL,
PRIMARY KEY ' PRIMARY ' (' a '),
KEY ' IDX_T1_BC ' (' B ', ' C ')
) Engine=innodb DEFAULT Charset=utf8;

#...done.

Notice that the varchar recovered by using--basedir is 3 times times the--server mode, which should be caused by the inability of the mysqlfrm to perform character encoding checks when using Basedir mode.

Again looked at the next--server file: (Focus on the red bold text), suggested: can use--server mode, as far as possible using--server while ensuring that the MYSQLD environment and the original production environment consistent.

--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 is validated against this Server.

4. Reference materials:

MYSQLFRM Official documents
Https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqlfrm.html

Restore the FRM table structure using MYSQLFRM