There are six types of log files in MySQL: Redo logs, undo logs, binary logs, errorlogs, slow Query logs, general logs, and relay logs. Redo logs and rollback logs are closely related to transaction operations, and binary logs also have a certain relationship with transaction operations. These three kinds of logs are of great significance to understand transaction operations in MySQL. Here is a brief summary of the three related logs.
Redo log
Function:
Ensure the persistence of transactions. In case there are dirty pages that have not been written to disk at the time of the failure, redo the mysql service according to the redo log to achieve transaction persistence.
Content:
Physical log format. Redo logs are written sequentially to physical files called redo log files.
When:
Redo logs are created after a transaction has started. Redo logs are not written to the redo log file as the transaction commits.
When to release:
After the dirty pages of the transaction are written to disk, the redo log is done, and the space occupied by the redo log can be reused (overwritten).
Corresponding physical file:
Ib_logfile1&ib_logfile2 innodb_log_group_home_DIR Specifies the path where the logfile group resides. By default, it is in the data directory of the database./ indicates that the logfile group resides. Innodb_log_files_in_group Specifies the number of files in the redo log file group. By default, 2 specifies the size and number of files. Innodb_log_file_size Specifies the size of redo log files. Innodb_mirrored_log_groups specifies the number of log mirror groups. The default was 1
Other:
Very importantly, when was the redo log written? As mentioned above, I gradually write the disk after things start. The redo log is written to the redo log file gradually after the transaction starts, not necessarily to the redo log cache after the transaction commits, because the redo log has a buffer Innodb_log_buffer. The default size of Innodb_log_buffer is 8M(16M is set here). Innodb storage engine writes redo logs to Innodb_log_buffer first.
The Master Thread flusher Innodb_log_buffer to redo log files once per second. The Master Thread flusher Innodb_log_buffer to redo log files once per second. 2. The redo log is flushed to the redo log file at each transaction commit. Innodb_log_buffer to the redo log file is a scheduled task for the Master Thread. Innodb_log_buffer to the redo log file is a scheduled task for the Master Thread. Innodb_log_buffer to the redo log file is a scheduled task for the Master Thread. Therefore, the write disk of the redo log is not necessarily written to the redo log file as the transaction commits, but is gradually started as the transaction begins. Innodb storage Engine (Page37) : Even if a transaction has not committed, Innodb storage engine will flush the redo log cache to redo log files every second. This is important to know, because it helps to explain how short the commit time is for even large transactions.
Rollback log (undo log)
Function:
Holds a version of the data prior to the transaction, can be used for rollback, and can also provide multi-version concurrency control read (MVCC), that is, unlocked read
Content:
Logic-format logs, unlike redo logs, only logically restore data to the pre-transaction state when undo is executed, rather than operating from the physical page.
When:
Before the transaction starts, the current version of undo log is generated. Undo generates redo to ensure the reliability of Undo log
When to release:
After a transaction is committed, the Undo log cannot be deleted immediately. Instead, it is placed in the linked list to be cleaned, and the Purge thread determines whether the undo log space can be cleaned by other transactions that used version information prior to the last transaction in the undo segment table.
Corresponding physical file:
Before MySQL5.6, the undo tablespace was in the rollback segment of the shared tablespace. The default name of the shared tablespace was ibData and it was in the data file directory. After MySQL5.6, the undo tablespace can be configured as an independent file, but it must be configured in the configuration file in advance. The number of undo log files takes effect after the database initialization and cannot be changed. Innodb_undo_directory = /data/undospace/ — innodb_undo_logs = 128 Innodb_undo_tablespaces = 4 — Specify 4 undo log files
If undo uses a shared table space, the shared table space does not only store undo information. The default shared table space is under 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. The generation of undo logs is also accompanied by redolog, which is similar to the protection mechanism for transaction persistence. By default, undo files are stored in the shared tablespace, that is, ibDatafile files. When some large transactional operations occur in the database, a large amount of undo information is generated and stored in the shared tablespace. Therefore, shared tablespaces can become very large. By default, when undo logs use shared tablespaces, “stretched” shared tablespaces do not and cannot shrink automatically. Therefore, the “separate Undo tablespace” configuration after mysql5.7 is necessary.
Binary log:
Function:
1. It is used for replication. In master-slave replication, the secondary library uses the binlog on the master library to replay and achieve master-slave synchronization. 2. Point-in-time restore for databases.
Content:
Logs in logical format can simply be thought of as SQL statements in executed transactions. It contains information about the reverse of the SQL statement executed (add, delete, alter), which means that delete corresponds to delete itself and its reverse insert. Update indicates the version before and after the update. Insert corresponds to delete and insert information itself. Some of this will come to light after parsing the binlog using mysqlbinlog. So you can use binlog to do something similar to Oracle’s flashback function, which actually relies on logging in binlog.
When:
When a transaction is submitted, the SQL statements in the transaction (one transaction may correspond to multiple SQL statements) are recorded in a binlog in a certain format. The obvious difference with the redo log is that the redo log is not necessarily flushed to disk at transaction commit time. The redo log is progressively written to disk after the transaction has started. Therefore, the commit of transactions, even large transactions, is fast, but with bin_log enabled, the commit of large transactions may be slower. This is because the binlog is written once at transaction commit time, which can be verified by testing.
When to release:
By default, the retention time of binlog is set by expire_logs_days. That is, inactive log files are automatically deleted after the generation time exceeds expire_logs_days.
Corresponding physical file:
The path of the configuration file is log_bin_basename. The size of the binlog file is specified. When the size of the log file reaches the specified maximum, a new log file is generated. For each binlog file, a unified index file is organized.
Other:
One of the functions of binary logs is to restore a database. This is very similar to redo logs, which are often confused with redo logs, but are fundamentally different: Redo logs are used to ensure the persistence of a transaction, while binlogs are used to restore data at the database level. 2. The contents of the log are different: Redo log is a physical log, which is the physical record of the modification of the data page. Binlog is a logical log, which can be considered as SQL statement 3. 4. Data recovery efficiency: Physical log redo log restores data more efficiently than statement logical log binlog
The redo log and the binlog are written in the same order as the redo log and the binlog. The redo log and the binlog are written in the same order as the redo log and the binlog. A transaction is not complete until the redo log is committed and then the binlog is committed.
Conclusion:
MySQL, for the above three types of logs, each of which can be detailed enough to write a chapter, here is a rough summary of the three types of log features and functions, to help understand the things in MySQL and the principles behind things.
Innodb Storage Engine