Friday Aug 01, 2008

How to dump mysql table definition file header

If you decide to copy over one table from MySQL installation to another installation, this could be done as simple as copying over your-table-name.\* in data directory. Ofcourse, to do this, the database should not be running at the time of copying.

For example, for tables created by MyISAM storage engine, the files to be copied for table mytable are mytable.frm, mytable.MYD and mytable.MYI. The ".frm" file contains table definition, the ".MYI" contains info about index and ".MYD" contains data.

If you do this kind of stuff often, you may want to dump the header of the ".frm" header file. I just wrote a script for this. You can download this php script "frmdump" from here. Note that it is a php script, but meant to be run from command line (not to be used as web page) :


   $  ./frmdump   mytable.frm
  
Dumping  mytable.frm using .frm header format ...

 OFFSET |Len| Expected   | Found  | Comments
 (Dec)  |   |  (Hex)     | (Hex)  |
-----------------------------------------------
 0      | 2 |  FE 01     | fe 01  | Magic mark. 0xFE 01
 2      | 1 |  09        | 09     | FRMVER + 3 + VarcharInTab?1:0
 3      | 1 |  09(MyISAM)| 09     | Found type: DB_TYPE_MYISAM
 4      | 1 |  ??        | 03     | ??
 5      | 1 |   0        | 00     | Always 0
 6      | 2 |  00 10     | 00 10  | IO_SIZE 4096(dec)is 0x1000(LittleEnd)
 8      | 2 |  01 00     | 01 00  | ???
 \*      | \* |  .......   | ...... | .......
 33     | 1 |  05        | 05     | Marked 5 for Ver5 FRM File
 38     | 1 |  08? 2e?   | 2e     | default charset num for this table.
                                  | 08 is latin1 default. 2e is utf8_bin
 51     | 4 |  Version   | 00ea66 | MySQL Version ID Found: Dec: 60006

Just notice the 4 bytes at offset 51. That is the MySQL version ID that created this .frm file! The decimal 60006 indicates that this is 6.0.6 (it is now alpha) release.

If you want to know more about the file format you can check out this link

About

thavaa

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today