preface

Star: github.com/ZhongFuChen…

A while ago, my brother asked me, “Do you know how MySQL is guaranteed to be atomic?” I’m confused. How does MySQL guarantee atomicity? I can’t.

Everyone knows what atomicity means in a transaction: “A transaction contains multiple operations that are either all or none executed.”

So my brother said to me, “It’s undo log.”

Me: “Oh my God, another intellectual blind spot.”

Later, I checked on the Internet. There are several common logs in MySQL, respectively:

  • undo log
  • binlog
  • redo log

If you haven’t followed these logs, please leave a comment in the comments section to make me feel like I’m not the only one.

Later, I searched again. In fact, these kinds of logs are often asked in interviews. This article is told in the simplest way, hoping to be helpful to you.

What is a binlog

Binlog is often heard in daily development, because many times data updates rely on binlog.

Take a very simple example: our data is stored in the database, now we change the content of a certain field of a product (database change), and the data retrieved by users is through the search engine. In order for users to find the latest data, we need to change the engine data as well.

In a word: the database changes, the search engine data also need to change.

So, we’re going to listen for changes in the binlog, and if the binlog changes, we’re going to write the changes to the corresponding data source.

What is a binlog?

Binlog recorded the database table structure and table data changes, such as update/delete/insert/truncate/create. It does not record the SELECT (because there is no change to the table)

What does a binlog look like?

A binlog is simply an SQL statement that stores each change (not only the SQL, but also the XID “transaction Id”, etc.).

What is a binlog used for

There are two main functions: replication and recovery of data

  • MySQL is often used in companiesFrom more than a masterStructurally, the slave server needs to be consistent with the master server’s data, which is passedbinlogTo implement the
  • The database’s been drained. We can get throughbinlogTo recover the data.

Because binlog records changes to database tables, we can use binlog for replication (master-slave replication) and recovery of data.

What is redo log

Suppose we have an SQL statement:

update user_table set name='java3y' where id = '3'
Copy the code

Select * from ‘id’ where id=3; select * from ‘id’ where id=3; Is that okay?

In fact, the basic storage structure of Mysql is a page (records are stored in pages), so Mysql first finds the page where the record is located, and then loads the page into memory to modify the corresponding record.

Now there may be a problem: what if the database dies after changing the data in memory before it can be dropped to disk? Obviously this change is lost.

If every request required data to be dropped off disk immediately, it would be slow and MySQL might not be able to handle it. So what does MySQL do?

MySQL introduces a redo log, and when memory runs out, it writes a redo log, and that redo log records what changes were made on a particular page.

When you write a redo log, there are buffers. Buffers are written first and then stored in disk. As for when to drop disk from buffer, there will be configuration for us to configure.

Writing redo logs also requires disk writing, but it has the advantage of sequential IO (we all know that sequential IO is much faster than random IO).

So, the redo log exists for when a redo log has been written to memory, but data has not been written to disk. When our database is down, we can restore the data according to the redo log. Because redo logs are sequential I/OS, they are fast to write. Redo logs record physical changes (XXX was changed on the XXXX page). They are small and fast to recover.

Binlog and redo log

If you look at this, you might be thinking: Binlog and redo log are very similar. They are both used for recovery.

In fact, they are similar except for the “recovery” part, and there are many differences, so let me list them.

Stored content

Binlog records SQL statements such as update/delete/ INSERT, and redo log records physical changes (XXX was changed on XXXX).

The redo log records physical changes to the data. The binlog records logical changes to the data

function

Redo logs are designed for persistence. If the database hangs, redo logs can be used to restore data that was not flush to disk. Redo logs can be used to restore data that was not flush to disk before the database hangs.

The binlog function is to copy and restore.

  • The primary and secondary servers need to maintain data consistencybinlogTo synchronize data.
  • If the entire database is deleted,binlogStores all data changes, so you can passbinlogTo recover the data.

Again, you’re thinking, “If the entire database was deleted, can I use the redo log to restore it? “You can’t

