Shulou(Shulou.com)06/01 Report--

How does the Mysql database manage logs? In order to solve this problem, the editor summarizes this article on log management today, which can be used as a reference for interested friends. I hope it will be helpful to you.

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

Slow query log, which will be recorded when the execution time of sql exceeds the set value.

Error log, which is recorded in the error log when an error occurs when mysql starts, stops, or runs

Binary log, which records the sql statements that change the database data, through which we can recover Mysql data

Query log, when enabled, the query SQL will be recorded.

Error log

The error log records the problems that occur when Mysql starts and stops, as well as the major problems that occur when Mysql is running. 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.01sec)

The error log, which uses the hostname as the file name by default and the suffix err, is stored in the / var directory under the mysql directory. If we want to modify it, we can modify the log_error entry in the configuration file. You can use a relative path or an absolute path, which only modifies the error log file name, while using an absolute path can change 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 the actions of the user, so it generally takes up a large amount of space, which is not enabled in daily operation and maintenance, and the log is turned off by default. If you want to turn it on, you can add the log option to the configuration file.

Slow query log

Slow query is a very good tool for troubleshooting SQL problems, and in many cases, our sql causes the system to run poorly. The author has made this mistake before, using a very complex query statement, the system is not old on 502. Some time ago, I found that there were corresponding problems in the company's old project. When the amount of data reached a certain level, there would be a large number of slow queries, and then the cpu of the whole system would be full. Therefore, we should often check the slow query log to find out where the problem with sql lies.

Slow query configuration item

Slow-query-log= {0 | off | 1 | on} whether to enable slow log function

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

Location and file name of slow_query_log_file=/mydata/data/hostname-slow.log slow query log

The log_queries_not_using_indexes=OFF item indicates that queries that do not use indexes are also recorded in the slow query log.

Case

Now, I open my mysql's slow query log and set the time to 0 to record all the SQL. Then let's show you what the slow query log looks like.

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.