Preface:

In MySQL systems, there are many different types of logs. Each type of log has its own purpose. By analyzing logs, you can optimize database performance, troubleshoot problems, and even restore data. These different types of logs help us to have a clearer understanding of the database, 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 the MySQL database.

1. Errorlog (errorlog)

The error log records information about starting and stopping mysqld, as well as errors and warnings that occur while the server is running. When the database unexpectedly goes down or some other error occurs, we should go through 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 location and name of the error log file.

Error log location and name vim/etc/my.cnf 
[mysqld] 
log_error = /data/mysql/logs/Error. log Related configuration variables are described as log_error={1 | 0 | /PATH/TO/ERROR_LOG_FILENAME} defines an error log file. The scope is global or session level and is a non-dynamic variable.Copy the code

2. Slow query log

The slow query log is used to record queries that have taken longer to execute than the length defined by the variable long_query_time. By slowly querying logs, you can find out which query statements are executed inefficiently for optimization.

Several parameters related to slow query are as follows:

  • Slow_query_log: specifies whether to enable slow query logs. The default value is 0. The value can be 0, 1.
  • Slow_query_log_file: specifies the location and name of slow query logs. The default value is host_name-slow.log, and the absolute path can be specified.
  • Long_query_time: specifies the time threshold for slow query execution. When this time is exceeded, the system records the value. The default value is 10, in seconds.
  • Log_output: indicates the output target of slow query logs. The default value is file, that is, output to a file.

By default, slow query log is disabled. In general, you are advised to enable this function for slow SQL optimization. You can add the following parameters to the configuration file:

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

3. General Log

The general query log, also known as the general query log, is the most detailed log in MySQL. This log records all operations related to mysqld. When clients connect or disconnect, the server writes information to this log and records each SQL statement received from clients. The generic 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 function is disabled. Enabling the general log function increases disk I/O. Therefore, you are not advised to enable the general log function for debugging and troubleshooting purposes. The related parameters are described as follows:

Configure vim for general log/etc/my.cnf 
[mysqld]
general_log = 0 //The default value is0, that is, you can set it to1
general_log_file = /data/mysql/logs/general.log //Specify the log location and nameCopy the code

4. Binlog

Binary logging was described in an earlier article. It records all DDL and DML statements executed by the database (except for data query statements SELECT, show, and so on) in the form of events and stored in binary files. It is used for data recovery and primary/secondary 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 a server. This parameter must be set when binlog is enabled.
  • Binlog_format: Specifies the binlog mode. ROW is recommended.
  • Max_binlog_size: controls the size of a single binary log file. If the size of the current log file exceeds this variable, the switch is performed.
  • Expire_logs_days: specifies the retention days of binary log files. The default value is 0, indicating that binary log files will not be automatically deleted. The value ranges from 0 to 99.

Binlog is disabled by default, but in general, it is recommended to enable it, especially for 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
Copy the code

5. Relay log

Relay logs are used on the slave server in the master-slave replication architecture. The slave process of the slave server obtains binary logs from the master server and writes them to the relay log. Then the I/O process reads and executes the statements in the relay log.

Relay log parameters are generally set in the slave library. The parameters are described as follows:

  • Relay_log: Defines the location and name of the relay log.
  • Relay_log_purge: Whether to automatically purge unwanted relay logs. The default value is 1(enabled).
  • Relay_log_recovery: If the slave slave database breaks down and some relay logs are not processed because the relay logs are damaged, the slave automatically abandons all unexecuted relay logs and obtains the logs from the master again to ensure the integrity of the relay logs. This function is disabled by default. To enable this function, set relay_LOG_recovery to 1.

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

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

Conclusion:

If you do not specify an absolute path for these types of logs, they will be saved in the data directory by default. You can also create a new log directory for these types of logs. There are also redo logs and undo logs that I haven’t covered, but I’ll leave them for the next article.