MySQL various log introduction

Directory

1. Log classification

2, various log introduction

1. Log classification

Error log

Query log

Slow query log

Transaction log

Binary log

Relay Log

2, various log introduction

2.1. Error log

By default, the error log is stored in the data directory with the name "Server_name.err"

Event logged by error log:

A), the server starts the shutdown process information

b), error messages during server operation

c), the event debugger runs an event time-of-birth information

d), information generated when starting the process from the server on the server

To view the variables associated with the log:

Mysql> SHOW GLOBAL VARIABLES like '%log% ';

There are two variables associated with the error log:

Log_error=/path/to/error_log_filename

Example: Log_error =/mydata/data/hostname.err

Defines the error log file. The scope is global or session level and can be used for configuration files, which are non-dynamic variables.

log_warnings=#

Sets whether warning messages are logged in the error log. The default setting is 1, which means it is enabled, it can be set to 0 to disable, and a value greater than 1 indicates that error messages for the "Failed connections" and "denied access" classes that are generated when a new connection is initiated are also logged in the error log.

2.2. Query log

Query logging query statement and startup time, it is not recommended in the debugging environment do not open the query log, because it will continue to occupy your disk space, and will generate a lot of IO.

Variables related to query log:

Log={yes|no}

Whether logging of all statements is enabled is usually off by default in the General query log. MySQL 5.6 has deprecated this option.

general_log={on| OFF}

Sets whether the query log is enabled, and the default value depends on whether the--general_log option is used when starting mysqld. If enabled, the output location is defined by the--log_output option, and if the value of Log_output is set to none, no log information is logged even if the query log is enabled. The scope is global and can be used for configuration files, which belong to dynamic variables.

log_output={table| file| NONE}

Defines how the general query log and the slow query log are saved, either table, file, NONE, or a combination of table and file (separated by commas), which defaults to table. If none is present in the combination, the other settings are invalidated, and no relevant log information is logged, regardless of whether logging is enabled or not. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.

sql_log_off={on| OFF}

Used to control whether generic query log class information is forbidden to be logged into the query log file. The default is off, which means that the logging feature is not suppressed. The user can modify the value of this variable at the session level, but it must have super privileges. The scope is global and session level, which belongs to the dynamic variable.

General_log_file=file_name

The log file name of the query log, which defaults to "Hostname.log", is the default in the data directory. The scope is global and can be used for configuration files, which belong to dynamic variables.

2.3. Slow query log

Slow query Log Suggestions Open

Variables related to the slow query log:

slow_query_log={on| OFF}

Sets whether to enable slow query logging. 0 or off means disabled, 1 or on indicates enabled. The output location of the log information depends on the definition of the log_output variable, and if the value is None, no slow query information is logged even if Slow_query_log is on. The scope is the global level, which can be used for the option file, which is a dynamic variable.

Log_slow_queries={yes|no}

Whether to log slow query logs. A slow query is an event where the execution time of a query exceeds the length set by the Long_query_time parameter. MySQL 5.6 Modifies this parameter in order to Slow_query_log. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.

Slow_query_log_file=/path/to/somefile

Sets the name of the slow query log file. The default is Hostname-slow.log, which defaults to the data directory, but can be modified by the--slow_query_log_file option. The scope is the global level, which can be used for the option file, which is a dynamic variable.

log_query_not_using_indexes={on| OFF}

Sets whether query operations that do not use an index are logged to the slow query log. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.

long_query_time=#

Sets the length of the statement execution time between slow and generic queries. The execution time of the statement here is actual execution times, not the CPU time, so it is more prone to slow queries on servers with heavier loads. Its minimum value is 0, the default value is 10, and the unit is seconds. It also supports the resolution of the millisecond level. The scope is global or session level and can be used for configuration files, which belong to dynamic variables.

2.4. Transaction log

The transaction log file is named "Ib_logfile0" and "Ib_logfile1" and is stored by default in the directory where the table space resides

Variables related to transaction log:

Innodb_log_group_home_dir=/path/to/dir

Sets the storage directory for the InnoDB redo log file. When all variables related to the InnoDB log are used by default, it creates two log files in the data directory, named Ib_logfile0 and Ib_logfile1, with a size of 5MB. The scope is the global level, which can be used for option files, which are non-dynamic variables.

innodb_log_file_size={108576:4294967295}

Sets the size of each log file in the log group, in bytes, and the default value is 5MB. A more sensible range of values is 1/n from 1MB to the cache pool volume, where n represents the number of log files in the log group. The larger the log file, the fewer checkpoint brush writes you need to perform in the cache pool, which means fewer I/O operations are required, but this also results in slower recovery times. The scope is the global level, which can be used for option files, which are non-dynamic variables.

INNODB_LOG_FILES_IN_GROUP={2:100}

