MySQL Physical file Architecture Simple collation description

The source of this article: http://www.cnblogs.com/wy123/p/7102128.html
(It is not the original works right to retain the source, I my book still far to reach, just to link to the original text, because the following may exist some errors to amend or supplement, without him)

The database version of this article is MySQL5.7.18, briefly introduces the physical structure and function of MySQL data file directory, from which we can see the physical file structure and logical function of MySQL as a whole.
The physical architecture of MySQL can be learned from the overall structure (the way I learn is often to learn from the things I already know, or to quickly understand their contours, and then gradually refine the entire knowledge system)
Given that any of the logical or physical files in MySQL are configurable, and because of the open source, MySQL has some improvements in every major version that cannot be mechanically according to an item or default configuration.

As MySQL (5.7.18) in the Linux system yum default installed data files directory, you can see the following types of files.

1, database path: You can see that both the system database and the user-defined database are a path, and after the specific path is expanded, each database is its own object)
2,logbin binary log file: If binary logs are turned on, there will be several binary log files (mysql-bin.000042,mysql-bin.000043) with their corresponding profiles (Mysql-bin.index)
3,redo Redo log file: Ib_logfile0,ib_logfile1, which is a redo log file that supports the transactional engine
4, shared tablespace: ibdata1, if you specify a InnoDB table as a non-stand-alone file, the data for the table in the user-defined library is stored in the shared table space.
Even if the Innnodb table is specified as a stand-alone tablespace, the user automatically stores metadata information in the library (the structure information of the custom table, stored procedures, triggers, and so on are stored in the shared table space).
At the same time, shared tablespace is also responsible for storing the undo data for the purpose of storage (undo data is also the value before the modification of the data of the thing's operations).
However, starting with 5.6, the user can store the undo log in a separate tablespace and split it into multiple undo log files.
5, temporary tablespace: IBTEMP1, storing space for temporary objects, such as temporary table objects.
6,errorlog:error_log.log, logging the process of starting, running, or stopping the MySQL server, and some of the more serious error messages during MySQL running
7,mysql.sock: When a local connection is initiated when a client connection to the MySQL server itself is active
8,slow_log: The MySQL service does not have a slow query log configured, and if you configure a slow query log for MySQL, MySQL logs the slow query log in the run process to the Slow_log file
9,general_log: With the above 8, the server has not configured the general query log MySQL, if the general query log is configured, MySQL will run all the SQL in the process is recorded in this file.
10, the other is the final MySQL configuration file, My.cnf,yum installed MySQL configuration file my.cnf default in the ETC directory

The above files can be categorized and presented in a structured manner, such as the following, a series of document structures described above
It should be noted that the list of files listed above does not include some files, such as the time to enable replication of the relay log files and so on (superficial image, the Big God Light spray)

Here is a simple description of each file from the category:

　　System Database

In MySQL5.7.18, the system database includes Information_schema,mysql,sys,performance_schema
1,information_schema Library, provides the database metadata information, is the database data, such as the database name, database table name, field name, field type, etc., can be said to be database data dictionary information.
The information in this library is not physically stored in the table, but rather dynamically read from other files, such as the shared table space mentioned above, which is stored in the shared table space for objects in the user data, such as the table structure.
Some of the information in the INFORMATION_SCHEMA library can be thought of as directly mapped to the information in the shared table space.
The 2,performance_schema library, which is the data of database performance-related information, records the performance parameters of the database server.
1) Keep the process waiting information, including locks, mutex variables, file information, etc.
2) Save historical Event summary information and provide reference information for MySQL server performance evaluation
3) configuration options, to determine whether to record some performance-related information, such as profile information, reference http://www.cnblogs.com/wy123/p/6979499.html
3,sys Library, can be based on the data in the SYS library to quickly understand the operation of the system information, easy to query out the database information, in the performance bottleneck, automation bar operations and other aspects have a great help
The information in the SYS library is a view that combines data from the INFORMATION_SCHEMA and Performance_schema libraries to get more intuitive and understandable information
4,mysql Library, stores the system user rights information and help information, the new user, the user's permission information are stored in the MySQL library.
For example, when you modify the root password of MySQL, you must first use the MySQL system library, and then perform user, authorization and other operations.

　　User Database

