MySQL log management details _ MySQL

This article describes MySQL log management in detail. This article describes log types, log functions, and common server variables related to MySQL logs, if you need it, you can refer to the log file. it is very important for a server. it records the running information of the server. many operations are written to the log file every day, the log file can monitor the running status of the server and view the server performance. It can also handle server errors and faults. There are six different types of logs in MySQl.

I. log types

----> 1. error log: records the problems that occur during startup, running, or stop, and generally records the warning information.
----> 2. general query logs: records the established client connection and executed statements.
----> 3. slow query log: records all queries whose execution time exceeds long_query_time seconds or queries that do not use indexes, which can help us locate server performance problems.
----> 4. binary log: any operation that causes or may cause database changes, mainly used for replication and instant recovery.
----> 5. relay logs: events copied from the binary log file of the master server and saved as log files.
----> 6. transaction logs: logs generated when transactions are executed by InnoDB and other transaction-supporting storage engines.

MySQL has a lot of log file environments than Variables. you can use the following command to view them:

mysql> show global variables like '%log%';+-----------------------------------------+-----------------------------------------+| Variable_name              | Value                  |+-----------------------------------------+-----------------------------------------+| back_log                | 50                   || binlog_cache_size            | 32768                  || binlog_direct_non_transactional_updates | OFF                   || binlog_format              | MIXED                  || binlog_stmt_cache_size         | 32768                  || expire_logs_days            | 0                    || general_log               | OFF                   || general_log_file            | /mydata/data1/localhost.log       || innodb_flush_log_at_trx_commit     | 1                    || innodb_locks_unsafe_for_binlog     | OFF                   || innodb_log_buffer_size         | 8388608                 || innodb_log_file_size          | 5242880                 || innodb_log_files_in_group        | 2                    || innodb_log_group_home_dir        | ./                   || innodb_mirrored_log_groups       | 1                    || log                   | OFF                   || log_bin                 | ON                   || log_bin_trust_function_creators     | OFF                   || log_error                | /mydata/data1/localhost.localdomain.err || log_output               | FILE                  || log_queries_not_using_indexes      | OFF                   || log_slave_updates            | OFF                   || log_slow_queries            | OFF                   || log_warnings              | 1                    || max_binlog_cache_size          | 18446744073709547520          || max_binlog_size             | 1073741824               || max_binlog_stmt_cache_size       | 18446744073709547520          || max_relay_log_size           | 0                    || relay_log                |                     || relay_log_index             |                     || relay_log_info_file           | relay-log.info             || relay_log_purge             | ON                   || relay_log_recovery           | OFF                   || relay_log_space_limit          | 0                    || slow_query_log             | OFF                   || slow_query_log_file           | /mydata/data1/localhost-slow.log    || sql_log_bin               | ON                   || sql_log_off               | OFF                   || sync_binlog               | 0                    || sync_relay_log             | 0                    || sync_relay_log_info           | 0                    |+-----------------------------------------+-----------------------------------------+41 rows in set (0.00 sec)

II. log functions

1. error log
Error logs mainly record the following types of logs:
----> Server startup and shutdown information
----> Error message during server running
----> Information generated when the event scheduler runs an event
----> Information generated when the process on the slave server is started
Error log definition:
You can use the-log-error [= file_name] option to specify the location where mysqld saves the error log file. If the file_name value is not specified, mysqld uses the error log name host_name.err and writes the log file to the data directory. If you execute flush logs, the error log uses-old to rename the suffix and mysqld to create a new empty log file. (If the-log-error option is not provided, it will not be renamed ).
The error log can be defined by the preceding two variables:

Error log file: log_error
Enable warning information: log_warnings (enabled by default)

mysql> show global variables like 'log_error';+---------------+-----------------------------------------+| Variable_name | Value                  |+---------------+-----------------------------------------+| log_error   | /mydata/data1/localhost.localdomain.err |+---------------+-----------------------------------------+1 row in set (0.00 sec)mysql> show global variables like 'log_warnings';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_warnings | 1   |+---------------+-------+1 row in set (0.00 sec)

2. query logs

Start switch: general_log = {ON | OFF}
Log file variable: general_log_file [=/PATH/TO/file]
Global log switch: log = {ON | OFF}
Record Type: log_output = {TABLE | FILE | NONE}
Log_output defines the log output format, which can be a table or a file. if it is set to NONE, logs are not enabled. therefore, to enable general query logs, you must configure general_log = ON at least, log_output = {TABLE | FILE }. If general_log_file is not specified, the default name is host_name.log. Generally, because the query usage is relatively large, enabling the write log file requires a large number of I/O operations on the server, which greatly reduces the performance of the server. Therefore, it is disabled by default.

