?
Solved

Data file structure of MySQL

Posted on 2008-10-27
3
Medium Priority
?
1,589 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 79

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 79

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:
Umesh earned 2000 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

762 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