MySQL Physical file composition

Log file error logs: Error log

The error log records all the more severe warnings and error messages during MySQL operation, as well as details of each startup and shutdown of MySQL server. By default, the system logs the function of the error log is turned off, if you want to turn on the system logging error log function, you need to start-log-error[=file_name], modify its storage directory and file name.

For ease of maintenance, sometimes you might want to back up the contents of the error log and start recording again, so you can use MySQL's flush log command to tell MySQL to back up the old log file and generate a new log file. The backup file name ends with ". old".

Binary binary files binary log & binary log Index

The binary log, which we often call Binlog, is one of the most important logs in MySQL. We need to use this file when we copy from the master. When we open the function of the record through "-log-bin=[=file_name", MySQL logs all query changes to the database data into the log file in binary form. Of course, the log is not limited to SQL statements, but also includes the time each SQL executes, the resources consumed, and the related transaction information, so Binlog is transaction-safe.

As with the error log, the Binlog logging function requires the "-log-bin[=file_name]" parameter display specified to turn on, and if no file_name is specified, it is recorded in the Data directory as mysql-bin.****** (* represents a number between 0-9 , to identify the ordinal of the log)

Binlog There are some additional option parameters

"--max_binlog_size" setting Binlog maximum storage on-line, when the log reaches the limit, MySQL will re-create a journal to start the record. Occasionally, however, there will be binlog that exceed this setting, generally because a larger transaction is generated when the upper limit is reached, and MySQL does not log the same transaction to two binlog in order to ensure transaction security.

The "--binlog-do-db=db_name" parameter explicitly tells MySQL that it is necessary to record binlog for a (db_name) database, if there is a display of the "-binlog-do-db=db_name" parameter, MySQL ignores SQL statements that are executed against other databases, but only the SQL that is executed on the database.

"--binlog-ignore-db=db_name" is the exact opposite of "--binlog-do-db=db_name", which shows a binlog record that specifies to ignore a (db_name) database, and when this parameter is set, MySQL will record the binlog of all databases outside of the established database.

--binlog-ignore-db=db_name "and"--binlog-do-db_db_name "even a parameter has a common concept that needs to be understood clearly, the db_name in the argument is not the database in which the SQL statement is updated. It refers to the database that is currently executing SQL. Regardless of which database data is updated, MySQL simply compares the database name set with the parameters of the database in which the current connection is located (through the use_db_name switch), and does not parse the database in which the SQL statement was updated.

The function of the Mysql-bin.index file (binary log index) is to record the absolute path of all binary logs, ensuring that all MySQL threads can successfully find all the required binary log files based on it.

Querying logs: Query log

The query log records all of the query in MySQL and opens it with "--log[=file_name". Due to the record of all query, including all the Select, the volume is relatively large, open after the performance has a greater impact, so this function should be used with caution. This feature is typically used to track some special SQL performance issues before it is briefly turned on. The default query log file name is Hostname.log.

Slow query log: Slow query log

The slow query log is recorded in a long execution query, which is what we often call slow query, by setting--log-slow-queries[=file_name] to open the function 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 the content in a variety of text editors. It records the time of statement execution, the time spent executing, executing the user, connecting 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.

InnoDB Online redo log: InnoDB redo Log

InnoDB is a transaction-safe storage engine whose transactional security is ensured primarily through online redo of the undo information in the log and record table spaces. All physical changes and transaction information made by InnoDB are recorded in the Redo log, and InnoDB guarantees transaction security under any circumstances through redo logs and undo information. InnoDB redo logs are also stored in the data directory by default, you can change the location of the settings log by Innodb_log_home_dir, and set the number of logs by Innodb_log_files_in_group.

Data files

In MySQL each database will be defined (or default) in the data directory of a folder named after the database, to hold the database of various table data files. Different MySQL storage engines have their own different data files, and there are differences in storage locations. Most storage engine data files are stored in the same directory as the MyISAM data file, but each data file has a different extension. such as MyISAM use ". MYD "As an extension, InnoDB with". IBD "and so on.

". frm" file

Table-related metadata (meta) information is stored in the ". frm" file, which contains the definition information for the table structure. Regardless of the storage engine, each table will have a ". frm" file named after the table name. All the ". frm" files are stored under the folder of the owning database.

“. MYD "File

". MYD "File is a MyISAM storage engine dedicated to 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.

“. 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, stored in location and". frm "and". MYD "the same.

". ibd" File and Ibdata file

Both of these files are files that hold InnoDB data, and there are two types of files that hold InnoDB data (including indexes) because InnoDB's data storage can be configured to determine whether to use shared tablespace to store data or to store data in a single table space. Exclusive Tablespace storage uses the ". IBD" file to hold data, and each table has a ". ibd" file, which is stored in the same location as the MyISAM data. If you use a shared tablespace to hold the data, the "Ibdata" file is used to store it, and all tables share one (or more, self-configuring) ibdata files. The Ibdata file can be configured by a common configuration of Innodb_data_home_dir and Innodb_data_file_path two parameters, innodb_data_home_dir the total directory where data is stored, and Innodb_data_ File_path Configure 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. Files can be sized or expanded automatically, but that is InnoDB limit only the last Ibdata file can be configured to automatically expand the type. When we need to add a new Ibdata file, we can only add it at the end of the Innodb_data_file_path configuration, and we have to restart MySQL to complete the ibdata add job. However, if we use the exclusive Tablespace storage method, there will be no such problem.

Replication related files Master.info file

Master.info file exists in the slave end of the data directory, which contains the slave of the master side of the relevant information, including Master's host address, connection user, connection password, connection port, the current log location, has been read the log location and other information.

Relay log and relay log index

The mysql-relay-bin.****** file is used to hold the binary log information that the I/O thread of the slave side reads from the master side, and then the SQL thread from the slave side reads and parses the corresponding log information from the relay log. Translate the SQL statement executed on the master side and apply it on the slave side.

The Mysql-relay-bin.index file functions like Mysql-bin.index, which is also the absolute path where the log is stored, except that he records not binary log, but relay log.

Relay-log.info

Similar to Master.info, it stores information about the relay log that is written to the local slave I/O thread. SQL threads for the slave end and some administrative operations are ready to get information about the current replication.

Other Files System config file

MySQL's system configuration file is generally "my.cnf", "my.cnf" file contains a variety of parameter option groups, each parameter group is given a fixed group name through the brackets, such as "[Mysqld]" group includes the initialization parameters of the Mysqld service startup, "[Client ] "group contains parameters that the client tool can read, in addition to other specific parameter groups for each client software, such as the MySQL program using" [MySQL] "and so on, if the reader wrote a client program, you can also set a parameter group name, the relevant parameters are configured in the inside, Then the parameter reading API in the MySQL client API program reads the relevant parameters.

PID File

PID file is a mysqld application in the Unix/linux environment of the process files, and other Unix/linux server-side programs, as well as the storage of this own process ID.

Socket file

The socket file is also in the Unix/linux environment, the user in the Unix/linux environment, the client connection can not use TCP/IP network and directly using the UNIX socket to connect to MySQL.

This article references: "MySQL performance tuning and architecture design"

MySQL Physical file composition