Experimental environment: MySQL Community Server (GPL) 5.7.17, Ubuntu 16.04

MySQL Log File Classification

Official documentation:



Log files record various types of activities affecting MySQL databases. Common logs are as follows:

1. Error log; 2. Slow query log; 3. Binlog; 4. Query log (log);

Error Log

1. Official introduction document:


2. The MySQL error log is to record the more serious warnings and errors during the operation of MySQL, as well as the detailed information of each startup and shutdown of MySQL.

The MySQL error log file location can be viewed with the command:

mysql> show variables like "log_error"\G;

Check it out below:


Slow Query Log

1. Official introduction document:


2. MySQL's slow query log is a log record provided by MySQL, which is used to record the statement whose response time exceeds the threshold in MySQL, specifically refers to the running time exceeding the threshold.long_query_time

The default value of long_query_time is 10, meaning a statement that runs for more than 10s.

By default, the MySQL database does not start the slow query log, and we need to manually set this parameter. Of course, if it is not required for tuning, it is generally not recommended to start this parameter, because turning on the slow query log will bring more or less certain benefits. performance impact.

The slow query log supports writing log records to files, and also supports writing log records to database tables.

3. You can check the default threshold and whether it is enabled by the following command:

mysql> show variables like "long_query_time"\G;

mysql> show variables like "slow_query_log"\G;

4. Enable slow query log:

mysql> set global slow_query_log="ON";

5. The default slow query log file directory

mysql> show variables like "slow_query_log_file"\G;

It can be seen that the default slow query log is named after the host name and '-slow', the default: host_name-slow.log

6. View the slow query log

The usemysqldumpslow command is viewed as follows:

xuliugen@xuliugen:~$ sudo mysqldumpslow /var/lib/mysql/xuliugen-slow.log

For more information on how to use mysqldumpslow, use man mysqldumpslow to view:


7. Change the storage method of slow queries

First, check the default storage method of logs by command:

mysql> show variables like "log_output"\G;

It can be known that the default storage method is FILE, that is, stored in/var/lib/mysql/xuliugen-slow.log

Check out the official documentation forlog_output

mysql> set global log_output="TABLE";

After changing to TABLE, the slow query log will be stored in the form of a table.mysql slow_log

mysql> show create table mysql.slow_log\G;

8. Test the slow query log

Simulate a slow query with the command:

mysql> select sleep(10);

View slow query log:

mysql> select * from mysql.slow_log\G;

9. There are two more properties in MySQL 5.7 version:


By default, administrator commands to operate the database (including: ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE.) and queries without indexes are not logged.


These two properties can be set in the development environment. In the actual production environment, because the index is not used, such SQL statements will be frequently recorded in the slow log, making the size of the slow log file larger. Keep increasing, therefore, make reasonable settings due to improvements.

Related articles

Python Errors and Exceptions

The keyword for exception handling in python is different from that in c#. In python, try and except are used to handle exceptions, such as the following: 2. raise excepption

Spring 406 error

The web page appears 406 generally for the following two situations: 1.Missing jar package 2.If the URL of the access url has a suffix ending in .html, the server cannot respond to the json data. Because springMVC will mistakenly think that the request

squid Deployment

YUM installation yum install squid -y yum install httpd-tools -y Compile and install cd squid* ./configure \ --prefix=/usr \ --exec-prefix=/usr \ --includedir=/usr/include \ --datadir=/usr/share \ --libdir=/usr/lib64 \ --libexecdir=/usr/lib64/squid \ --lo

webservice example

What is webservice: WebService is a remote invocation technology across programming languages and operating system platforms. The so-called cross programming language and cross operating platform means that the server program is written in java, and the

Native jdbc example with spring JDBC example

Native jdbc integration with spring Introduction to JDBC The JDBC API allows users to access any form of tabular data, especially data stored in relational databases. Implementation process: Connect to a data source, such as a database. Pass query

Deploying Nginx

How to install PostgreSQL Linux downloads (Red Hat family) Tip: The current version is Version 10.3 surroundings [root@h210 ~]# hostnamectl Static hostname: h210 Icon name: computer-vm

Install OpenVPN

OpenVPN is an open source VPN (Virtual private network) software OpenVPN is a full-featured SSL VPN which implements OSI layer 2 or 3 secure network extension using the industry standard SSL/TLS protocol, supports flexible client authentication methods ba

mysql binlog transaction logs

binlog is binary log, binary log file, this file records all mysql dml operations. Through the binlog log, we can do data recovery, master-resident replication and master-slave replication, etc. Developers may not pay much attention to binlog

istio Deployment

1.istio Deployment Download address: https://github.com/istio/istio/releases Network disk link: https://pan.baidu.com/s/1L4CK2icK6teT5Ef4eiJwKw Password: i16u Resource allocation: ​master 2U2G ​node 2U8G curl -L https://git.io/getLatestIstio | ISTIO_

gitlab Deployment

GitLab is an open source project for warehouse management systems. Use Git as a code management tool and build a web service on this basis. Public or private projects can be accessed through a web interface. It has similar functionality to Github