mysql> show global variables like 'general_log';+---------------+-------+| Variable_name | Value |+---------------+-------+| general_log  | OFF  |+---------------+-------+1 row in set (0.00 sec)mysql> show global variables like 'general_log_file';+------------------+-----------------------------+| Variable_name  | Value            |+------------------+-----------------------------+| general_log_file | /mydata/data1/localhost.log |+------------------+-----------------------------+1 row in set (0.01 sec)

Run the following command to enable general_log:

mysql> set global general_log=1;Query OK, 0 rows affected (0.00 sec)mysql> show global variables like 'general_log';+---------------+-------+| Variable_name | Value |+---------------+-------+| general_log  | ON  |+---------------+-------+1 row in set (0.00 sec)

3. slow query logs

Query timeout: long_query_time
Start slow query log: log_slow_queries = {YES | NO}
Start slow query log: slow_query_log
Log File: slow_query_log_file [= file_name]
If the slow_query_log = ON option is enabled for MySQL, the query whose execution time exceeds long_query_time will be recorded (the initial time when the table is locked is not counted as the execution time ). If the file_name value is not provided for the log file, the default value is the host name and the suffix is-slow. log. If the file name is provided but not the absolute path name, the file is written to the data directory.

mysql> show global variables like '%slow_query_log%';+---------------------+----------------------------------+| Variable_name    | Value              |+---------------------+----------------------------------+| slow_query_log   | OFF               || slow_query_log_file | /mydata/data1/localhost-slow.log |+---------------------+----------------------------------+2 rows in set (0.00 sec)

Slow query is not enabled by default. we recommend that you enable it for server optimization.

mysql> show global variables like 'long_query_time';+-----------------+-----------+| Variable_name  | Value   |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.00 sec)

The default value for a long time is 10 seconds. if the time limit is exceeded, the query is slow.

4. binary log

Binary log startup switch: log-bin [= file_name]
You must manually specify this parameter in version 5.6 or later. In versions earlier than 5.6, the default file_name is $ datadir/mysqld-binlog. binary logs are used to record all statements for changing data and are mainly used for copy and instant recovery. The main purpose of the binary log is to update the database as much as possible (instant recovery) when the database fails, because the binary log contains all updates made after the backup, binary logs are also used to record all statements that will be sent to the slave server on the master replication server.
The tool for viewing binary logs is mysqlbinlog.
Binary log format:
--> Statement-based: statement
--> Row-based: row
--> Mixed mode: mixed
Because both statement-based and line-based log formats have their own advantages, the binary log files used by MySQL are mixed binary logs, and the built-in policy will automatically select the best format.
Binary log event:
--> Generation Time: starttime
--> Relative position: position
Binary log file:
--> Index file
--> Binary log file
A mysql-bin.index in the data directory is an index file, a file starting with mysql-bin and ending with a number is a binary log file.
Log scrolling:
The scrolling method of MySQL is not the same as that of other logs. a new log with a new ID greater than 1 is created to record the latest log, and the original log name is not changed. Logs are automatically rolled every time you restart the MySQL service.
In addition, if you need to manually scroll, use the command:

Mysql> flush logs; Query OK, 0 rows affected (0.02 sec) mysql> show master status; # view the currently used binaries + -------------------- + ---------- + -------------- + bytes + | File | Position | Binlog_Do_DB | bytes | + bytes + ------------ + -------------- + ---------------- + | mysql-bin.000033 | 107 | | + ------------------ + ---------- + -------------- + ------------------ + 1 row in set (0.00 sec)

Its default position starts from 107.

Mysql> show binary logs; # View all the binaries + ------------------ + ----------- + | Log_name | File_size | + ------------------ + ------------- + | mysql-bin.000020 | 107 | mysql-bin.000021 | 107 | mysql-bin.000022 | 50676 | mysql-bin.000023 | 150 | mysql-bin.000024 | 621 | mysql-bin.000025 | 107 | mysql-bin.000026 | 4509 | mysql-bin.000027 | 150 | mysql-bin.000028 | 150 | mysql-bin.000029 | 150 | mysql-bin.000030 | 357 | mysql-bin.000031 | 107 | mysql-bin.000032 | 150 | mysql-bin.000033 | 107 | + ------------------ + ----------- + 14 rows in set (0.00 sec) mysql> use mysql_shiyan; Database changedmysql> insert into department (dpt_name) values ('feiyu '); Query OK, 1 row affected (0.00 sec) mysql> show master status; + metric + ---------- + -------------- + ---------------- + | File | Position | Binlog_Do_DB | usage | + usage + ---------- + -------------- + usage + | mysql-bin.000033 | 331 | + usage + -------- + -------------- + ------------------ + 1 row in set (0.00 sec)

