Foreword

For the redo log and undo log mentioned in the previous article, it is the log of the storage engine, and this chapter introduces the log of MySQL Server.

Error log

The error log is one of the most important logs in MySQL. It records information about when mysqld is started and stopped, and when any serious errors occur while the server is running.This log can be viewed first when there is any failure of the database and it cannot be used normally.
mysqld uses the error log name host_name.err (host_name is the host name) and by default writes the log file in the directory specified by the parameter DATADIR (data directory).

cordump error exception…

Query log

The query log records all statements made by the client.Due to the large number of sql on-line projects, opening the query log IO too much leads to low efficiency of MySQL. It is only enabled during debugging. For example, by viewing the sql to find hot data for caching.

mysql> show global variables like “%genera%”;

Binary log

BINLOG records all DDL (Data Definition Language) statements and DML (Data Manipulation Language) statements, but does not include data query statements.Statements are stored in the form of "events", which describe the process of changing data.This log plays an extremely important role in data recovery in the event of a disaster.

Two important application scenarios: master-slave replication, data recovery

View binlog: mysql> show binary logs;
A large number of binlog log files can be quickly parsed through the mysqlbinlog tool (mysql native tool), such as:

shell> mysqlbinlog --no-defaults --database=school --base64-output=decode-rows
-v --start-datetime='2022-08-01 00:00:00' --stop-datetime='2022-08-10 00:00:00'
mysql-bin.000001 | more

A practical example of bin-log data recovery

First create a table, add some data,

Assuming no message to delete the library:

Check the log to know which part of the log the previous operation is stored in:

Enter the path

/var/lib/mysql

Find the log just now and check:

mysqlbinlog binlog.000040

Here you can see the SQL done before

Restore operation: use the start and end position you want to restore + filename

Check for success:
Restored successfully

Slow query log

MySQL can set a slow query log. When the SQL execution time exceeds the time we set, these SQL will be recorded in the slow query log. Then we can view the log and analyze the execution plan of these SQL with explain.To determine why the efficiency is low, is the index not used?Or is there something wrong with the index itself?Or the index is used, but because the amount of data in the table is too large, it takes a long time. At this time, we can divide the table into n small tables, for example, the order table is divided into multiple small tables by year, etc.

The parameters related to the slow query log are as follows:

The slow query log records the log containing all SQL statements whose execution time exceeds the value set by the parameter long_query_time (unit: second), which can be viewed with the command on MySQL, as follows:

This value can be modified as follows:

Now the SQL modified to more than 1 second will be recorded in the slow query log!Can be set to 0.01 seconds, which means 10 milliseconds.

Slow query log, the default name is host_name-slow.log, which is stored in the MySQL data path. The content format is roughly as follows:
The following:

By querying the slow query log, it is found that the project is running, the execution time of the above SQL statement exceeds the set slow query time, then you need to use explain to analyze the execution plan of the SQL, and find out how to optimize the SQL and index according to the specific situation.