MySQL-Log type

Seven types of log files

There are seven types of log files in MySQL, namely:

• Redo log (redo log)

• Rollback log (undo log)

• Binary log (Binlog)

• Error log (errorlog)

• Slow query logs (slow query log)

• General query log (generic log)

• Relay logs (relay log)

where redo logs and rollback logs are closely related to transactional operations, binary logs are also related to transactional operations, and these three kinds of logs are important for understanding the transactional operations in MySQL. Here's a brief summary of the three logs with a certain correlation.

Redo log (redo log)function

The Redo log (redo log) is useful for ensuring the persistence of transactions and preventing dirty pages from being written to disk at the point of failure.

When restarting the MySQL service, the redo log is re-made to achieve the persistence of the transaction.

content

The physical format of the log, recorded by the Physical data page modification information, and its redo log is written in order to write redo log file in the physical files.

when is it produced?

The redo Log,redo log is generated after the transaction begins not written with the commit of the transaction, but in the execution of the transaction, it begins to write to the redo log file.

when is it released?

When the dirty page of the corresponding transaction is written to disk, the mission of 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 file is located in the database's data directory under Ib_logfile1&ib_logfile2.

INNODB_LOG_GROUP_HOME_DIR specifies the path to the log file group, by default./, indicates the number of files in the data directory of the database, Innodb_log_files_in_group specified in the Redo log filegroup, default to 2.

The size and number of files is 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, default 1.

other

It's important to know when redo log was written. What I said earlier is that we write the dishes gradually after the beginning of things.

The redo log is said to be progressively written to the redo log file after the transaction starts, not necessarily the transaction commit, to the redo log cache.

The reason is that the redo log has a buffer innodb_log_buffer,innodb_log_buffer the default size of 8M (16M set here), the INNODB storage engine first writes the redo log to Innodb_log_buffer.

The log of the Innodb log buffer is then flushed to disk in the following three ways:

• Master Thread flushes innodb_log_buffer to redo log files once per second.

• The redo log is flushed to the redo log file when each transaction commits.

• When the redo log cache has less than half of free space, the redo log cache is flushed to the redo log file.

As you can see, the Redo log is written to disk in more than one way, especially for the first way, the Innodb_log_buffer to redo log file is the timer task for the Master thread thread.

Therefore, the redo log write disk does not have to be written to the redo log file as the transaction commits, but begins gradually as the transaction begins.

another quote from the MySQL technology insider Innodb Storage Engine (PAGE37):

Even though a transaction has not yet been committed, the INNODB storage engine flushes the redo log cache to the redo log file every second.

This is something that must be known, as this can be a good way to explain that the time to commit a large transaction is also very short.

Rollback log (undo log) function

Saves a version of the data before the transaction occurs, can be used for rollback, and can provide read (MVCC) under multi-version concurrency control, which is also non-locking read.

content

The logical format of the log, when executing undo, is simply to restore the data logically to the state before the transaction, rather than from the physical page operation implementation, this is different from the redo log.

when is it produced?

Before the transaction begins, generating the current version of Undo Log,undo will also produce redo to guarantee the reliability of the undo log.

when is it released?

When the transaction is committed, undo log cannot be deleted immediately, but instead is placed in the list to be cleaned, and the purge thread determines whether the log space of the undo log can be cleaned up by other transactions before the previous transaction in the undo segment using the version information of the table.

the corresponding physical file

Prior to MySQL 5.6, the undo tablespace was in the rollback segment of the shared tablespace, and the default name for the shared tablespace is Ibdata, which is located in the data file directory.

After MySQL 5.6, the undo Tablespace can be configured as a standalone file, but it needs to be configured in the configuration file in advance, which takes effect after initialization of the database and cannot change the number of undo log files.

If the database is not configured before it is initialized, it cannot be configured as a standalone tablespace.

After MySQL 5.7, the standalone undo table space configuration parameters are as follows:

