Summary of several common logs in MySQL

Preface:

In the MySQL system, there are many different types of logs. Various logs have their own uses. By analyzing logs, we can optimize database performance, troubleshoot, and even restore data. These different types of logs help us understand the database more clearly, and we will also deal with these logs in daily learning and operation and maintenance. This section will introduce you to the functions and management methods of several common logs in MySQL database.

1. Error log

The error log records information related to mysqld startup and stop, as well as errors and warnings that occur during server operation. When the database crashes unexpectedly or other errors occur, we should check the error log.

The log_error parameter controls whether the error log is written to a file and the file name. By default, the error log is written to the terminal standard output stderr. Of course, it is recommended to specify the log_error parameter to customize the error log file location and name.

# Specify the error log location and name vim /etc/my.cnf 
[mysqld] 
log_error = /data/mysql/logs/error.log

Description of related configuration variables:
log_error={1 | 0 | /PATH/TO/ERROR_LOG_FILENAME}
Defines the error log file. The scope is global or session level and is a non-dynamic variable.

2. Slow query log

The slow query log is used to record query statements whose execution time exceeds the time defined by the long_query_time variable. Through the slow query log, you can find out which query statements have low execution efficiency so as to optimize them.

Several parameters related to slow queries are as follows:

1. slow_query_log: Whether to enable slow query log, the default is 0, can be set to 0, 1.
2. slow_query_log_file: Specifies the slow query log location and name. The default value is host_name-slow.log. An absolute path can be specified.
3. long_query_time: Slow query execution time threshold. If the time exceeds this threshold, the query will be recorded. The default value is 10, in seconds.
4. log_output: The output destination of the slow query log. The default value is file, which means the log is output to a file.
   

By default, the slow query log is not enabled. It is generally recommended to enable it to facilitate slow SQL optimization. The following parameters can be added to the configuration file:

# Slow query log related configuration, you can modify vim /etc/my.cnf according to the actual situation 
[mysqld] 
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 3
log_output = FILE

3. General query log (general log)

The general query log, also known as the universal query log, is the most detailed log in MySQL. This log records all related operations of mysqld. When clients connect or disconnect, the server writes information to this log and records each SQL statement received from the clients. The general query log is useful when you suspect a bug in the client and want to know exactly what the client sent to mysqld.

By default, the general log is disabled. Enabling the general query log will increase disk I/O a lot, so it is not recommended to enable the general query log unless it is for debugging purposes. The relevant parameter configuration is introduced as follows:

# General log related configuration vim /etc/my.cnf 
[mysqld]
general_log = 0 //The default value is 0, which means it is not enabled. You can set it to 1
general_log_file = /data/mysql/logs/general.log //Specify the log location and name

4. Binary log (binlog)

Regarding binary logs, there was an article that introduced them earlier. It records all DDL and DML statements executed by the database (except data query statements such as select and show), records them in the form of events and saves them in binary files. Commonly used for data recovery and master-slave replication.

Several parameters related to binlog are as follows:

• log_bin: Specifies whether binlog is enabled and the file name.
• server_id: Specifies the unique ID of the server. This parameter must be set to enable binlog.
• binlog_format: Specifies the binlog mode. It is recommended to set it to ROW.
• max_binlog_size: Controls the size of a single binary log. When the current log file size exceeds this variable, a switch action is executed.
• expire_logs_days: Controls the number of days to retain binary log files. The default value is 0, indicating that they are not automatically deleted. It can be set to 0~99.
  

Binlog is not enabled by default, but it is generally recommended to enable it, especially when doing master-slave synchronization.

#binlog related configuration vim /etc/my.cnf 
[mysqld]
server-id = 1003306
log-bin = /data/mysql/logs/binlog
binlog_format = row
expire_logs_days = 15

5. Relay log

The relay log is used on the slave server in the master-slave replication architecture. The slave process of the slave server obtains the contents of the binary log from the master server and writes it to the relay log. The IO process then reads and executes the statements in the relay log.

Relay log related parameters are generally set in the slave database. Several related parameters are introduced as follows:

• relay_log: Defines the location and name of the relay log.
• relay_log_purge : Whether to automatically purge relay logs that are no longer needed. The default value is 1 (enabled).
• relay_log_recovery: When the slave goes down, if the relay log is damaged, resulting in some relay logs not being processed, all unexecuted relay logs will be automatically abandoned and the logs will be retrieved from the master again, thus ensuring the integrity of the relay log. This feature is disabled by default. You can enable it by setting the value of relay_log_recovery to 1.
  

The default location of relay log is in the directory of data files. The file name is host_name-relay-bin. You can customize the file location and name.

# Relay log related configuration, set vim from the library side /etc/my.cnf 
[mysqld]
relay_log = /data/mysql/logs/relay-bin
relay_log_purge = 1
relay_log_recovery = 1

Summarize:

This article mainly describes the purpose and setting methods of several types of logs in MySQL. It should be noted that if the absolute path is not specified, the above-mentioned types of logs are saved in the data directory by default. We can also create a new log directory dedicated to saving these logs. There are still redo log and undo log that have not been explained yet, so I will leave them for the next article.

This is the end of this article about several common logs in MySQL. For more relevant MySQL common log 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!