Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

binlog

The Mysql sever layer maintains a binary log. The log is used to record the SQL statements that update Mysql data. The log is stored as a “transaction” on disk.

The main functions are:

  • Replication: MySQL Replication enables binlog on the Master side and the Master passes its binary log to Slaves and plays back to achieve master-slave data consistency
  • Data recovery: Use the mysqlbinlog tool to restore data
  • Incremental backup

copy

  • A.aster logs data changes to the binary log
  • The IO process on b. slave connects to the Master and requests the log content from the specified location in the specified log file (or from the original log)
  • C. Master After receiving the request from the Slave I/O process, the I/O process in charge of replication reads the specified log information based on the request information and returns the log information to the Slave I/O process. In addition to the log information, the returned information includes the name of the bin-log file that has been sent to the Master and the location of the bin-log file
  • After receiving the log information, the IO process of d.Slave adds the received log content to the end of the relay log file on the Slave end, and records the file name and location of the bin-log on the Master end to the master-info file. So that the next read can clearly tell the Master from a certain bin-log location to the next log content
  • When the Sql process of E. slave detects the new content added to the relay log, it immediately parses the content of the relay log into the executable content that is actually executed on the Master side and executes it itself

restore

mysqlbinlog   --start-position=1847 --stop-position=2585 mysql-bin.000008 > test.sql
mysql> source /var/lib/mysql/3306/test.sql

Copy the code

Trading set

Sync_binlog: indicates the frequency at which the binary log of MySQL is synchronized to the disk. The recommended value is 0 to N

  1. Sync_binlog =0: After a transaction is committed, MySQL does not flush the binlog_cache to disk with a disk synchronization command such as fsync. Filesystem decides when to synchronize the binlog_cache to disk or when the cache is full. This is the best performance.
  2. Sync_binlog =1: After each transaction commit, MySQL will perform a disk synchronization command such as fsync to force data from binlog_cache to disk.
  3. Sync_binlog =N: After every N transaction commits, MySQL will perform a disk synchronization command such as fsync to force data from binlog_cache to disk.

MySQL Binlog – Tencent Cloud technology community article – Zhihu

redolog

Redolog is innoDB-specific physical log. When a record needs to be updated, the InnoDB engine writes the record to the redo log and updates the memory. Meanwhile, the InnoDB engine updates the operation record to disk when appropriate, usually when the system is idle. This is essentially WAL in MySQL (Write Ahead logging).

Crash-Safe

A fixed-size, “write loop” log file that records the physical log — “a change was made on a data page.”

What’s a binlog?

An unlimited size, “appending” log file that records logical logs — “add 1 to the C field on the ID=2 line”.

One of the major differences between redo logs and binlogs is that redo logs are circular and redo logs are appending. That is, the redo log records only unflushed logs, and all flushed data is deleted from the redo log file. Binlog is the append log, which stores the full log.

If you want to restore redo log and binlog data to the memory after a database crash, the binlog cannot be restored. Although Binlog has a full log, there is no flag for innoDB to determine which data has been flushed and which data has not. ———————————————— Copyright notice: This article is originally published BY CSDN blogger “Zzz -_CH”, in accordance with CC 4.0 BY-SA copyright agreement. Please attach the original source link and this statement. Original link: blog.csdn.net/weixin_4369…

Trading set

Innodb_flush_log_at_trx_commit: flushes the ib_logfile unique to InnoDB. Ib_logfile records redo logs and undo logs. The value can be 0, 1, or 2

innodb_flush_log_at_trx_commit=0: Innodb’s Log Thread writes data from the Log buffer to a file every second. It also informs the file system to flush to ensure that data has actually been written to the physical file on disk. However, the end of each transaction (commit or ROLLBACK) does not trigger the Log Thread to write data from the Log buffer to a file. Therefore, when set to 0, the most extreme case is the loss of 1 second of data changes after MySQL Crash, OS Crash or host power failure.

innodb_flush_log_at_trx_commit=1: Innodb’s default setting is to flush the log buffer to the file system (OS buffer) every time a transaction commits, and flush the buffer to disk by calling the file system’s “flush” operation. If the underlying hardware provides poor IOPS, then the concurrency of the MySQL database will soon fail to improve due to hardware IO problems.