• Innodb_undo_directory = storage directory for/data/undospace/–undo stand-alone table space

• Innodb_undo_logs = 128– rollback segment is 128KB

• Innodb_undo_tablespaces = 4– Specifies 4 undo log files

If the shared tablespace used by Undo is not just storing the undo information in this shared table space, the shared tablespace will default 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 start of the transaction, and when the undo log is generated, it is also accompanied by a redolog similar to the protection of the transaction persistence mechanism.

By default, the undo file is persisted in the shared tablespace, which is the Ibdatafile file, where a large amount of undo information is generated when there are some large transactional operations in the database, all of which are saved in the shared table space.

As a result, shared table spaces can become very large, and by default, when the Undo Log uses shared table space, the shared table space that is "stretched" will not and cannot be automatically shrunk.

Therefore, the configuration of the "standalone undo table Space" after MySQL 5.7 is necessary.

Binary log (binlog) function

For replication, in master-slave replication, the master-slave synchronization is performed from the library using the Binlog on the main library, and the database is used for point-in-time restore.

content

The logical format of the log, can be simply considered to be executed in the transaction in the SQL statement, but not exactly the SQL statement is so simple.

It includes the execution of the SQL statement (the deletion) of the reverse information, it means that delete corresponds to the delete itself and its inverse insert;update corresponds to the version of the update before and after the execution of information; Insert corresponds to delete and insert itself The information.

After parsing binlog with Mysqlbinlog, some will come to the truth. As a result, Oracle-like flashback can be done based on Binlog, in fact relying on binlog logging.

when is it produced?

When a transaction commits, the SQL statement in the transaction (a thing may correspond to multiple SQL statements) is recorded to Binlog in a certain format.

The obvious difference here with redo log is that redo log is not necessarily flushed to disk at the time the transaction is committed, redo log is written to disk gradually after the transaction begins.

As a result, the commit of a transaction, even for a larger transaction, is very fast, but in the case of Bin_log, the submission of larger transactions may become slower.

This is because Binlog is caused by a one-time write at transaction commit, which can be verified by the test.

when is it released?

Binlog By default, the hold time is configured by the parameter expire_logs_days, which means that for inactive log files, it is automatically deleted after the build time exceeds the number of expire_logs_days configured.

the corresponding physical file

The path to the configuration file is a log_bin_basename,binlog log file that, after the specified size, is rolled up to the specified maximum size, and a new log file is generated.

For each binlog log file, it is organized by a unified index file.

other

One of the functions of the binary log is to restore the database, which is similar to redo log, and many people confuse it, but the two are fundamentally different:

• Different functions:
  Redo log is a transactional dimension that guarantees the persistence of transactions, and Binlog is a database-level (and, of course, transaction-level) function as a restore. Although all have the meaning of restore, but the level of data protection is not the same.

• Different content:
  Redo log is the physical log, the data page after the modification of the physical record, Binlog is a logical log, you can simply think that the record is the SQL statement.

• Other than that:
  The time that the logs are generated, the time that can be freed, and the cleanup mechanism in the case of release are completely different.

• Efficiency when recovering data:
  The physical log-based redo log recovers data more efficiently than the binlog of the statement logical log.

When it comes to transaction commits, the redo log and Binlog are written in order to ensure that the master-slave replication is consistent (including, of course, the use of binlog for Point-in-time restore-based situations) is strictly consistent.

MySQL through the two-phase commit process to complete the consistency of the transaction, that is, redo log and binlog consistency, in theory, write redo log, then write Binlog, two logs are submitted successfully (brush into the disk), the transaction is really complete.

Summary

MySQL, for the above three kinds of logs, each kind of refinement can be enough to write a chapter, here a rough summary of the three kinds of log features and functions to help understand the things in MySQL and the principle behind things.

Blog Handling Address

• How many types of logs does MySQL need us to remember?

MySQL-Log type