MySQL Physical file Architecture Simple collation description

Original: 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, some metadata information in the user-defined library, such as stored procedures, is 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. So the first one, and no Information_schema path (folder)
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
However, other objects in the data, including the undo information, that is, the version information between transaction commits after the data has been modified, 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 is to convert distributed random writes (data from multiple tables to data files) of a database-specific data file into a log-based sequential write operation, and the data file is an asynchronous write disk.
If the data file writes an exception, it can be "redo" by redo the log, thus improving the efficiency of the operation of the thing.
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.

　　Binary log based on MYSQL-BIN.N

Bin-log The write operations that occur in the logging data (add-and-remove), but do not log query operations, the statements are saved as events and describe the process of changing the data, which plays a critical role in recovering data in the event of a disaster.
The corresponding physical files such as

　　

MySQL By default does not open the binary log, you need to configure the path of Log_bin in My.cnf, after restarting MySQL will automatically turn on the Log_bin binary logging function.
This includes a series of configurations about Log_bin.

Including:
1) path configuration of Log-bin: Log-bin=/var/lib/mysql/mysql-bin
2) format of the binary log: Binlog_format = MIXED
3) Set the expiration period of the log file: Expire_logs_days = N, the log is automatically deleted Expire_logs_days,set global expire_log_days=n,n days ago;
4) binary log cache size, Binlog_cache_size = * *: Binary log cache size, is the size of each connection incoming thread allocation, not the size of the entire server;
5) Maximum cache size: max_binlog_cache_size=***
6) The maximum size of a single file: Max_binlog_size = 100m, the maximum size of a single file, more than this size is allocated a file, but a transaction must be in a file, so it may be a little bit larger;
Detailed configuration details are shown below.

　　

After you turn on Log_bin, you can query the system variables for the relevant configuration.

　　

In the event of a database disaster, most of the data can be recovered with full and differential backups, but Log_bin records all occurrences in the database,
Therefore, on the basis of full and differential backup restores, the database can be recovered to a specific point in time with the log_bin of the last differential backup (provided that there is a full backup).
The time to record the operation (increase and deletion) in the data to Log_bin is controlled by sync_binlog parameters.
Sync_binlog=0, when the transaction commits, MySQL does not do fsync such as the disk synchronization instructions to refresh Binlog_cache information to disk, and let filesystem decide when to do synchronization,
At this point (sync_binlog=0) increases the controllability (how many logs have not yet been written to the Log_bin file after the thing is committed).
Sync_binlog=n, after every n transaction commits, MySQL will perform a disk synchronization instruction such as Fsync to force the data in the Binlog_cache to disk.
In MySQL, the system default setting is Sync_binlog=0, that is, do not make any mandatory disk refresh instructions, the performance is the best, but the risk is also the largest.
As soon as the system crash, all binlog information in the Binlog_cache is lost. When set to "1", it is the safest and most performance-depleting setting.
Because when set to 1, even if the system is crash, a transaction that is not completed in Binlog_cache is lost at most, and there is no material impact on the actual data (you can redo it with the redo log if it is down).

Sync_binlog is also a system variable that allows you to view configuration information using the following command.

　　

For more information, see here: http://www.cnblogs.com/ggjucheng/archive/2012/11/15/2771535.html

　　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. In the middle experienced a variety of books, all kinds of search information, all kinds of copying (learning is not a process of imitation and creation, self-comfort).
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