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

This article introduces the relevant knowledge of "what is Redo log and Binlog". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Redo log

Why is there a redo log?

We can think of it this way. If there is no redo log, it is easy to query how MySQL works. After all, it is only a query of the records, and no changes have been made to the data.

What about add and update operations? Now that a update statement comes over, is there bound to be a qualification? for example, if you want to update a record to update the bank card balance of A to 1k, should there be a qualification for this statement, similar to where userName ='A'? that is to say, the general update operation is accompanied by the query operation, so you have to find this person first, and then update the operation, right?

If the amount of data is small, it will be found and updated soon.

But what if the amount of data is relatively large and there are 100 million pieces of data in it? And the update operation must be written to disk, what about the IO cost in the middle? What if I have dozens of update statements updated one after another? If you think of it this way, you can think that the cost of these operations will be very high, so can you reduce these costs?

That's what redo log is for.

That is, when a record is updated, the InnoDB engine will first write the record to redo log and update the memory at the same time, so that the data can be updated successfully.

But at this point, it's not updated to disk, is it? Don't worry, InnoDB will update this and record to disk at the right time.

There is a proper term for such an idea or technology: WAL technology, that is, WriteAheadLogging, whose core is to write a log first and then write a disk.

Again, there's a problem.

Redo log can't keep writing, can it? If the update operation is written to redo log all the time, and the size is not limited, the storage space on the server may be occupied by redo log.

So the redo log of InnoDB is of fixed size. For example, if we configure a set of 4 files, each with a file size of 1GB, then its operation may be like this:

As you can see, it is mainly write pos and checkpoint. Write pos is easier to understand. It is the location of the current record. If there is any operation that needs to be recorded, move back from the current position. After writing the ib_logfile_3, go back to the beginning of the ib_logfile_0 file and continue to write.

Checkpoint is the current position to be erased, that is, the InnoDB engine will not persist these operations and update them to disk at the right time, so can the persisted data be erased?

The part between write pos and checkpoint is the part that can be used to record the operation, so what if write pos and checkpoint meet? Does it mean that the allocated redo log size has been used up at this time, and the update operation can no longer be carried out at this time? you must stop and deal with it and push the checkpoint forward.

It is because of redo log that InnoDB can guarantee that even if an abnormal restart occurs in the database, it doesn't matter. All the records submitted before are still there, and you only need to recover them according to the records in redo log.

So if you are familiar with DBA, you can ask whether our MySQL can return to any second in half a month. If the answer is yes, don't doubt that he is really not bragging.

Binlog

Binlog is the logging of the MySQL Server layer. Let's take this as an example.

Before we say this, we need to understand the difference between redo log and binlog:

Redo log is unique to the InnoDB engine, binlog is implemented by the Server layer of MySQL, and all engines are available.

Redo log is a physical log that records "XXX changes made on the XXX page"; binlog is a logical log, such as "add 1 to the c field of the line id = 2"

Redo log has a fixed size, so its space will be used up. If it is used up, some operations must be done to write to disk before it can continue. Binlog can be appended to write, that is, binlog has no concept of space, so just keep writing.

After understanding the difference between them, let's take an update operation as an example

Now I'm going to add 1 to the c field of the line id = 2, and at the MySQL level, how does it do that?

First of all, the data with id = 2 will be found, and then the c field will be found to add 1. At this time, the engine will update the line of data into memory and record the update operation in redo log. At this time, the redo log is in the prepare state, and then the executor generates the binlog of the operation, and after writing the binlog to disk, the executor calls the engine's commit transaction interface. The engine changes the redo log that has just been written from prepare state to commit state, so that the update operation is complete.

Two-phase submission

In the above description, you can find that redo log actually has the prepare status first, and then the commit status after binlog has finished writing. This way is called "two-phase commit".

Why is there such a way?

Both redo log and binlog can be used to indicate the commit status of a transaction, and two-phase commit is to keep the two states logically consistent.

Suppose, instead of doing this, what would happen if you wrote redo log first and then binlog? If an exception occurs when writing binlog, the update operation has already been in the redo log, but the binlog has not been updated at this time, is there a data inconsistency?

It's the same reason to write binlog before redo log.

So, when writing, first let redo log in the prepare state, wait for binlog to finish writing, and then let redo log in the commit state, so that the logic is consistent

This is the end of "what is Redo log and Binlog". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.