Summary of important mysql log files

Author: Ding Yi

Source: https://chengxuzhixin.com/blog/post/mysql_zhong_yao_ri_zhi_wen_jian_zong_jie.html

Logs are important data for all applications. MySQL also has error logs, query logs, slow query logs, transaction logs, etc. This article briefly summarizes various logs for reference.

Binary log

Binary log binlog is used to record the write operation (excluding query) information performed by the database and is saved in the disk in binary form. The MySQL database using any storage engine will record binlog logs. What is recorded in binlog is the logical log, that is, the SQL statement. After the SQL statement is executed, binlog is appended to the log file. You can set the binlog file size. When the size is exceeded, a new file will be automatically created.

There are three binlog formats: STATMENT, ROW, and MIXED.

• STATMENT: records SQL statements that modify data in binlog; this is the default format before MySQL 5.7.7;
• ROW: does not record the context information of each SQL statement, only records which data has been modified; it is the default format after MySQL 5.7.7;
• MIXED: Mixed replication based on STATMENT and ROW modes. Generally, the STATEMENT mode is used, and the ROW mode is used for operations that cannot be replicated.

In practical applications, binlog is mainly used for master-slave replication and data recovery. Master-slave replication means opening binlog on the master machine and sending the binlog to the slave machine in some way. The slave machine performs data operations based on the binlog content to ensure master-slave data consistency. In addition, data can be restored from binlog by using the mysqlbinlog tool.

After MySQL 5.7, the built-in default engine has been changed to InnoDB engine. When the InnoDB engine processes transactions, you can set the timing of writing logs to disk. By default, logs are written to disk at each commit. You can also set the sync_binlog parameter to automatically determine the system or write once every N transactions.

Query log

The query log records information about all database requests. Whether or not these requests were properly executed. It has a significant impact on performance when enabled, so it is not often used.

Slow query log

The slow query log is used to record statements whose execution time exceeds a certain threshold. The execution time threshold can be set via long_query_time, the default is 10 seconds. The slow query log needs to be enabled manually, which has some impact on performance and is generally not recommended. The slow query log supports writing records to files or database tables.

Transaction log redo log

One of the four major characteristics of a transaction is durability. Therefore, after the transaction succeeds, the database changes are permanently saved and cannot be restored to the original state for any reason. The redo log is a log implemented at the InnoDB engine layer. Not all engines have it. It is used to record changes made to data pages by transactions and can be used to recover data in the event of a crash. The redo log includes the log buffer in memory and the log file on disk. After the SQL statement is executed, it is first written to the log buffer, and then multiple buffers are written to the file at once.

In InnoDB, data pages are also flushed to disk. The main purpose of the redo log is to reduce the requirement for flushing data pages to disk. It is not necessary to save all redo logs for changes to data pages. If the data page is flushed faster than the redo log, the redo log record is of little significance for data recovery; if the data page is flushed slower than the redo log, the part of the redo log that is faster than the data page can be used to quickly recover data. Therefore, the size of the redo log file is fixed. When the redo log reaches the end, it will go back to the beginning and write the log in a loop.

Transaction log undo log

One of the four major characteristics of transactions is atomicity. A series of operations on the database must either all succeed or all fail. Partial success and partial failure are not allowed. Therefore, it is necessary to record the logical changes of data. Atomicity is achieved through undo log. For example, if an insert statement is executed in a transaction, the undo log will record a delete statement; if an update statement is executed in a transaction, the undo log will record an opposite update statement. In this way, when a transaction fails, you can roll back to the state before the transaction through undo log.

The above is the detailed content of the summary of important MySQL log files. For more information about MySQL log files, please pay attention to other related articles on 123WORDPRESS.COM!