MySQL Log knowledge essentials

MySQL logs mainly include: error log, query log, slow query log, transaction log, binary log;

Logs are an important part of the MySQL database. The log file records the changes that occurred during the MySQL database operation, which is used to record the client connection status of the MySQL database, the execution of the SQL statement, and the error message. When the database is accidentally damaged, you can view the cause of the file error through the log, and you can use the log file for data recovery.

Error log

In the MySQL database, the error logging feature is turned on by default. Also, the error log cannot be disabled. By default, the error log is stored in the data file of the MySQL database. The error log file is typically named Hostname.err. where hostname represents the server host name.

The error log information can be configured on its own, and the information logged by the error log can be defined by Log-error and log-warnings, where Log-err is the function that defines whether the error log is enabled and where the error log is stored. Log-warnings is the definition of whether warning messages are also defined in the error log. By default, the error log probably records information about the server startup and shutdown processes (not necessarily error messages, such as how MySQL starts a innodb tablespace file, how to initialize its own storage engine, and so on), error messages during server operation, The information that is generated when the event Scheduler runs an event, and the information that is generated when the server process is started from the server.

Query log

The query log is turned off by default. Because the query log will record all of the user's actions, including additions and deletions and other information, in the context of large concurrent operation will generate a large amount of information resulting in unnecessary disk IO, will affect the performance of MySQL. It is not recommended to open the query log for the purpose of debugging the database.

To see if the query log is turned on:

Mysql> SHOW GLOBAL VARIABLES like '%log% ';

Slow query log

The slow query log is used to record query statements that execute longer than a specified time. By slowly querying the log, you can find out which query statements are inefficient to perform for optimization. Generally recommended, it has minimal impact on server performance, but it can record query statements that have been executed for a long time on the MySQL server. can help us locate performance issues.

To view the definition of a slow query log:

Mysql> SHOW GLOBAL VARIABLES like '%log% ';
| Slow_query_log | OFF #定义慢查询日志的
| Slow_query_log_file |/mydata/data/stu18-slow.log #输出方式为file (file) defines the location of the slow query log

Transaction log

Transaction logs (InnoDB-specific logs) can help improve the efficiency of transactions. With transaction logs, the storage engine modifies the table's data only by modifying its memory copy, and then logging the modified behavior to the transaction log that is persisted on the hard disk without having to persist the modified data itself to disk each time. The transaction log is appended, so the log operation is sequential I/O in a small area of the disk, rather than a random I/O that needs to move the heads in multiple places on the disk, the transaction log is relatively much faster. After the transaction log is persisted, the in-memory modified data can be slowly brushed back to disk in the background. Most of the current storage engines are implemented in this way, which we often call pre-written logs, which require two disk writes to modify the data.

If the modification of the data has been recorded to the transaction log and persisted, but the data itself has not yet been written back to the disk, the system crashes and the storage engine recovers this partially modified data automatically when it restarts. The recovery method you have is dependent on the storage engine.

To view the definition of a transaction log:

Mysql> ShowGlobal VARIABLES like '%log% ';

Transaction log:

Transaction logs (InnoDB-specific logs) can help improve the efficiency of transactions. With transaction logs, the storage engine modifies the table's data only by modifying its memory copy, and then logging the modified behavior to the transaction log that is persisted on the hard disk without having to persist the modified data itself to disk each time. The transaction log is appended, so the log operation is sequential I/O in a small area of the disk, rather than a random I/O that needs to move the heads in multiple places on the disk, the transaction log is relatively much faster. After the transaction log is persisted, the in-memory modified data can be slowly brushed back to disk in the background. Most of the current storage engines are implemented in this way, which we often call pre-written logs, which require two disk writes to modify the data.

If the modification of the data has been recorded to the transaction log and persisted, but the data itself has not yet been written back to the disk, the system crashes and the storage engine recovers this partially modified data automatically when it restarts. The recovery method you have is dependent on the storage engine.

To view the definition of a transaction log:

Mysql> ShowGlobal VARIABLES like '%log% ';

Binary log

The binary log is also called a change log, which is used primarily to record modified data or MySQL statements that may cause data changes, and to record the time of the statement, the length of execution, the data of the operation, and so on. So you can query the MySQL database for changes by using the binary log. The general size of the volume is capped at 1G.

Binary Open state:

Mysql> ShowGlobal variables like "%log_bin%";

Binary log-related parameters:

Mysql> ShowGlobal variables like "%log%";

Binary logs are defined in the following way:

One, log_bin can be defined directly as a file path, or it can be on| OFF.

Second, the binary log can be opened by editing the Log-bin option in the MY.CNF

MySQL Log knowledge essentials