Whenever MySQL database installation is done, all the database related data and metadata are stored in one folder. This is the actual database schema with some values in it. Let us explore more about it.
The file extensions are as follows:
- .frm – This is the extension of file which contains the schema or definition of the table.
- .myd – This is the extension of file which contains MyISAM table data.
- .myi – This is the extension of file which contains MyISAM table indices.
Inside the MySQL Server 5.5/data/mysql folder, some of the files are *.frm, *.MYD, and *.MYI,
where the asterisks are actual table names. If the MyISAM engine is used, the data folder will contain all the above files, otherwise in the case of InnoDB, the folder contains .frm files.
These database files are used for backup purpose for securing schema, data and indexes for some migration or upgrade of the database. The configuration files for MySQL for Windows and Linux are my.ini and my.conf respectively.
The Path for Windows:
C:\Program Files\MySQL\MySQL Server 5.5\my.ini
In my.ini file, if we search the keyword basedir, we can get the path of the MySQL server installation.
In the similar way, if we search the keyword datadir, we can get the path of the Database root. Apart from this, there are many other files which are created or used by MySQL server to perform various activities. Some of them are as follows
- my.cnf :
Its a MySQL database configuration file. This is the main configuration file of MySQL server.
This is found in the root directory where the installation is done. In this file, the user can find the location of data folder.
The default location of the configuration file is '/etc/my.cnf'
Other file formats which are compatible with MySQL are .ibc, .tmd, .rul, .cnf, .ddl, .ibd, .mysql, .sql, .opt.
Depending on the table type, tables are stored in files with these extensions.
- db.opt :
Whenever database is created or changed using MySQL commands, the characteristics of database are stored in text file namely db.opt file .
- .ibd :
These are the files with extensions *.ibd which stores the data and index of MySQL InnoDB tables. This type of file is created or used by MySQL InnoDB software and associated with it .
- .sock :
All the MySQL database connections are managed by a special file called the socket file. This socket file namely mysqld.sock is created by MySQL service automatically which helps in communicating between different processes.
- Pid file :
The process id of MySQL server is written in such file. The default will be hostname of the MySQL server.
- .db :
These are the files with extensions ' .db ' which stores the data and indexes of BerkeleyDB storage engine.
- error log :
The error log file really plays an important role during troubleshooting of application. These are MySQL error log files which will give the exact reason or information for MySQL failure in the server. This effectively helps in the debug process for any error issue raised in MySQL server. By default it will log errors in hostname.err file.
- Slow Query Log :
Slow query log file have all the ' slow ' SQL queries. The performance of the application goes down because of the MySQL queries taking more time to complete than the expected result. So this helps in monitoring the slow queries which helps in improving the queries for higher performance.
- general query log :
General query log file gives all the general details like server start or end timings, up or down details, connect or disconnect details etc. It is enabled by log[=filename]. By default, MySQL will create hostname.log for the entries .
- binary log files :
The binary log files contains detail information related to any table creation or data modifications made to MySQL server. This also have information regarding the time taken by MySQL statement, state of the server, error codes, metadata for maintenance of log file. This is enabled by -log-bin[=basename] option. By default, its hostname of the server.
- .index :
To monitor which binary log files are used, a binary log index file is created which contains the names of all binary log files. It is enabled by –log-bin-index[=filename] else basename will be the binary log file with the extension .index. By default, relay log index file name is host_name-relay-bin.index .
- .TMD :
These are the intermediate database file created by MySQL server created during repair operations. This file contains information regarding database recoveries. These files are also be created by some other MySQL database operations.
- TRG and TRN Files :
TRG files are trigger parameter files and TRN files are trigger namespace files. In MySQL server, whenever triggers are defined, the definitions are stored in text files namely
tablename.TRG file. It contains triggers for multiple events like BEFORE or AFTER of INSERT, UPDATE or DELETE operations in MySQL.
- .ARZ, .ARM and .ARN files :
The table data and table metadata files have extensions of .ARZ and .ARM respectively. An .ARN file is the optimization file during optimization process. The files are related to Archive Storage Engine.
- .ARZ :
ARZ files are the metadata files for archive tables. The files with this extension stores data for table. The files are included in the backups created by mysqlbackup command of MySQL.
Similar Reads
Flat File Database A database kept in a file known as a flat file is referred to as a flat-file database. Every entry has the same format, and there are no systems in place for indexing or determining the links between items. The file is simple to read. A flat file can be a binary file or a plain text file such as CSV
6 min read
Database Backup from MySQL Backing up your MySQL database is crucial for data protection and recovery. There are several methods available to create backups each with its own benefits and use cases. In this article, we will explore four popular methods for backing up a MySQL database such as using mysqldump, phpMyAdmin, MySQL
4 min read
Types of Databases Databases are essential for storing and managing data in today’s digital world. They serve as the backbone of various applications, from simple personal projects to complex enterprise systems. Understanding the different types of databases is crucial for choosing the right one based on specific requ
11 min read
What is Database? A database is an organized collection of data stored electronically. It allows users and applications to easily access, update, and manipulate information. This data contains text, numbers, images, videos and more. Databases are managed using specialized software known as a Database Management Syste
13 min read
Database Schemas A database schema defines the structure and organization of data within a database. It outlines how data is logically stored, including the relationships between different tables and other database objects. The schema serves as a blueprint for how data is stored, accessed, and manipulated, ensuring
9 min read