Solved

Data file structure of MySQL

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

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 78

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
remote mysql 8 53
Formating field inside mysql query 2 48
database connection error mysql stops 7 82
MySQL-Design Help 12 67
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 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