MySQL various log file related variables introduced

Article turned from: http://www.ywnds.com/?p=3721

MySQL various log file related variables introduced

Querying variables for all logs

1	MySQL> show global variables like '%log% ';

Global indicates the status value of the globally variable (about 61 variables).

First, error log

Log_error=/var/log/mysqld.log

Where the error log is stored, MySQL 5.5 in the data directory, MySQL 5.6 in the/var/log/mysql.log file

Log_warnings=1

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.

Second, the general log

General_log=off

General query log, closed by default, you can use the –general_log option to turn on general queries when you start mysqld. If enabled, the output location is defined by the –log_output option, and if the value of Log_output is set to none, the query log is enabled and no log information is logged. The scope is global, can be used for configuration files, belong to dynamic variable

General_log_file=file_name

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

Log=yes

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

three, slow query log

Slow_query_log = OFF

The default value is off, whether the slow query log is logged, or the slow query is the event that the query executes longer than the Long_query_time parameter sets. MySQL 5.5 This parameter is Log_slow_queries={yes|no}, scoped to the global level, can be used for configuration files, is a dynamic variable.

log_output=file| table| NONE

Defines the log output mode, which is file by default. function on query log and slow query log, belong to dynamic variable can be modified online. If you change to table storage, a general_log and Slow_log table is generated in the MySQL schema, which uses the CSV engine by default. It is best to change the secondary engine to MyISAM, but you must close the slow query before you can modify it.

Slow_query_log_file =/Path/to/localhost-slow.log

Set storage path for slow query log in file format

Long_query_time= 10.000000

Set the length of the statement execution that distinguishes between slow and generic queries, where the statement executes at the actual execution time rather than on the CPU, making it more prone to slow queries on servers with heavier loads. Its minimum value is 0, the default value is 10 seconds, it is important to note that MySQL only records more than 10 seconds of SQL query and is equal to or less than 10 seconds is not logged. A SQL statement running for 0.5 seconds and 0.05 seconds is very different, the former may have been scanned, and the latter may have gone through the index. It also supports the resolution of microsecond levels. The scope is global or session level and can be used for configuration files, which belong to dynamic variables.

Log_queries_not_using_indexes=off

The default is off, which defaults to off, and when turned on, if you run an SQL statement that does not use an index, the MySQL database will also log the SQL statement to the slow query log file.

Log_throttle_queries_not_using_indexes=0

The default value is 0, which is a new variant of the MySQL5.6.5 version that represents the number of SQL statements that are allowed to log to slow log per minute without using an index. The value defaults to 0, which means there is no limit. This parameter is an issue that prevents SQL statements in production environments from using indexes that cause slow query logs to be too large.

Log_slow_admin_statements=off

The default value is off, which is added in MySQL5.6.11, and records include table management statements such as change tables, parse tables, tables, create indexes, drop indexes, optimize tables, and repair tables. The scope is a global variable, which can be used in a configuration file, which belongs to a dynamic variable.

Log_slow_slave_statements=off

The default value is off, this variable is added in the MySQL5.6.11, when the slow query log is turned on, this variable can be set to replicate from the library records the main library more than long_query_time time of the query log. The scope is a global variable, which can be used in a configuration file, which belongs to a dynamic variable.

four or two binary log

Expire_logs_days=0

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, can be used for configuration files, belong to dynamic variable

binlog-format={row| Statement| MIXED}

This parameter affects the recording binary log format, the value has row, STATEMENT, mixed three kinds, MySQL5.5 default is mixed promiscuous mode (is usually used STATEMENT record binary log, but need to use the row will be used); MySQL 5.6 were instead based on statement.

Log_slave_updates=off

The default value is off, which is used to set whether the from server in the replication scenario logs the update operations received from the primary server into the native binary log, which requires the binary logging feature to be enabled on the server from which this parameter is set. By default, binary logs that are taken from master and executed are not written to their binary log files.

Log_bin=on

The default is on, enabling the binary logging feature, which is scoped to the global level and belongs to a static variable.

Sql_log_bin=on

The default value is on, which controls whether binary log information is logged into the log file, as long as the binary logging feature is turned on. The scope is the session variable, which belongs to the dynamic variable.

Max_binlog_size = 1073741824

The default is 1G, which specifies the maximum value of a single binary log file, and if this value is exceeded, a new binary is generated, with the suffix name +1, and recorded in the. index file. The default is 1G, or you can manually refresh the file.

Sync_binlog=0

The default value is 0, and by default, the binary log is not synchronized to the disk each time it is written, it buffers the write. Therefore, the last part of the data may not be written to the binary log file when the operating system on which the database resides is down. This parameter indicates how many times each buffer is synchronized to the disk, 1 means the synchronous write disk to write the binary log, when the write operation does not apply to the operating system buffer to write binary log, will bring a certain performance degradation. The value defaults to 0, which means that buffer writes are taken and performance is good.

However, setting Sync_binlog to 1 o'clock has another situation that can cause the problem to occur. When using the InnoDB storage engine, the binary log is written to disk immediately before a commit action is issued on a transaction, since Sync_binlog is set to 1. If the binary log has been written at this time, but the commit has not yet occurred, and the outage is sent at this time, the next time the MySQL database is started, the commit operation does not occur, so this thing will be rolled back. However, the binary log and the information that records the thing cannot be rolled back. This problem can be resolved by setting the parameter Innodb_support_xa, although INNODB_SUPPORT_XA is related to XA transactions, it also ensures synchronization of binary logs and InnoDB storage engine data files.

Log_bin_trust_function_creators=off

