Article Directory

• 1. MySQL log file

• 
  

• 1.4.1. Introduction to binlog

• 1.4.2. Commit transaction: write to binlog

• 1.4.3. Binlog brushing strategy analysis

• 
  

• 1.4.3.1.sync_binlog = 0

• 1.4.3.2.sync_binlog = 1

• 1.3.1. Undo log file: record the value before data modification

• 1.3.2. Redo log file: record data about to modify the value

• 1.3.3. The difference between Undo and Redo (record, roll forward, roll back)

• 1.3.4. Commit transaction: Redo log is written to disk (flashing strategy)

• 
  

• 1.3.4.1.trx_commit = 0

• 1.3.4.2.trx_commit = 1

• 1.3.4.3.trx_commit = 2

• 1.3.4.4. Comparison of 3 brushing strategies for Redo logs

• 1.1. Review the execution of SQL statements

• 1.2. InnoDB memory structure: buffer pool

• 1.3. Logging: Undo and Redo

• 
  

• 1.4. Archive log: binlog

• 
  

• 1.5. Commit mark based on binlog and redo log

• 1.6. The IO thread flushes the dirty data after the memory update to the disk

1. MySQL log file

1.1. Review the execution of SQL statements

In the last article I, we focused on a process of SQL statement execution.
MySQL performance optimization (1) How to execute SQL statements in MySQL

1.2. InnoDB memory structure: buffer pool

A very important component in the InnoDB storage engine is the buffer pool (Buffer Pool), which will cache a lot of data, so that when you query later, if you have data in the memory buffer pool, You don't need to check the disk.

When the engine wants to execute an update statement, for example, for the row of data "id=1", he will actually first check the row of data "id=1" to see if it is in the buffer pool. If not, it will directly retrieve the data from the disk. Load it into the buffer pool, and then add an exclusive lock to this row of records.

1.3. Logging: Undo and Redo

1.3.1. Undo log file: record the value before data modification

Then in the next step, suppose the name of the row of data "id=1" was originally "zhangsan", and now we want to update it to "hutao", then we have to update the original values "zhangsan" and "id= 1" This information is written to the undo log file.

Before the transaction is committed, we can all roll back the data, that is, roll back the value you updated to "hutao" to the previous "zhangsan". Considering the need to roll back the data in the future, here will write the value before your update to the undo log file to

update the cached data in the buffer pool

When we load the record to be updated from the disk file into the buffer pool, lock it at the same time, and write the old value before the update to the undo log file, we can officially start to update this record. When updating, the records in the buffer pool are first updated, and this data is dirty data at this time.

The so-called updating the data in the memory buffer pool here means that the name field of the row of data "id=1" in the memory is changed to "hutao".
So why is it said that this row of data is dirty data at this time?
Because the name field of the row of data "id=1" on the disk is still "zhangsan" at this time, but the row of data in the memory has been modified, it will be called dirty data.

1.3.2. Redo log file: record data about to modify the value

Redo is the redo log, which records the log of database changes. Now that the data in the memory has been modified, but the data on the disk has not been modified, at this time, if the machine where MySQL is located goes down, the modified data in the memory will inevitably be lost. What can I do?
Redo's changes to the memory are written into a Redo Log Buffer, which is also a buffer in the memory, which is used to store
redo logs. The redo log is actually used to restore your updated data when MySQL suddenly goes down.

If you haven't committed the transaction yet, what should you do
if MySQL is down? Only when you commit the transaction, the SQL statement is considered to be executed. So here we all know that, so far, the transaction has not been committed. If MySQL crashes at this time, the modified data in the Buffer Pool in the memory will inevitably be lost. At the same time, the redo log written in the Redo Log Buffer will be lost. Will be lost.

Does data loss matter at this time? In fact, it does not matter, because your update statement did not commit the transaction, it means that it did not execute successfully. At this time, although MySQL downtime causes the data in the memory to be lost, you will find that the data on the disk still stays in the original Look like. In other words, the value of the name field of the row of data "id=1" is still the old value, "zhangsan", so at this time your transaction failed to execute, and the update failed to complete, you will receive a The database is abnormal. Then when mysql restarts, you will find that your data has not changed. So if mysql is down at this time, there will be no problems.