Because the redo log stores physical changes, the redo log is invalid if the data in memory has been flushed to disk. Therefore, the redo log does not store all historical data changes, and the contents of the file are overwritten.

Binlog and redo log write details

Redo logs are generated by MySQL’s InnoDB engine.

Binlog comes with whatever engine MySQL uses.

InnoDB has transactions, and one of the four main features of transactions is persistence using redo logs (if a write to memory is successful, but the data has not been flushed to disk, if the database hangs, we can use redo logs to restore the data in memory, which implements persistence).

As mentioned above, when modifying data, the binlog records the contents of the changed classes, and the redo log records the contents of the changed classes. (except that one stores physical changes and one stores logical changes). So what’s the order in which they write?

The redo log records each change at the beginning of a transaction, while the binlog records each change at the time of a transaction commit.

So a new problem arises: what happens if I fail to write one of these logs? Redo log; binlog;

  • If you writeredo logIf it fails, we consider this transaction to be a problem, roll back, and don’t write againbinlog.
  • If you writeredo logThere you go. WritebinlogWrite,binlogWhat if you fail halfway through? We’re still gonna take this one seriouslyTransaction rollback, will be invalidbinlogTo delete (becausebinlogWill affect the data from the slave library, so need to delete)
  • If you writeredo logandbinlogIf it’s a success, then it’s a real success.

In a nutshell: MySQL needs to ensure that the redo log and binlog data are consistent. If they are inconsistent, it is a mess.

  • ifredo logWriting failed whilebinlogIt worked. So let’s say the machine dies before the data in memory falls off the disk. Then the data from the master and slave servers is inconsistent. (Through the serverbinlogGet the latest data while the master server is dueredo logNo record, can’t recover data)
  • ifredo logIt worked, andbinlogWrite failed. Then we can’t get the latest data from the server.

MySQL uses a two-phase commit to ensure that redo log and binlog data are consistent.

Process:

  • Stage 1: InnoDBredo log writes to a disk. InnoDB transaction enters the prepare state

  • Phase 2: Binlog writes disks and InooDB transactions enter the commit state

  • At the end of each transaction binlog, an XID event is recorded to indicate whether or not the transaction has committed successfully, which means that the contents of the binlog after the last XID event should be purge during recovery.

What is undo log

What is the use of undo log?

Undo log has two main functions: rollback and multi-version control (MVCC).

During data modification, not only the redo log is recorded, but also the Undo log is recorded. If a transaction fails or is rolled back for some reason, you can use the Undo log to roll back

Undo log mainly stores logical logs. For example, if we want to insert data, undo log will record a corresponding DELETE log. When we update a record, it records a corresponding reverse update record.

This should also be easy to understand, after all, rollback, as opposed to the need to modify the operation, so that the purpose of the rollback can be achieved. Because rollback operations are supported, we can guarantee that “a transaction contains multiple operations, all of which are executed or none of which are executed”. [Atomicity]

Since undo log stores the data before modification, it is equivalent to a previous version. MVCC implements read and write without blocking, and only needs to return the data of the previous version.

The last

This article covers the kernel of binlog /redo log/undo log, and some details can be added (for example, there are several modes of binlog, and the brush disk strategy mentioned in the article, etc.).

If you think you got it, please give me a thumbs up.

References:

  • www.jianshu.com/p/4bcfffb27…
  • Yq.aliyun.com/articles/61…
  • MySQL ACID principle!
  • How does MySQL implement D in ACID?
  • www.cnblogs.com/myseries/p/…

If you want to follow my updated articles and shared dry goods in real time, you can follow my official account “Java3y”.

  • 🔥Java beautiful brain map
  • 🔥Java Learning path
  • 🔥 Develop common tools

Reply “888” under the public number to obtain!!

This has been included in my GitHub featured articles, welcome to Star: github.com/ZhongFuChen…

Ask for praise ask for attention ️ ask for share 👥 ask for a message 💬 for me really very useful!!