Do you know all the log files in MySQL?

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 the 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, PID and Socket files.

Parameter file

The parameter file is the configuration file in mysql, which is my under Linux CNF file, my. Under Windows INI file. The file content is mainly divided into two modules: server and client. The server module is configured with service information about mysql, such as slow query logs. The client module configures the 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. As long as the intention log is configured in the configuration file in MySQL_ Error.

mysql root@127.0.0.1:(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 addition, deletion, modification and query will be recorded.

mmysql root@127.0.0.1:(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    root@172.18.0.1 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    root@172.18.0.1 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    root@172.18.0.1 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 kind of log file that records the fast and slow query 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 root@127.0.0.1:(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 root@127.0.0.1:(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

After the MySQL select statement and the MySQL binary statement, the contents of the MySQL operation log will not be recorded. 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. For data recovery. Retrieve the operation log before data loss according to the physical log.

It can be configured through the following parameters:

mysql root@127.0.0.1:(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 the network activities of MySQL and make statistics, analysis and report on the operation records of MySQL. 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 the binary file to the slave server, the slave server will not execute immediately, but put it 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 it to its own data.

PID file

PID is the process file number of a MySQL instance. MySQL is a single process service. When starting a MySQL instance, 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.

Keywords: MySQL

Added by ReDucTor on Thu, 03 Mar 2022 20:20:48 +0200