Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is about the MySQL log". The explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what about the MySQL log".

MySQL log

Speaking of MySQL logs, there are three types of logs that are critical to MySQL: Binlog, Undo Log, and Redo Log.

Because there are similarities between Binlog and UndoLog, we introduce the three major logging principles in MySQL in the following order: UndoLog-- > Redo Log-- > Binlog.

Undo Log log

What is Undo Log?

As the name implies, Undo Log literally means the log of an undo operation, which refers to bringing the data in the MySQL back to a certain state.

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

When MySQL adds, modifies, and deletes data, the information is written to Undo Log before the transaction begins. When a transaction commits, the Undo Log will not be deleted immediately. The InnoDB storage engine will put the corresponding Undo Log of the transaction in the list to be deleted, and then delete the list through the purge thread in the background. Here, it is worth noting that Undo Log is a logical log that records a process of change. For example, MySQL performs a delete operation, Undo Log logs an insert operation; MySQL performs an insert operation, Undo Log logs a delete operation; MySQL performs a update operation, and Undo Log logs an opposite update operation.

Undo Log manages and records log information in a segmented manner. The data file of the InnoDB storage engine contains a rollback segment called rollback segment, which contains 1024 undo log senment.

Undo Log action

Undo Log plays a vital role for MySQL to implement transactions. It implements the atomicity of transactions and multi-version concurrency control, which is often referred to as MVCC.

Realize the atomicity of the transaction

Undo Log can realize the atomicity of MySQL transactions. If there is an error in MySQL or if the user manually performs the rollback operation of the transaction (performing the rollback operation), MySQL can use Undo Log logs to restore the data in the database to the previous state.

Implement the MVCC mechanism

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

Transaction A manually opens the transaction, updates the data with id 1 in the goods data table, and first writes the data hit by the update to the Undo Buffer. Before transaction A commits, transaction B manually opens the transaction and queries the data with id 1 in the goods data table. Transaction B will read the data in Undo Log and return it to the client, which is the MVCC mechanism in MySQL.

You can view the parameters that control the Undo Log log in MySQL with the following command.

Show variables like'% innodb_undo%'

Redo Log log

Having said Undo Log in MySQL, let's take a look at the Redo Log log in MySQL.

What is Redo Log?

As the name implies, Redo Log literally means redo log, which refers to the ability to perform some operation on a database in the event of an unexpected situation. In MySQL, any data modified in a transaction writes the latest data to Redo Log for backup.

In MySQL, the Redo Log log will be generated with the execution of the transaction operation, and the Redo Log will be generated and written to the Redo Buffer,Redo Buffer when the transaction is committed. It is not immediately written to the disk as the transaction commits, but after the dirty pages of the transaction operation are written to the disk, the mission of the Redo Log will be completed. At this time, the space occupied by the Redo Log log can be reused and will be overwritten by the subsequent Redo Log log.

The principle of Redo Log

Redo Log can achieve transaction persistence and prevent uncommitted transactions from being persisted by redoing according to Redo Log when the MySQL service is restarted in ibd files with dirty pages that are not written into the table at the point of failure. This process can be simplified to the figure below.

The Writing Mechanism of Redo Log

The contents of the Redo Log file are written to the file in a sequential loop, and when it is full, it will go back to the first file and overwrite it.

Write Pos is the location of the current record, move back while writing, and then go back to the beginning of file 0 after writing to the end of the last file.

CheckPoint is the current position to be erased, and it is also pushed back and looped. Update the record to the data file before erasing the record.

The space between Write Pos and CheckPoint can be used to record new operations. If Write Pos catches up with CheckPoint, indicating that it is full, you need to move CheckPoint backward to erase the data.

Each InnoDB storage engine has at least 1 redo log filegroup (group), and each filegroup has at least 2 redo log files, default to ib_logfile0 and ib_logfile1.

You can view the parameters that control Redo Log in MySQL with the following command.

Show variables like'% innodb_log%'

Redo Log write mechanism

When the Redo Log log information is persisted from Redo Buffer to Redo Log, the specific persistence policy can be set through the innodb_flush_log_at_trx_commit parameter, as shown below.

0: commit Redo buffer-> OS cache-> flush cache to disk per second, and transaction data within one second may be lost. The background Master thread performs the operation every 1 second.

