Solved

Data file structure of MySQL

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

Expert Comment

by:arnold
Comment Utility
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 76

Expert Comment

by:arnold
Comment Utility
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
Comment Utility

>>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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

743 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

11 Experts available now in Live!

Get 1:1 Help Now