Mysql log file type

Brief introduction:

MySQL provides several types of log files that reflect different MySQL information, and it is necessary to understand them.

1. Error log

The error log records all the more severe warnings and error messages during MySQL server operation, as well as details about each startup and shutdown of MySQL server.

By default, the system logging error log is turned off (the Mysql 5.6 I am using is turned on) and the error message is output to the standard error output (STDERR).

When you need to turn on the system logging error log feature, you need to turn on the-log-error option at startup. The error log is stored by default in the data directory, with the Hostname.err command.

You can use--log-error=file_name or specify its location and file name in MY.CNF.

For ease of maintenance, a new log file can be generated by flush logs (the original log file should be first mv a bit).

2. Binary log (binary logs)

Binary log, which is binlog. is also one of the most important logs in Mysql Server.

After we have opened the binary log function via--log-bin=file_name, Mysql logs all query changes to the database to the log file in binary form.

It also includes the time that each query executes, the resources it consumes, and the related transaction information, so Binlog is transaction-safe.

As with the error log, the Binlog logging function needs to be--log-bin=file_name or my.cnf specified to open, and if no file_name is specified, a mysql-bin.******* log file is generated in the data directory.

The function of the Mysql-bin.index file is to record the absolute path of all binary logs, ensuring that all Mysql threads can successfully find the binary log file required by it.

Other parameters related to Binlog:

--max_binlog_size # # To set the maximum storage limit for Binlog, when the log reaches that limit, Mysql will regenerate a new journal to start recording.
Occasionally, this limit is exceeded because a larger transaction is generated when the upper limit is reached, and Mysql does not store the same transaction in two Binlog in order to ensure transaction security.

--binlog-do-db=db_name # # Only records Binlog for the db_name and ignores query execution against the rest of the database.

--binlog-ignore-db=db_name # # In contrast to the previous parameter, ignore query for the database and record the binlog of all the remaining databases.

# # These two parameters refer to the Db_name not the database in which the query statement was updated, but the database in which the query was executed. (Feel good to understand, say more easily disorderly)

3. Update log (Changelog)

The update log is used by Mysql on older versions, and is similar in functionality to Binlog, except that it is not recorded in binary format but rather is recorded in simple text format.

Starting with MySQL 5.0, MySQL no longer supports the update log.

4. Query log

The query log records all of the query in Mysql and opens the feature via--log=file_name.

Since the log records all of the query, including all the Select, the volume is relatively large, open will have a greater impact on performance, so generally do not open.

This log is typically used to track some special SQL performance issues before it is briefly turned on. The default file name is Hostname.log and the location is in the data directory.

5, Slow query log (slow query logs)

Slow query logging is the execution of a long time query, that is, Slow query.

Open this feature by--log-slow-queries=file_name and set the file location and file name, the default file name is Hostname-slow.log, the location is also in the data directory.

The slow query log is a simple text format that allows you to view the content in a variety of text editors.

It records the time of execution of the statement, the duration of the execution, the user being executed, the connection host, and other related information.

Mysql also provides tool mysqlslowdump for specialized analysis of slow query logs to help us understand possible performance issues.

6, Innodb Redo log (Innodb's online redo logs)

Innodb is a transaction-safe storage engine whose transactional security is ensured primarily through online redo logs and the undo information recorded in the tablespace.

All physical changes and transaction information made by Innodb are recorded in the Redo log, and Innodb guarantees transaction security under any circumstances through redo logs and undo information.

Innodb Redo log is also default in the data directory, you can change its storage location by Innodb_log_group_home_dir, and set the number of logs by Innodb_log_files_in_group.

Mysql log file type