Mysql operation and maintenance log management

Generally mature software will have its own log files. Use these logs to quickly locate problems. In Mysql, logs are divided into the following categories:

• Slow query log. When the execution time of sql exceeds the set value, it will be recorded

• Error log, when an error occurs when mysql starts, stops or runs, it will be recorded in the error log

• Binary log, this log records the SQL statements that change the database data. Through him, we can recover Mysql data

• Query log. When turned on, the query SQL will be recorded.

Today we will talk about slow queries, error logs, query logs, and binary log files. We will explain them in detail in a new article later.

Error log

The error log records problems that occur when Mysql starts and stops. It also records major problems that occur during Mysql operation. . You can use show variables like 'log_err%'; to view the location of the error log file.

mysql> show variables like 'log_err%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| log_error           | ./iZbp171edtq2kx5iy0xufeZ.err |
| log_error_verbosity | 3                             |
+---------------------+-------------------------------+
2 rows in set (0.01 sec)

The error log uses the host name as the file name by default, err as the suffix, and is stored in the /var directory under the mysql directory. If we want to modify it, we can modify the log_error item in the configuration file. You can use a relative path or an absolute path. If you use a relative path, you will only modify the error log file name, and if you use an absolute path, you can modify the location where the error log is stored.

Viewing the error log is also very simple, just use cat or vim to view it.

General query log

The general query log records all user operations, so it generally takes up a large space and is generally used in daily operation and maintenance. It will not be enabled, and the log is also closed by default. If you want to enable it, you can add the log option to the configuration file.

Slow query log

Slow query is a very good tool for troubleshooting SQL problems. In many cases, it is our sql that causes the system Not running smoothly. The author has made this mistake before, using a very complex query statement, and the system got 502 as soon as it was out of date. Some time ago, I found that the company's old project also had corresponding problems. The project used dedecms. When the amount of data reached a certain level, a large number of slow queries would appear, and then the entire system's CPU would be full. Therefore, we need to check the slow query log frequently to find the SQL problem.

Slow query configuration item

• ##slow-query-log={0|off|1|on} Whether Turn on the slow query log function

• slow-query-time=10 The set threshold, in seconds, supports floating point numbers. When the execution time of SQL exceeds this value, it will be recorded in the slow query log. If set to 0, all queries will be recorded

• slow_query_log_file=/mydata/data/hostname-slow.log The location and file name of the slow query log

• log_queries_not_using_indexes=OFF This item indicates that queries that do not use indexes will also be recorded in the slow query log.

Case

Now, I turn on my mysql slow query log and set the time to 0 , record all SQL. Then let’s take a look at what the slow query log looks like.

Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2020-05-31T12:00:01.895700Z
# User@Host: blog[blog] @ localhost []  Id:     3
# Query_time: 0.000170  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use blog;
SET timestamp=1590926401;
SET NAMES utf8mb4;
# Time: 2020-05-31T12:00:01.902684Z
# User@Host: blog[blog] @ localhost []  Id:     3
# Query_time: 0.006914  Lock_time: 0.006529 Rows_sent: 0  Rows_examined: 120
SET timestamp=1590926401;
SELECT * FROM knowledge 
            WHERE is_delete=0 AND star < 5 AND show_time <= 1590854400   ORDER BY  show_time ASC 
            LIMIT 1;

We mainly look at the Query_time line

• Query_time: Query time

• Lock_time: Lock table time

• Rows_sent: Obtained data rows

• Rows_examined: Scanned data rows

The above is the detailed content of Mysql operation and maintenance log management. For more information, please follow other related articles on the PHP Chinese website!