Socket file

A local connection to Mysql on a UNIX system can be in the form of a socket, requiring a sockt file, which can be used to view the location as follows:

mysql> show variables like 'socket'; +---------------+---------------------------+ | Variable_name | Value | +---------------+---------------------------+ | Socket | / var/lib/mysql/mysql. The sock | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.01 SEC)Copy the code

Pid file

When the mysql instance starts, it writes its pid to a file, controlled by the following parameters, which default is hostname.pid.

mysql> show variables like 'pid_file'; +---------------+--------------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------------+ | pid_file | /var/lib/mysql/iZ2ze7sn66bchxncut8rgsZ.pid | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.01 SEC)Copy the code

Table structure definition file

Mysql data is stored according to tables, and each table has a corresponding file. Any storage engine will have a file with the suffix FRM that records the structure definition of this table. The view is also recorded in the FRM file. You can run the cat command to view the view.

FRM files are deleted in mysql8, and Serialized Dictionary Information (SDI) is adopted, which is a new product introduced after the redesign of data Dictionary in MySQL8.0. InnoDB storage engine has been used to store metadata Information of tables. SDI source records are stored in IBD files. An official tool called IBd2sdi is provided, which can be found in the installation directory. It can extract the REDUNDANT SDI information from ibD files offline and output it to terminals in JSON format.

4. InnoDB stores engine files

4.1 Tablespace Files

InnoDB uses the design of storing data in a tablespace. In the default configuration, there is a default tablespace file named ibDatA1, which is 10M by default. You can view the following:

mysql> show variables like 'innodb_data_file_path'; +-----------------------+------------------------+ | Variable_name | Value | +-----------------------+------------------------+ | innodb_data_file_path | ibdata1:12M:autoextend | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

The default tablespace ibDATA1 is 12M, and the autoextent parameter increases it automatically when it runs out. You can set more than one tablespace, as follows:

ibdata1:12M; ibdata2:12M:autoextendCopy the code

If the two tablespaces are on different disks, the load may be scored to improve overall performance.

If innodb_file_per_TABLE is set, a default separate tablespace is generated for each table.

mysql> show variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

View mysql5.6 files:

View the file of 8:

As shown above, the same IBD file is larger in 8. Ibd files are independent tablespace files. This file only stores the data, index, and insert buffer bitmap of the table. Sdi files for table structures are also included in 8.

4.2 Redo log files

By default, there are two files in the mysql installation path; Ib_logfile0 and ib_logfile1. Log files, officially defined as log files for the storage engine, are called redo log files. They record transaction logging information for the storage engine. Redolog is used to recover data caused by server downtime. Each storage engine has at least one redo log file group and at least two redo log files under each file group. As mentioned earlier, two files.

You can configure multiple log groups to ensure data reliability. The two files under each log group are of the same size, and data is written in a loop. When one file is full, the other file is switched.

Main parameters:

mysql> show variables like '%innodb_log_%'; +------------------------------------+----------+--------------------------------------------------+ | Variable_name | Described the Value | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Innodb_log_buffer_size | 16777216 | written to the log files on disk buffer size (in bytes). The default value is 16MB. | innodb_log_checksums | | ON page | redo log calibration parameters innodb_log_compressed_pages | ON | | innodb_log_file_size | 50331648 | log group within the total file size, slightly less than 512 g. If the value is too large, data recovery is slow. If the value is too small, checkpoint logs may flush to disks and add I/OS. | innodb_log_files_in_group | 2 | log file number in the group, . The default is 2. | innodb_log_group_home_dir | | / file path +------------------------------------+----------+--------------------------------------------------+ 10 rows in set (0.00 SEC)Copy the code