The mysqlfrm utility is designed as a recovery tool that reads .frm files and produces facsimile CREATE statements from the table definition data found in the file. In most cases, the CREATE statement produced will be usable for recreating the table on another server or for extended diagnostics. However, some features are not saved in the .frm files and therefore will be omitted. The exclusions include but are not limited to:
foreign key constraints
auto increment number sequences
The mysqlfrm utility has two modes of operation. The default mode
is designed to spawn an instance of an installed server by
reference to the base directory using the
--basedir option or by connecting
to the server with the
option. The process will not alter the original .frm file(s). This
mode also requires the
option to specify a port to use for the spawned server. It must be
different than the port for the installed server and no other
server must be using the port. The spawned server will be shutdown
and all temporary files removed after the .frm files are read.
A diagnostic mode is available by using the
--diagnostic option. This will
switch the utility to reading the .frm files byte-by-byte to
recover as much information as possible. The diagnostic mode has
additional limitations in that it cannot decipher character set or
collation values without using an existing server installation
specified with either the
--basedir option. This can also
affect the size of the columns if the table uses multi-byte
characters. Use this mode when the default mode cannot read the
file or if there is no server installed on the host.
To read .frm files, list each file as a separate argument for the utility as shown in the following examples. You will need to specify the path for each .frm file you want to read or supply a path to a directory and all of the .frm files in that directory will be read.
You can specify the database name to be used in the resulting CREATE statement by prepending the .frm file with the name of the database followed by a colon. For example, oltp:t1.frm will use 'oltp' for the database name in the CREATE statement. The optional database name can also be used with paths. For example, /home/me/oltp:t1.frm will use 'oltp' as the database name. If you leave off the optional database name and include a path, the last folder will be the database name. For example /home/me/data1/t1.frm will use 'data1' as the database name. If you do not want to use the last folder as the database name, simply specify the colon like this: /home/me/data1/:t1.frm. In this case, the database will be omitted from the CREATE statement.
The base directory for the server installed. Use this or
--server for the default
onnection information for a server in the format:
Use this option or
for the default mode. If provided with the diagnostic mode,
the storage engine and character set information will be
validated against this server.
Tables with certain storage engines cannot be read in the default
mode. These include PARTITION,
PERFORMANCE_SCHEMA. You must read these with
--diagnostic mode for
tables that fail to open correctly in the default mode or if there
is no server installed on the host.
To change the storage engine in the CREATE
statement generated for all .frm files read, use the
To turn off all messages except the CREATE
statement and warnings or errors, use the
--show-stats option to
see file statistics for each .frm file.
If you need to run the utility with elevated privileges, use the
--user option to execute the
spawned server using a normal user account.
If you encounter connection or similar errors when running in
default mode, re-run the command with the
--verbose option and view the
output from the spawned server and repair any errors in launching
the server. If mysqlfrm fails in the middle, you may need to
manually shutdown the server on the port specified with
The following example will read a single .frm file in the default
mode from the current working directory using the server installed
in /usr/local/bin/mysql and port 3333 for the
spawned server. Notice the use of the
db:table.frm format for specifying the
database name for the table. The database name appears to the left
of ':' and the .frm name to the right. In this case, we have
database = test1 and table = city, so the
CREATE statement reads
$ mysqlfrm --basedir=/usr/local/bin/mysql test1:city.frm --port=3333 # Starting the spawned server on port 3333 ... done. # Reading .frm files # # Reading the city.frm file. # # CREATE statement for city.frm: # CREATE TABLE `test1`.`city` ( `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `city` varchar(50) NOT NULL, `country_id` smallint(5) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`city_id`), KEY `idx_fk_country_id` (`country_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 #...done.
The following demonstrates reading multiple .frm files in the default mode using a running server. The .frm files are located in different folders. Notice the use of the database name option for each of the files. The t1 file was given the database name temp1 since that is the folder in which it resides, t2 was given db1 since that was specified in the path, and t3 was not given a database name since we used the ':' without providing a database name.
$ mysqlfrm --server=root:pass@localhost:3306 /mysql/data/temp1/t1.frm \ /mysql/data/temp2/db1:t2.frm --port=3310 # Starting the spawned server on port 3333 ... done. # Reading .frm files # # # Reading the t1.frm file. # # CREATE statement for ./mysql-test/std_data/frm_files/t1.frm: # CREATE TABLE `temp1`.`t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 # Reading the t2.frm file. # # CREATE statement for ./mysql-test/std_data/frm_files/t2.frm: # CREATE TABLE `db1`.`t2` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 # # Reading the t3.frm file. # # CREATE statement for ./mysql-test/std_data/frm_files/t3.frm: # CREATE TABLE `t3` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 #...done.
The following demonstrates running the utility in diagnostic mode to read all of the .frm files in a directory.
$ mysqlfrm --diagnostic /mysql/data/sakila # WARNING: Cannot generate character set or collation names without the --server option. # CAUTION: The diagnostic mode is a best-effort parse of the .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 /mysql/data/sakila/city.frm: # The .frm file is a TABLE. # CREATE TABLE Statement: CREATE TABLE `city` ( `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `city` varchar(150) NOT NULL, `country_id` smallint(5) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY `PRIMARY` (`city_id`), KEY `idx_fk_country_id` (`country_id`) ) ENGINE=InnoDB; #...done.
The permissions for using mysqlfrm will vary and depend entirely on how you use it. If you use the utility to read .frm files in a protected folder like the example above (in either mode), you must have the ability to run the server as root.
If you use the utility with a server connection, the user you use to connect must have the ability to read system variables at a minimum (read access to the mysql database).