The user database is actually a directory in which the tables and data in the database are stored, and the following are the file information in a typical database directory.
For the InnoDB engine table, a table corresponding to two files, one is *.frm, the table structure information is stored, one is *.IBD, the data stored in the table, from the size can be seen *.ibd larger and *.frm smaller.
Another file is db.opt, which holds the configuration information of the database, such as encoded information.
For InnoDB tables, the table structure in the database and the data are stored in the path of the database (not in the Ibdata1 file in the shared tablespace), if it is a separate table space
But other objects in the data, such as stored procedures, triggers, and so on, are still stored in the Ibdata1 file of the shared tablespace.

TEST_DATABASE1 the corresponding database physical files

　　

TEST_DATABASE1 corresponding logical database is as follows

shared table space based on ibdata1 files

The innodb,innodb_file_per_table option determines whether to start a stand-alone table space, which is initiated by default in MySQL5.7, which means that the MySQL user database uses a separate table space to store the data.

As you can see, there is only one file in the shared table space of this test server, as shown by show variables like ' innodb_data% '; the command queries the file information of the shared tablespace, and the shared tablespace can actually be configured as multiple physical files.

　　

There are pros and cons to both shared and stand-alone table spaces, and this article is not copied, or you can transfer data files from a shared tablespace to a stand-alone table space.
However, from the current (MySQL5.7.18), the MySQL default InnoDB engine is a separate table space, so that MySQL data files live in "single room" instead of the collective dormitory, there is a certain advantage of independent table space.

temporary tablespace based on the Ibtmp1 file

Temporary tablespace is the place where you store the global level, the reply level, the thing level, the Retrieval Level temporary table object, and the parameter Innodb_temp_data_file_path can see the temporary tablespace information.

For more information on temporary tablespace, refer to: https://yq.aliyun.com/ziliao/89528

　　

　　Ib_logfilen-based redo logs

Redo log By default There are two files, namely: Ib_logfile0 and Ib_logfile1, if you do not have these two files during database startup, the system will automatically generate these two files by default.
By default, IB_LOGFILE0 and Ib_logfile1 are two separate log files (more Ib_logfile files that can be configured), but redo log writes are logically contiguous for IB_LOGFILE0 and Ib_logfile1.
Redo log is the core file of MySQL thing processing, one of the core of transaction processing is consistency, that is, to either do it all or not.
The operation of Things is based on the operation of some data in one or more tables, in order to ensure consistency, it is necessary to write directly or indirectly when things are submitted.
MySQL thing operation is logwrite-ahead operation, that is, write the log first (how to write the log depends on the configuration of Innodb_flush_log_at_trx_commit), equivalent to the indirect write operation.
The purpose of this paper is to convert the scattered random writes of the data files of the database (data from multiple tables to data files) into sequential write operations based on the log, and the data files are asynchronous writes, and if the data files are written in an abnormal form, they can be "re-done" by redo the log to improve the efficiency of the operation of things.
The use of redo log space, logically equivalent to a ring space, redo log constantly forward to write records, the background of the scheduled execution of checkpoint the transaction has modified the record has not been written to the data file, log space can be reused.

　　

　　Undo Table Space

Redo redo Log In improving the efficiency of the operation of things, but also through the redo mechanism to ensure the reliability of things.
If things roll back, you need to rely on the undo log for rollback operations, and MySQL will record the information before the transactional operation modifies the data, which is the undo log, to ensure that it can roll back to the state before the thing happened.
By default, MySQL logs the Undo log in the shared tablespace (the Bdata1 shared file mentioned above), and the undo log recorded in the shared tablespace will be puege cleaned up by the background process if the event is successfully committed.
Of course, this undo log is also different, if the update operation after committing the transaction, the undo log will also need to provide a server for MVCC, if it is an insert operation, after the thing commits can be directly cleaned back to rollback log records.

However, starting with 5.6, the user can store the undo log in a separate tablespace and split it into multiple undo log files. Detailed reference: http://mysqllover.com/?p=873

　　

Summarize:

Based on the basic analysis of the MySQL physical file structure and the corresponding logical functions, most of the configuration is to show by default, which can be a simple and superficial explanation of MySQL's physical Structure and logic function (very coarse and shallow, welcome correct).
But after all, it is a spy glimpse only to be reflected in the road, Mann's repair far.
Again, MySQL every step is configurable, different configuration, and even every version of the MySQL hairstyle, some places are not consistent, do not copy.

MySQL Physical file Architecture Simple collation description