This is the 9th day of my participation in the August Wen Challenge.More challenges in August

Focus on PHP, MySQL, Linux and front-end development, thank you for your attention!! The article is organized on GitHub, mainly including PHP, Redis, MySQL, JavaScript, HTML&CSS, Linux, Java, Golang, Linux and tool resources and other relevant theoretical knowledge, interview questions and practical content.

Introduction to the

This article is a summary and brief introduction to MySQL logging without going too deep. The main goal is to have a systematic understanding of log files in MySQL. Each type of log file will be analyzed and summarized later.

Classification of log

Log files in MySQL, Configuration files, error log files, binary log, Slow-query-log, Genera log, Audit log, database files & data table files, storage engine files, Relay logs Log, process file (PID), and Socket file.

Parameter file

Parameter files are MySQL configuration files, my.cnf files in Linux, my.ini files in Windows. The file is divided into two modules: Server and client. The server module is configured with information about MySQL services, such as slow query logs. The client module configures information about the MySQL client connection, such as the port number of the client connection.

The file format is 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 = From the '+'

Copy the code

Error log file

The error log file records the log information from MySQL startup, running, and shutdown. For example, MySQL connection failure, query command error, SQL execution flow, and so on. This is very helpful in locating MySQL errors.

The general contents of the document are as follows:

Version: '5.7.28 - log' socket: '/ var/run/mysqld/mysqld. The 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, 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)Copy the code

How to start error logging. Simply configure intent log_error in the MySQL configuration file.

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
Copy the code

Full log file

The full log file records all SQL operation logs of MySQL. Operations such as adding, deleting, modifying, and checking are recorded.

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

The configuration item has three values, table, None, and file. If file is configured, the log file will be recorded. If none is configured, the log file will not be recorded. If table is configured, the default MySQL data will be logged by creating a table named general-log.

This function is not recommended because too many log files consume not only performance but also invalid space.


Log file formatMysqld, Version: 5.7.28-log (MySQL Community Server (GPL)). Startedwith:
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. 01. 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. 01. 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. 01. 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."The '@'",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%'
Copy the code

Slow Query logs

Slow query log is a log file that is recorded to determine whether SQL statement queries are fast or slow. When the query time of an SQL statement exceeds a fixed threshold, the SQL statement is defined as a slow query statement and recorded in the slow query log file.

The configuration of slow query consists of the following three parameters.

Whether to enable slow query and slow query of 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
Copy the code

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
Copy the code

Binary log file

The binary log file is used to record DML statements of the MySQL database. The physical log content after operations is recorded. The select and show statements of the MySQL database are not recorded. The binary log file provides the following functions:

  1. The master server sends physical logs in binary files to the slave server, and the slave server writes logs to itself.

  2. Used for data recovery. Retrieve operation logs before data loss based on physical logs.

You can set the parameters as follows:

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
Copy the code

Log_bin_basename Indicates the directory and prefix of the log file. Log_bin_index Indicates the index of the log file (name of the log file). 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
Copy the code
# cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
Copy the code

The audit log

Audit logs are used to record the network activities of the MySQL database and collect statistics, analyze, and report the MySQL operation records. This log file belongs to the MySQL security monitoring log file.

MySQL itself does not include this feature, and this feature is also available on the MySQL website for a fee. I’m not going to do a demo here.

Relay log

Relay logging is an important role in the primary/secondary replication of MySQL on the secondary server. When the master sends the binary file to the slave server, the slave server does not execute it immediately, but instead places it in a specified class 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.

The 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 is created.

The Socket file

Sockets are also a way for MySQL to communicate. MySQL has two communication modes: TCP and Socket. TCP is network-based communication, and services can be deployed to any accessible server. The Socket is used to communicate with outgoing files and must reside on the same server.

# TCP model
mysql -hxxxx -pxxxx -uxxxx -Pxxx
Copy the code
mysql -uxxxx -pxxxx -s /path/socket
Copy the code

Databases and tables

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
Copy the code

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
Copy the code

Storage engine file

Different storage engines are implemented differently. InnoDB storage engine is divided into redolog and undolog log files. Redolog is a physical log, and ubdolog is a logical log.