Innodb_flush_log_at_trx_commit =2: indicates that the log buffer is flushed to the file system at each transaction commit, but not immediately to disk. If only the MySQL database is down, the corresponding transaction data is not lost because the file system is fine. Only in the case of a host operating system failure or sudden power failure can the transaction data of the database be lost for a second or so. In this way, the probability of transaction data loss is reduced, and the IO requirements on the underlying hardware are not so high (the log buffer is written to the file system, usually only in the memory cache of the file system from the memory of the log buffer, no pressure on the underlying IO).

undolog

At the RU isolation level, you simply read the latest record of the version. For SERIALIZABLE isolation level, data is accessed through locking mutex.

Read commits and repeatable reads at the isolation level are implemented using MVCC

Read commits and repeatable reads rely on MVCC implementations.

MVCC relies on undo log and ReadView implementations. Where undo log provides the version chain of the data row, ReadView determines the version that the transaction can see.

Each row of data has two hidden columns, DATA_TRX_ID (transaction ID, 6 bytes), which record the transaction ID of the most recently updated record; And DATA_ROLL_PTR (rollback pointer, 7 bytes), pointing to the previous version of the row, thus forming the version chain of the row. Every time data is updated, the system copies the current data and saves it to the Undo log. And modify the rollback pointer for the current row to point to the old data row in the undo log.

ReadView is the list of currently active transaction ids, called m_IDS, where the minimum value is up_limit_id and the maximum value is low_limit_id

/* The last active transaction has the smallest id: */
view->up_limit_id = view->trx_ids[view->n_trx_ids - 1];

view->low_limit_id = trx_sys->max_trx_id;

/*
Additional trx ids which the read should not see: typically, these are the read-write active transactions at the time when the read is serialized, except the reading transaction itself; the trx ids in this array are in a descending order. These trx_ids should be between the "low" and "high" water marks, that is, up_limit_id and low_limit_id.
*/
trx_ids

Copy the code

Judge visibility according to the following rules:

  1. DB_TRX_ID < up_limit_id. Transactions that modify this record are committed before the current transaction starts and are visible to the current transaction.
  2. DB_TRX_ID >= low_limit_id indicates that the transaction whose value is changed is not visible to the current transaction after the current transaction.
  3. Up_limit_id <= DB_TRX_ID < low_limit_id, if DB_TRX_ID is in trx_IDS and does not equal the current transaction ID, it is not visible for the current transaction. Otherwise, it is visible.

At the read commit isolation level, a new ReadView is generated on each query; At the repeatable read isolation level, a ReadView is only created on the first query of a transaction

High performance MySQL- How to implement repeatable read?

Mysql MVCC principle low_limit_id ReadView

MySQL MVCC mechanism

MySQL InnoDB MVCC implementation

Q&A

Redolog and binlog

  1. Redo logs are unique to InnoDB; Binlog is implemented in the Server layer of MySQL and is available to all engines.
  2. A redo log is a physical log of what was changed on a data page. Binlog is a logical log

Is the original logic of the statement, such as “add 1 to the C field on the line ID=2”. 3. Binlog indicates full logs. Redo logs are written in a redo log cycle. Binlog can be appended. “Appending” means that the binlog file will be switched to the next one after being written to a certain size without overwriting the previous log.

Two-phase commit:

Redo logs and binlogs are committed in two phases to ensure logical consistency between the two logs.

  • If redolog is at COMMIT, the transaction commits directly,
  • If redolog is prepared, binlog is complete, and the transaction is committed,
  • When the binlog is incomplete, the transaction is rolled back and the previously updated data page is lost.

How do redo logs relate to binlogs?

XID: When the service is restarted, it scans the XID field in Redolog to the binlog to find the corresponding transaction and determine whether the binlog status needs to be submitted

Redo log and redo buffer

When a transaction is in cmMIT, the redo buffer is written first because it saves the data in the redo log and writes the data in the redo log at commit time because it is faster. Write data in memory to redo log