Detailed explanation of MySQL file storage

What is a file system

We know that storage engines such as InnoDB and MyIASM store tables on disk (persistent). When we want to read data, these storage engines will read the data from the file system and return it to us. When we want to write data, these storage engines will write the data back to the file system.

Of course, in addition to storing actual data, MySQL also stores a series of other logs, which also belong to the file system.

The address of the file stored on the storage engine

After using the client to establish a connection with the server, you can view the value of this system variable:

show variables like 'datadir';

Of course, this directory can be modified through the configuration file and specified by ourselves.

What is in the disk file?

What data will MySQL generate during its operation? Of course, it will include user data such as databases, tables, views and triggers that we created. In addition to these user data, MySQL will also create some other additional data for better program operation.

Table information under the database Date directory

Whenever we create a database using the CREATE DATABASE statement, what actually happens on the file system? It's actually very simple. Each database corresponds to a subdirectory under the data directory, or a folder. Whenever we create a new database, MySQL will help us do two things:

• Create a subdirectory (or folder) with the same name as the database name in the data directory.
• Create a file named db.opt in the subdirectory with the same name as the database name. This file contains various properties of the database, such as the character set and comparison rules of the database. Let's say we check what databases are currently on my computer:

You can see that there are currently 5 databases, of which the mysqladv database is our custom one, and the other 4 databases are system databases that come with MySQL. Let's take a look at the contents of the data directory:

Of course, there are many files and subdirectories under this data directory, but if you look closely, except for the information_schema system database, other databases have corresponding subdirectories under the data directory. This information_schema is quite special, and we will discuss its function later.

How InnoDB stores data

When our InnoDB adds a database, it will add a folder in the log root directory.

Each folder stores all the corresponding table data. The data in each table is generally divided according to the following two rules:

Definition of table structure

Let's take the index_condition_pushdown table in the study library we created as an example:

The table structure is these data, and the corresponding storage file name is:

The table structure includes the name of the table, how many columns are in the table, the data type of each column, the constraints and indexes, the character set and comparison rules used, etc. All this information is reflected in our table creation statement. In order to save this information, both InnoDB and MyIASM storage engines create a file specifically used to describe the table structure in the corresponding database subdirectory under the data directory. The file name is: table name.frm

Data in the table

Create a file representing the independent tablespace in the subdirectory corresponding to the database to which the table belongs. The file name is the same as the table name, except that a .ibd extension is added. Therefore, the complete file name looks like this: table name.ibd.

How MyISAM stores table data

Data and indexes in MyISAM are stored separately. Therefore, different files are used in the file system to store data files and index files. (Compared to InnoDB, the ibd file stores data + indexes, and MyISAM splits it again).

As shown in the figure, we change the storage engine.

The file directory is changed to the one shown above. It can be simply understood as splitting the InnoDB .ibd file into the following two files.

.MYD represents the data file of the table.

.MYI represents the index file of the table.

Here, we summarize an important difference between InnoDB and MyISAM. That is, InnoDB's index and data are in one folder, while MyISAM stores data files and index files separately in two files.

Log files

During the operation of the server, various logs are generated, such as regular query logs, error logs, binlog logs, redo logs, undo logs, etc. The log files record various types of activities that affect the MySQL database. Common log files include: error log, slow query log, query log, and binary log.

Error Log

The error log file records the startup, operation, and shutdown process of MySQL. When you encounter a problem, you should first check this file to locate the problem. This file not only records all error messages, but also some warning messages or correct messages. Users can view the location of the error log file through the following command:

show variables like 'log_error'\G;

When MySQL cannot start normally, the first file you must look for should be the error log file, which records the error information.

Slow query log

Slow query logs can help locate SQL statements that may have problems, thereby optimizing SQL statements.

Query log

The query log records information about all requests to the MySQL database, whether or not those requests were executed correctly.

Default file name: hostname.log

Starting from MySQL 5.1, query log records can be put into the general_log table under the mysql schema

Binary log (binlog)

The binary log records all operations that change the MySQL database. If the operation itself does not cause the database to change, the operation may also be written to the binary file. However, operations such as select and show are not included (because these operations do not modify the data itself)

Several functions of binlog

Recovery

Recovery of some data requires binary logs.

copy

The principle is similar to recovery. By copying and executing binary logs, a remote MySQL database (generally called slave or standby) is synchronized with a MySQL database (generally called master or primary) in real time.

Audit (a little unpopular, db is responsible for it)

Users can use the information in the binary log to audit and determine whether there are any injection attacks on the database.

Summarize

This is the end of this article about MySQL file storage. For more relevant MySQL file storage content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!