本文列出了MySQL Server用到的文件类型及其用途.
my.cnf :
MySQL Server主配置文件，可以在MySQL Server主安装目录中找到。Linux下一般在/etc/mysql/my.cnf
.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将它的进程ID写到这个文件中，用户可以指定pid文件,否则MySQL 默认写到hostname.pid文件中
.frm :
一个.frm文件代表一个表，它存储了表定义，存放在data目录中。
.MYD :
.MYD文件时MyISAM 表的数据存储文件，它也存放在data目录下。
.MYI :
MyISAM表的索引文件
.ibd :
如果在my.cnf中指定了innodb_file_per_table(一个InnoDB表一个ibd文件)， 那么每个Innodb表将分别存储一个.ibd文件中， Innodb表的数据和索引都存储在.ibd文件中，不分别存储。
ibdata1 :
如果my.cnf中没有定义innodb_file_per_table选项，那么所有Innodb的表和数据都将存储在ibdata1文件中。ibdata1表示Innodb共享的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 :
BDB (BerkeleyDB) 存储引擎的data file。同时存储数据和索引。
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 :
当用户运行ALTER TABLE 时，通常会创建临时文件。执行Alter Table语句时，MySQL先创建表的备份，然后在上面修改，删除原始数据，完成Alter后将临时文件重命名为原来的名字。临时文件的名字格式通常是 #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.