Writing in the front

Over the weekend, I had a conversation about MySQL with ali P9 senior technical expert (who will not be named here). Why did we talk about MySQL? Because he saw that I published a “MySQL Technology: Development, Optimization and Operational Combat”, the book is also good evaluation. We then talked about MySQL on several topics, one of which was MySQL logging. Today, I would like to share with you some of the content of the general conversation in the form of written articles. Hope to be able to bring substantial help for small partners!

The article has been included:

Github.com/sunshinelyz…

Gitee.com/binghe001/t…

MySQL logs

There are three types of logs that are important to MySQL: Binlog, Undo Log, and Redo Log.

Since Binlog is similar to UndoLog, we introduce the three principles of MySQL logging in the following order: UndoLog — > Redo Log — > Binlog.

The Undo Log Log

What is Undo Log

As the name implies, Undo Log literally means Log of Undo operations, referring to the return of MySQL data to a certain state.

In the MySQL database, before the transaction starts, MySQL saves the records to be modified in the Undo Log. If the database crashes or the transaction needs to be rolled back, MySQL can use the Undo Log Log to roll back the data in the database to the previous state.

When MySQL adds, modifies, and deletes data, it writes the information to the Undo Log before the transaction begins. When a transaction commits, the Undo Log is not deleted immediately. InnoDB storage engine puts the Undo Log in the list to be deleted and then deletes the deleted list through the Purge thread in the background. It’s worth noting that Undo Log is a logical Log that records a change process. For example, when MySQL performs a DELETE operation, Undo Log records an INSERT operation. MySQL performs an INSERT operation and Undo Log records a delete operation. When MySQL performs an update, Undo Log records a reverse update.

Undo Log manages and records Log information in segments. The data files of InnoDB storage engine contain rollback segments, which contain 1024 Undo Log senment.

The Undo Log function

Undo Log plays an important role in MySQL’s implementation of transactions. It implements atomicity and multi-version concurrency control, also known as MVCC.

  • Implement atomicity of transactions

Undo Log can realize the atomicity of MySQL transactions. In the process of a transaction, if MySQL has an error or users manually perform a rollback operation (rollback operation), MySQL can use the Undo Log Log to restore data in the database to the previous state.

  • Implement MVCC mechanism

Undo Log implements multi-version concurrency control (MVCC) in MySQL’s InnoDB storage engine. Before a transaction is committed, Undo Log stores the data of the previous version. The data in Undo Log can be used as a copy or snapshot of the data of the previous version for other concurrent transactions to read.

After transaction A manually starts the transaction, it updates the goods data table with ID 1. The data hit by the update is first written to the Undo Buffer. Before transaction A is committed, transaction B manually starts the transaction to query the goods data table with ID 1. At this time, transaction B will read the Undo Log data and return it to the client, which is the MVCC mechanism in MySQL.

You can run the following command in MySQL to view the parameters that control the Undo Log.

show variables like '%innodb_undo%';
Copy the code

Redo Log Log

Redo Log: Undo Log; Redo Log: Redo Log;

What is a Redo Log

As the name suggests, the Redo Log literally stands for Redo Log, which is the ability to Redo an operation in the event of a database failure. In MySQL, any data that is modified during a transaction is backed up by writing the latest data to the Redo Log.

When a transaction is committed, a Redo Log is generated and written to the Redo Buffer. The Redo Buffer is not written to disk immediately after the transaction is committed, but after the dirty pages of the transaction are written to disk. The Redo Log is done. The space occupied by the Redo Log can be reused and overwritten by subsequent Redo logs.

The Redo Log principle

The Redo Log implements persistence of uncommitted transactions. It prevents uncommitted transactions from being redone when MySQL services are restarted at a time when dirty pages are not written to tables in ibD files. This process can be simplified as shown below.

Redo Log writing mechanism

The Redo Log file is written in a sequential loop. When the Redo Log is full, it goes back to the first file and overwrites.

  • Write Pos is the position of the current record, which moves backwards as you Write, returning to the beginning of file 0 after writing to the end of the last file.
  • CheckPoint is the current point to be erased, and it is also updated to a data file before being erased.

Write The remaining space between Pos and CheckPoint, which can be used to record new operations. If Write Pos catches up with CheckPoint, it indicates that the Write is full. In this case, move the Write Pos backward to CheckPoint to erase data.

Each InnoDB storage engine has at least one redo logfile group (group), and each file group has at least two redo log files, the default is IB_logFILE0 and IB_logFILe1.

You can run the following command in MySQL to view the parameters controlling the Redo Log.

show variables like '%innodb_log%';
Copy the code

Redo Log writing mechanism

