MySQL Log File Type

Source: Internet
Author: User

MySQL Log File Type

Introduction:

MySQL provides various types of log files that reflect different MySQL information and are necessary to understand them.

1. Error log (Error log)

The error log records all severe warnings and error messages during MySQL Server running, as well as detailed information about each MySQL Server startup and shutdown.

By default, the error log logging function is disabled (MySQL 5.6 is enabled), and the error information is output to the standard error output (Stderr ).

To enable the system logging error log function, you must enable the-log-error option at startup. By default, error logs are stored in the data directory and run the hostname. err command.

You can use -- log-error = file_name or specify the storage location and file name in my. cnf.

To facilitate maintenance, you can generate a new log file through flush logs (you must first mv the original log file ).

2. Binary log (Binary log)

Binary log, that is, binlog. It is also one of the most important logs of MySQL Server.

After we enable the binary log function through -- log-bin = file_name, MySQL records all the queries that modify the database in binary form to the log file.

It also includes the execution time, resources consumed, and related transaction information of each query. Therefore, binlog is transaction secure.

Binary logs are the same as error logs. binlog logging also requires the -- log-bin = file_name or my. in cnf. If file_name is not specified, MySQL-bin is generated in the data directory..

The MySQL-bin.index file is used to record the absolute paths of all Binary logs, ensuring that various MySQL threads can find the desired Binary log file based on it smoothly.

Other binlog-related parameters:

-- Max_binlog_size # set the maximum storage limit of binlog. When the log reaches the upper limit, MySQL will generate a new log to continue recording.

Occasionally, this limit is exceeded because a large transaction is generated when the upper limit is reached. To ensure transaction security, MySQL will not store the same transaction in two binlogs.

-- Binlog-do-db = db_name # records binlog only for this db_name, and ignores the query executed for other databases.

-- Binlog-ignore-db = db_name # is the opposite of the previous parameter. ignore the query for this database and record the binlog of all other databases.

# The two parameters indicate that db_name is not the database where the data updated by the query statement is located, but the database where the query is executed. (I feel very easy to understand. If I say more, it is easy to mess up)

3. Update log (Update log)

The Update log is used by MySQL in earlier versions. Its function is similar to binlog, but it is not recorded in binary format but recorded in simple text format.

MySQL no longer supports log updates since MySQL 5.0.

4. Query log (Query log)

Query all query records in MySQL. This function is enabled through -- log = file_name.

Because the log records all the queries, including all the select statements, and the size is relatively large, it will have a great impact on the performance after enabling, so it is generally not enabled.

This log is generally used to track certain special SQL Performance issues before it is temporarily enabled. The default file name is hostname. log, which is also located in the data directory.

5. Slow query log (Slow query log)

The Slow query log records the query with a long execution time, that is, the Slow query.

Open this feature with -- log-slow-queries = file_name and set the file location and file name. The default file name is hostname-slow.log, And the location is also under the Data Directory.

The slow query log uses a simple text format. You can view the content in the log in a variety of text editors.

Records the statement execution time, consumed time, executed user, connected host, and other related information.

MySQL also provides the MySQLslowdump tool for analyzing slow query logs to help us understand possible performance problems.

6. Innodb redo log (Innodb's online redo log)

Innodb is a transaction-safe storage engine. Its transaction security is mainly guaranteed by the online redo log and the undo information recorded in the tablespace.

The redo log records all the physical changes and transaction information of Innodb. Innodb ensures transaction security under any circumstances through the redo log and undo information.

By default, redo logs of Innodb are stored in the data directory. You can use innodb_log_group_home_dir to change the storage location and use innodb_log_files_in_group to set the number of logs.

This article permanently updates the link address:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.