Sets the number of log files in the log group. InnoDB use these log files in a circular manner. The default value is 2. The scope is the global level, which can be used for option files, which are non-dynamic variables.

innodb_log_buffer_size={262144:4294967295}

Sets the size of the log buffer used by InnoDB to assist in the completion of log file writes, in bytes, by default to 8MB. Larger transactions can use a larger log buffer to avoid writing log buffers ' data to the log file before the transaction completes, reducing I/O operations and thereby improving system performance. Therefore, in a scenario with a larger transaction, it is recommended that you set a larger value for this variable. The scope is the global level, which can be used for option files, which are non-dynamic variables.

Innodb_flush_log_at_trx_commit = 1

# indicates that after a transaction commits, the transaction will not be written into buffer, then synchronized to the transaction log file, but once a transaction commits to write into the transaction log immediately, and also every 1 seconds will also synchronize the data in the buffer to the file, so IO consumption is large, the default value is "1", can be modified to "2"

Innodb_locks_unsafe_for_binlog OFF

#这个变量建议保持OFF状态, the detailed principle is unclear

Innodb_mirrored_log_groups = 1

#事务日志组保存的镜像数

2.5. Binary Log

Log MySQL statements that modify data or are likely to cause data changes

Binary log format:

A), statement (statement) Format: Data volume is small, but data inconsistency may occur during data recovery

b), row (ROW) format: large data volume, but accurate data

c), mixed (mixed format): Alternately using statement and row, let MySQL decide which one to use

Show binary Log related statements:

mysql> show {binary | master} logs ;  #显示当前mysql有哪些二进制日志文件mysql >  show master status;  #显示当前服务器所使用的二进制日志文件及所处的位置 +------------------+----------+--------------+---- --------------+| file             |  position | binlog_do_db | binlog_ignore_db |+------------------+----------+---- ----------+------------------+| mysql-bin.000021 |      311 |               |                   |+------------------+----- -----+--------------+------------------+mysql> show binlog events [in  ' log_name ')  [FROM pos] [LIMIT [offset,] row_count];   #读取二进制日志的事件详情mysql > show  binlog events in  ' mysql-bin.000021 ', +------------------+-----+-------------+-----------+-------------+--------- ------------------------------------------------+| log_name          | Pos | Event_type  | Server_id | End_log_pos |  info                                                      |+------ ------------+-----+-------------+-----------+-------------+---------------------------------------------------- -----+| mysql-bin.000021 |   4 | format_desc |          1 |         107 |  server ver: 5.5.36-log, binlog ver: 4                    | |  mysql-bin.000021 | 107 | Query       |          1 |          213 | use  ' mydb2 '; drop table  ' TB2 '  /* generated by server  */ | |  mysql-bin.000021 | 213 | Query       |          1 |          311 | use  ' MYDB2 '; create table tb2 select * from tb1          |+------------------+-----+-------------+-----------+--------- ----+---------------------------------------------------------Mysql> show binlog events in  ' mysql-bin.000021 '  FROM 213;  #指定位置 +----------- -------+-----+------------+-----------+-------------+-------------------------------------------------+|  log_name         | pos | event_type |  server_id | end_log_pos | info                                              |+------------ ------+-----+------------+-----------+-------------+-------------------------------------------------+|  mysql-bin.000021 | 213 | query      |          1 |         311 |  use  ' MYDB2 ';  create table tb2 select * from tb1 |+------------------+-----+------ ------+-----------+-------------+-------------------------------------------------+

There are also tools under the shell that read binary log files, which are more readable than the information read in the interactive environment:

[Email protected] ~]# mysqlbinlog/mydata/data/mysql-bin.000021

The binary log file and the Mysqlbinlog command enable data recovery operations, which are the 4 most common options for this command:

--start-datetime=#

Represents the start time of an event in a binary log file

--stop-datetime=#

Indicates the end time of an event in a binary log file

--start-position=#

Represents the starting position of an event in a binary log file

--stop-position=#

Represents the end position of an event in a binary log file

Binary log File Security Delete method (before deleting, please back up):

Syntax: PURGE {BINARY | MASTER} LOGS {to ' log_name ' | Before datetime_expr}

mysql> show binary logs;+------------------+-----------+| log_name          | file_size |+------------------+-----------+|  mysql-bin.000001 |     27702 | |  mysql-bin.000002 |   1063490 | |  mysql-bin.000003 |       733 | |  mysql-bin.000004 |       150 | |  mysql-bin.000005 |       126 | |  mysql-bin.000006 |       126 | |  mysql-bin.000007 |       126 | |  mysql-bin.000008 |       126 | |  mysql-bin.000009 |       126 | |  mysql-bin.000010 |       126 | |  mysql-bin.000011 |       381 | |  mysql-bin.000012 |       126 | |  mysql-bin.000013 |      1625 |+------------------+-----------+ mysql> purge binary logs to  ' mysql-bin.000010 ';   #把此二进制日志文件之前的都删除Query  OK, 0 rows affected  (0.04 sec) mysql> show binary logs;+------ ------------+-----------+| log_name         | file_size  |+------------------+-----------+| mysql-bin.000010 |        126 | |  mysql-bin.000011 |       381 | |  mysql-bin.000012 |       126 | |  mysql-bin.000013 |      1625 |

