MySQL log file detailed

This article mainly introduces the MySQL log file, this article explains the error log, binary log, General query log, slow query log, InnoDB online redo log, update log and other log types and role introduction, need friends can refer to the following

Overview

Log files are an important part of the MySQL database. MySQL has several different log files, usually including error log files, binary logs, common logs, slow query logs, and so on. These logs can help us locate events within MYSQLD, database performance failures, record changes in data history, user recovery databases, and so on. This article mainly describes the various log files of MySQL.

MySQL log file classification

1. Error log

2. Binary log (Binary log & Binary log Index)

3. General Query Log

4. Slow query logs (slow query log)

5.Innodb online redo logs (InnoDB redo log)

6. Update Log

Error log

The error log records all the more serious warnings and error messages that MYQL server is running, and the MySQL

Details for each startup and shutdown of the Server. By default, the system logs error log is turned off, error messages are output to standard error output (stderr), and if you want to turn on the system logging error log, you need to turn on the-log-error option at startup. The default location of the error log is named in Hostname.err under the data directory. However, you can modify its storage directory and file name by using the command:-log-error[=file_name].

To facilitate maintenance, there are times when you want to back up the contents of the error log and start recording again, and you can use the MySQL FLUSH LOGS command to tell MySQL to back up the old log files and generate new log files. The backup file name ends with ". old".

Binary log

The binary log, which we often say is binlog, is one of the most important logs in MySQL Server.

When we open the recording function through "-log-bin[=file_name", MySQL logs all query that modifies database data to the log file in binary form. Of course, the log is not limited to the query statement, but also includes the time each query executes, the resources it consumes, and the related transaction information, and BI nl is OG for transaction security.

As with the error log, the Binlog record feature requires an explicit designation of the "-log-bin[=file_name]" parameter to be opened, and if file_name is not specified, it is recorded in the Data directory as mysql-bin.** (* represents a number between 0~ 9. To represent the ordinal of the log).

Binlog also has some additional option parameters:

"-max_binlog_size" sets the maximum storage limit for binlog, and when the log reaches that limit, MySQL creates a new journal to resume recording. Occasionally, however, there are binlog that exceed this setting, typically because a larger transaction is generated when the cap is about to be reached, and MySQL does not record the same transaction separately into two binlog in order to ensure transaction security.

The "-binlog-do-db=db_name" parameter explicitly tells MySQL that you need to record a binlog for a (db_name) database, and if you have an explicit designation for the "-binlog-do-db=db_name" parameter, MySQL ignores the other Database, and only the query that executes against the specified database is logged.

"-binlog-ignore-db=db_name" is the exact opposite of "-binlog-do-db=db_name", which explicitly specifies that the Binlog record of a (db_name) database be ignored, and when this parameter is specified, MySQL records the specified Binlog of all databases outside the database.

"-binlog-ignore-db=db_name" and "-binlog-do-db=db_name" two parameters have a common concept that you need to understand that the db_name in the parameter is not the database in which the query statement is updated, but rather the execution The database in query that is currently in place. Regardless of which database data is updated, MySQL only compares the database name of the database where the current connection is located (through the database where the use db_name is switched) and the parameter, without parsing the database where the data is updated by the query statement.

The function of the Mysql-bin.index file (binary log index) is to record the absolute path of all binary logs to ensure that the MySQL thread can successfully find all the required binary log files according to it.

Common query Log

The query log records all of the queries in MySQL and opens the feature through "-log[=fina_name]". By

To record all of the query, including all the Select, the volume is larger, the performance has a greater impact after opening, so please use this function with caution. Typically, it is only used to track certain specific SQL performance issues to temporarily turn on the feature. The default query log file name is Hostname.log.

Slow query log

As the name suggests, the slow query log records the execution of a long time query, which we often say slow

Query by setting-log-slow-queries[=file_name] to open the feature and set the record location and file name, the default file name is Hostname-slow.log, and the default directory is the data directory.

The slow query log is a simple text format that you can use to view the contents of a variety of text editors. It records the execution of the statement at the moment, the execution of the time spent, execute the user, connect the host and other related information. MySQL also provides a tool program Mysqlslowdump that is designed to analyze full query logs to help database administrators solve possible performance problems.

InnoDB's online redo log

Innodb is a transaction-safe storage engine whose transaction security is primarily through online redo of logs and records

The undo information in the tablespace is guaranteed. All physical changes and transaction information made by Innodb are recorded in the redo log, Innodb guarantees transaction security under any circumstances through redo log and undo information. Innodb's redo log is also stored in the data directory by default, and you can change the location of the settings log by Innodb_log_group_home_dir to set the number of logs by Innodb_log_files_in_group.

Update log

The update log is used by the older version of MySQL and is essentially similar in functionality to Binlog, except that it is not

Binary format to record content in a simple text format. Since MySQL has added binlog functionality, the update log is rarely used. Starting with version 5.0, MySQL is no longer supporting the update log.

Summarize

This article collates the various log files in MySQL, each with its specific purpose, which will be described in future articles.