MySQL Log management

MySQL Log management

Error log
Information in the server startup and shutdown process
Error message during server run
Information generated when the event Scheduler runs an event
Information generated from the server process when it is started from the server
Log_error
Log_warnings
General Query log:
General_log
General_log_file
Log
Log_output
Slow query log
Long_query_time
Log_slow_queries={yes|no}
Slow_query_log
| Slow_query_log_file

Binary log: Any operation that causes or may cause changes in the database;
Replication, instant point recovery;
Mysqlbinlog

Binary log format:
Statement based: statement
Row-based: row
Mixing mode: Mixed

Binary Log events:
Time of Generation
Relative position

Binary log files:
Index file
Binary log files

View binary log files currently in use mysql> SHOW MASTER STATUS;         View binary log files mysql> show binary LOGS;  View events: mysql> SHOW BINLOG events in ' binary log filename ' [from position]; log file before deletion of a binary file mysql> PURGE binary LOGS To ' a binary log file ' for binary log scrolling: Mysql>flush LOGS;

Mysqlbinlog
--start-datetime
--stop-datetime

--start-position
--stop-position


Relay Log
An event that is copied from the binary log file of the primary server and saved as a log file;
Transaction log:
Ib_logfile0
Ib_logfile1
ACID, the random Io is converted to sequential IO;
The transactional storage engine is used to ensure atomicity, consistency, isolation and persistence;
Innodb_flush_log_at_trx_commit:
0: Synchronize every second, and perform disk flush operation;
1: Synchronize each transaction and perform disk flush operation;
2: Synchronize per transaction, but do not perform disk flush operation;





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.

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, the query log is enabled and no log information is logged. The scope is global and can be used for configuration files, which belong to dynamic variables.

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

binlog-format={row| Statement| MIXED}
Specifies the type of binary log, which defaults to statement. 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.

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.

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 set value of the Log-bin. The scope is the global level, which is a non-dynamic variable.

log_bin_trust_function_creators={true| FALSE}
This parameter is only valid when binary logging is enabled, and is used to control whether the creation of a storage function is prohibited when creating a stored 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. The scope is the global level, which can be used for configuration files, which belong to dynamic variables.

Log_error=/path/to/error_log_filename
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_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.

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.

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.

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.

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.

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.

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_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.

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_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_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.

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.

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.

Slow_query_log_file=/path/to/somefile
Sets the name of the slow query log file. The default is Hostname-slow.log, but can be modified with 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.

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.

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.

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.


MyISAM:
Transaction not supported
Table lock
Foreign keys are not supported
B-Tree Index, fulltext Index, spatial index
Support Table Compression
. frm
. MYD
. MYI
InnoDB:
Transaction
Row-level Locks
B-Tree index, clustered index, Adaptive hash Index
Table space, raw disk device;
. frm
. ibd

This article is from the "bustling Down" blog, please be sure to keep this source http://chenxujiang.blog.51cto.com/11737025/1847030

MySQL Log management