Summary of 7 types of logs in MySQL

There are the following log files in MySQL:

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

Among them, redo logs and rollback logs are closely related to transaction operations, and binary logs also have a certain relationship with transaction operations. These three logs are of great significance for understanding transaction operations in MySQL.

1. Redo log

effect:
Ensure transaction durability. The redo log records the status after the transaction is executed and is used to recover the data updated by the successful transaction that has not been written to the data file. To prevent dirty pages from being written to disk at the time of a failure, redo the transaction based on the redo log when the MySQL service is restarted, thereby achieving the durability of the transaction.

content:
The physical format log records the modification information of the physical data pages, and its redo log is written sequentially to the physical file of the redo log file.

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

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

The corresponding physical file:
By default, the corresponding physical files are located in ib_logfile1&ib_logfile2 in the data directory of the database.
innodb_log_group_home_dir specifies the path where the log file group is located. The default value is ./, which means 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 mirror file groups, the default is 1

other:
A very important point is, when is the redo log written to disk? As mentioned before, the disk is written gradually after the transaction starts.
The reason why the redo log is written to the redo log file gradually after the transaction starts, and not necessarily written to the redo log cache after the transaction is committed, is that the redo log has a buffer area Innodb_log_buffer. The default size of Innodb_log_buffer is 8M (16M is set here). The Innodb storage engine first writes the redo log to innodb_log_buffer.

Then the logs in the innodb log buffer will be flushed to disk in the following three ways:

The Master Thread executes flushing of Innodb_log_buffer to the redo log file once per second.

Each transaction commits and flushes the redo log to the redo log file.

When the redo log cache has less than half of its available space, the redo log cache is flushed to the redo log files.

From this we can see that the redo log is written to disk in more than one way, especially for the first way, Innodb_log_buffer to the redo log file is a scheduled task of the Master Thread thread.

Therefore, the writing of redo logs to disk is not necessarily done when the transaction is committed, but gradually starts with the start of the transaction.

In addition, I quote the original words from "MySQL Technology Insider Innodb Storage Engine" (page 37):

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

This is important to know, because it can well explain why the commit time of even the largest transactions is very short.

2. Rollback log (undo log)

effect:
Ensures data atomicity and saves a version of the data before the transaction occurs, which can be used for rollback. It can also provide reads under multi-version concurrency control (MVCC), that is, non-locking reads.

content:
Logical logs only restore data to the state before the transaction when performing undo operations, rather than operating on physical pages. This is different from redo logs.

When is it generated:
Before the transaction starts, the current version is generated into an undo log. Undo will also generate redo to ensure the reliability of the undo log.

When released:
After a transaction is committed, the undo log is not deleted immediately, but is put into a linked list to be cleaned up. The purge thread determines whether other transactions are using the version information before the previous transaction in the table in the undo segment, and decides whether the log space of the undo log can be cleaned up.

The corresponding physical file:
Before MySQL 5.6, the undo tablespace is located in the rollback segment of the shared tablespace. The default name of the shared tablespace is ibdata, which is located in the data file directory.
After MySQL 5.6, the undo tablespace can be configured as an independent file, but it needs to be configured in the configuration file in advance. It will take effect after the database is initialized and the number of undo log files cannot be changed. If no relevant configuration is performed before initializing the database, it cannot be configured as an independent tablespace.

The configuration parameters for independent undo tablespaces after MySQL 5.7 are as follows:
innodb_undo_directory = /data/undospace/ – the storage directory of the undo independent tablespace innodb_undo_logs = 128 – the rollback segment is 128KB innodb_undo_tablespaces = 4 – specifies 4 undo log files If the shared tablespace used for undo, this shared tablespace not only stores the undo information, the shared tablespace defaults to the MySQL data directory, and its properties are configured by the parameter innodb_data_file_path.

other:
Undo is a version of the modified data saved before the transaction starts. When an undo log is generated, a redo log similar to the mechanism for protecting transaction persistence is also generated.
By default, the undo file is kept in the shared tablespace, that is, the ibdatafile file. When some large transactional operations occur in the database, a large amount of undo information is generated and all saved in the shared tablespace.
Therefore, the shared tablespace may become very large. By default, when the undo log uses the shared tablespace, the "expanded" shared tablespace will not and cannot shrink automatically.
Therefore, the configuration of "independent undo tablespace" after MySQL 5.7 becomes very necessary.

