Brother! You have been working for 3 years, even binlog redo log. . . Shouldn't

Some time ago, a reader asked me from the backstage: Migrant workers, I went for an interview a few days ago, just because my resume says Proficient MySQL , the interviewer kept following me and asked: what binlog, redo log, and Still ask as much as you want, making me look dumbfounded. . .

At that time, I also saw that this was a stunned expression, but he smiled and replied: Brother, you have been working for 3 years, and you don't even know the bin log and redo log. You shouldn't. . .

Therefore, today, the migrant worker will share the relevant knowledge about the problem encountered by the reader last time, hoping to have certain help or reference value for readers who are going to interview or studying this area in the future. If you think the article is useful to you, please don't be stingy with your reading and forwarding support, migrant workers, thank you all in advance.

First, let’s take a look at the one-time query/update statement flow chart

This article will focus on the interaction between executors and storage engines.

mysql does not write every data change to disk immediately, but temporarily stores the modified results in memory, and after a period of time, writes multiple modifications to disk at once to reduce disk io cost. At the same time increase the operating speed.

MySQL guarantees transactions through WAL (write-ahead logging) technology

In the same transaction, whenever the database performs a data modification operation, after the modification result is updated to the memory, a row of records will be added to the redo log to record "what data page needs to be modified", and the record status is set to Prepare, after the commit commits the transaction, the status of the record added in the redo log in this transaction will be set to the commit state, and then when the modification is placed, the modification of the record in the redo log with the status of commit will be written Disk. The process is as follows

Redo log recording method

The size of redolog is fixed. In mysql , you can modify the configuration parameters innodb\_log\_files\_in\_group and innodb\_log\_file\_size to configure the number of log files and the size of each log file. Redolog uses circular writing. Mode recording, when writing to the end, it will return to the beginning to write the log circularly. As shown below

write pos indicates the current record position of the log. When ib\_logfile\_4 is full, it will start recording from the beginning of ib\_logfile\_1; check point indicates that the modification of the log record is written to the disk, and the data is placed on the disk after the data is placed on the disk. Then checkpoint will erase the relevant records on the log, that is, the part between write pos->checkpoint is the empty part of redo log, which is used to record new records, and the part between checkpoint->write pos is redo log pending Disk data modification record. When writepos catches up with the checkpoint, it has to stop recording first, push the checkpoint forward first, and leave a place to record a new log. recommended to collect for future reference! MySQL common error code description

With redo log, when the database is down and restarted, the data that has not been placed on the disk can be recovered through redo log, that is, it is guaranteed that the transaction records that have been committed will not be lost.

With redo log, why do we need binlog?

• 1. The size of the redo log is fixed. After the records on the log are modified and placed on the disk, the log will be overwritten and cannot be used for data rollback/data recovery and other operations.
• 2. Redo log is implemented by the innodb engine layer, not all engines have it.

Based on the above, binlog is essential

• 1. Binlog is implemented at the server layer, which means that all engines can use binlog logs
• 2. Binlog is written by appending, and the size of each binlog file can be set by the configuration parameter max\_binlog\_size. When the file size is greater than the given value, the log will roll, and the subsequent logs will be recorded to a new file. on.
• 3. Binlog has two recording modes. The statement format is to record the sql statement, and the row format will record the content of the row. There are two records, both before and after the update.

The binlog and redo log must be consistent. Binlog has records but redolog does not, and vice versa. I said before that in a transaction, redolog has two states: prepare and commit. Therefore, recording the binlog when the redolog state is prepare can ensure that the records of the two logs are consistent. The following figure lists various situations to illustrate.

Now let’s take a look at the entire flow chart

Related parameter setting suggestions

• 1. innodb_flush_log_at_trx_commit: set to 1, which means that the redolog of each transaction is directly persisted to disk (note that this refers to the redolog log itself) to ensure that data is not lost after mysql restarts.
• 2. Sync_binlog: set to 1, which means that the binlog of each transaction is directly persisted to disk (note that this refers to the binlog log itself), to ensure that the binlog record is complete after mysql

Author: Will play code
Link: https://www.jianshu.com/p/4bcfffb27ed5