Documentation Home
MySQL Utilities 1.5 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb
EPUB - 365.4Kb
HTML Download (TGZ) - 287.5Kb
HTML Download (Zip) - 298.6Kb

MySQL Utilities 1.5 Manual  /  ...  /  My Server Crashed! I Need to Know the Structure of a Table. How Can I Do That?

3.3.2 My Server Crashed! I Need to Know the Structure of a Table. How Can I Do That?

When things go wrong badly enough that your server is down, but you can still access the disks, 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 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).

Fortunately, there is a utility for situations like this. The mysqlfrm utility can be used to discover the structure of a table.


With a downed or offline server, discover the structure of a table. More specifically, generate the CREATE TABLE SQL command.

Example Execution

shell> sudo env PYTHONPATH=$PYTHONPATH mysqlfrm --basedir=/usr/local/mysql \
          --port=3333 --user=<user> /usr/local/mysql/data/welford_kindle/books.frm

# 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` (
  `title` char(128) DEFAULT NULL,
  `purchase_date` date DEFAULT NULL,
  `cost` float(10,2) DEFAULT NULL



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.

The --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.

We use the --basedir option instead of the --server 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.

Permissions Required

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).

Tips and Tricks

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.


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. Lets 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 --server option.

Download this Manual
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb
EPUB - 365.4Kb
HTML Download (TGZ) - 287.5Kb
HTML Download (Zip) - 298.6Kb
User Comments
Sign Up Login You must be logged in to post a comment.