2

How I can find out the MySQL database engine in the file system (data structure, no MySQL commands possible)?

It would be nice if you can help me.

Best regards, Jonniboy

Jonniboy
  • 136
  • 1
  • 13
  • Thank you, guys. I want to view the files. Do one of you know a good hex editor to view this files as hex code? It should be an editor for the linux terminal. I use Debian 6.0. – Jonniboy Nov 22 '13 at 00:40
  • `od -x` should always be available on a standard Linux box. – Bill Karwin Nov 22 '13 at 01:46

2 Answers2

4

MySQL ".frm" (table) files have a file signature and a defined header format.

This format is briefly explained in 8 bits: MySQL File formats and headers:

  1. The first two bytes are always FE,01
  2. The 4th byte is the storage type: e.g. 0C for InnoDB, 09 for MyISAM, 14 for MyIASM w/ partitions. (See Bill Karwin's comment; these are extracted from the legacy_db_type enum.)

That should be all the information required to perform a cursory check - either with file (which may or may not need additional rules) or by manual inspection with something like xxd, e.g.

sh$ xxd -l 4 table.frm

Keep in mind that a single database may contain MyISAM and InnoDB tables.

user2864740
  • 60,010
  • 15
  • 145
  • 220
  • Thank you very much! This is the solution of my problem. So I can easily check if the table use MyISAM as it's engine or not (certainly that means it's most probably InnoDB). :) – Jonniboy Nov 22 '13 at 00:49
  • It would be nice if you can edit your answer because the command is wrong. The command is xxd, not xdd. ;) – Jonniboy Nov 22 '13 at 01:10
  • 1
    According to sql/handler.h, DB_TYPE_MYISAM = 0x09, DB_TYPE_INNODB = 0x0C, and DB_TYPE_PARTITION_DB = 0x14. There are also a bunch of others defined in the same `enum legacy_db_type`. – Bill Karwin Nov 22 '13 at 01:21
  • @BillKarwin "legacy_db_type" doesn't sound so promising :| Anyway, thanks for the value, I've updated my answer. – user2864740 Nov 22 '13 at 01:38
  • It seems like the MySQL developers hate the .frm file, because it's inflexible and limits their ability to add features to the product. I think they're gradually moving in the direction of making that file obsolete. – Bill Karwin Nov 22 '13 at 01:43
0

SHOW ENGINES is the syntax

http://dev.mysql.com/doc/refman/5.0/en/show-engines.html for more info

WebsterDevelopine
  • 117
  • 4
  • The problem is that I haven't any mysql client on my system. That's why I can't use these MySQL commands. – Jonniboy Nov 22 '13 at 00:22
  • you have PHP? Do you have a PHP connection string already setup so you can connect? – WebsterDevelopine Nov 22 '13 at 00:23
  • 1
    I've no opportunity to connect to this server from external MySQL clients. I've the data structure in /var/lib/mysql/ and want to find out the engine. – Jonniboy Nov 22 '13 at 00:30
  • But I've an idea. The MyISAM engine only use MYI and MYD files in the database directory. So I think that InnoDB don't use them (and this engine only uses .frm files). Can it be that database directories which only contain .frm files take InnoDB as engine? – Jonniboy Nov 22 '13 at 00:35
  • @Jonniboy That is not necessarily correct. See my linked article. – user2864740 Nov 22 '13 at 00:39
  • Yes, I've forgot it. But I think that my databases do not use two engines. Nevertheless, it would be possible. – Jonniboy Nov 22 '13 at 00:43
  • There are other table types besides MyISAM and InnoDB. Also, there are partitioned tables so there would be no .MYD for the table, though there would be .MYD files for each partition of it. Also there are options to store the data files outside the data directory. So many corner cases! – Bill Karwin Nov 22 '13 at 01:45