mysql series: log

Summary

log existence, offers many features for the database guarantee . Like undo log redo log 16130dffae361d for data recovery binlog for master-slave backup. This article will briefly introduce the log categories in the database, and focus on analyzing the relevant knowledge points of the transaction log.

Log classification

There are many types of logs in mysql. Generally speaking, they can be divided into Server layer and storage engine layer (for the overall architecture of mysql, please see this article: mysql series: Overall architecture overview ).

The log classification in the Server layer is as follows:

Error log

The error log is a log of an exception occurred when mysql was started, run, or stopped. We can use the following command to view the location of the error log:

SHOW VARIABLES LIKE 'log_error';

The above is the default query to stderr , expressed standard error output , if there is a terminal, the terminal will print an error. Of course, we can also set the error log location my.cnf

[mysqld]
 log-error=错误日志位置

Usage scenario : Like when we fail to start mysql, we can generally check it in the error log.

General query log

The general query log records all user operations, including query updates of sql statements. Under normal circumstances, it will not be turned on, which is a bit similar to the debug level logs we usually use. Similarly, we can also use the following statement to check whether it is turned on and its output location:

SHOW VARIABLES LIKE '%general%';

When we want to open the general query log to record all client sql operations, we can use the following statement:

SET GLOBAL general_log=on;

The above log output is to a file. If you want to use the sql statement to find the log, you can set the log output as a table:

SET GLOBAL log_output='table';

Then you can use the following sql statement to query:

select * from mysql.general_log;

In this way, you can monitor the all 16130dffae37dc clients of . Of course, don't forget to close the log after use, otherwise it will take up a lot of disk space.

DDL log

The DDL log records the change information metadata DDL is data definition sentence, like create，drop，alter sentence.

The DDL log is a binary file, which cannot be read or modified by humans, and there is no other configuration item to configure it. Generally stored in the data directory, which is the ddl_log.log file. And it will be deleted after successfully starting mysqld, and will be recreated only when the metadata is recorded.

binlog binary log

The binlog log records the data modification , including DDL, such as table creation, data update, etc. But it does not include select query statements.

The binlog log is a record of logical statements and can be used to synchronize the master-slave database.

relay log

The relay log is used for master-slave backup and recovery. After the master transmits the binlog, the slave server will have an I/O thread to process the received log and maintain it in the relay log.

Relay log With the binlog logic operation statement of the main server, you can restore the database with

In addition to the contents of the binlog, the relay log also records the current recovery position. If you disconnect from the server and reconnect, you can resume from the last recorded position.

Slow query log

The slow query log is used to record time-consuming statements whose execution time exceeds the expected value in mysql, and is mainly used for performance bottleneck analysis.

Generally, slow query records need to be manually set:

SET GLOBAL slow_query_log=1;

As for the record location of its log, you can query it like this:

SHOW VARIABLES LIKE '%slow_query_log%';

The query for the time threshold is as follows:

SHOW VARIABLES LIKE 'long_query_time%';

The corresponding setting statement:

SET GLOBAL long_query_time=8;

Transaction log

The transaction log is designed by the InnoDB storage engine to support the persistent transaction, mainly redo log and undo log.

redo log

The redo log is a record of the modified result data page loaded into the memory. Unlike binlog, the binlog records logical operation statements and is biased towards process records. The redo log is a modification log of the data page, which is biased towards the recording of results.

Whenever a transaction is executed in mysql, the data modification will not be synchronized to the hard disk from time to time. Instead, a buffer pool will be maintained in the memory, and the operation will be given priority when reading the updated data. If the data does not exist, it will be loaded from the disk before the operation.

The modified data in the memory will not always reside, and will be updated to the disk regularly. This is the so-called dirty page .

Careful friends may find that the memory data is not reliable. If a power outage occurs and the data in the buffer pool cannot be landed on the disk, wouldn't the data be lost? This is when redo log comes into play.

Whenever a transaction performs an operation, the result of the modification to the data page will be recorded in the redo log first, and then the transaction will be committed. This log-first approach ensures that data can be recovered from the log even if it is too late to synchronize data.

In fact, after the memory data page is modified and written to the redo log, the transaction is only marked in the prepare state at this time, and will not be marked in the commit state. Only when the binlog is also written successfully will the real commit be made.

The reason why the binlog is written successfully can be considered as a complete commit, mainly because binlog is used to recover from the database. If the redo log is written successfully but the binlog is not written successfully, then the main database will have the data of the result of this operation. , And missing data when synchronizing from the database.

This two-time commit state is called a two-phase commit by mysql. In addition, redo log and binlog also have a group submission process, which is mainly used for batch transaction submission and log writing.

undo log

The rollback log is mainly used to roll back data. Unlike redo log, undo log is a logical log, which is a record of reverse operations. For example, during rollback, if it is an insert operation, it will be reversed to delete. In the delete operation, the reverse is the insert operation, and the update is restored to the current version data.

undo is also used for the use of the MVCC version chain, you can read this article for details: MVCC .

Summarize

At the beginning of mysql, there was no transaction log. Later, with the InnoDB storage engine, it needed to support the characteristics of transaction persistence, so there was redo log and undo log. Although for development, it may not involve the operation and maintenance of mysql logs, but its implementation principle is worth learning, and maybe it can also implement a persistent database of its own in the future~~~ ㋡㋡㋡

---

interested in 16130dffae3a95 can search the public account "Read new technology" to follow more push articles.
you can, please like, leave a comment, share, thank you for your support!
read new technology, read more new knowledge.