Solved

Data file structure of MySQL

Posted on 2008-10-27
3
1,580 Views
Last Modified: 2008-11-02
How MySQL stores data. Which file contains the data. In windows, whats the default location of DB. Here I am not concern about table information files but more on real data.
0
Comment
Question by:Shabzt
  • 2
3 Comments
 
LVL 77

Expert Comment

by:arnold
ID: 22816730
Table information and the real data are in the data folder where you installed mysql.  go through the start\programs\mysql\mysql server\ right click on the "mysql instance config" and get properties. Then go to the target location. move up one directory from bin and there you should find a data directory.
Another option is to look in the main directory where mysql is installed and consult the my.ini file there which should point you to the datadir.
0
 
LVL 77

Expert Comment

by:arnold
ID: 22816742
get the gui tools if you do not have them: http://dev.mysql.com/downloads/gui-tools/5.0.html 

Use the mysql administrator to look at the configuration/variables which will also point you to where the files are stored.
0
 
LVL 26

Accepted Solution

by:
ushastry earned 500 total points
ID: 22819515

>>How MySQL stores data. Which file contains the data. In windows, whats the default location of DB. Here I am not concern about table information files but more on real data.

It depends upon which engine you use..

MyISAM -
           Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.

InnoDB -
               InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a tablespace, which may consist of several files (or raw disk partitions). This is different from, for example, MyISAM tables where each table is stored using separate files.

If you specify no InnoDB configuration options, MySQL creates an auto-extending 10MB data file named ibdata1 and two 5MB log files named ib_logfile0 and ib_logfile1 in the MySQL data directory.

Merge -
          When you create a MERGE table, MySQL creates two files on disk. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format, and an .MRG file contains the names of the tables that should be used as one. The tables do not have to be in the same database as the MERGE table itself.

To view the edfault data dir on windows.. just run below

mysql> show variables like 'datadir';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| datadir       | E:\MySQLMaster\data\ |
+---------------+----------------------+
1 row in set (0.00 sec)

mysql>




On how each engines stores data.. pls take a look at the manual

http://dev.mysql.com/doc/refman/5.1/en/myisam-storage-engine.html
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question