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

This article is about the differences between redo log and binlog in mysql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

I'd like to talk to you about two small knowledge points in mysql: redo log and binlog.

Redo log: InnoDB stores logs on the engine layer, so if you're not using a storage engine called InnoDB, it's not redo log at all.

Binlog: the log recorded by the MySQL Server layer, so no matter what storage engine is used, as long as it is MySQL, there will be binlog. When you do MySQL master-slave replication, you use binlog.

Next, let's take a closer look at what they have done.

Redo log

Why do you have this redo log log file?

Here, we can give an example. Now we want to modify the data in the database. Now a update statement comes over. Generally, update operations are accompanied by query operations. You have to find this data first, and then update it, right?

If the amount of data is relatively small, it will be found and updated quickly, but what if the amount of data is 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?

At this point, redo log plays a role. 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 update of the data is successful.

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.

Such an idea or technology, there is a proper term: WAL technology, that is, WriteAheadLogging, the core is to write the log first, and then write the disk.

Redo log can't keep writing, can it?

The size of the redo log is fixed, and the previous content will be overwritten, and once it is full, it will trigger the synchronization of the redo log to the disk to make room for subsequent changes.

If the database is down or restarted, the data will not be lost.

Because of redo log, all the previously submitted records are still there, so you only need to recover them according to the records in redo log.

Binlog

Binlog is the logging of the MySQL Server layer.

The difference between redo log and binlog:

Redo log is specific to the InnoDB engine; binlog is implemented at 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 on the line id = 2".

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

Binlog records all DDL and DML statements in the form of events (because it records operations rather than data values, belonging to logical logs) and can be used for master-slave replication and data recovery.

When the binlog function is enabled, we can export binlog to SQL statements and replay all the operations to achieve data recovery.

With these two logs, let's take a look at how an update statement is executed (redo cannot be written at once):

For example, a sentence: update user set name=' pony 'where id=1

This data is queried first, and if there is a cache, it will also be used.

Change the name to a pony, then call the engine's API interface, write this line of data to memory, and record the redo log. At this point, the redo log enters the prepare state and tells the executor that the execution is complete and can be submitted at any time.

After receiving the notification, the executor records the binlog, and then calls the storage engine interface to set the redo log to the commit state.

Update complete.

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 represent 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 thing 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.

Thank you for reading! This is the end of the article on "what is the difference between redo log and binlog in mysql". 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, you can 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.