1.3.3. The difference between Undo and Redo (record, roll forward, roll back)

The difference between recorded data:

1. redo record: record the log of database changes, as long as you modify the data block, redo information will be recorded, except of course nologging

2. Undo record: refers to the historical data stored in the database in order to maintain read consistency, that is, the data before modification.

Roll forward and roll back:

1. Roll forward: When the instance crashes, you can use redo to roll forward from the previous normal point to the crash point. When the database returns to the consistency checkpoint, it is equivalent to nothing happened afterwards, and the data is all emptied. The database had to simulate human operations based on redo, use redo log to redo (use redo log to redo), construct undo blocks, table blocks, index blocks, etc.

2. Rollback: In the constructed table data block, if there is modified dirty data but not submitted, you need to use the information in the undo data block constructed during the roll forward to undo the undo restoration.

1.3.4. Commit transaction: Redo log is written to disk (flashing strategy)

Then we want to commit a transaction. At this time, we will flush the redo log from the redo log buffer to the disk file according to a certain strategy. At this time, this strategy is configured through innodb_flush_log_at_trx_commit (0, 1, 2 can be configured).

1.3.4.1.trx_commit = 0

When committing the transaction, the data in the redo log buffer will not be flushed to the disk file. At this time, you may have committed the transaction. As a result, mysql crashes, and then all the data in the memory is lost. It is equivalent to that you successfully submitted the transaction, but due to the sudden downtime of MySQL, the data in the memory and the redo log are lost. Let's look at the following figure:

1.3.4.2.trx_commit = 1

When committing a transaction, you must flash the redo log from the memory to the disk file. As long as the transaction is successfully committed, the redo log
must be on the disk. Let’s look at the following figure:

Then as long as the transaction is successfully committed, the redo log must be In the disk file, you must have a redo log record at this time, "Which data has been modified, for example, the name field is changed to hutao". Then even if the updated data in the buffer pool has not been flushed to the disk at this time, the data in the memory at this time is the updated "name=hutao", and then the data on the disk is still the unupdated "name=zhangsan" ". Let's look at the following figure, a state that may be in after the transaction is committed.

At this point, if you are in the state shown in the figure above after committing the transaction, and then the mysql system suddenly crashes, what will happen at this time? Will you lose data?
Certainly not, because although the data modified to name=hutao in the memory will be lost, it has been said in the redo log that the data of XX was modified with name=hutao. So after mysql restarts at this time, he can restore the changes made before according to the redo log. Let's see the figure below.

1.3.4.3.trx_commit = 2

When committing the transaction, write the redo log into the os cache cache corresponding to the disk file instead of directly entering the disk file. It may
take 1 second before the data in the os cache is written to the disk file.
In this mode, after you commit the transaction, the redo log may only stay in the os cache memory cache without actually entering the disk file. If your machine goes down at this time, the redo log in the os cache will be lost. It will also make you feel that the transaction is committed, and the resultant data is lost. See the figure below.

1.3.4.4. Comparison of 3 brushing strategies for Redo logs

If you choose 0, maybe after you commit the transaction, mysql crashes, then the redo log is not flushed at this time, resulting in the loss of the redo log in the memory, and the data updated by the transaction you submitted will be lost;

If you choose 1, that is to say, when committing the transaction, the redo log must be flushed to the disk file. This can strictly guarantee that after the transaction is committed, the data will never be lost, because there is a redo log in the disk file that can restore all the changes you made.

If you choose 2, if the machine is down, even though the redo log has entered the os cache when the transaction was submitted before, but has not yet entered the disk file
, the downtime of the machine will still cause the redo log in the os cache to be lost.

Therefore, for a strict system such as a database, it is generally recommended to set the redo log flushing strategy to 1, to ensure that the data must not be lost after the transaction is committed.

1.4. Archive log: binlog

1.4.1. Introduction to binlog

Redo log, he is a kind of redo log that is biased towards physical nature, because it records things like this, "what record in which data page, what changes have been made". And redo log itself is a thing unique to the InnoDB storage engine.

