Star: github.com/yehongzhi/l…

preface

Have you ever wondered why MySQL database can implement master-slave replication, implement persistence, implement rollback? There are three types of logs in MySQL:

  • binlog
  • redo log
  • undo log

These three kinds of logs are often asked in interviews, so let’s explore them.

A, binlog

Binlog is probably one of the most common things you hear about in mysql.

So what is a binlog?

A binlog is a binary log that records table structure and data changes in a database. For example, insert, UPDATE, DELETE, CREATE, truncate, and other operations are not recorded. The select and show operations are not recorded because the data itself is not changed.

What does a binlog file look like?

You can view it using the mysqlbinlog command.

The SQL statement for each change is recorded, along with the execution start time, end time, transaction ID, and so on.

How to check whether binlog is enabled? If not, how to set it?

Run the show variables like ‘%log_bin%’ command; Check whether binlog is enabled.

If binlog is not enabled, as shown above, how?

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

Binlog log-bin=mysql-binCopy the code

After the modification, restart mysql for the configuration to take effect.

Run SHOW MASTER STATUS. You can view the binlog file name currently written.

So what’s the binlog for?

First, for master-slave replication. Generally, in a company with one master and two slave, the master node needs to open the binlog log and the slave machine needs to subscribe to the information of the binlog log. Because the binlog log records the changes of the database data, when the data of the master node changes, the slave machine can also change with the data of the master node. 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 in the figure above there is a field called Position, which is a pointer to the binlog. To restore data, specify –start-position and –stop-position, or –start-datetime and –stop-datetime.

Second, the redo log

Suppose we have an UPDATE statement:

UPDATE `user` SET `name`='Andy Lau' WHERE `id`='1';
Copy the code

Id =’1′; id=’1′; id=’1′; Further down the line, mysql uses pages as a storage structure, so mysql first loads the page of the record into memory and then modifies the record. But we all know that mysql supports persistence, and ultimately data is stored on disk.

Suppose that the data to be modified is loaded into memory, and the modification succeeds, but before it is flushed to disk, the database breaks down, and the data after the modification is successful is lost.

To avoid this problem, MySQL introduces the redo log.

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

The redo log file records that XXX changes have been made to the XXX page, so that data can be restored through the redo log in the event of a mysql outage. This means that data is not lost due to downtime, even if the mysql database is not flushed to disk.

How do redo logs and transactions work together?

As shown in the figure:

Steps 1-3 are to write the changes to memory.

Step 4: Log to the redo log and set the log to the prepare state.

Step 5 and step 6 commit the transaction. After committing the transaction, step 7 changes the record state to COMMIT.

Transaction and redo log consistency is guaranteed.

Both binlog and redo log can restore data. What’s the difference?

Redo log restores data that has not been flushed to disk after a memory update.

A binlog is a record of all data changes. In theory, as long as the data recorded in the binlog, it can be restored.

For example, if the entire database was accidentally deleted, can I use the redo log file to restore the data?

You cannot use the redo log file for recovery. You can only use the binlog file for recovery. Because the redo log file does not store all historical data changes, the redo log becomes invalid when the memory data is flushed to disk, and the redo log file is overwritten.

When was the binlog recorded?

Answer, when committing a transaction.

Third, undo the log

Undo log is mainly used for rollback, and the atomicity of mysql database transactions is realized through Undo log. We all know that atomicity is a series of operations on a database that either all succeed or all fail.

Undo log mainly stores logical changes of data. For example, if we want to insert a data, undo log will generate a corresponding DELETE log. To put it simply, undo log records the data before it was modified, because rollback is required.

When rollback is required, only undo log logs can be used to restore the data before modification.

Undo log is also used for multi-version control (MVCC). The undo record contains a mirror of the data before the change. If the transaction that changed the data is not committed, the old version of the data should not be returned for transactions with an isolation level greater than or equal to Read COMMIT.

conclusion

Mysql > mysql > all three types of logs play an important role in mysql.

  • Binlog is used for replication and data recovery.
  • The redo log is used to restore data that has not been flushed to disk after a memory update.
  • Undo log is used to implement rollback and multi-version control.

That’s all for this article, thank you for reading, I hope you can learn something after reading!

The article continues to update, wechat search “Java technology enthusiasts”, the first time after paying attention to the technical article push, article classification is collected on github: github.com/yehongzhi, you can always find what you are interested in

Please give me a thumbs-up if you think it is useful. Your thumbs-up is the biggest motivation for my creation

I’m a programmer who tries to be remembered. See you next time!!

Ability is limited, if there is any mistake or improper place, please criticize and correct, study together!