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.

my.cnf

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.

mysql

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?

mysql/mysql

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.

ibdata1

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.

DatabaseName

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.

.frm

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.

.MYD

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.

.MYI

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 (

or

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

.ibd

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

IMPORT TABLESPACE;”

.CSV

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

.CSM

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.

ib_logfile*

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.

mysql-bin.*

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.

mysql-bin.index

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

./mysql-bin.000001

./mysql-bin.000002

./mysql-bin.000003

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.

mysqld.log

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.

slow.log

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.

db.opt

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.

mysql.pid

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.

master.info
relay-log.info

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.

15
mysql-bin.006004
1006945586
192.168.1.2
repl_user
P@sSw0Rd
3306
60
0

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

*relay-bin.index

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.

.TRG

Contains the list of triggers belonging to a given table

.TRN

Contains some metadata on the trigger

.ARZ

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

.par

Contains the definition of a partition

tablename#P#partitionname.{MYD,ARZ,ibd}
contains the data related to a given partition

References:

FULL DISK
http://dev.mysql.com/doc/refman/5.1/en/full-disk.html

MySQL Database Backup .MYI and .MYD
http://www.aeonity.com/frost/mysql-database-backup-myi-myd

Recovering from Crashes
http://dev.mysql.com/tech-resources/articles/recovering-from-crashes.html

Using Per-Table Tablespaces

http://dev.mysql.com/doc/refman/5.0/en/innodb-multiple-tablespaces.html

Troubleshooting

Data Dictionary Operations
http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html

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

Tweet