The binlog is called an archive log. It records a log that is biased towards logic, similar to "updated a row of data in the users table with id=1, what is the value after the update"
binlog is not unique to the InnoDB storage engine The log file belongs to the mysql server's own log file.

1.4.2. Commit transaction: write to binlog

When we commit the transaction, the redo log will be written to the disk file. Then in fact, when the transaction is committed, we will also write the binlog log corresponding to this update to the disk file, as shown in the following figure:

You can see some changes in this figure, that is, I store it with InnoDB The engine interaction component adds the previously mentioned executor component. It will be responsible for interacting with InnoDB, including loading data from the disk to the Buffer Pool for caching, including writing to the undo log, including updating the data in the Buffer Pool , And write redo log buffer, flash redo log to disk, write binlog, etc.
In fact, the executor is a very core component that is responsible for completing all data update operations of a SQL statement at the disk and memory level in cooperation with the storage engine. . And as we can see in the above figure, I split the execution of an update statement into two stages. The steps 1, 2, 3, and 4 in the above figure are actually when you execute the update statement. Thing. Then the two steps 5 and 6 in the above figure start from when you commit the transaction, which belongs to the stage of committing the transaction.

1.4.3. Binlog brushing strategy analysis

For binlog logs, there are actually different flushing strategies. There is a sync_binlog parameter to control the binlog flushing strategy.

1.4.3.1.sync_binlog = 0

His default value is 0. At this time, when you write binlog to disk, you actually do not directly enter the disk file, but enter the os cache memory cache. So like the previous analysis, if the machine is down at this time, your binlog log in the os cache will be lost.

1.4.3.2.sync_binlog = 1

If the sync_binlog parameter is set to 1, then it will be forced to write the binlog directly to the disk file when the transaction is submitted.
Then after the transaction is submitted, even if the machine is down, the binlog on the disk will not Lost, as shown in the picture below

1.5. Commit mark based on binlog and redo log

When we write the binlog to the disk file, the final transaction commit will be completed. At this time, the name of the binlog file corresponding to this update and the location of the updated binlog log in the file will be written to the redo log. Go to the log file, and write a commit mark in the redo log log file. After completing this matter, the transaction submission is finally completed. Let's look at the diagram below:

What is the point of writing the commit mark in the redo log at the end? It is actually used to keep the redo log consistent with the binlog log .
For example, suppose that when we commit the transaction, there are a total of three steps 5, 6, and 7 in the above figure. All three steps must be executed before the transaction is committed. So when we just completed step 5, that is, when the redo log was flashed to the disk file, mysql was down. What should I do at this time?
At this time, because there is no final transaction commit mark in the redo log, the transaction can be judged as unsuccessful. It will not be said that there is a log of this update in the redo log file, but there is no log of this update in the binlog log file, and there will be no data inconsistency. What if mysql is down when the binlog is written to disk when step 6 is completed? In the same way, because there is no final commit mark in the redo log, the transaction commit also fails at this time. The final transaction commit mark must be written in the redo log, and then the transaction is submitted successfully, and there is the log corresponding to this update in the redo log, and the log corresponding to this update is also in the binlog. The redo log and binlog are completely Consistent.

1.6. The IO thread flushes the dirty data after the memory update to the disk

Now we assume that the transaction has been committed, update at this time

update users set name='hutao' where id=11

He has updated the cache data in the buffer pool in the memory. At the same time, there are redo logs and binlog logs on the disk, both of which record that the line of data we specified "id=1" has been modified to "name='hutao'". At this point we will think about a problem, but at this time the name field of the row of data "id=1" in the data file on the disk is still equal to the old value of zhangsan!

Therefore, MySQL has a background IO thread that will randomly flush the modified dirty data in the memory buffer pool back to the data file on the disk at a certain time.

When the IO thread in the above figure flushes the modified dirty data in the buffer pool back to the disk, the data on the disk will be the same as in the memory, which is the modified value of name=hutao! Before your IO thread flushes the dirty data back to disk, it doesn’t matter even if mysql crashes or crashes, because after restarting, the changes made by the transaction before committing to the memory will be restored according to the redo log, which is the name modification of the data with id=1 For hutao, and then wait for an appropriate time, the IO thread will naturally flash the modified data to the data file on the disk.