A MySQL database creates a number of different data files in the mysql data directory (typically /var/lib/mysql). The following are the differences between the data files created:

File Extension Purpose
.frm table definition
.MYD table data
.MYI table indices

I believe these files are created for both MyISAM and InnoDB table types because the MySQL documentation references these types with respect to MyISAM and I definitely see them on my InnoDB tables.

I couldn’t find this information directly in the MySQL documentation and google wasn’t much help either so hopefully this post will save someone else some search effort.

  27 Responses to “MySQL data files .MYD, .MYI, and .frm”

  1. >hopefully this post will save someone else some search effort.
    It did, thanks for your definition !

  2. Thanks for your post. It did help me.

  3. Thanks from me, as well … answered this MySQL newbie’s questions right away … :-)

  4. Thanks from another satisfied reader :)

  5. Thanks, I was looking for this info too.

  6. As database is created , one folder is created mapping with database name.
    As you create tables in it for each table these three files are created

    .frm:- table definition
    .MYD:-table data
    .MYI:-table indices

    if you copy that folder from wamp\mysql\data\(database name folder) to your pc.
    and then delete that database from console,, after that put again (database name folder) to wamp\mysql\data\ that path,,,,it recovers the all table structure with table data… :)

  7. Thanks a lot for the information.

  8. Thx :-) ..

  9. Very useful information. Could we recover .MYD or .MYI files, if these files had been missed?

  10. Thanks A lot Dude…….This article helped me a lot.

  11. For INNODB, only 1 file of type .frm will be created. For MyISAM all the above 3 files will be created. Incase of INNODB, .frm file holds data and indexes.

  12. i have an also a whichs disks is full so how can i move these .myd and .myi files from disk and database safely?

  13. Thanks dude.

  14. Thanks. It helped.

  15. Hello
    I am using xampp. I made some tables and noticed some of them didnt have the MYD and MYI files, hence i could not transfer the data to another computer. I would like to ask is there a problem with my msql or is it a bug. Then how can i fix this?

  16. Thank you so much for taking the time to post this. Apparently you saved me time worrying about whether the files with these extensions showing in my /tmp dir was an indication that I’d been hacked.

  17. InnoDB stores each newly created table in its own tbl_name.ibd file in the appropriate database directory. Unlike the MyISAM storage engine, with its separate tbl_name.MYD and tbl_name.MYI files for indexes and data, InnoDB stores the data and the indexes together in a single .ibd file. The tbl_name.frm file is still created as usual.

    http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

  18. thanks A lot……very simple
    and too hard

  19. Thanks a lot. Simple, direct and clean.

  20. Thank you from here as well. It helps me. :)

  21. This blog site has lots of extremely useful info on it! Cheers for informing me!

  22. I am facing a problem related to mysql-php. I have got a php-mysql project, I dont know in which version this php, mysql (Database have MYD, MYI, FRM files) project have done.Then, I have created a database in same name and attach the tables from that project in the new database. When I run the project . It shows no database connection… ? (Anyway, I have changed the hostname, username, password accordingly). Can anybody help me regarding this…

  23. Thanks a lot for this information. I’d like to ask a question if it is not inconvenient for you.

    I have been SQL Server developer and am using MySQL for the first time. In SQL Server, MDF and LDF files are created for a database. When I want to move my database, I usually copy these files in my external drives and attach MDF file in other SQL Server system. This way table definitions, indices, procedures and, most importantly, records are intact. Is there any way I can use same way for MySQL databases also?

    I use Windows Xp/7 with MySQL 5.1.

    Thank you.

    Ritesh

  24. In my system c:/…../mysql/data/ a forlder named bugs is present and I think that is my database name. The bugs folder contains 65 files which are table names with .frm extension. If I will make a copy of the buds folder and rename it to bugsBKP. Will the bugsBKP folder behaves like a database containing all 65 tables?

  25. Helpful, thank you.

  26. it saved a lota time of me, thank you very much for it.

  27. Thanks for your short and clear clarification about data files created for MySQL tables.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" highlight="">

   
© 2013 rootsmith blog Suffusion theme by Sayontan Sinha