Solved

Data file structure of MySQL

Posted on 2008-10-27
3
1,575 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=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.wikiped…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

947 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now