4

I wonder if it is possible to retrieve the MySQL/MariaDB table definitions from the .frm files?

For example, if I wanted to steal your database and I had access to your MySQL/MariaDB files but didn't have its user account, could I copy all the .frm files from your database, paste them into my own server and then run mysqldump --no-data to retrieve the exact DDL?

Next if the answer is yes:

  • Does it apply to different server versions and/or OS?
  • What can I do to prevent or make it difficult for people to do this with my database?

Next if the answer is no:

  • Is there any dependency to other file? Or would I have to do something?
  • What exactly is the role of the .frm files?
1

A 'partial' Answer...

The .frm contains, in an encoded format, the CREATE TABLE for the one table.

Copying a .frm into your copy of MySQL would, in theory, give you the schema definition. But, other info is needed. So, now the challenge is to try to re-establish a table where the data is lost, but the schema is still in the .frm. MyISAM depends on the existence of a .MYD (.MYI can be reconstructed); InnoDB has some hooks in ibdata1, but these may be reconstructable. (Hence, this Answer is only 'partial'.)

.frm files are probably binary compatible between OS versions, even big-endian vs little-endian. And across large swaths of MySQL/MariaDB versions. A table with a JSON column would not be backward compatible. There have been only a few other incompatible changes in the past decade.

As for hacking into your system. Which is easier to get into -- The filesystem, or MySQL? The .frm file is somewhat protected by directory and file permissions. MySQL is somewhat protected by its password mechanism. Obviously, you should do whatever you can to prevent access to both.

A common entry point into MySQL is via web arguments (<form>s in HTML) where the values are not properly escaped before INSERTing text into the database. There are different solutions to that entrypoint, depending on which API is used. (I've seem a hacker post /etc/passwd on the Internet; he got it from a PHP program that failed to escape simple data entry fields.) See "SQL Injection".

Is your schema the most valuable thing? I would expect the data to be more important. And also the root password for your server.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.