Do you know all the log files in MySQL?

Time:2021-7-30

brief introduction

This article summarizes and briefly introduces the logs in mysql, which will not be involved too deeply. The main purpose is to have a systematic understanding of log files in MySQL. Each log file will be specifically analyzed and summarized later.

Log classification

Log files in MySQL include configuration files, error log files, binary logs, slow query logs, general logs, audit logs, database files & data table files, storage engine files, relay logs, process files (PID) and socket files.

Do you know all the log files in MySQL?

Parameter file

Parameter files are configuration files in mysql, including my.cnf file under Linux and my.ini file under windows. The file content is mainly divided into two modules: server and client. The server module is configured with MySQL service information, such as slow query logs. The client module configures MySQL client connection information, such as the port number of the client connection.
The file format is roughly as follows:

[client]
port                    = 3306
default-character-set   = utf8mb4

[mysqld]
user                    = mysql
port                    = 3306
sql_mode                = ""
default-storage-engine  = InnoDB
default-authentication-plugin   = mysql_native_password
character-set-server    = utf8mb4
collation-server        = utf8mb4_unicode_ci
init_connect            = 'SET NAMES utf8mb4'
slow_query_log
long_query_time         = 3
slow-query-log-file     = /var/lib/mysql/mysql.slow.log
log-error               = /var/lib/mysql/mysql.error.log
default-time-zone       = '+8:00'

Error log file

The error log file records the log information of MySQL from startup, operation and shutdown. For example, MySQL connection failure, query command error, SQL execution process, etc. It is very helpful for locating MySQL errors.
The general contents of the document are as follows:

Version: '5.7.28-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server (GPL)
2021-04-17T21:23:00.865868Z 3 [Note] Aborted connection 3 to db: 'exam_wechat' user: 'root' host: '172.18.0.1' (Got timeout reading communication packets)
2021-04-17T21:23:00.865969Z 2 [Note] Aborted connection 2 to db: 'exam_wechat' user: 'root' host: '172.18.0.1' (Got timeout reading communication packets)
2021-04-19T22:33:24.137143Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 18415ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2021-04-20T07:03:21.765208Z 79 [Note] Access denied for user 'root'@'172.18.0.1' (using password: NO)
2021-04-20T07:03:23.825044Z 81 [Note] Aborted connection 81 to db: 'unconnected' user: 'root' host: '172.18.0.1' (Got an error reading communication packets)
2021-04-20T07:14:25.033983Z 82 [Note] Access denied for user 'root'@'172.18.0.1' (using password: NO)
2021-04-20T07:14:27.442608Z 84 [Note] Aborted connection 84 to db: 'unconnected' user: 'root' host: '172.18.0.1' (Got an error reading communication packets)
2021-04-20T07:27:13.971644Z 83 [Note] Aborted connection 83 to db: 'unconnected' user: 'root' host: '172.18.0.1' (Got timeout reading communication packets)
2021-04-20T07:41:02.916249Z 85 [Note] Aborted connection 85 to db: 'unconnected' user: 'root' host: '172.18.0.1' (Got timeout reading communication packets)

How to start the error log. Just configure the log in the configuration file in MySQL_ Error.

mysql [email protected]:(none)> show variables like '%log_error%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| binlog_error_action | ABORT_SERVER                   |
| log_error           | /var/lib/mysql/mysql.error.log |
| log_error_verbosity | 3                              |
+---------------------+--------------------------------+
3 rows in set
Time: 0.010s

Full log file

The full log file records all SQL operation logs of MySQL. For example, operations such as adding, deleting, modifying and querying will be recorded.

mmysql [email protected]:(none)> show variables like '%general%';
Reconnecting...
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | OFF                             |
| general_log_file | /var/lib/mysql/7fdc5f723ff9.log |
+------------------+---------------------------------+

Configuration items have three values: table, none and file. The configuration file will be recorded in the log file, the configuration none will not be recorded, and the configuration table will create a table (called general log) in the default MySQL data of Mysql to record the log.

It is not recommended to open it. There are too many log files recorded, which not only consumes performance, but also occupies too much invalid space.

#Log file format
mysqld, Version: 5.7.28-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
2021-04-20T09:16:48.572888Z       88 Connect    [email protected] on  using TCP/IP
2021-04-20T09:16:48.574591Z       88 Connect    Access denied for user 'root'@'172.18.0.1' (using password: NO)
2021-04-20T09:16:50.325379Z       89 Connect    [email protected] on  using TCP/IP
2021-04-20T09:16:50.329894Z       89 Query    select connection_id()
2021-04-20T09:16:50.335222Z       89 Query    SELECT @@VERSION
2021-04-20T09:16:50.339432Z       90 Connect    [email protected] on  using TCP/IP
2021-04-20T09:16:50.339621Z       89 Query    SELECT @@VERSION_COMMENT
2021-04-20T09:16:50.343525Z       90 Query    select connection_id()
2021-04-20T09:16:50.347115Z       90 Query    SHOW DATABASES
2021-04-20T09:16:50.380236Z       90 Query    select TABLE_NAME, COLUMN_NAME from information_schema.columns
                                    where table_schema = 'None'
                                    order by table_name,ordinal_position