1 (default): execute Redo Buffer-> OS cache-> flush cache to disk every transaction commit, which is the safest and worst performance.

2: each transaction commit executes Redo Buffer-> OS cache, and then the background Master thread executes the OS cache-> flush cache to disk operation every 1 second.

It is generally recommended to choose a value of 2, because there is no data loss when the MySQL is hung up, and the transaction commit data of 1 second will be lost only if the whole server is hung up.

Binlog log

What is Binlog?

Binlog records binary logs of all MySQL database table structure changes and table data modifications, and does not log query operations such as select and show. The Binlog log is recorded as an event and contains the elapsed time that the statement was executed. There are two most important usage scenarios for opening Binlog logs.

Master-slave replication: enable the Binlog function in the master database, so that the master database can pass the Binlog to the slave database, and achieve data recovery to achieve master-slave data consistency after getting the Binlog from the database.

Data recovery: recover data through tools such as mysqlbinlog

Binlog file recording mode

There are three Binlog file recording modes: STATEMENT, ROW and MIXED, and the specific meanings are as follows.

ROW mode

ROW (row-based replication, RBR): each row of data is recorded in the log, and then the same data is modified on the slave side.

Advantages: it can clearly record the modification details of each row of data, and can fully realize master-slave data synchronization and data recovery.

Disadvantages: batch operations will generate a large number of logs, especially alter table will make logs soar.

STATMENT mode

STATMENT (statement-based replication, SBR): each SQL of the modified data will be recorded in the Binlog of master, and when the slave is copied, the SQL process will be parsed to the same SQL executed on the original master side and executed again. Abbreviated as SQL statement replication.

Advantages: small log volume, reduced disk IO, improved storage and recovery speed

Disadvantages: in some cases, it can lead to inconsistency between master and slave data, such as last_insert_id (), now (), and so on.

MIXED mode

MIXED (mixed-based replication, MBR): the mixed use of the above two modes generally uses STATEMENT mode to save binlog, and for operations that cannot be copied in STATEMENT mode, using ROW mode to save binlog,MySQL will select the write mode according to the SQL statement executed.

Binlog file structure

There are three versions of the Binlog file structure for MySQL, as shown in the figure below.

For specific information about the structure of Binlog files, friends can refer to the official documents of MySQL. The specific link is: https://dev.mysql.com/doc/internals/en/event-header-fields.html

Binlog writing mechanism

According to the recording mode and operation to trigger event events to generate log event (event trigger execution mechanism).

The log time (log event) generated during transaction execution is written to the buffer, and each transaction thread has a buffer. Log Event is stored in a binlog_cache_mngr data structure in which there are two buffers, one is stmt_cache, which is used to store information that does not support transactions, and the other is trx_cache, which is used to store information that supports transactions.

The transaction writes the resulting log event to an external binlog file during the commit phase. Different transactions write log event to the Binlog file in a serial manner, so the log event information contained in one transaction is contiguous in the binlog file, and the log event of other transactions is not inserted in the middle.

Binlog file operation

Binlog status View

Show variables like 'log_bin'

To enable Binlog, you need to modify the my.cnf or my.ini configuration file, add log_bin=mysql_bin_log under "mysqld", and restart the MySQL service.

Binlog-format=ROW log-bin=mysqlbinlog

Use the show binlog events command

Show binary logs; / / is equivalent to show master logs; show master status; show binlog events; show binlog events in 'mysqlbinlog.000001'

Use the mysqlbinlog command

Mysqlbinlog "file name" mysqlbinlog "file name" > "test.sql"

Using binlog to recover data

/ / 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-- start-position=1789-- stop-position=2674 mysqlbinlog.000001 by event location number | mysql-uroot-p123456

Delete Binlog Fil

Purge binary logs to 'mysqlbinlog.000001'; / / Delete the specified file purge binary logs before' 2021-03-01 00 reset master; / / erase all files before the specified time

You can start the automatic cleanup feature by setting the expire_logs_days parameter. The default value of 0 means that it is not enabled. An integer set to greater than 0 indicates how many days beyond which the binlog file will be cleared automatically.

Thank you for your reading, the above is "what about the MySQL log" content, after the study of this article, I believe you have a deeper understanding of what about the MySQL log, the specific use of the need for you to practice and verify. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.