Mysql Log _ MySQL

Mysql logs are stored in all kinds of logs in any database, recording all aspects of the database's work, to help the database administrator track various events that have occurred in the database. In mysql, there are four different types of logs, namely error logs, binary logs, query logs, and slow query logs. these logs record different traces of the database.

1. error log

The error log records information related to mysqld startup and stop, and any serious errors that occur on the server during running. When the database fails to be used properly due to any fault, you can first view this log.

You can use the -- log-error [= file_name] option to specify the location where mysqld (MySQL server) saves the error log file. If the file_name value is not specified, mysqld uses the error log name host_name.err (host_name is the host name) and writes the log file to the directory specified by the DATADIR parameter by default.

2. binary logs

Binary logs record all DDL (Data Definition Language) statements and DML (data manipulation language) statements, but do not include data query statements. The statement is saved as an "event" and describes the data change process. This log plays an extremely important role in data recovery in the event of a disaster.

When the -- log-bin [= file_name] option is enabled, mysqld writes SQL commands containing all the updated data to the log file. If the file_name value is not given, the default name is "-bin" after the host name ".

Because logs are stored in binary format and cannot be directly read, you need to use mysqlbinlog to view them.

1) insert two test records to the emp test table.

2) use the mysqlbinlog tool to view logs. the operations in step (1) are displayed in bold.

3. query logs

The query log records all the statements of the client, while the binary log does not contain statements that only query data.

When mysqld (MySQL server) is started with the -- log [= file_name] or-l [file_name] option, the query log is recorded. Because the Log Query format is plain text, you can directly read it.

1) first, perform some simple operations on the database on the client, including querying and inserting the database.

2) view all client operations recorded in the query log.

Note: log logs record all database operations. for systems with frequent access, this log has a great impact on system performance. we recommend that you disable it in general.

4. slow query logs

The slow query log records all SQL statements whose execution time exceeds the value set by long_query_time (unit: seconds.

When mysqld (MySQL server) is started using the -- log-slow-queries [= file_name] option, slow query logs are recorded.

Like error logs and query logs, slow query log records are in plain text format and can be directly read.

Slow log query setting and reading process:

1) first query the value of long_query_time

2) to facilitate the test, set the slow query time to 2 seconds.

3) execute the following two query statements.

The first query: Because the query time is less than 2 seconds, it will not appear in the slow query log:

The second query: Because the query time is greater than 2 seconds, it should appear in the slow query log:

4) view slow query logs

From the preceding logs, you can find SQL statements that have been queried for more than 2 seconds. SQL statements that have been queried for less than 2 seconds are not displayed in the log. If there are many records in slow query logs, you can use mysqldumpslow to classify and summarize slow query logs.

Note: slow query logs are very helpful for SQL statements that find performance problems in applications. we recommend that you enable this log and view the analysis frequently under normal circumstances.

Summary:

The four most common log types of MySQL:

Error log, binary log, query log, and slow query log

When the system fails, we recommend that you first check the error log to help you quickly locate the cause of the fault.

If you want to record data changes, data backup, data replication, and other operations, the binary log must be enabled to help you recover data and other operations.

If you want to record any operations performed by the database, you need to use -- log to open the query log. This log is disabled by default. generally, we recommend that you do not enable this log to avoid affecting the overall system performance.

To view system performance problems and find SQL statements with performance problems, use -- log-slow-queries to Open slow query logs.