2021-04-20T09:16:50.391019Z       90 Query    SELECT CONCAT("'", user, "'@'",host,"'") FROM mysql.user
2021-04-20T09:16:50.415062Z       90 Query    SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE="FUNCTION" AND ROUTINE_SCHEMA = "None"
2021-04-20T09:16:50.432015Z       90 Query    SELECT name from mysql.help_topic WHERE name like "SHOW %"
2021-04-20T09:16:52.572608Z       89 Query    show variables like '%general%'
2021-04-20T09:17:13.532046Z       89 Query    show variables like '%general%'

Slow query log

Slow query log is a log file that records the fast and slow queries of SQL statements. When the query time of an SQL statement exceeds a fixed threshold, the SQL statement will be defined as a slow query SQL statement and recorded in the slow query log file.

The configuration of slow query mainly has the following three parameters.

Whether to enable slow query and slow query log files.

mysql [email protected]:(none)> show variables like '%slow%';
+---------------------------+-------------------------------+
| Variable_name             | Value                         |
+---------------------------+-------------------------------+
| slow_query_log            | ON                            |
| slow_query_log_file       | /var/lib/mysql/mysql.slow.log |
+---------------------------+-------------------------------+
5 rows in set
Time: 0.014s

Slow query time threshold.

mysql [email protected]:(none)> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set
Time: 0.013

Binary log file

The binary log file is used to record the DML statements of MySQL. It records the physical log contents after the operation, and does not record the select, show and other statements in MySQL. The main functions of binary log files are as follows:

  1. Users copy from master to slave. The master server sends the physical log in the binary file to the slave server, and the slave server writes the log to itself.

  2. Used to recover data. Retrieve the operation log before data loss according to the physical log.

It can be configured through the following parameters:

mysql [email protected]:(none)> show variables like '%log_bin%';
Reconnecting...
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
+---------------------------------+--------------------------------+
6 rows in set
Time: 0.015s

log_ Bin whether to open binary log file, log_ bin_ Basename storage directory and log file prefix, log_ bin_ Index stores the log file index (log file name). If the log file does not specify a file name, the native name is used by default.

List of log files.

-rw-r-----   1 mysql root       154 Apr 12 09:31 mysql-bin.000041
-rw-r-----   1 mysql root       154 Apr 12 19:45 mysql-bin.000042
-rw-r-----   1 mysql root   1459325 Apr 17 20:26 mysql-bin.000043
-rw-r-----   1 mysql mysql    24576 Apr 17 22:18 mysql-bin.000044
# cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006

Audit log

The audit log is used to record MySQL network activities and make statistics, analysis and reports on MySQL operation records. Log files belonging to MySQL security monitoring record class.

MySQL itself does not contain this function, and this function is also charged on the MySQL official website. There is no specific demonstration here.

relay logs

Relay log is MySQL master-slave replication and plays an important role on the slave server. When the master server sends binary files to the slave server, the slave server will not execute immediately, but will put them in a specified type of log file. The slave server starts an SQL thread to read the contents of the relay log file and write them to its own data.
Do you know all the log files in MySQL?

PID file

PID is the process file number of a MySQL instance. MySQL is a single process service. When a MySQL instance is started, a PID file will be created.

Socket file

Socket is also a way of MySQL communication. There are two modes of MySQL communication, TCP and socket. TCP is network communication, and services can be deployed to any accessible server. Socket is a file communication mode. It must be on the same server.

#TCP mode
mysql -hxxxx -pxxxx -uxxxx -Pxxx
mysql -uxxxx -pxxxx -s /path/socket

Database and table

The between database and table value is the table structure file, data file and index file in MySQL.
Data table structure of InnoDB storage engine

-rw-r-----  1 mysql root   13650 Apr 13 09:46 wechat_user.frm
-rw-r-----  1 mysql mysql  98304 Apr 17 13:43 wechat_user.ibd

Data table structure of MyISAM storage engine

-rw-r-----  1 mysql mysql      0 Apr 20 17:53 users.MYD
-rw-r-----  1 mysql mysql   1024 Apr 20 17:53 users.MYI
-rw-r-----  1 root  root    8586 Apr 20 17:53 users.frm

Storage engine files

Different storage engines have different implementations. The InnoDB storage engine is divided into two log files: redolog and undolog.

This work adoptsCC agreement, reprint must indicate the author and the link to this article

Love the official account of “two technical circles of cards”.

Recommended Today

Implementation example of go operation etcd

etcdIt is an open-source, distributed key value pair data storage system, which provides shared configuration, service registration and discovery. This paper mainly introduces the installation and use of etcd. Etcdetcd introduction etcdIt is an open source and highly available distributed key value storage system developed with go language, which can be used to configure sharing […]