When Redo Log information is persisted from Redo Buffer to Redo Log, the innodb_flush_log_at_trx_commit parameter is used to set the persistence policy, as shown below.

  • 0: Redo buffer ->OS cache -> Flush cache to disk may lose transaction data for 1 second. The operation is performed every second by the background Master thread.
  • 1 (default) : Redo Buffer -> OS cache -> Flush cache to disk for each transaction commit. This method is safest and has the worst performance.
  • 2: Perform Redo Buffer -> OS cache on each transaction commit, and then perform OS cache -> Flush cache to disk every second on the Master thread.

Generally, the value of 2 is recommended, because the data is not lost when the MySQL server is down. Only when the entire server is down, the transaction commit data is lost for 1 second.

Binlog log

What is the Binlog

Binlog Records binary logs about all MySQL database table structure changes and table data changes. The logs about query operations such as select and show are not recorded. Binlog logs are recorded in the form of events and contain the elapsed time of the statement execution. There are two most important scenarios for enabling Binlog.

  • Master/slave replication: Enable the Binlog function in the master library, so that the master library can transfer the Binlog to the slave library. After the slave library gets the Binlog, the data can be recovered to achieve the consistency between the master and slave database.
  • Data recovery: Use tools such as mysqlbinlog to recover data

The Binlog usage scenario can be found in my book MySQL Technology: Development, Optimization, and Operation.

Binlog File record mode

There are three recording modes of a Binlog file: STATEMENT, ROW, and MIXED.

The ROW pattern

ROW (Row-based replication, RBR) : Logs record the modification of each ROW of data, and then modify the same data on the slave end.

Advantages: The modification details of each row can be clearly recorded, and master/slave data synchronization and data recovery can be fully realized.

Disadvantages: Batch operation, will produce a large number of logs, especially ALTER table will cause the log skyrocketing.

STATMENT mode

STATMENT (SBR) : Each SQL file modified is recorded in the master’s Binlog. During replication, the SLAVE SQL process parses the same SQL file that was executed by the master and executes it again. SQL statement replication for short.

Advantages: A small amount of logs reduces disk I/OS and improves storage and recovery speed

Disadvantages: Inconsistencies in primary and secondary data in some cases, such as last_insert_id(), now(), etc.

MIXED mode

MIXED (mixed-based replication, MBR) : If the two modes are used together, STATEMENT mode is used to save binlogs. For operations that cannot be copied in STATEMENT mode, ROW mode is used to save binlogs. MySQL selects a write mode based on the SQL STATEMENT to be executed.

Binlog file structure

There are three versions of MySQL’s Binlog file structure, as shown below.

Specific information about the Binlog file structure, friends can refer to the MySQL’s official documents, specific links for: dev.mysql.com/doc/interna…

Binlog write mechanism

Log events (event-triggering execution mechanism) are generated based on logging mode and action triggering events.

Log events generated during transaction execution are written to a buffer. Each transaction thread has a buffer. Log events are stored in a binlog_cache_mngr data structure with two buffers: stmT_cache for information that does not support transactions; The other is trx_cache, which stores information that supports transactions.

The transaction writes the generated log events to an external binlog file during the commit phase. Different transactions write log events into the Binlog file in a serial manner, so the log event information contained in one transaction is continuous in the Binlog file, and no log events of other transactions are inserted in the middle.

Binlog file operation

View the Binlog status

show variables like 'log_bin';
Copy the code

To enable the Binlog function, modify the my. CNF or my.ini configuration file and add log_bin=mysql_bin_log to [mysqld] to restart the MySQL service.

binlog-format=ROW
log-bin=mysqlbinlog
Copy the code

Run the show binlog events command

show binary logs; //Is equivalent toshow master logs;
show master status;
show binlog events;
show binlog events in 'mysqlbinlog.000001';
Copy the code

Use the mysqlbinlog command

Mysqlbinlog "mysqlbinlog"> "test.sql"
Copy the code

Restore data using binlog

//Restore mysqlbinlog at the specified time--start-datetime="2021-02-28 18:00:00" --stopdatetime="2021-03-01 00:00:00" mysqlbinlog.000001 | mysql -uroot -p123456
//Restore mysqlbinlog by event location number--start-position=1789 --stop-position=2674 mysqlbinlog.000001
| mysql -uroot -p123456
Copy the code

Delete the Binlog file

purge binary logs to 'mysqlbinlog.000001'; //Delete the specified file purgebinary logs before '2021-03-01 00:00:00'; //Reset master;//Clear all filesCopy the code

You can enable the automatic cleanup function by setting expire_logs_days. The default value 0 indicates that the function is disabled. An integer greater than 0 indicates the number of days after which the binlog file will be automatically cleared.

For more information on Binlog, please refer to my book MySQL Technology: Development, Optimization, and Operation.

If you have any questions, you can leave a comment below or add me to wechat: SUN_shine_LYz. I will pull you into the group. We can exchange technology together, advance together, and make great force together