What is this MySQL file used for?

February 16, 2011

MySQL keeps many different files, some contain real data, some contain meta data. Witch ones are important? Witch can your throw away?

This is my attempt to create a quick reference of all the files used by MySQL, whats in them, what can you do if they are missing, what can you do with them.

When I was working for Dell doing Linux support my first words to a customer where “DO YOU HAVE COMPLETE AND VERIFIED BACKUP?” Make one now before you think about doing anything I suggest here.

You should always try to manage your data through a MySQL client.  If things have gone very bad this may not be possible. MySQL may not start. If your file system get corrupt you may have missing files. Sometimes people create other files in the MySQL directory (BAD).  This should help you understand what is safe to remove.

Before you try to work with one of these files make sure you have the file permissions set correctly.

This may not be a complete list of files used my MySQL.  It most certainly doesn’t describe everything each table is used for. If you know of ways to replace a missing file or what happens to MySQL when a file is missing that I haven’t described here, please leave me a comment or email me.  I’ll update this document and give your a reference.


This file alters the default configuration settings. MySQL looks in the /etc directory for my.cnf. You should review this file to insure you are looking in the right place for all other MySQL files.  MySQL WILL run without it.  If you have trouble getting MySQL to start, read the error log then try moving are renaming this file.

There are other places MySQL looks for configuration files.

  • /etc/my.cnf
  • /etc/mysql/my.cnf
  • ~/my.cnf
  • <$MYSQLHOME>/my.cnf
  • ~/.my.cnf

MySQL will use only one configuration file if you use DEFAULTS-FILE = /etc/foo.bar.


This directory contains all your data.  This may not be its name.  On Linux servers the default location for MySQL files is /var/lib/mysql.  This directory is controlled by the “datadir” variable in your my.cnf file.

Do I need to say, deleting this directory deletes everything?


This directory is MySQL’s database and is inside the directory containing all your data (see above).  All the files in this directory are MyISAM tables for MySQL information like table and column privilages, users, functions and more.  MySQL will not run without this this database.

If you delete this directory, and all the files/tables within, you can recreate a blank set with the MySQL install utility.

mysql_install_db --user=mysql --datadir=/var/lib/mysql

After running this MySQL will rediscover any database that still exist (directory in the MySQL directory).  You will however have to recreate all users, permissions, functions, events, processes, and maybe more.


If your remove this file your InnoDB DATA IS GONE and MySQL will recreate an empty file.

If you are not using the innodb_file_per_table option (default), this file holds almost ALL of your data in InnoDB tables.  This file is all but useless without its corresponding  ‘.frm’ file for each table in the right database directory. If all you have is the .frm files you can recreate the structure of your tables. (See below.)

Idbdata1 can get really big. The default size is 10MB. MySQL will automatically extended it by the default size as needed. If MySQL crashed, some of your InnoDB data may be in your transaction logs (ib_logfile.*).

By design the InnoDB file does not shrink. The safest way to shrink this file is to take a complete backup, stop MySQL, remove the ib* files, start MySQL and restore all your data.  REALLY.


Each MySQL database has a directory named after it.  Each directory holds the meta data for the database. Your MyISAM data is in this directory. InnoDB tables may be here if the innodb_file_per_table variable is used. By default InnoDB tables are in the ibdata1 file (see above).

If you delete the directory your data may be gone. MyISAM data WILL be lost.  InnoDB tables may still be in the ibdata1.  If so, you will need to recreate the meta data files to recover your data.

Creating a directory is almost equivalent to ‘create database’.  If a directory exists MySQL will show you have a database by that name. The create database command may also creates a db.opt file.


This file is key to both InnoDB and MyISAM databases. It is the meta data to the location of your data. It contains the table column definitions.

If you remove this file MySQL will tell you your DATA doesn’t exist.  It does. Your data is still in the ibdata (.ibd) file or the ibdata1 file. You need to recreate the table to recreate this file. If you don’t know the exact structure of this table your out of luck.

Stop MySQL and move the .MYD and .MYI files to another directory.  (You might also make a backup copy.) Start MySQL and recreate this table. Stop MySQL and copy the .MYD and .MYI files back to the database directory and restart MySQL.


THIS IS YOUR MyISAM DATA. If this is all you have, and you know the data structure of the the table, all is not lost.  (See .frm Above.)  You may also need to recreate the .MYI index file.


This file contains the indexs for your table.  If it becomes corrupt or is deleted you can recreate it using the ‘REPAIR TABLE table_name USE_FRM;’ command. Nil Infobin has a interesting artical about this file.

/tmp/#.[ frm MYD MYI ]

MySQL creates temporary files in the /tmp director (C:\windows\temp) when sorting (




).  It also creates temp files on a replication server when  the LOAD DATA LOCAL INFILE… command is used. Replication will fail if these files are removed.


