Introduction to error_log in MySQL

Introduction to MySQL log types

MySQL logs can be roughly divided into three types, error log (error_log), query log (query_log), and binary log (binary_log):

• Query log (query log): Generally, query log can be divided into two types, general query log (general query log) and slow query log (slow query log); among them, general query log The query log can be used to obtain relevant information about each client connection and the SQL statements executed on the database; the slow query log records statements whose SQL statement time exceeds the preset long_query_time. In the case of large amounts of data, you can see Check which statements in the slow query log need to be optimized.

• Binary log (binary_log): Simply put, the binary log records the operation of MySQL updates. The main purpose is to restore the database to the point of database failure as much as possible, because the binary log contains All updates made after backup.

The three types of logs have different functions and require different methods for configuration. Here we will talk about the configuration method of the ordinary error log first, and the other two will be added in the future.

error_log

Similar to the alert in Oracle, MySQL's error log is used to record error information, but error records not only error information, but also error information about the service process. Be logged (critical level); if the mysqld process finds that some tables need to be automatically checked or repaired, relevant information will also be thrown to the log.

Configuration method

1. Find the configuration file /etc/my.cnf, if not foundfind / -type f -name 'my.cnf ' Just search globally

2. Write the error log parameters into the configuration file

[mysqld_safe]log-error=/var/lib/mysql/mysql.err

3. Another method
You can add logs when MySQL is started on the command line The loading parameter --log-output, of which --log-output also has three optional parameters to specify the log file output method:
- TABLE: record the log to In the database table
- FILE: record the log in the file
- NONE: do not record

Example

• Enable error log and record the log file To the database table and log file: <br/>--log-output=TABLE,FILE --error_log

• Enable slow query log and normal query log, And record their logs in the table: --log-output=TABLE --general_log --slow_query_log

• Enable slow query log and record to log file , and specify the output path: --log-output=FILE --slow_query_log --slow_query_log_file=/var/lib/mysql/- mysql_slow.log

After successful setting, enter to view:

mysql> show variables like 'log_error';
+---------------+---------------------+| Variable_name | Value               |
+---------------+---------------------+| log_error     | /var/log/mysqld.log |
+---------------+---------------------+1 row in set (0.00 sec)

[root@localhost mysql]# tailf /var/log/mysqld.log
2017-08-07T12:32:54.258884Z 0 [Note] IPv6 is available.
2017-08-07T12:32:54.258892Z 0 [Note]   - '::' resolves to '::';
2017-08-07T12:32:54.258908Z 0 [Note] Server socket created on IP: '::'.
2017-08-07T12:32:54.259622Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2017-08-07T12:32:54.260814Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170807  8:32:54
2017-08-07T12:32:54.266749Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.19'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2017-08-07T12:32:54.266772Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check. 
2017-08-07T12:32:54.266774Z 0 [Note] Beginning of list of non-natively partitioned tables
2017-08-07T12:32:54.318211Z 0 [Note] End of list of non-natively partitioned tables

The above is the detailed content of Introduction to error_log in MySQL. For more information, please follow other related articles on the PHP Chinese website!