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

Comments:

Thank you for frmdump, I needed a perl version, I thought it might be useful.
Cheers,
Stig

{{{
#!/usr/bin/perl

use strict;

my %dbtypes = (
'0' => 'DB_TYPE_UNKNOWN',
'1' => 'DB_TYPE_DIAB_ISAM',
'2' => 'DB_TYPE_HASH', '3' => 'DB_TYPE_MISAM', '4' => 'DB_TYPE_PISAM',
'5' => 'DB_TYPE_RMS_ISAM', '6' => 'DB_TYPE_HEAP', '7' => 'DB_TYPE_ISAM',
'8' => 'DB_TYPE_MRG_ISAM', 9 => 'DB_TYPE_MYISAM', '10' => 'DB_TYPE_MRG_MYISAM',
'11' => 'DB_TYPE_BERKELEY_DB', '12' => 'DB_TYPE_INNODB',
'13' => 'DB_TYPE_GEMINI',
'14' => 'DB_TYPE_NDBCLUSTER',
'15' => 'DB_TYPE_EXAMPLE_DB',
'16' => 'DB_TYPE_ARCHIVE_DB',
'17' => 'DB_TYPE_CSV_DB',
'18' => 'DB_TYPE_FEDERATED_DB',
'19' => 'DB_TYPE_BLACKHOLE_DB',
'20' => 'DB_TYPE_PARTITION_DB',
'21' => 'DB_TYPE_BINLOG',
'22' => 'DB_TYPE_SOLID',
'23' => 'DB_TYPE_PBXT',
'24' => 'DB_TYPE_TABLE_FUNCTION',
'25' => 'DB_TYPE_MEMCACHE',
'26' => 'DB_TYPE_FALCON',
'27' => 'DB_TYPE_MARIA',
'42' => 'DB_TYPE_FIRST_DYNAMIC',
'127' => 'DB_TYPE_DEFAULT',
);

sub frm_dump {
my ($fname) = @_;

# print "Dumping {$fname} using .frm header format ... \\n" ;
open my $fh, '<' ,$fname or die("can't open file ". $fname);
binmode $fh;
sysread($fh, my $buf, 64);
close $fh;
my @buf = split //,$buf;

printf("\\n OFFSET |Len| Expected | Found | Comments \\n");
printf(" (Dec) | | (Hex) | (Hex) | \\n");
printf("-----------------------------------------------\\n");
printf(" 0 | 2 | FE 01 | %02x %02x | Magic mark. 0xFE 01\\n",
ord($buf[0]), ord($buf[1]) );
printf(" 2 | 1 | 09 | %02x | FRMVER + 3 + VarcharInTab?1:0\\n",
ord($buf[2]) );
printf(" 3 | 1 | 09(MyISAM)| %02x | Found type: %s\\n",
ord($buf[3]), $dbtypes{ord($buf[3])});
printf(" 4 | 1 | ?? | %02x | ?? \\n", ord($buf[4]));
printf(" 5 | 1 | 0 | %02x | Always 0 \\n", ord($buf[5]));
printf(" 6 | 2 | 00 10 | %02x %02x | IO_SIZE 4096(dec)is 0x1000(LittleEnd)\\n",
ord($buf[6]), ord($buf[7]));
printf(" 8 | 2 | 01 00 | %02x %02x | ??? \\n",
ord($buf[8]), ord($buf[9]));
printf(" \* | \* | ....... | ...... | ....... \\n");
printf(" 33 | 1 | 05 | %02x | Marked 5 for Ver5 FRM File\\n",
ord($buf[33]));
printf(" 38 | 1 | 08? 2e? | %02x | default charset num for this table.\\n".
" | 08 is latin1 default. 2e is utf8_bin\\n",
ord($buf[38]));
my $vers = ord($buf[54]);
$vers = ($vers \* 256) + ord($buf[53]);
$vers = ($vers \* 256) + ord($buf[52]);
$vers = ($vers \* 256) + ord($buf[51]);
printf(" 51 | 4 | Version | %06x | MySQL Version ID Found: Dec: %d\\n",
$vers, $vers);
}

my $fname = shift || die ("Usage: $0 frm-file-name \\n") ;
frm_dump($fname);
}}}

Posted by Peter (Stig) Edwards on October 24, 2008 at 11:38 AM IST #

This is just great. We have success to re-mount old MySql 4 database after all.

Thank you very much

Posted by WotLK Power Leveling on November 18, 2008 at 12:16 AM IST #

Hi,
@Thava, @Peter,
would you mind posting your scripts at MySQL Forge? ( http://forge.mysql.com/tools/ )

They could be more easily retrieved by all the users.

Thanks

Giuseppe

Posted by Giuseppe Maxia on February 15, 2009 at 07:08 PM IST #

Hello,

Would you happen to know the header information for all three mySQL file types (.frm, .myi, .myd)? I am trying to recover the mySQL database from a damaged hard drive using raw file search, but in order to do that, I need to first give the software the header information (Dec offset, HEX, etc...)of all 3 file types so it knows what to look for.

Thank you in advance.

Mike

Posted by Mike on March 04, 2009 at 09:35 PM IST #

Post a Comment:
  • HTML Syntax: NOT allowed
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