After the data is inserted, the position has changed.

Mysql> insert into department (dpt_name) values ('feiyu1'); # insert another data Query OK, 1 row affected (0.00 sec) mysql> show binlog events in 'MySQL-bin.000033 '; # view the content recorded in the binary file + ------------------ + ----- + ------------- + ----------- + signature + | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | + signature + ----- + ------------- + ----------- + ------------- + ------------------------------------------------------------------------- + | mysql-bin.000033 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.36-log, Binlog ver: 4 | mysql-bin.000033 | 107 | Query | 1 | 183 | BEGIN | mysql-bin.000033 | 183 | Query | 1 | 304 | use 'MySQL _ shiyan '; insert into department (dpt_name) values ('feiyu ') | mysql-bin.000033 | 304 | Xid | 1 | 331 | COMMIT/* xid = 12 */| mysql-bin.000033 | 331 | Query | 1 | 407 | BEGIN | mysql-bin.000033 | 407 | Query | 1 | 529 | use 'MySQL _ shiyan '; insert into department (dpt_name) values ('feiyu1 ') | mysql-bin.000033 | 529 | Xid | 1 | 556 | COMMIT/* xid = 14 */| + ------------------ + ----- + --------------- + ----------- + rows + 7 rows in set (0.00 sec) mysql> show binlog events in 'MySQL-bin.000033 'from 407; # You can also view the binary file + ------------------ + ----- + ------------ + ----------- + ------------- + signature + | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | + ------------------ + ----- ------------ + ----------- + ------------- + hour + | mysql-bin.000033 | 407 | Query | 1 | 529 | use 'MySQL _ shiyan '; insert into department (dpt_name) values ('feiyu1 ') | mysql-bin.000033 | 529 | Xid | 1 | 556 | COMMIT/* xid = 14 */| + ------------------ + ----- + ------------ + ----------- + ------------- + rows + 2 rows in set (0.00 sec) mysql> purge binary logs to 'MySQL-bin.000025'; # Delete the log file Query prior to a sequence number. OK, 0 rows affected (0.04 sec) mysql> show binary logs; + ------------------ + ----------- + | Log_name | File_size | + ------------------ + ----------- + | mysql-bin.000025 | 107 | mysql-bin.000026 | 4509 | mysql-bin.000027 | 150 | mysql-bin.000028 | 150 | | mysql-bin.000030 | 357 | mysql-bin.000031 | 107 | mysql-bin.000032 | 150 | mysql-bin.000033 | 556 | + ------------------ + ----------- + 9 rows in set (0.00 sec)

Run the mysqlbinlog command to view the binary log content:
Basic syntax:

Mysqlbinlog [options] log-files
Common options (similar to the number of byte offsets ):
-- Start-position: start position
-- Stop-position: end position
-- Start-datetime 'yyyy-mm-dd hh: mm: SS': start time
-- Stop-datetime 'yyyy-mm-dd hh: mm: SS': End Time

←#4#root@localhost ~ →mysqlbinlog --start-position 407 --stop-position 556 mysql-bin.000033/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;mysqlbinlog: File 'mysql-bin.000033' not found (Errcode: 2)DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

5. relay logs

This will be discussed later.

6. transaction logs

The transactional storage engine is used to ensure atomicity, consistency, isolation, and durability. it is not written to the data file immediately, but to the transaction log.
Innodb_flush_log_at_trx_commit:
----> 0: Synchronize data every second and perform the disk flush operation;
----> 1: synchronize each transaction and perform the disk flush operation;
----> 2: synchronize each transaction, but do not perform the disk flush operation;

mysql> show global variables like 'innodb_flush_log_at_trx_commit';+--------------------------------+-------+| Variable_name         | Value |+--------------------------------+-------+| innodb_flush_log_at_trx_commit | 1   |+--------------------------------+-------+1 row in set (0.00 sec)

III. description of common server variables related to MySQL logs:

Expire_logs_days = {0 .. 99}

Sets the expiration days of binary logs. binary log files that exceed the specified days are automatically deleted. The default value is 0, indicating that the expired automatic deletion function is not enabled. If this function is enabled, automatic deletion usually occurs at MySQL startup or FLUSH logs. It has a global scope and can be used in configuration files and is a dynamic variable.

General_log = {ON | OFF}

Set whether to enable Log Query. the default value is determined by whether the-general_log option is used when mysqld is started. If this option is enabled, the output location is defined by the-log_output option. if the value of log_output is set to NONE, the query log is enabled, and no log information is recorded. It has a global scope and can be used in configuration files and is a dynamic variable.