Binary Log related variables:

Log_bin={yes|no}

Whether to enable binary logging, if the--log-bin option is set for MYSQLD, the value is on, otherwise it is off. It is used only to show if the binary log is enabled, and does not reflect the Log_bin set value, that is, the exact location where the binary log files are stored, which can be defined in MY.CNF. The scope is the global level, which is a non-dynamic variable. Value can be the same as Log_error value as a path, do not add suffix

Log_bin =/mydata/data/mysql-bin

#定制二进制文件路径, do not add suffixes

Binlog_cache_size = 32768

#启动mysql服务器时二进制日志的缓存大小

binlog_format={row| Statement| MIXED}

Specifies the type of binary log, 5.5.31 previously default to statement, and the default is mixed later. If the binary log format is set and the binary log is not enabled, MySQL starts with warning log information and is logged in the error log. The scope is global or session, can be used for configuration files, and is a dynamic variable.

Binlog_stmt_cache_size = 32768

Cache size in #基于statement (statement) format

expire_logs_days={0..99}

Sets the number of days that the binary log expires, and binary log files that exceed this number are automatically deleted. The default is 0, which means that the expired auto-delete feature is not enabled. If this feature is enabled, automatic removal of work usually occurs at MySQL startup or flush logs. The scope is global and can be used for configuration files, which belong to dynamic variables.

max_binlog_cache_size{4096:18446744073709547520}

The binary log cache space size, 5.5.9 and later versions apply only to transaction caches, and the upper limit is determined by max_binlog_stmt_cache_size. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.

max_binlog_size={4096:1073741824}

Sets the upper limit of the binary log file in bytes, the minimum value is 4K, the maximum value is 1G, and the default is 1G. The log information generated by an office can only be written to a binary log file, so the actual binary log file may be larger than the specified upper limit. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.

Max_binlog_stmt_cache_size = 18446744073709547520

#基于statement格式的二进制日志文件的最大缓存大小

sql_log_bin={on| OFF}

Used to control whether binary log information is logged into the log file. The default is on, which means the logging feature is enabled. The user can modify the value of this variable at the session level, but it must have super privileges. The scope is global and session level, which belongs to the dynamic variable.

sync_binlog=#

Set how often the binary log is synchronized to the disk file, 0 means no synchronization, and any positive value represents a synchronization once for each number of writes to the binary. When the value of autocommit is 1 o'clock, the execution of each statement causes the binary log to be synchronized, otherwise, the commit of each transaction causes the binary log to synchronize.

In a production environment, it is recommended that you do not put binary log files in the same directory as your data.

2.6. Relay Log

Log information that is produced in the replication environment

Variables associated with the trunk log:

Log_slave_updates

Used to set whether the from server in the replication scenario logs updates received from the primary server into the native binary log. The effective setting of this parameter requires the binary logging feature to be enabled from the server.

Relay_log=file_name

Sets the file name for the trunk log, which defaults to Host_name-relay-bin. You can also use an absolute path to specify a non-data directory to store the relay log. The scope is the global level, which can be used for option files, which are non-dynamic variables.

Relay_log_index=file_name

Sets the index file name for the trunk log, which defaults to Host_name-relay-bin.index in the data directory. The scope is the global level, which can be used for option files, which are non-dynamic variables.

Relay-log-info-file=file_name

Sets the file that the relay service uses to log relay information, which defaults to Relay-log.info in the data directory. The scope is the global level, which can be used for option files, which are non-dynamic variables.

relay_log_purge={on| OFF}

Sets whether the relay logs that are no longer needed are cleaned automatically. The default value is on. The scope is the global level, which can be used for the option file, which is a dynamic variable.

relay_log_space_limit=#

Sets the amount of free space used to store all trunk log files. The default is 0, which means no qualification. The maximum value depends on the number of system platform bits. The scope is the global level, which can be used for option files, which are non-dynamic variables.

max_relay_log_size={4096..1073741824}

Sets the maximum size of the trunk log from the server, which automatically scrolls the trunk log when it reaches this limit. With this parameter value of 0 o'clock, Mysqld will use the Max_binlog_size parameter to set the log file volume limit for both binary and trunk logs. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.

This article is from the "knowledge needs summary and records" blog, please be sure to keep this source http://zhaochj.blog.51cto.com/368705/1632812

MySQL various log introduction