MySQL file directory format and storage location

Know MySQL knows, in MySQL to build any data table, in its data directory corresponding to the database directory has a corresponding table of. frm files,. frm files are used to hold each data table metadata (meta) information, including the definition of table structure, etc. The frm file is not related to the database storage engine, which means that the data table for any storage engine must have a. frm file named in the data table name. frm, such as user.frm. The. frm file can be used to restore the table structure when the database crashes.

MySQL files include database files built by MySQL and database files created by MySQL engine .

The. frm file has a file with the same name as the table name regardless of the operating system and database engine.

Files for the MyISAM engine:

. MyD is the My data file

. Myi is my index, indexed files

The. log log file.

Files for the InnoDB engine:

Use Tablespace (tablespace) to manage data, store table data and indexes,

InnoDB database file (that is, InnoDB file set, Ib-file set):

IBDATA1, IBDATA2, etc.: System tablespace file, store InnoDB system Information and user database table data and indexes, all tables are shared.

. ibd Files: Single-table space files, each using a tablespace file (file per table) that holds user database table data and indexes.

Log files: Ib_logfile1, Ib_logfile2

############################################################################################################### #################################################

InnoDB Storage engine management is based primarily on two files: tablespace data files and log files.

InnoDB stores its table and index in a table space, a tablespace can contain several files (or raw disk partitions).

If you do not specify the INNODB configuration option, MySQL will create a 10MB size automatic extension data file named Ibdata1 in MySQL Data directory, and two log files of 5MB size named Ib_logfile0 and Ib_logfile 1.

The size of the IBDATA1 is configured in the my.cnf file: Innodb_data_file_path = Ibdata1:10g:autoextend

You can set the maximum data file limit to avoid exceeding the maximum file supported by the system:

Innodb_data_file_path = ibdata1:100m:autoextend:max:500m

Log file size is configured in the my.cnf file: innodb_log_file_size = 256M Innodb_log_files_in_group = 2

The InnoDB storage engine can use shared table spaces or stand-alone table spaces, and when using a stand-alone tablespace, you need to add innodb_file_per_table to the configuration file or open it in variables.

Shared tablespace is the data and index of all tables stored in the IBDATA1, the disadvantage is that the copy must copy the entire large file, and delete the table is prone to fragmentation.

A stand-alone tablespace is a. ibd file for each table that is used to store data and. frm To save data dictionary information, so that MySQL will innodb the table data into their corresponding. ibd file, but the structure and other information will be written to Ibdata.

The innodb_file_per_table variable can only be modified in the configuration file and cannot be used with set global ...

After the innodb_file_per_table is closed, only the. frm file is generated when the InnoDB table is created, and the data and indexes are saved in the shared tablespace ibdata1. ############################################################################################################### #################################################

MySQL Database storage location:

1, MySQL If you use the MyISAM storage engine, the database file type includes. frm,. MYD,. MYI, the default storage location is C:\Documentsand Settings\All Users\Application Data\mysql\mysql Server 5.1\data

2, MySQL If you use the InnoDB storage engine, the database file type includes. frm, ibdata1,. IBD, storage location two,

The default storage location for. frm files is C:\Documents and Settings\All Users\applicationdata\mysql\mysql Server 5.1\data,ibdata1. The default location for IBD files is the Data folder in the MySQL installation directory

MySQL file directory format and storage location