Shulou(Shulou.com)05/31 Report--

This article will give you a detailed introduction to the MySQL log module. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Catalogue

I. brief introduction

II. Redo log

three。 Binlog

four。 Internal workflow

MySql Learning column

1. Detailed description of MySQL infrastructure

2. Underlying data structure and algorithm of MySQL index

3. MySQL5.7 enables binlog log and simple example of data recovery

4. MySQL log module

I. brief introduction

MySQL has two important logging modules: redo log (redo log) and binlog (archive log).

Redo log is the log of the InnoDB storage engine layer, and binlog is the log recorded by the MySQL Server layer. Both are logs that record certain operations, but they are recorded in different formats.

II. Redo log

Redo log: also known as (redo log) file, used to record changes in transaction operations, recording the value of the data after modification, regardless of whether the transaction was committed or not.

In media failure, redo log files can come in handy. For example, if the database is powered off, the InnoDB storage engine will use redo log to restore to the moment before the power failure to ensure the integrity of the data.

When there is a record that needs to be updated, the InnoDB engine will first write the record to redo log and update the memory, and the update is complete.

The InnoDB engine will update the operation record to disk at the appropriate time, and this update is usually done when the system is idle, so as to improve the update efficiency.

When it comes to WAL, or Write-Ahead Logging technology, his key point is to write the log first and then write the disk.

InnoDB redo log is a fixed size, for example, can be configured as a group of 4 files, each file size is 1GB, then a total of 4GB operations can be recorded.

Redo log will write from the beginning, and at the end of the write, it will cycle back to the beginning, as shown in the following figure.

Write pos is the location of the current record, move back while writing, and then go back to the beginning of file 0 after writing to the end of document 3.

Check point is the current location to be erased, and it is also pushed back and looped, updating the record to the data file before erasing the record.

Between write pos and check point is the unused part that can be used to record new operations.

If write pos catches up with check point, it means that the redo log records are full and no new updates can be performed at this time. You have to stop and erase some records first and push check point forward.

With redo log,InnoDB, you can ensure that even if the database is abnormally restarted, the previously submitted records will not be lost, which is called crash-safe.

Why use redo log logs?

If we DML the database and write the execution SQL directly to disk, when the write concurrency is large, the pressure of writing data to disk will be affected to a certain extent.

When we insert the operation and find that there is not enough data per page in the current non-leaf node, the efficiency of the paging algorithm will be relatively low.

When I use the redo log log, first write our DML operations to the log, through a "transit station", and then write to disk through check point in my spare time, it will be much more efficient.

MySQL sets Redo Log

Size of the innodb_log_buffer_size written on the day: (default is 8m)

Size of the innodb_log_file_size redo log file.

Innodb_log_files_in_group specifies the number of files in the redo log filegroup. The default is 2.

Innodb_mirrored_log_groups specifies the number of log image filegroups. The default is 1.

Innodb_log_group_home_dir specifies the path where the log filegroup is located. / by default, it is in the data directory of the database.

When innodb_flush_log_at_trx_commit sets commit, how to set log brush in log buffer in log file (values 0, 1, 2) defaults to 1

three。 Binlog

Redo log is unique to the InnoDB engine, while the Server layer also has its own log, called binlog (Archive Log).

Why are there two journals?

Because at first there was no InnoDB engine in MySQL. MySQL comes with an engine of MyISAM, but MyISAM does not have the capability of crash-safe, and binlog logs can only be used for archiving.

InnoDB is another company that introduced MySQL in the form of plug-ins, and since there is no crash-safe capability to rely on binlog alone, InnoDB uses another logging system, that is, redo log, to implement crash-safe capabilities.

There are three differences between the two kinds of logs.

Redo log is specific to the InnoDB engine; binlog is implemented at the Server layer of MySQL and can be used by all engines.

Redo log is a physical log that records "what changes have been made on a data page"; binlog is a logical log that records the original logic of the statement, such as "add 1 to the c field of the ID=2 line."

Redo log is written in a loop and the fixed space is used up; binlog can be appended to write. "append write" means that when the binlog file is written to a certain size, it will switch to the next one and will not overwrite the previous log.

four。 Internal workflow

Taking the update statement of a table as an example, take a look at the internal workflow of the executor and the InnoDB engine:

Mysql > update T set c=c+1 where ID=2

As shown in the following figure, the light box indicates that it is executed inside the InnoDB, and the dark box indicates that it is executed in the actuator:

The actuator first goes to the engine to fetch the ID=2 line. ID is the primary key, and the engine uses a tree search to find this line. If the data page of the ID=2 line is already in memory, it is returned directly to the executor; otherwise, you need to read the memory from disk and then return it.

The executor takes the row data given by the engine, adds 1 to this value, for example, it was N, now it is Number1, gets a new row of data, and then calls the engine interface to write this new line of data.

The engine updates the new line of data to memory and records the update operation to redo log, when the redo log is in the prepare state. The executor is then informed that the execution is complete and the transaction can be committed at any time.

The executor generates the binlog for this operation and writes the binlog to disk.

The executor calls the commit transaction interface of the engine, and the engine changes the redo log just written to the commit (commit) state, and the update is completed.

The last three steps seem a bit "winding", splitting the redo log write into two steps: prepare and commit, which is actually a "two-phase commit".

Why do logs need "two-phase commit"? It can be explained by counterproof here.

Because redo log and binlog are two separate logic, if you don't need a two-phase commit, either write the redo log first and then write the binlog, or in reverse order. Using the previous update statement as an example, let's see what's wrong with these two approaches.

Suppose the line of the current ID=2, the value of field c is 0, and what happens if crash occurs during the execution of the update statement after the first log is written and the second log is not finished?

1. Write redo log first and then binlog. Suppose that the MySQL process restarts abnormally when the redo log is finished and the binlog is not finished. After the redo log is written, the system can still recover the data even if it crashes, so the value of c in this line after recovery is 1.

However, because the crash is not finished with binlog, there is no record of this statement in binlog at this time. Therefore, when you back up the log later, there is no such statement in the saved binlog.

Then you will find that if you need to use this binlog to restore the temporary library, because the binlog of this statement is missing, the temporary library will lose this update, and the value of the recovered line c is 0, which is different from the value of the original library.

two。 Write binlog first and then redo log. If crash is written after binlog, the value of c in this line is 0 because the redo log has not been written and the transaction is invalid after crash recovery.

But the log "change c from 0 to 1" has been recorded in binlog. So, when you use binlog to recover later, there is one more transaction, and the value of the recovered row c is 1, which is different from the value of the original library.

As you can see, if you do not use two-phase commit, the state of the database may be inconsistent with that of the library recovered with its logs.

This is the end of this article on "introduction to the MySQL log module". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.