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

This article mainly explains "what are the three kinds of MySQL logs". Friends who are interested may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are the three MySQL logs?"

Preface

Have you ever wondered why MySQL database can achieve master-slave replication, persistence and rollback? In fact, the key lies in the three kinds of log in MySQL, which are:

Binlogredo logundo log

These three kinds of log are also questions often asked in interviews, so let's discuss them together.

1. Binlog

Binlog should be the most heard about log in mysql every day.

So what is binlog?

Binlog is a binary log used to record database table structure and table data changes, such as insert, update, delete, create, truncate, and so on. Select and show operations are not recorded because there are no changes to the data itself.

What does an binlog file look like?

You can view it using the mysqlbinlog command.

The sql statement for each change is recorded, as well as the execution start time, end time, transaction id, and so on.

How to check whether binlog is open, and if not, how to set it?

Use the command show variables like'% log_bin%'; to see if binlog is open.

If binlog is not enabled as shown above, how can it be turned on?

Locate the my.cnf configuration file and add the following configuration (mysql version 5.7.31):

# Open binlog

Log-bin=mysql-bin

# Select ROW (line) mode

Binlog-format=ROW

After modification, restart mysql, and the configuration takes effect.

Execute SHOW MASTER STATUS; to view the currently written binlog file name.

What does binlog do?

First, it is used for master-slave replication. Generally, when you do the structure of one master and two slaves in the company, you need the master node to open the binlog log and subscribe to the information of the binlog log. Because the binlog log records the changes of database data, when the master data changes, the slave machine can also change with the data changes of the master node to achieve the effect of master-slave replication.

Second, it is used for data recovery. Because binlog records changes to the database, it can be used for data recovery. We see that there is a field called Position in the figure above, and this parameter is the pointer to record the binlog log. When we need to recover data, just specify-- start-position and-- stop-position, or specify-- start-datetime and-- stop-datetime, then we can recover the data in the specified range.

II. Redo log

Suppose you have a update statement:

UPDATE `user`SET `name` = 'Andy Lau' WHERE `id` ='1'

Let's imagine the steps for mysql to modify the data. It must be to look up the id='1' data first, and then change the name to 'Andy Lau'. Deeper, mysql uses pages as the storage structure, so MySQL loads the page on which the record is located into memory, and then modifies the record. But we all know that mysql supports persistence, and eventually the data exists on disk.

Suppose the data that needs to be modified is loaded into memory, and the modification is successful, but before it can be brushed to disk, and the database goes down, then the modified data will be lost.

To avoid this problem, MySQL introduces redo log.

As shown in the figure, when performing a data change operation, the data is first loaded into memory, then updated in memory, written to redo log buffer after the update is completed, and then written to redo log file by redo log buffer.

Redo log file records that the xxx page has been modified by xxx, so even if the mysql goes down, the data can be recovered through redo log, that is, after a successful update in memory, even if it is not refreshed to the disk, the data will not be lost due to downtime.

How do redo log and transaction mechanisms work together?

As shown in the figure:

Step 1-3 is to change the data and then write it to memory.

Step 4 record to redo log, and then set the record to prepare state.

Steps 5 and 6 commit the transaction, and after committing the transaction, step 7 changes the record state to commit (commit) state.

It ensures the consistency between the transaction and redo log.

Both binlog and redo log can recover data. What's the difference?

Redo log is to recover the data that has not been flushed to disk after the memory update.

Binlog stores all data changes, and theoretically, as long as the data is recorded on binlog, it can be recovered.

For example, if the data in the entire database is accidentally deleted, can you use redo log files to recover the data?

You cannot restore using redo log files, you can only restore using binlog files. Because the redo log file does not store all historical data changes, when the memory data is flushed to disk, the redo log data will be invalidated, that is, the contents of the redo log file will be overwritten.

When was binlog recorded?

Answer, when committing a transaction.

III. Undo log

The role of undo log is mainly used for rollback, and the atomicity of mysql database transactions is achieved through undo log. We all know that atomicity refers to a series of operations on a database that either succeed or fail.

Undo log mainly stores logical change logs of data. For example, if we want to insert a piece of data, undo log will generate a corresponding delete log. To put it simply, undo log records the data before it was modified, because rollbacks need to be supported.

Then when a rollback is needed, only the log of undo log can be used to recover the pre-modified data.

Another role of undo log is to implement multi-version control (MVCC). Undo records contain mirrors before records are changed. If the transaction that changed the data is not committed, for transactions with an isolation level greater than or equal to read commit, the changed data should not be returned, but the old version of data should be returned.

Summary

After learning, we know that all three kinds of logs play an important role in mysql. Let's review:

Binlog is mainly used for replication and data recovery. Redo log is used to recover data that has not been flushed to disk after the memory update. Undo log is used for rollback and multi-version control.

At this point, I believe you have a deeper understanding of "what are the three kinds of MySQL logs?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.