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:
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.
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.