Summary: This article will provide a brief overview of MySQL .frm file format. Also, it will discuss methods that can be used to recover MySQL database (db) table from FRM files.
What is FRM File in MySQL?
Irrespective of MySQL storage engine (InnoDB/MyISAM) you are using, the table structure of MySQL database is stored in an FRM file. Essentially, the FRM describes a table definition (including the fields and structure of the table). The file has same name as the database table with ‘.frm’ extension. For instance, table1.frm is an FRM file for MySQL database table named table1.
The FRM file of a tableis located in the directory representing the database to which that table belongs. You can find the name of the directory in the datadir system variable by using the following command:
| mysql -uUSER -p -e ‘SHOW VARIABLES WHERE Variable_Name = “datadir”‘ |
Opening FRM File in MySQL
MySQL users often look for ways on how to open .frm file in MySQL. It is imperative for you to understand that FRM files cannot be opened manually; rather they are referenced by MySQL for formatting a table in MySQL database.
Further, the FRM files are saved along with MySQL data file (i.e., .MYD for MyISAM and ibdata for InnoDB db). The data file contains the actual data stored in a db. Both, MySQL data file and .frm files are required to recognize the database. Most importantly, you need both the files to recover a dropped/corrupted table or restore a MySQL db.
How to Recover Table Structure from .frm Files?
As MySQL users, you may have come across situations that call for recovering table structure to find deleted/lost data or to recreate the table. Fortunately, using MySQL utility such as ‘mysqlfrm’ can help you restore table structure from .frm files.
The ‘mysqlfrm’ utility helpsread .frm filesandgenerate ‘CREATE TABLE’ statements that can be used for recreating table structure on another server.The utility provides two different ways of performing structure recovery.
Method 1 – Run the mysqlfrm Utility with ‘–diagnostic’ Option
Using ‘mysqlfrm’ utility with –diagnostic helps read the .frm file byte-by-byte,retrieving all the information possible without the need of MySQL instance.
Note: Use this method if you need to extract information from damaged .frm files without running MySQL Server instance.
| shell> mysqlfrm –diagnostic /usr/local/mysql/data/database
# 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 /usr/local/mysql/data/database/test_table.frm: # The .frm file is a TABLE. # CREATE TABLE Statement: CREATE TABLE `test_table` ( ` test_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, ` name` varchar(150) NOT NULL, `type` smallint(5) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY `PRIMARY` (`test_id`), KEY `name_idx` (`name`) ) ENGINE=InnoDB; |
This method has a limitation. As mentioned in the warning message in the beginning of this method, you cannot recover all the information. For instance, “character set or collation can’t be recovered without using an existing server installation”.
Method 2 – Using a Spawned Server to Recover Table Structure from .frm File
This method requires connecting to MySQL Server instance.
In this method, the tool reads an .frm file from the –basedir directory using the server installed in the path ‘/usr/local/bin/mysql’ along with spawned server port ‘3333’.
| shell> mysqlfrm –basedir=/usr/local/bin/mysql database:test_table.frm –port=3333
# Starting the spawned server on port 3333 … done. # Reading .frm files # # Reading the test_table.frm file. # # CREATE statement for test_table.frm: #
CREATE TABLE `database’. `test_table`( `test_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `type` smallint(5) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`test_id`), KEY `name_idx ` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
This method requires using a spawned server to recover all the information from the .frm file. Once the information is recovered, it shuts down the spawned server. However, this method might not help you recover foreign key constraints and auto-increment number sequences.
Wrapping Up
MySQL utility, mysqlfrm, can help you recover table structure from .frm files. However, you might not be able to restore all the information. A better alternative is to use a MySQL database repair software such as Stellar Repair for MySQLthat helps restore the entire database and all its components including tables, primary keys, views, etc. without the risk of data loss. Also, the software helps in repairing table data on which default and auto-increment properties are applied.