General_log_file = FILE_NAME

The name of the log file to query. the default value is "hostname. log ". It has a global scope and can be used in configuration files and is a dynamic variable.

Binlog-format = {ROW | STATEMENT | MIXED}

Specifies the binary log type. the default value is STATEMENT. we recommend that you change it to MIXED. If the binary log format is set but the binary log is not enabled, the warning log is generated and recorded in the error log at MySQL startup. Global or session is used in configuration files and is a dynamic variable.

Log = {YES | NO}

Whether to enable logging of all statements in general query log, which is usually OFF by default. MySQL 5.6 has been deprecated.

Log-bin = {YES | NO}

Whether to enable binary log. if The-log-bin option is set for mysqld, the value is ON; otherwise, the value is OFF. It is only used to show whether binary logs are enabled, and does not reflect the set value of log-bin. It is a non-dynamic variable at the global level.

Log_bin_trust_function_creators = {TRUE | FALSE}

This parameter is valid only when binary logs are enabled. it is used to control whether to prohibit the creation of storage functions under the binary log condition of insecure event records when a storage function is created. The default value is 0, indicating that unless you have the SUPER permission in addition to the create routing or alter routine permissions, you are not allowed to CREATE or modify a storage function, you must also use the DETERMINISTIC attribute when creating a function. Otherwise, the reads SQL data or no SQL attribute is attached. If the value is set to 1, these restrictions are not enabled. The scope is global. it can be used in configuration files and is a dynamic variable.

Log_error =/PATH/TO/ERROR_LOG_FILENAME

Define the error log file. It can be used in configuration files at the global or session level and is a non-dynamic variable.

Log_output = {TABLE | FILE | NONE}

Defines the storage methods for general query logs and slow query logs, which can be TABLE, FILE, NONE, or a combination of TABLE and FILE (separated by commas). The default value is TABLE. If NONE appears in the combination, other settings will be invalid. at the same time, no log information is recorded whether or not the log function is enabled. The scope is global. it can be used in configuration files and is a dynamic variable.

Log_query_not_using_indexes = {ON | OFF}

Set whether to record query operations without indexes to slow query logs. The scope is global. it can be used in configuration files and is a dynamic variable.

Log_slave_updates

Used to set whether the slave server in the replication scenario records the update operations received from the master server into the binary log of the local machine. The binary log function must be enabled on the slave server.

Log_slow_queries = {YES | NO}

Whether to record slow query logs. A slow query is an event that exceeds the scheduled duration set by the long_query_time parameter. MySQL 5.6 changed this parameter to slow_query_log. The scope is global. it can be used in configuration files and is a dynamic variable.

Log_warnings = #

Set whether to record warning information into the error log. The default value is 1, indicating that it is enabled. you can set it to 0 to disable it; when the value is greater than 1, it indicates that the "failed connection" and "access denied" errors generated when the new connection is initiated are also recorded in the error log.

Long_query_time = #

Set the statement execution time for the difference between slow query and general query. The statement execution time here is the actual execution time, rather than the execution time on the CPU. Therefore, the server with heavy load is more prone to slow queries. The minimum value is 0, and the default value is 10, in seconds. It also supports millisecond-level resolution. It can be used in configuration files at the global or session level and is a dynamic variable.

Max_binlog_cache_size {4096 .. 18446744073709547520}

The size of the log cache space is determined by the limit of max_binlog_stmt_cache_size. versions 5.5.9 and later are only applied to the transaction cache. The scope is global. it can be used in configuration files and is a dynamic variable.

Max_binlog_size = {4096 .. 1073741824}

Sets the maximum number of binary log files, in bytes. the minimum value is 4 kB and the maximum value is 1 GB. the default value is 1 GB. The log information generated by a transaction can only be written into one binary log file. Therefore, the actual binary log file may be larger than the specified upper limit. The scope is global. it can be used in configuration files and is a dynamic variable.

Max_relay_log_size = {4096 .. 1073741824}

Sets the maximum size of the relay log on the slave server. when this limit is reached, the relay log is automatically rolled. If this parameter is set to 0, mysqld uses the max_binlog_size parameter to set the maximum size of log files for both binary logs and relay logs. The scope is global. it can be used in configuration files and is a dynamic variable.

Innodb_log_buffer_size = {262144 .. 4294967295}

Set the size of the log buffer used by InnoDB to assist in log file write operations. the unit is byte and the default value is 8 MB. Large transactions can use a larger log buffer to avoid writing data in the log buffer before the transaction is completed, so as to reduce I/O operations and improve system performance. Therefore, we recommend that you set a larger value for this variable in scenarios with large transactions. The range is global. it can be used in option files and is a non-dynamic variable.

