Comprehensive inventory of important log files in MySQL

Introduction
Log classification
Parameter File
Error log files
Full log file
Slow query log
Binary log files
Audit log
Relay log
PID File
Socket File
Database and Table
Storage engine files

Introduction

This article summarizes and briefly introduces the logs in MySQL without going too deep. The main purpose is to have a systematic understanding of log files in MySQL.

Log classification

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

Snipaste_2021-04-20_16-55-53

Parameter File

The parameter file is the configuration file in MySQL, the my.cnf file in Linux and the my.ini file in Windows. The file content is mainly divided into two modules: server and client. The server module configures MySQL service information, such as the slow query log. 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 files

The error log file records the log information of MySQL from startup, operation and shutdown. For example, failure to connect to MySQL, query command error, SQL execution process, etc. It is very helpful for locating MySQL errors.
The general contents of the file 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 error logging. Just configure the intent log_error in the configuration file in MySQL.

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 checking 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 |
+------------------+---------------------------------+

The configuration item has three values: table, none, and file. If you configure file, the data will be recorded in the log file. If you configure none, the data will not be recorded. If you configure table, a table (called general-log) will be created in the default MySQL database to record the data.

It is not recommended to enable it. Too many log files are recorded, which not only consumes performance but also takes up too much invalid space.

# Log file formatmysqld, 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

The slow query log is a log file that records the speed of SQL query. When the query time of a SQL statement exceeds a fixed threshold, this 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 includes the following three parameters.

Whether to enable slow query and slow query log file.

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 files

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

For master-slave replication, the master server sends the physical log in the binary file to the slave server, and the slave server writes the log to itself.

Used for data recovery. Based on the physical log, retrieve the operation log before data loss.

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 indicates whether to open binary log files, log_bin_basename indicates the directory and log file prefix stored, and log_bin_index indicates the log file index (log file name). If the log file does not specify a file name, the local 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

Audit logs are used to record MySQL network activities and to perform statistics, analysis, and reporting on MySQL operation records. A log file that records MySQL security monitoring information.

MySQL itself does not include this function, and this function is also charged on the MySQL official website. No specific demonstration is given here.

Relay log

The relay log plays an important role in MySQL master-slave replication on the slave server. When the master server sends a binary file to the slave server, the slave server does not execute it immediately, but puts it in a specified type of log file. The slave server then starts an SQL thread to read the contents of the relay log file and write it into its own data.

Snipaste_2021-04-20_17-39-50

PID File

PID is the process ID of a MySQL instance. MySQL is a single-process service. When you start a MySQL instance, a PID file is created.

Socket File

Socket is also a way of MySQL communication. There are two ways of MySQL communication, TCP and Socket. TCP uses network communication and can deploy services on any accessible server. Socket is a file communication method and must be on the same server.

# TCP mode mysql -hxxxx -pxxxx -uxxxx -Pxxx

mysql -uxxxx -pxxxx -s /path/socket

Database and Table

The database and table values are the table structure files, data files and index files in MySQL.
InnoDB storage engine data table structure

-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

MyISAM storage engine data table structure

-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 types of log files: redolog and undolog.

This is the end of this article about a comprehensive inventory of important log files in MySQL. For more relevant MySQL log file content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:

MySQL log file details
Introduction to MySQL log files and log types
mysql binary log file restore database
Where is the MySQL log file? How to modify the MySQL log file location

<<: HTML table mouse drag sorting function

>>: Sample code for realizing book page turning effect using css3

In-depth explanation of JavaScript this keyword

Table of contents

Introduction

Log classification

Parameter File

Error log files

Full log file

Slow query log

Binary log files

Audit log

Relay log

PID File

Socket File

Database and Table

Storage engine files

Recommend