MySQL storage engine InnoDB learning review file

1. Various types of files in the MySQL database and its storage engine InnoDB include:

Parameter files, log files, socket files, PID files, MySQL table structure files, storage engine files

2. parameter file

When a MySQL instance is started, it first reads some parameters in the configuration file to find the location of various database files and to specify some initialization parameters, these parameters usually define the setting information such as the memory structure size.

You can think of parameters as key-value pairs. You can use show variables to view all the parameters or use like to filter parameter names.

MySQL parameters can be divided into two types: Dynamic Parameters and static parameters. Dynamic parameters are set during MySQL instance running, static parameters indicate that they are neither needed nor modified throughout the life cycle of the MySQL instance. They seem to be read-only. You can use the set command to set dynamic parameters and set read_buffer_size.

3. Log Files

Common logs include error logs, binary logs, slow query logs, and query logs.

The error log records information during MySQL startup, running, and shutdown. When a problem occurs, you should first check the error log file, which not only records the error information, some warnings or correct information are recorded. You can use show variables like 'Log _ error' to locate the file.

4. Slow query of Files

Slow Query files bring benefits to optimizing SQL statements. You can set a threshold to record all SQL statements whose running time exceeds this threshold to slow query logs, the threshold value can be set through the long_query_time parameter. The default value is 10, indicating 10 s. By default, MySQL does not start the slow query log. You can set it through set.

5. binary log

Binary records all the operations that change the database, but does not include operations that do not change the database data such as select and show. For queries that do not change the data such as select and show, you can use query logs. Binary logs have two main functions: 1) recovery. Recovery of some data requires binary logs. For example, when a database is recovered from a full backup file (a fully backup file, point-in-time recovery can be performed using a binary file.

2) replication: By copying and executing binary files, one remote MySQL database and another database can be synchronized in real time.

6. Socket File

In a UNIX system, MySQL can be locally connected to MySQL through the Unix domain socket method. This socket can be controlled through the socket parameter, which is generally named mysql. Sock under/tmp /.

7. Table structure definition file

InnoDB Storage engine files, including redo log files and tablespace files