MySQL related file types and basic information

MySQL related file types and basic information

This post covers the basic information of files that MySQL Server uses / creates for various tasks.

my.cnf :

It is the main configuration file for MySQL. You may find it under base directory in windows or under /etc/.

.sock :

mysqld creates a socket for programs to connect to and notes in this file. It is named as mysql.sock.

Pid file :

MySQL server write its process ID in this file. You may specify it by –pid-file=file_name otherwise it defaults to hostname of the server.

.frm :

MySQL represents each table by an .frm table format file, stores table definition in the database directory. It is stored as
.frm under data directory.

.MYD :

This is the extension of the Data files for MyISAM tables. It is stored as
.MYD under data directory.

.MYI :

This is the extension of the Index files for MyISAM tables. It is stored as
.MYI under data directory.

[ad#ad-2-300×250]

.ibd :

If you specify innodb_file_per_table option to my.cnf, InnoDB stores each table in its own .ibd file in the same MySQL database directory where the .frm file is created. For InnoDB tables, the data and the indexes are stored together in the .ibd file.

ibdata1 :

Shared tablespace / data file for InnoDB tables when innodb_file_per_table option is not specified in my.cnf, all tables are created in this shared tablespace.

ib_logfile0 & ib_logfile1 :

InnoDB log files. The information is logged in circular fashion. InnoDB applies changes recorded in logfiles to tablespace. This is also important for performance and recovery.

.db :

This is data file for BDB (BerkeleyDB) storage engine which stores table data and indexes.

db.opt :

In CREATE DATABASE command, create_specification options specify database characteristics. Database characteristics are stored in the db.opt file in the database directory.

error log :

This log contains information about mysqld start and stop events, any critical errors that occur while the server is running etc. In my.cnf you can specify log-error[=file_name] option and error log will be created under data directory file_name.err. If no name is specified MySQL will create hostname.err file.

slow query log :

This log contains queries that runs slower than specified long_query_time variable. You can specify log-slow-queries[=file_name] option. If no file name specified, MySQL will log slow queries in host_name-slow.log file under data directory.

general query log :

This file logs all the SQL sent from client about MySQL server and mysqld start up/down details. You may enable it by log[=file_name]. If file_name is not specified MySQL will create hostname.log under data directory.

#sql-55c_6.MYD / #sql-55c_6.frm / #sql-55c_6.MYI :

Temporary files created when you normally issue ALTER TABLE, MySQL creates a copy of table, apply changes, delete original table and then rename temporary table to original. The names are like #sql-…/MYD/MYI/frm.

binary log files :

These files contains events that describe database changes such as table creation operations or changes to table data. You may specify their name by –log-bin[=base_name] option. Default name is value of pid-file option (defaults to hostname of server).

relay log files :

These files contains events read from the binary log of the master and written by the slave I/O thread. Events in the relay log are executed on the slave as part of the SQL thread.

master.info :

This file contains the status and current configuration information for the slave’s connectivity to the master.

relay-log.info :

This file holds the status information about the execution point within the slave’s relay log.

.index :

To keep track of which binary log files have been used, mysqld creates a binary log index file. It contains the names of all used binary log files. You may change it by –log-bin-index[=file_name] option otherwise basename will be same as that of binary log file with default extension “.index”.
In case of replication, relay logs uses .index files for same purpose. The default relay log index file name is host_name-relay-bin.index in the data directory. You may change the name by –relay-log-index option.

.TMD:

This is an intermediate data file for a table that needs to recreate its data file. You can find this files normally during REPAIR TABLE operations.

TRG & TRN Files:

Trigger definitions are stored in plain text files in the directory that contains the schema objects.
The file tablename.TRN is the TRIGGERNAME file stored under database directory.
The file tablename.TRG is the TRIGGERS file. It represents all the table triggers attached to a given table, so this file can contain triggers for multiple events (BEFORE/AFTER, INSERT/UPDATE/DELETE).

.ARZ, .ARM & .ARN files:

These are files related to Archieve Storage Engine.

.ARZ is data files holds data for table, .ARM holds metadata while during table optimization process a .ARN file may appear.