Documentation Home
MySQL Utilities 1.5 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 1.5Mb
PDF (A4) - 1.5Mb
EPUB - 366.2Kb
HTML Download (TGZ) - 289.0Kb
HTML Download (Zip) - 301.1Kb


MySQL Utilities 1.5 Manual  /  ...  /  How do you recover the CREATE statement from a damaged or offline server?

3.2.2 How do you recover the CREATE statement from a damaged or offline server?

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.

Objectives

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

Discussion

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

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.

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.

Risks

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


User Comments
Sign Up Login You must be logged in to post a comment.