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