Innodb_log_file_size = {108576 .. 4294967295}

Set the size of each log file in the log group in bytes. the default value is 5 MB. The wise value range is from 1 MB to 1/n of the cache pool size, where n indicates the number of log files in the log group. The larger the log file, the fewer checkpoint write operations that need to be performed in the cache pool, which means that fewer I/O operations are required. However, this will lead to slow fault recovery. The range is global. it can be used in option files and is a non-dynamic variable.

Innodb_log_files_in_group = {2 .. 100}

Set the number of log files in the log group. InnoDB uses these log files cyclically. The default value is 2. The range is global. it can be used in option files and is a non-dynamic variable.

Innodb_log_group_home_dir =/PATH/TO/DIR

Set the directory for storing InnoDB redo log files. When all variables related to InnoDB logs are used by default, two log files, ib_logfile0 and ib_logfile1, are created in the data directory by default. The range is global. it can be used in option files and is a non-dynamic variable.

Innodb_support_xa = {TRUE | FLASE}

Storage Engine transactions are granted the ACID attribute inside the storage engine. distributed (XA) transactions are high-level transactions, it uses the "prepare" and "submit" (prepare-then-commit) two-step method to extend the ACID attribute to the external storage engine, or even the external database. However, the "prepare" phase will result in additional disk write operations. XA requires the transaction coordinator, which notifies all participants to prepare for committing the transaction (phase 1 ). When the coordinator receives a "ready" message from all participants, it instructs all participants to perform real "submit" operations.
This variable is used to define whether InnoDB supports two-segment commit distributed transactions. it is enabled by default. In fact, all MySQL servers that enable binary logs and support multiple threads to write data to binary logs at the same time must enable distributed transactions. otherwise, multiple threads may write binary logs in a different order from the original one, this will create different raw data results in binary log-based recovery operations or from the server. Therefore, this function should not be disabled in other application scenarios except that only one thread can change data. In applications where only one thread can modify data, disabling this function is safe and can improve the performance of InnoDB tables. The range is global and session level. it can be used in option files and is a dynamic variable.

Relay_log = file_name

Set the name of the relay log file, which defaults to the host_name-relay-bin. You can also use an absolute path to specify a non-data directory to store relay logs. The range is global. it can be used in option files and is a non-dynamic variable.

Relay_log_index = file_name

Sets the index file name for the relay log, which defaults to the host_name-relay-bin.index in the data directory. The range is global. it can be used in option files and is a non-dynamic variable.

Relay-log-info-file = file_name

Sets the file that relay services use to record relay information, which defaults to the relay-log.info in the data directory. The range is global. it can be used in option files and is a non-dynamic variable.

Relay_log_purge = {ON | OFF}

Set whether to automatically clean up relay logs that are no longer needed. The default value is ON. The range is global. it can be used in option files and is a dynamic variable.

Relay_log_space_limit = #

Set the available space for storing all relay log files. The default value is 0, indicating not limited. The maximum value depends on the number of digits on the system platform. The range is global. it can be used in option files and is a non-dynamic variable.

Slow_query_log = {ON | OFF}

Set whether to enable slow query logs. 0 or OFF indicates disabled, 1 or ON indicates enabled. The output location of log information depends ON the definition of the log_output variable. if the value is NONE, even if slow_query_log is ON, no slow query information is recorded. The range is global. it can be used in option files and is a dynamic variable.

Slow_query_log_file =/PATH/TO/SOMEFILE

Set the name of the slow query log file. The default is hostname-slow.log, but can be modified through the-slow_query_log_file option. The range is global. it can be used in option files and is a dynamic variable.

SQL _log_bin = {ON | OFF}

Used to control whether binary log information is recorded in a log file. The default value is ON, indicating that the record function is enabled. You can modify the value of this variable at the session level, but it must have the SUPER permission. The scope is global and session level, which is a dynamic variable.

SQL _log_off = {ON | OFF}

It is used to control whether to prohibit logging general query log information into the query log file. The default value is OFF, indicating that the record function is not prohibited. You can modify the value of this variable at the session level, but it must have the SUPER permission. The scope is global and session level, which is a dynamic variable.

Sync_binlog = #

Set how often binary logs are synchronized to the disk file. if the value 0 indicates that the logs are not synchronized, any positive value indicates the number of write operations performed on the binary data before the binary data is synchronized. When the autocommit value is 1, the execution of each statement will cause binary log synchronization. Otherwise, the commit of each transaction will cause binary log synchronization. We recommend that you set it to 1.