Shulou(Shulou.com)06/01 Report--

Today, I would like to share with you the relevant knowledge of what the concept of MySQL redo log is, the content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article.

In the ACID characteristics of transactions, atomicity (A), consistency (C), persistence (D) are implemented by undo log and redo log, and isolation (I) is implemented by lock + MVCC.

Undo log: the transaction does not have commit yet, and an exception is executed midway. You can use undo log to restore the data to the state before the transaction execution to ensure the atomicity of the transaction.

Redo log: transaction commit is successful. Since it takes some time to update disk data, if an exception occurs, you can use redo log to re-execute the SQL of the transaction to ensure the persistence of the transaction (as long as the transaction commit is successful, no matter what abnormal events occur, as long as the next MySQL service runs normally, the data of the last commit must be recovered)

I. the concept of redo log

Redo log: known as the physical log, it records the data page stored by page after the final modification, directly stores the final state of the data, and is used to ensure the persistence of the transaction.

Undo log: known as a logical log, it stores specific corresponding SQL statements. If insert is executed now, delete; will be executed when rollback. If update is executed now, the old value will be returned to update.

Redo log is placed under / var/lib/mysql by default

Redo log is recorded when the transaction begin (not when the transaction commit is recorded, because there may be a lot of operations done by the whole transaction. If redo log is written during the commit, once an exception occurs and the redo log is not written, it is too late to ensure the persistence of the transaction). No matter whether the transaction commits or not, it will be recorded when the exception occurs (such as power loss during data persistence) InnoDB will use redo log to restore to the moment before the power outage to ensure the integrity of the data

Innodb_log_buffer_size defaults to 16m, which is the size of the redo log buffer. It starts to write redo log as the transaction starts. If the transaction is relatively large, in order to avoid spending too much disk IO during transaction execution, you can set a larger redo log cache to save disk IO. There is an opportunity to refresh to the disk. When the time is right, it will cost the disk IO. If the buffer is relatively large, the refresh time will be slower and the efficiency will be higher.

InnoDB modifies the operational data, not directly modifying the data on disk, but actually modifying the data in Buffer Pool. InnoDB always records the data changes in Buffer Pool to redo log first, which is used for data recovery after a crash. Record the redo log first, and then find the opportunity to slowly flush the dirty data in the Buffer Pool to disk.

Two files in the directory specified by innodb_log_group_home_dir: ib_logfile0,ib_logfile1, which is called the redo log

Buffer pool cache pool: can store index cache, data cache, etc., can accelerate read and write, directly manipulate data pages, write redo log modification even if completed, there are special threads to write dirty page in buffer pool to disk

The default size of buffer pool is 134m (MySQL 5.7m)

The approximate structure is shown in the figure:

Transaction reads and modifications are all priority operations on the data in the cache pool. In the actual project, mysqld will run on a single machine, and a large amount of memory can be allocated specifically for InnoDB buffer pool to speed up CRUD.

Second, cache and disk structure

When the transaction commit, the operation on the diagram is to write the contents of the InnoDB Log Buffer to disk. If the write is successful, the redo log on the disk will record the status-commit. If it is not successful or finished, record the status-prepare.

Problems such as exceptions and power outages may also occur when log writes to disk, resulting in unfinished writing of redo log (which means that the transaction is not successful with commit). In this case, it is not necessary for MySQL to consider the integrity of the transaction when it is restored next time, because the state is not commit, and only when the state is written to disk means that redo log writes successfully, and the status becomes commit. After the state changes to commit, you need to maintain the ACID feature of the transaction.

Is it true that the dirty data (data has been modified) in the buffer poll is only written to disk during commit?

You don't have to wait for commit to start. The transaction may modify a large amount of data, but the cache capacity is limited. For the data cached by buffer poll, a special thread will refresh it to disk at the right time. If there is a power outage, the data will be recovered according to the data recorded in redo log after the next MySQL startup.

Undo log itself is also recorded in redo log.

Undo log supports transaction rollback, which is not complete in an instant. In the end, it is the data on disk that needs to be modified. In order to prevent exceptions during rollback, undo log should be recorded in redo log. Transaction commit success or rollback success, for the underlying, are successful to write the operation to the redo log.

What is the real transaction commit success?

Instead of brushing all the data to disk, the redo log recording the complete operation of the transaction is written to disk from log buffer, and then the state of the modified data is set to commit in order to achieve the success of transaction commit. At this time, although the data is still in buffer poll, as long as our redo log is intact, the data can be recovered, and there will be a special thread responsible for writing the data in buffer poll to disk.

When a transaction operates, always write redo log first, and then write buffer pool; transaction success commit, that is, to ensure that the redo log is fully recorded on disk

As for the change of table data, whether the dirty data page of buffer pool is refreshed to disk or not, we don't have to worry at all. As long as redo log is completely written to disk, we can restore the data state of transaction successful commit at any time through redo log redo log (the most important thing in the database is the log, not the data).

These are all the contents of the article "what is the concept of MySQL redo log". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

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.