Prior to that, I had more than one year of working experience in the client area. Later, I also did back-end business in Byte for more than a year. Now I want to do some MySQL summaries to enrich my knowledge of the back-end domain. How to query MySQL? How is MySQL updated? How can MySQL prevent data loss? How does MySQL guarantee high availability?

primers

In the last article, we covered: how does MySQL query? So how does MySQL work if we execute an update statement?

Such as:

select * from <table_name> set <table_column> = "test" where id = 647;
Copy the code

First, in the previous article, we talked about the design architecture of MySQL. In terms of MySQL flow, it still will

Go through the Server layer first:

  1. Connector :(connect/verify permissions)
  2. Analyzer :(lexical/grammar analysis)
  3. Optimizer :(select index)
  4. Executor :(callengineLayer interface, perform update operation)

To the Engine layer: invoke the specific update interface.

As we know, disk I/O is a bottleneck. If every update is written directly to disk, MySQL can easily become overwhelmed.

So, in order to solve this problem. MySQL uses write-Ahead Logging (WAL), which writes logs first and then disks. When you’re busy, write in a journal first. Take your time and brush to disk together.

Therefore, you need to have two special log modules, which are the focus of today: binlog and redo log. In the command, binlog corresponds to logs of the Server layer. Redo log Log of the innoDB Engine at the Engine layer Since innoDB is the most commonly used storage engine for MySQL, it is also the default storage engine for MySQL. Other storage engines are different.)

Next, we will focus on these two logging systems.

binlog

Binlog is the Server layer general archive log of MySQL.

Binlog is available to all storage engines in the Engine layer.

  • Binlog is a logical log. The DDL and DML statements of the database are sequentially recorded.

  • The binlog file can be appended indefinitely. When the binlog file reaches a certain size, it automatically switches to the next file. It doesn’t overwrite the original binlog file.

Binlog functions:

1. Restore data

As long as we have a backup of the database at some point in time, and all binlogs since that point. Then, we can recover the data in the database. In our daily work, our DBA classmates can often help us restore database data to any second. They use a binlog.

2. Master/slave replication

In order to improve the efficiency of MySQL, we often do read/write separation. One master (write), multiple slave (read). At this point, the slave library can listen to the master library’s binlog and synchronize all changes to the write library.

redo log

Redo log is the MySQL Engine layer. InnoDB stores log files specific to the MySQL Engine. Also known as redo logging.

Q: Why is it called a redo log?

A: Because it guarantees MySQL’s crash-safe capabilities. Even if the MySQL database is abnormal, data is not lost after the MySQL database is restarted.

Redo logs are physical logs. You can think of it as a queue of a fixed size, which is duplicated in an infinite loop.

Whenever MySQL is idle, or the redo log file space is full. MySQL flushed some of the redo log header data to disk and cleared the redo log. Then, the update work continues and the updated data is written to the end of the queue.

Two-phase commit

MySQL commits binlog and redo log as one transaction in innoDB to ensure consistency. Binlog or redo log is never written first.

Finally, what is the difference between a binlog and a redo log?

  1. Binlog is a logical log implemented by MySQL Server layer and available to all engines.

The redo log is a physical log unique to the InnoDB engine.

  1. A binlog is a logical log that records everythingDDL,DMLThe original logic of the statement.

The redo log is a physical log that records what changes have been made to a data page.

  1. Binlog has unlimited space and can be appended to.

The redo log limits the size of the log and can only be repeated.

Conclusion: So, how does MySQL ensure data is not lost? Let’s look forward to the next article.

Reference and thanks: 1. “MySQL Practice 45” (Teacher Lin Xiaobin) 2. “Introduce you to the secret of MySQL Binlog”