The default value is off, which is only valid when the binary log is enabled, and is used to control whether the creation of a storage function is prohibited when creating a storage function if it causes an unsafe event to log the binary log condition. The default value is 0, which means that unless the user has super privileges in addition to the create routing or alter routine permission, it is forbidden to create or modify the storage function, and also requires that the deterministic property be used when creating the function. Or else it comes with reads SQL data or no SQL properties. When you set its value to 1, these restrictions are not enabled. Scoped to the global level and can be used for configuration files, which belong to dynamic variables

binlog_cache_size=32768

The default is 32k, and when the transaction engine is used, all uncommitted binary logs are logged to a cache, and the size of the buffer is controlled by this parameter. This value can not be set too large, of course, it is not too small, or when a transaction record is greater than the set of Binglog_cahce_size, MySQL will write the buffer log in a temporary file. Use the show GLOBAL Status command to view the status of Binlog_cache_use (record the number of times the buffer is used to write binary logs), Binglog_cahce_disk_use (the number of times a binary log is written using temporary files), You can determine whether the current Binlog_cache_size settings are appropriate.

Max_binlog_cache_size = 18446744073709547520

The binary log statement cache size, in bytes. Represents the maximum cache memory size that Binlog can use

When we execute a multi-statement transaction, when all the sessions use more memory than the Max_binlog_cache_size value

will be error: "Multi-statement transaction required more than ' max_binlog_cache_size ' bytes ofstorage". The size of this value is related to the amount of time the data in memory is synchronized to disk, the larger the cache performance, the greater the ratio of data loss, the smaller the cache performance, the lower the data loss ratio.

binlog_stmt_cache_size=32768

The default value is 32k, which determines the size of the binary log cache that is released during a transaction when a non-transactional statement is held. If the server supports any transactional storage engine, if the server has binary logging enabled, each client is assigned a separate binary log transaction and statement cache. If you frequently use large non-transactional statements during trading, you can increase the cache size to achieve better performance. It is possible to determine whether 32k is sufficient by checking the values of the state variables Binlog_stmt_cache_use and Binlog_stmt_cache_disk_use.

Max_binlog_stmt_cache_size = 18446744073709547520

If a non-transactional statement requires more than a few bytes of memory in the transaction, the server generates an error.

Binlog_rows_query_log_events

MySQL 5.7 New parameters, default off, optional open, suggest open, or more useful. You can see the SQL statement in the case of the binary log format as row, to facilitate troubleshooting and recovery of data.

Binlog_max_flush_queue_time

The default value is 0, which controls the new BLGC (binary log Group commit) of MySQL 5.6, which is the time to wait in the flush phase of the binary log group submission, even if a previous set of transactions are committed, and the current set of transactions does not immediately enter the sync phase. Instead, it has to wait at least a while, and the benefit is that group commits have more transactions, which can also lead to slower response times for the transaction. The default of 0 means no wait, and the recommended setting is still 0. Unless there is a large number of connections (such as 100 connections) in the user's MySQL database system, and the transaction is constantly being written or updated. (Note: Binlog_max_flush_queue_time is no longer valid on MySQL's 5.7.9 and later versions)

Binlog_group_commit_sync_delay=n

Binlog_group_commit_sync_no_delay_count=n

MySQL 5.7. After version 9, the Binlog_max_flush_queue_time parameter is invalid. Added, MySQL waits binlog_group_commit_sync_delay milliseconds until the number of Binlog_group_commit_sync_no_delay_count transactions is reached and a group commit is made.

v. Relay Log

Relay_log = Mysql-relay-bin

Turn on the trunk log, where the value is the base name of the trunk log. By default, the name of the library is Host_name-realy-bin, and the server writes files in the data directory unless a different directory is created with a file with a leading absolute path. The server creates a relay log file by adding a numeric suffix to the base name.

Relay_log_index = Mysql-relay-bin.index

The name of the file used to relay the log index, the default name is in the data directory Host_name-relay-bin.index, where host_name is the name from the server.

Relay_log_info_file = Relay-log.info

This file is used to record the file and event location of the relay log and the file and event location of the binaries.

Relay_log_info_repository = FILE

This variable determines the location of the trunk log to be written to a file (trunk log information) or a table (Mysql.slave_relay_log_info).

Relay_log_purge = On

The default value is on, which initiates automatic cleanup of the relay log. This is a global variable.

Relay_log_recovery = OFF

The default value is off, and when slave is down from the library, if the relay-log is damaged, causing some of the relay logs to not be processed, all the relay-log that are not executed are automatically discarded, and the log is retrieved from master again, which guarantees the integrity of the relay-log. By default, this feature is turned off and the value of Relay_log_recovery is set to 1 o'clock, and it is recommended to turn on the slave from the library.

Relay_log_space_limit = 0

To prevent the trunk log from filling the disk, set the maximum trunk log limit. However, this setting has the main library crash, from the library trunk log is not the case, not the last resort, is not recommended to use.

Sync_relay_log = 10000

Sync_relay_log_info = 10000

This parameter is the same as Sync_binlog, when set to 1 o'clock, the slave I/O thread receives the Binlog log sent by master every time it is written to the system buffer, and then brushes in the relay log relay log, this is the safest, because in the crash, You will lose at most one transaction, but will cause a large amount of I/O to the disk. When set to 0 o'clock, it is not immediately brushed into the trunk log, but is determined by the operating system when to write, although the security is reduced, but reduced a lot of disk I/O operations. This value is 0 by default and can be modified dynamically, with default values recommended.

Max_relay_log_size = 0

#设定从服务器上中继日志的体积上限, when this limit is reached, it automatically scrolls through the trunk log. 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. Scoped to the global level and can be used for configuration files, which belong to dynamic variables

vi. Transaction Logs

The transaction log, also known as the Redo log (redo log), is about redo related variables, in the "InnoDB Files and Variables" section.

MySQL various log file related variables introduced