Summary of 7 logs in MySQL

Time:2021-12-4

MySQL has the following log files:

1: Redo log
2: Rollback log (undo log)
3: Binary log
4: Error log
5: Slow query log
6: General log
7: Relay log

Redo logs and rollback logs are closely related to transaction operations, and binary logs are also related to transaction operations. These three logs are of great significance for understanding transaction operations in MySQL.

1、 Redo log

effect:
Ensure the persistence of transactions. The redo log records the status after the transaction is executed and is used to recover the updated data of successful transactions that are not written to the data file. Prevent dirty pages from being written to the disk at the time point of failure. When restarting the MySQL service, redo according to the redo log, so as to achieve the persistence of transactions.

Content:
The log in physical format records the modification information of the physical data page, and its redo log is written to the physical file of redo log file in sequence.

When:
The redo log is generated after the transaction starts. The redo log is not written after the transaction is committed, but is written to the redo log file during the execution of the transaction.

When to release:
After the dirty pages of the corresponding transaction are written to the disk, the redo log mission is completed, and the space occupied by the redo log can be reused (overwritten).

Corresponding physical file:
By default, the corresponding physical file is located in IB under the data directory of the database_ logfile1&ib_ logfile2
  innodb_ log_ group_ home_ Dir specifies the path where the log file group is located. By default,. / indicates that it is in the data directory of the database.
  innodb_ log_ files_ in_ Group specifies the number of files in the redo log file group. The default is 2

The size and number of files are configured by the following two parameters:
  innodb_ log_ file_ Size the size of the redo log file.
  innodb_ mirrored_ log_ Groups specifies the number of log image file groups. The default is 1

other:
Very important, when was the redo log written to the disk? As mentioned earlier, it is written gradually after the beginning of things.
The reason why the redo log is written to the redo log file step by step after the transaction is started, not necessarily after the transaction is committed. The reason is that the redo log has a cache InnoDB_ log_ buffer,Innodb_ log_ The default size of the buffer is 8m (16m set here). The InnoDB storage engine writes the redo log to InnoDB first_ log_ In buffer

在这里插入图片描述  

Then, the logs of InnoDB log buffer will be flushed to disk in the following three ways

Master thread refreshes InnoDB once per second_ log_ Buffer to redo log file.

When each transaction is committed, the redo log is refreshed to the redo log file.

When the available space of the redo log cache is less than half, the redo log cache is flushed to the redo log file

It can be seen that the redo log is written to the disk in more than one way, especially for the first way, InnoDB_ log_ The buffer to redo log file is a scheduled task of the master thread thread.

Therefore, the redo log writing disk is not necessarily written to the redo log file after the transaction is committed, but gradually starts with the start of the transaction.

In addition, the original words in MySQL technology insider InnoDB storage engine (page37) are quoted:

Even if a transaction has not been committed, the InnoDB storage engine will refresh the redo log cache to the redo log file every second.

This must be known, because it can well explain that the commit time of a large transaction is very short.

2、 Rollback log (undo log)

effect:
To ensure the atomicity of the data, a version of the data before the transaction is saved, which can be used for rollback. At the same time, it can provide read under multi version concurrency control (mvcc), that is, unlocked read

Content:
When performing undo, the log in logical format only restores the data logically to the state before the transaction, rather than operating from the physical page. This is different from redo log.

When:
Before the transaction starts, the undo log will be generated from the current version, and undo will also generate redo to ensure the reliability of the undo log

When to release:
After the transaction is committed, the undo log cannot be deleted immediately. Instead, it is placed in the linked list to be cleaned up. The purge thread determines whether other transactions can clean up the log space of the undo log by using the version information of the previous transaction in the table in the undo segment.

Corresponding physical file:
Before MySQL 5.6, the undo table space was located in the rollback segment of the shared table space. The default name of the shared table space is ibdata, which is located in the data file directory.
After MySQL 5.6, the undo table space can be configured as an independent file, but it needs to be configured in the configuration file in advance. It takes effect after database initialization, and the number of undo log files cannot be changed
If the database is not configured before initialization, it cannot be configured as a separate table space.

The configuration parameters of independent undo tablespaces after MySQL 5.7 are as follows:
  innodb_ undo_ Directory = / data / undo space / – the storage directory of undo independent tablespace InnoDB_ undo_ Logs = 128 – the rollback segment is 128KB InnoDB_ undo_ Tablespaces = 4 – specifies that there are 4 undo log files
If undo uses a shared table space, the shared table space not only stores undo information, but also defaults to the MySQL data directory, and its properties are determined by the parameter InnoDB_ data_ file_ Path configuration.

在这里插入图片描述

