I am trying to convert .frm (sql data files) to text files, The reson for this is becouse i want to see how doe's the sql engine storage the data "behind the scenes", For example: when using "ENUM" sql storage an "INTEGER" called "INDEX", So if anyone know a software or method to convert the .frm to .txt, Thank you all and have a nice day.
Add a comment
|
1 Answer
This article provides a proven way of doing it:
The basics of how to do this are not too hard to understand, it’s the details that get tricky. The first part of this problem is how to extract the table definition from the .frm files. I’ll cover the second part of the problem in another post. You could write a program that reads and parses the .frm file and outputs the table definition, but that’s a lot of work — especially when you can trick MySQL into doing it for you. Here is how you would do this for the file foo.frm.
mysql> CREATE TABLE `test`.`foo` (id int) ENGINE=InnoDB;
We’ve created an InnoDB table called foo. MySQL has written a foo.frm and a foo.ibd file in $datadir/test. It has also made a record of this table in the data dictionary.
mysql> FLUSH TABLES;
This causes MySQL to close all open tables and flush the query cache. The idea is to force MySQL to forget about the table “foo` that we just created.
bash# cp foo.frm /var/lib/mysql/test;
We just overwrote the table definition for test.foo. We did it hot, while MySQL was still running.
mysql> SHOW CREATE TABLE `test`.`foo`;
That will output the table definition for the foo.frm file we just copied into the test directory. MySQL will probably be a bit confused, as the information in the .frm file is not likely to match what is in the system table space. You will probably see a message like this in your error log:
[ERROR] Table ./test/foo has no primary key in InnoDB data dictionary, but has one in MySQL! If you created the table with a MySQL version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MySQL internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + re import of the table.
That’s OK though, just clean up.
mysql> DROP TABLE `test`.`foo`;
To avoid the annoyance of having to go through this for a few hundred tables use the script provided at: bluegecko article. You should be sorted. Chagbert.
