MySQL log

Time:2020-8-14

First of all, remember a MySQL principle: log first.
The following is a summary:

  1. Server layer, binlog + relaylog is used for master-slave replication. But there will be a case unsave. Binlog is a logical log.
  2. Storage engine layer – InnoDB, redolog + undo log, to solve the problem of buffer pool data persistence, to achieve crash safe. However, redolog does not have the same archiving effect as binlog. Redolog is the physical log.
  3. The two-phase commit of redolog ensures the data consistency between redolog and binlog.

MySQL has two layers, one is the server layer, the other is the storage engine layer.
1. Binary log and relay log. Its server layer log.
2. Redo log and undo log. It is a unique log of InnoDB engine


One master and one slave copy of binlog. The basic process is as follows:

Three threads participate in the whole process of master-slave replication between master and slave. There are two threads (SQL thread and IO thread) on the slave side and the other thread (IO thread) on the master side.

  • a. The master records the data changes in the binary log
  • b. The IO thread above slave connects to the master and requests the log content after the specified location of the specified log file (or from the beginning log)
    (the log file name and log file storage location are specified when executing the change master command when configuring the master-slave replication task)
  • c. After the master receives the request from the IO thread of the slave, the IO thread on the master server reads the binlog log log information after the specified location of the specified binlog file according to the information requested by the IO thread of the slave server, and then returns it to the IO thread on the slave side. In addition to the binlog log content, the returned information includes the new binlog file name on the master server side after the log content is returned and the next specified update location in the binlog, that is, where to start the next copy.
  • d. When the IO thread of the slave server obtains the log contents, log files and location points sent by IO threads on the master server, the binlog log contents are written to the slave’s relay log file (i.e., the relay log) file (MySQL relay) in turn- bin.xxxxxx )And record the new binlog file name and location to the master info file, So that the next time you read the new binlog log log on the master side, you can tell the master server which file and location of the new binlog log log need to start requesting the new binlog log content.
  • e. The SQL thread on the slave server will detect the newly added log content in the local relay log in real time, and then parse the contents of the relay log file into the contents of the SQL statements executed in the master side in time, execute and apply these SQL statements in the sequence of the statements on the slave server, and save the execution status information in the relay- log.info File, clean up the applied log after application.
  • f. After the above process, you can ensure that the same SQL statements are executed on the master side and the slave side. When the replication status is normal, the data on the master side and the slave side are exactly the same.

    MySQL log

See the article: https://segmentfault.com/a/1190000019373762


Crash safe concept
The reason of slave crash unsafety lies in the application of binlog and the non atomicity of update files.
That is, there will be problems with step e above.

Start explaining:
The execution status information of IO thread is saved in master.info File, and the execution status information of SQL thread is saved in relay- log.info Documents.

Here’s a scenario:
SQL thread has applied four transactions of relay-log.01

trx1(pos:10)
trx2(pos:20)
trx3(pos:30)
trx4(pos:40)

However, SQL thread updates the site (relay-log. 01,30) to relay- log.info In the file, suddenly the system crashed. When the slave instance is restarted, SQL thread will repeat the transaction trx4. This is the typical crash unsafe.

The principle of crash safe is as follows

SQL under crash safe_ Working mode of thread
  SQL thread performs transactions and updates mysql.slave_ replay_ log_ The statements of info are merged into the same transaction, and the atomicity of the transaction is guaranteed by MySQL system.

MySQL log
The green ones represent the actual business transactions, and the blue ones are the update slave to be executed by mysql_ replay_ log_ Then merge the two SQL into one transaction to ensure atomicity by using MySQL transaction mechanism and InnoDB table. There will be no inconsistency between applying binlog and updating site information.


Buffer pool concept–A cache pool with InnoDB storage engine

The most basic requirement of the user for the database is to be able to read and store data efficiently. However, reading and writing data involves interaction with low-speed devices. In order to make up for the speed difference between the two, all databases have a cache pool to manage the corresponding data pages and improve the efficiency of the database In order to introduce this middle layer, the memory management of database becomes relatively complex.
 ---Buffer pool is a cache pool provided by InnoDB storage engine. When querying data, it will first query from memory. If it exists in memory, it will return directly, thus improving query response time. (function)
 ---Modification and update operations will change some data of buffer pool, and the hit rate of buffer pool will be maintained at a relatively high level through LRU algorithm update. (memory management)
 ---How to synchronize the data in the buffer pool to the disk. If you update the buffer pool and write to the disk once, the efficiency and direct read-write disk do not improve much. Here we need to design a synchronization strategy to solve this problem. ( redo.log , undo.log )
 ---The introduction of buffer pool will cause the updated data will not be persisted to the hard disk in real time. When the system crashes, although the data in the buffer pool is lost, the data is not persisted. However, the system can restore all data to the latest state according to the content of redo log.
 ---After modifying the data of buffer pool, the operation should be recorded in the transaction log to ensure the security of the transaction. The transaction log file is a file with a fixed size applied by InnoDB engine for continuous physical space. The reading and writing of log file is basically sequential, with few addressing operations. ( redo.log Sequential read and write, reduce addressing operation)
 ---MyISAM engine has key cache: only index files are cached, and data files are cached by the operating system itself. The buffer pool stores all kinds of data cache, including index page and data page.

See the article: https://www.cnblogs.com/iamsu…


redo log

Introduction: because there is no InnoDB engine in MySQL at the beginning. The engine of MySQL is MyISAM, but MyISAM does not have the ability to crash safe, and binlog logs can only be used for archiving.
InnoDB is introduced into MySQL in the form of plug-ins by another company. Since there is no crash safe capability only relying on binlog, InnoDB uses another set of log system, that is, redo log, to achieve crash safe capability.

Redo log has a fixed size. For example, it can be configured as a group of four files, and the size of each file is 1GB. Then, a total of 4GB operations can be recorded. Write from the beginning, write at the end and go back to the beginning. Therefore, redo log does not have the same archiving function as binlog.

The redo log writing is divided into two steps: prepare and commit, which is called “two-phase commit”. The two-phase commit mainly solves the problem of data consistency between binlog and InnoDB redo log
As shown in the figure below:
MySQL log


undo log
Undo log is to realize the atomicity of transactions. In the InnoDB storage engine of MySQL database, undo log is also used to implement multi version concurrency control (mvcc).

  • Simplified process of undo + redo transaction
    Suppose there are two data, a and B, with values of 1 and 2
    A. Business begins
    B. Record a = 1 to undo log
    C. Modify a = 3
    D. Record a = 3 to redo log
    E. Record B = 2 to undo log
    F. Modify B = 4
    G. Record B = 4 to redo log
    H. Write redo log to disk.
    1. Transaction commit

See the article: http://www.zhdba.com/mysqlops/2012/04/06/innodb-log1/