MySQL file structure (log file to be supplemented)

Introduction to Essentials1. Introduction2. Data Files3. log Files

1. Introduction

There are at least two operating system files per MYSQL database: One data file and one log file.

Data files contain data and objects, such as tables, indexes, stored procedures, and views.

The log file contains the information needed to recover everything in the database.

For ease of allocation and management, data files can be assembled and placed into filegroups.

2. Data files

In MYSQL, each database will have a folder named after the database name in the defined (or default) data directory to hold the various table data files in the database.

Different MYSQL storage engines have their own different data files, storage location also has a difference, most of the storage engine data files are stored in the same directory as the MyISAM data files, but each data file has different extension.

As MyISAM used '. MYD ' as an extension, InnoDB with '. IDB ', Archive with '. Arc ', CSV with '. csv ', and so on.

　　

2.1 ". frm" file

Table-related meta data (meta) is stored in the ". frm" file, including table structure and definition information. Regardless of the storage engine, each table will have a ". frm" file named after the table.

All the ". frm" files are stored under the folder of the owning database.

2.2. ". MYD "File

“. MYD "files are also dedicated to the MyISAM storage engine, storing data for MyISAM tables. Each MyISAM table will have a ". The MYD "file corresponds to the same folder that is stored in the owning database, together with the". frm "file.

2.3. ". MYI "File

“. MYI "files are also proprietary to the MyISAM storage engine, which mainly holds information about the indexes of MyISAM tables.

For MyISAM storage, the content of the cache can be mainly derived from ". MYI "file.

Each of the MyISAM tables corresponds to one ". MYI "file, storage location and". frm "and". MYD "the same.

2.4. IDB file and Ibdata file

Both of these files are files that hold InnoDB data,

There are two types of files that store InnoDB data, including indexes, because InnoDB's data storage can be configured to decide whether to use a shared space for storing data or to store data in a single table space.

Exclusive Tablespace storage uses the ". IDB" file to hold the data, and each table has an ". IDB" file, which is stored in the same location as the MyISAM data.

If the shared storage tablespace is used to hold the data, it will be stored using the Ibdata file, with all tables using one (or more, self-configuring) ibdata files.

The Ibdata file can be configured by the common configuration of the Innodb_data_home_dir and innodb_data_file_path two parameters,

Innodb_data_home_dir The total directory where the configuration data resides, and Innodb_data_file_path configures the name of each file.

Of course, you can also complete the configuration by using an absolute path without configuring Innodb_data_home_dir and directly with the Innodb_data_file_path parameter configuration.

You can configure more than one Ibdata file at a time in Innodb_data_file_path.

The file can be either a specified size or an automatic extension, but InnoDB restricts only the last Ibdata file to be configured as an auto-extended type.

When a new Ibdata file needs to be added, it can only be added at the end of the Innodb_data_file_path configuration, and MySQL must be restarted to complete ibdata additions.

3. log file (to be supplemented) 3.1 error log

PASS

3.2 Binary Log

PASS

3.3 Query Log

Query log records All of the queries in MySQL and opens the function with the "--log[=fina_name" parameter.

Because all of the query is recorded, including all the SELECT, the volume is relatively large, after opening the performance also has a greater impact, so please use this function with caution.

This feature is typically only briefly turned on for tracking specific SQL performance issues.

The default query log file name is Hostname.log.

3.4 Slow Query log

The slow query log (Slow query log) records the long execution time of query, which is often said to be "slow query".

Use the "--log-slow-queries[=file_name]" parameter to open the feature and set the record location and file name.

The default file name is Hostname_slow.log, and the default directory is the data directory.

　　　　

The slow query log uses a simple text format that allows you to view its contents through various text editors.

It records the time of execution of the statement, the duration of execution, the execution of the user, the connection of the host and other related information.

MySQL also provides utility Mysqlslowdump, which is designed to analyze slow query logs, to help database managers resolve possible performance issues.

　　　　

3.5 InnoDB's online redo log

PASS

MySQL file structure (log file to be supplemented)