When things go wrong badly enough that your server is down or cannot be restarted, but you can still access the data on disk, you may find yourself faced with a number of complex recovery tasks.
One of those is the need to discover the structure of a particular table or set of tables. Perhaps this is needed for an emergency recovery, a redeployment, or setup for a forensic investigation. Whatever the case, without a running MySQL server it is not possible to know the structure of a table unless you keep meticulous notes and/or use some form of high availability (redundancy) or source control for your database schemas.
Fortunately, there is a utility for situations like this. The mysqlfrm utility can be used to discover the structure of a table directly from the .frm files.
With a downed or offline server, discover the structure of a table. More specifically, generate the CREATE TABLE SQL command.
sudo env PYTHONPATH=$PYTHONPATH mysqlfrm --basedir=/usr/local/mysql \
--port=3333 --user=# Spawning server with --user=
user. # Starting the spawned server on port 3333 ... done. # Reading .frm files # # Reading the books.frm file. # # CREATE statement for /usr/local/mysql/data/kindle/books.frm: # CREATE TABLE `welford_kindle`.`books` ( `ISBN` char(32) NOT NULL PRIMARY KEY, `title` char(128) DEFAULT NULL, `purchase_date` date DEFAULT NULL, `cost` float(10,2) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 #...done.
For this example, we used three required parameters; the base directory for the offline server (basedir), a new port to use for the spawned server (port), and a user name to use to run the spawned server (port). The later is necessary since we must launch the mysqlfrm utility as root (sudo) in order to be able to read (copy) files from the protected data directory of the host server.
--port option is always
required for running the utility in default mode (it is not
needed for diagnostic mode). You must supply a valid unused
port. The utility will check to see if the port is in use and
if so will produce an error. The port is used to spawn a
temporary instance of a MySQL server in order to attempt to
recover the .frm file. This instance is shutdown at the end of
the process and is not used for diagnostic mode.
We use the
instead of the
option because we were faced with a situation where the
original server was offline (down). Note that you can use the
--basedir option for a
running server if you do not want the utility to connect to
the original server in any way.
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 spawned server with privileges that allow you to read the protected files. For example, you could use a user account that has root-level privileges.
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 including read access to the mysql database.
You should never use the root user to spawn the server nor should you use the mysql user when spawning the server or running the utility.
The utility is designed to work on the host where the .frm files reside. It does not permit connecting to a remote host to read .frm files.
If something goes wrong during the spawning of the server, use the verbosity option three times (-vvv) to turn on maximum depth debug statements. This will ensure you will see all of the messages from the start of the spawned server from bootstrap onward. Look for errors in these statements as to why the spawned server will not start.
If you do not want to permit the utility to launch a temporary instance of a MySQL server, you should use the diagnostic mode instead. However, the diagnostic mode may not recover all of the options for a table.
The utility performs a best effort approximation of the CREATE
statement when run in diagnostic mode. As such, if you read a
.frm file that uses character sets or
collations other than the default and you do not use a
--server option to connect to
a server to read the character sets, this can result in
miscalculated column sizes.
For example, suppose your default character set is latin1 which uses 1 byte per character. Let us also suppose you are attempting to read a .frm file that uses a character set that uses 3 bytes per character. Furthermore, we have no server to connect. In this case, the column sizes may be off by a factor of 3. A case in point would be a field such as col_a char(3) would appear in the output of the mysqlfrm utility as col_a char(9).
To mitigate risks such as this and to produce the most
accurate CREATE statement in diagnostic mode, always use the