other:
Undo is a version of the modified data saved before the start of a transaction. When generating an undo log, it will also be accompanied by a redolog similar to the protection transaction persistence mechanism.
By default, the undo file is kept in the shared table space, that is, the ibdatafile file. When some large transactional operations occur in the database, a large amount of undo information will be generated and saved in the shared table space.
Therefore, the shared table space may become very large. By default, that is, when the undo log uses the shared table space, the “expanded” shared table space will not and cannot shrink automatically.
Therefore, it is necessary to configure the “independent undo table space” after MySQL 5.7.

3、 Binary log

effect:
Used for replication. In master-slave replication, the slave database uses the binlog on the master database for replay to realize master-slave synchronization.
Point in time restore for databases.

Content:
Logs in logical format can be simply regarded as SQL statements in executed transactions.
However, it is not as simple as the SQL statement, but includes the reverse information of the executed SQL statement (addition, deletion and modification), which means that delete corresponds to the delete itself and its reverse insert; Update corresponds to the version information before and after update execution; Insert corresponds to the information of delete and insert itself.
After parsing binlog with mysqlbinlog, the truth will be revealed.
Therefore, the flashback function similar to Oracle can be achieved based on binlog. In fact, it all depends on the log records in binlog.

When:
When a transaction is submitted, the SQL statements in the transaction (one thing may correspond to multiple SQL statements) are recorded in binlog in a certain format at one time.
The obvious difference between the redo log and the redo log is that the redo log is not necessarily refreshed to the disk when the transaction is committed. The redo log is written to the disk step by step after the transaction starts.
Therefore, for transaction commit, even for large transactions, commit is fast, but bin is enabled_ In the case of log, the commit of larger transactions may become slower.
This is because the binlog is written once when the transaction is committed. These can be verified by testing.

When to release:
The default holding time of binlog is determined by the parameter expire_ logs_ Days configuration, that is, for inactive log files, the generation time exceeds expire_ logs_ Days is automatically deleted after the configured number of days.

在这里插入图片描述

Corresponding physical file:
The path of the configuration file is log_ bin_ Basename, the binlog log file is of the specified size. When the log file reaches the specified maximum size, it is scrolled to generate a new log file.
Each binlog log file is organized through a unified index file.

在这里插入图片描述

other:
One of the functions of binary log is to restore the database, which is very similar to redo log. Many people have confused it, but they are essentially different
Different functions: redo log ensures the persistence of transactions at the transaction level. As a restore function, binlog is at the database level (of course, it can also be accurate to the transaction level). Although they all mean restore, the level of data protection is different.
The contents are different: redo log is a physical log, which is the physical record after the modification of the data page, and binlog is a logical log. You can simply think that what is recorded is SQL statements
In addition, the time when the logs are generated, the time when they can be released, and the cleaning mechanism when they can be released are completely different.
The data recovery efficiency of redo log based on physical log is higher than that of binlog based on statement logic log
As for the writing order of redo log and binlog during transaction submission, in order to ensure the consistency between master and slave during master-slave replication (of course, including point in time restore using binlog), MySQL completes the consistency of transactions through a two-stage submission process, that is, the consistency between redo log and binlog. In theory, redo log is written first, Write the binlog again. After both logs are successfully committed (flushed to disk), the transaction is truly completed.

4、 Error log

The error log records the start and stop of mysqld, as well as the information about the errors that occur during the operation of the server. By default, the function of recording error log is turned off, and error information is output to standard error output.
There are two ways to specify the log path:
1) edit my.cnf and write log error = [path]
2) through the command parameter error log mysqld_ safe –user=mysql –log-error=[path] &

Command to display the error log (as shown in the following figure)

在这里插入图片描述

5、 General query log

Each query or command received by the server is recorded. Whether these queries or commands are correct or even contain syntax errors, the general log will record them in the format of {time, ID, command, argument}. It is also because the MySQL server needs to keep logging, and opening the general log will incur considerable system overhead. Therefore, MySQL closes the general log by default.

View the storage method of logs: Show variables like ‘log_ output’;

在这里插入图片描述

If MySQL > set global log is set_ If output = ‘table’, the log result will be recorded to a file named gengera_ Log, the default engine of this table is CSV
If you set the table data to the file set global log_ output=file;
Set the log file path of general log:
set global general_log_file=’/tmp/general.log’;
Open general log: set global general_ log=on;
Close general log: set global general_ log=off;

在这里插入图片描述

Then use: show global variables like ‘general_ log’

在这里插入图片描述

6、 Slow query log

The slow log records the query statements that take too long to execute and do not use the index, and reports errors in select, update, delete and insert statements. The slow log will only record the successful statements.

1. View slow query time:

show variables like “long_query_time”; Default 10s

在这里插入图片描述

2. View the slow query configuration:


show status like “%slow_queries%”;

在这里插入图片描述

在这里插入图片描述

3. View slow query log path:


show variables like “%slow%”;

在这里插入图片描述  

4. Enable slow log

在这里插入图片描述

View enabled:

在这里插入图片描述

This is the end of this article about the summary of 7 kinds of logs in MySQL. For more relevant MySQL logs, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!