Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what files are there in the mysql database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1.1. Pid file

After the instance is started, record its unique process number in its own pid file and save it in the data directory.

Note: pid-file=/data/mysql/mysql.pid must be configured in the configuration file my.cnf, otherwise the database startup error is reported.

Looking at the process, you can see the file path.

[root@localhost mysql] # ps-ef | grep mysql

Root 20196 10 14:15 pts/1 00:00:00 / bin/sh / usr/local/mysql/bin/mysqld_safe-- datadir=/data/mysql-- pid-file=/data/mysql/mysql.pid

Mysql 21556 20196 0 14:15 pts/1 00:00:06 / usr/local/mysql/bin/mysqld-basedir=/usr/local/mysql-datadir=/data/mysql-plugin-dir=/usr/local/mysql/lib/plugin-user=mysql-log-error=/data/mysql/error.log-open-files-limit=65535-pid-file=/data/mysql/mysql.pid-socket=/tmp/mysql.sock-port=3306

1.2. Socket file

There are two kinds of database links, network connection and local connection.

The mysql.sock file is the unix socket file that the server communicates with the local client. The default location is / tmp/mysql.sock

1.3. Table structure file

Before mysql8.0, the file ending with .frm was the table structure file. From 8.0, the definition file of frm table was eliminated, and the data was written to the system tablespace. The atomicity of table DDL statement was realized by using InnoDB engine. Previous versions failed to achieve atomicity of table DDL statement operations, such as truncate cannot be rolled back.

Question: does the book say that mysql's truncate can be rolled back? Check the relevant information, did not say that can be rolled back.

Previously tested to create the table, you can see the table file in the directory

# ls t.*

T.frm t.ibd

1.4. InnoDB Storage engine File

There are two main types of logs at the InnoDB storage engine level: redo log and undo log

Multiple versions of InnoDB are implemented by using undo and rollback segments

InnoDB is an index organization table with three hidden fields for each row of records:

N DB_ROW_ID

N DB_TRX_ID: transaction ID representing each row of records

N DB_ROLL_PTR: represents the rollback pointer for each row of records.

InnoDB has a global transaction linked list. At the beginning of each transaction, the transaction ID is placed in the linked list, and the DB_ROLL_PTR pointer points to the undo record to construct multiple versions.

Redo log is used to record changes in transaction operations, recording the value after the data has been modified.

Undo log file

Undo records (insert,update,delete), which only record the old data before the change, are recorded to the system tablespace ibdata1 by default. Since 5.6, independent undo tablespaces can be used, and undo files can be deployed to separate high-speed storage.

Main parameters of undo log

[mysql] > show variables like'% undo%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_max_undo_log_size | 1073741824 | |

| | innodb_undo_directory |. / | |

| | innodb_undo_log_truncate | OFF |

| | innodb_undo_logs | 128 | |

| | innodb_undo_tablespaces | 0 | |

+-+ +

Storage directory of the innodb_undo_directory:undo file

The number of innodb_undo_logs:undo rollback segments defaults to 128. large rollback segments can be divided into multiple small rollback segments, with a maximum of 1024 transactions per log segments.

Innodb_undo_tablespaces represents the number of undo tablespace (0 by default), and there are undo log files in the tablespace (default is 10m). The minimum number of undo tablespace is 2, in case of truncate undo tablespace switching.

Innodb_max_undo_size,5.7 is added. Default is 1G, and truncate undo logs is triggered when the threshold is reached. The undo logs size after truncate is restored to 10m by default. You can delete useless undo log online, but you need to turn on innodb_undo_log_truncate and turn it off by default.

Added to innodb_pure_rseg_truncate_frequency:5.7 to control the frequency of reclaiming undo log. Default is 128. Indicates that the truncate operation of undo is performed after purge undo polling for 128 times, but the undo log space does not shrink until the rollback segment is released.

This is the end of the content of "what are the files in the mysql database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.