THIS IS YOUR InnoDB DATA. If this is all you have, and you know the data structure of the the table, all is not lost.  (See .frm Above.) Unlike MyISAM tables the indexes are contained in this file with your data.

MySQL doesn’t create this file unless you are using the innodb_file_per_table option.

If you have a “Clean” .ibd file you can import it into a database with the command “ALTER TABLE




THIS IS YOUR CSV DATA.  This file contains comma separated text data. These file do not have indexes.


This file contains meta data for CSV and archive tables.  I have not found what is stored here. I do know it tells MySQL if you are logging to the general logs.


This file contains your un-committed transactions data. MySQL uses it to recover from a crash.

If you shut down InnoDB cleanly, you can remove them. MySQL will recreate them.

If you change the size of innodb_log_file_size, you will need to recreate these files by stopping MySQL cleanly and deleting them.


This “Bin Log” files contain any change made to any database. Each transaction is assigned a MASTER_LOG_POS(ion).  These files are not created by default. They are used for replication and point-in-time recovery.

You can stop the server and remove these files IF you remove the mysql-bin.index file as well.  MySQL creates a new bin log file each time it starts or the logs are flushed.  Deleting these files will the server is running will break replication.


This file is used by MySQL to keep a Bin Log list.  It is a simple text file like;




If you remove this file, MySQL will recreate it with only the newest bin log name. If you need to remove old bin logs use the command “purge binary logs [to mysql-bin.######] [before “yyyy-mm-dd”]”.

You can control the number of bin logs using the expire_logs_days variable.


This is MySQL’s primary administration log. MySQL reports starts and stops as well as some warning and errors in this file.  If MySQL crashes, mysqld_safe will restart it. This log will report this.

You can delete this file if needed.


The slow query log consists of all SQL statements that took more than long_query_time seconds to execute and (as of MySQL 5.1.21) required at least min_examined_row_limit rows to be examined.

You can delete this file if needed.


Database characteristics, like the CHARACTER SET clause are stored in the db.opt file. You may have strange query results if this file is missing. MySQL will use it’s default. You can recreate this file my altering the table with the correct settings.


The PID file hold the process ID number for the running server. MySQL creates this file and scripts that start and stop MySQL use it to control MySQL.

MySQL will remove this file when is stops. You should not delete it if MySQL is running. If mysql is NOT running and the file exists MySQL may have crashed and you should delete this file.


These are found only on the slave servers. Slaves read the commands from those files in order to replicate what the master has done. Simularly to the bin log, it will create files with numbered extensions as needed.

Master.info is a simple text file. This is a store of the master command is used by mysql when it restarts to make the slave connection.


Removing these files will breat replication and / or through your slave out of sync.


The “*” is either the hostname or the same as the .PID file.  This file is the index for the relay log used in replication.  Relay bin files are found only on slave servers.


Contains the list of triggers belonging to a given table


Contains some metadata on the trigger


It’s the file containing the data for an ARCHIVE table.


Contains the definition of a partition

contains the data related to a given partition



MySQL Database Backup .MYI and .MYD

Recovering from Crashes

Using Per-Table Tablespaces




Data Dictionary Operations

Giuseppe Maxia – who writes a great blog about mysql athttp://datacharmer.blogspot.com/ contributed several files I missed.


tags: , , , , , , , ,
posted in Tips & Tricks by mark

Follow comments via the RSS Feed | Leave a comment | Trackback URL

8 Comments to "What is this MySQL file used for?"

  1. Shlomi Noach wrote:

    Good write!

    Type: “This fail is used by MySQL” should be changed to “This file is used by MySQL”.
    What would Darwin say?

  2. Shlomi Noach wrote:

    My typo: type ==> typo

  3. Giuseppe Maxia wrote:

    Nice list. Thanks.
    I can contribute a few more:

    (where “*” can be either the hostname or the same name of the .PID file)
    It is the relay log files index used in replication. (found only on the slave). Similarly to the binlog, it will create
    *relay-bin.000001, *relay-bin.000002, etc

    information about replication (found only on the slave)

    contains the list of triggers belonging to a given table

    contains some metadata on the trigger

    It’s the file containing the data for an ARCHIVE table.

    Contains the definition of a partition

    contains the data related to a given partition

  4. kedar wrote:


  5. mark wrote:

    Thank you.

    Dyslexics of the world UNTIE!

  6. What is this MySQL file used for ? « joysana (java & php expt.) wrote:

    [...] What is this MySQL file used for? February 16, 2011 [...]

  7. Recuperando tablas InnoDB de archivos .ibd « Ουιλιαμ Αρκευα wrote:

    [...] What is this MySQL file used for? [...]

  8. Nima wrote:

    Thank u…

Leave Your Comment


Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org
Creative Commons License
MySQL Fan Boy by Mark Grennan is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.