3. Binary log (binlog)

effect:
Used for replication. In master-slave replication, the slave database uses the binlog on the master database for replay to achieve master-slave synchronization.
Used for point-in-time restore of a database.

content:
Logs in logical format can be simply considered as SQL statements in executed transactions.
But it is not just a simple SQL statement, but includes the reverse information of the executed SQL statement (addition, deletion and modification), which means that delete corresponds to delete itself and its reverse insert; update corresponds to the information of the versions before and after update is executed; insert corresponds to the information of delete and insert itself.
After using mysqlbinlog to parse binlog, some of the truth will become clear.
Therefore, based on binlog, we can achieve a flashback function similar to Oracle, which actually relies on the log records in binlog.

When is it generated:
When a transaction is committed, all SQL statements in the transaction (one transaction may correspond to multiple SQL statements) are recorded in the binlog in a certain format at one time.
The obvious difference here is that the redo log is not necessarily flushed to disk when the transaction is committed. The redo log is gradually written to disk after the transaction starts.
Therefore, the submission of transactions, even for larger transactions, is very fast. However, if bin_log is enabled, the submission of larger transactions may become slower.
This is because binlog is written once when the transaction is committed, which can be verified through testing.

When released:
The default retention time of binlog is configured by the parameter expire_logs_days, which means that for inactive log files, they will be automatically deleted after the generation time exceeds the number of days configured by expire_logs_days.

The 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 rolled over and a new log file is generated.
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 them, but the two are fundamentally different: redo log is to ensure the persistence of transactions, which is at the transaction level, while binlog, as a restore function, is at the database level (of course, it can also be precise to the transaction level). Although both have the meaning of restoration, the levels of data protection are different.
Different content: redo log is a physical log, which is a physical record of the modification of the data page, and binlog is a logical log, which can be simply considered to record SQL statements. In addition, the time when the logs are generated, the time when they can be released, and the cleanup mechanism when they can be released are completely different.
The efficiency of data recovery is higher than that of statement logical log binlog.
Regarding the writing order of redo log and binlog when a transaction is committed, in order to ensure the consistency between the master and the slave during master-slave replication (of course, this also includes the use of binlog for point-in-time recovery), it must be strictly consistent. MySQL completes the consistency of transactions through a two-phase commit process, that is, the consistency of redo log and binlog. In theory, the redo log is written first, and then the binlog is written. The transaction is truly completed only when both logs are successfully committed (flushed to disk).

4. Error log

The error log records information about mysqld starting and stopping, as well as errors that occur while the server is running. By default, the system error log function is disabled and error messages are output to standard error output.
There are two ways to specify the log path:
1) Edit my.cnf and write log-error=[path]
2) Error log through command parameters mysqld_safe –user=mysql –log-error=[path] &

Command to display the error log (as shown below)

5. General query log

The general log records every query or command received by the server, regardless of whether the query or command is correct or even contains syntax errors. The record format is {Time, Id, Command, Argument}. Because the MySQL server needs to record logs continuously, turning on the General log will incur considerable system overhead. Therefore, MySQL turns off the General log by default.

View the log storage method: show variables like 'log_output';

If you set mysql> set global log_output='table', the log results will be recorded in a table called gengera_log. The default engine for this table is CSV.
If you set the table data to a file set global log_output=file;
Set the log file path for the general log:
set global general_log_file='/tmp/general.log';
Enable general log: set global general_log=on;
Disable general log: set global general_log=off;

Then use: show global variables like 'general_log'

6. Slow query log

The slow log records query statements that take too long to execute and do not use indexes, and reports errors for select, update, delete, and insert statements. The slow log only records statements that are successfully executed.

1. Check the slow query time:

show variables like "long_query_time"; default 10s 

2. Check the slow query configuration:

show status like “%slow_queries%”; 

3. View the slow query log path:

show variables like "%slow%"; 

4. Enable slow log

Check if it is enabled:

This is the end of this article about the 7 types of log summaries in MySQL. For more relevant MySQL log 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!