MySQL Log Management and mysql Log

MySQL Log Management and mysql Log

MySQL Log Management

Query the log variables: show global variables like '% log % ';
Note: If you do not understand the mysql variables, click "mysql Server variables ".

Error Log:
Record Content: information during server startup and Shutdown
Error message during server running
Information generated when the event scheduler runs an event
Information generated when the slave server process is started on the slave server

1. log_error = file path/File Name: defines the error log file, which can be used at the global or session level and is a non-dynamic variable.

2. log_warnings =: Set whether to record warning information into the error log. 1 is enabled (default), 0 is disabled, if the value is greater than 1, the "failed connection" and "Access Denied" generated when the new connection is initiated are recorded in the error log.

Query logs:
Record Content: related information is recorded every time a select statement is executed. When log = yes (5.6 has been deprecated), dml statements are recorded in general query logs.

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

2. general_log_file = file path/File Name: defines the query log file. The default value is "hostname. log", which is a dynamic variable and can be used globally for configuration files.

3. log = {YES | NO}: whether to enable logging of all statements in general query logs. The default value is OFF, and 5.6 is deprecated.

4. log_output = {TABLE | FILE | NONE}: defines the storage mode for general query logs and slow query logs, which can be TABLE, FILE, NONE can also be a combination of tables and files (separated by commas). The default value is TABLE. If NONE exists, other settings will be invalid. Whether or not the log function is enabled, no related information is recorded. It is a dynamic variable and can be used globally in the configuration file.

Slow query log: (slow query refers to an event whose execution duration exceeds the scheduled duration set by the long_query_time parameter)

1. long_query_time =: If the query duration exceeds the time specified by long_query_time, the query duration is slow. Here, the statement execution duration is the actual execution time, not the execution time on the cpu, the default value is 10 seconds. Therefore, slow queries are more likely to occur on servers with heavy loads. 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.

2. log_slow_queries = {YES | NO}: whether to record slow query logs ,. Slow query refers to an event whose execution time exceeds the scheduled duration set by the long_query_time parameter. 5.6 considers the event as slow_query_log and takes effect globally. It can be used in configuration files and is a dynamic variable.

3. slow_query_log = {ON | OFF}: Set whether to enable slow query logs. 0 or OFF indicates disabled. 1 or ON indicates enabled. The log output position depends ON the definition of the log _ output variable, if its value is NONE, even if slow_query_log is ON, no slow query information is recorded. Its range is global and can be used in the configuration file, which is a dynamic variable.

4. slow_query_log_file = file path/location: 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.

Binary log:
Record Content: any operation that causes or may cause database changes;

Used for copy and instant point recovery;
As long as you restart mysql, binary logs will scroll

1. binlog_format =: Binary File Format

Binary log format:
Statement-based: statement
Row-based: row
Mixed Mode: mixed

Binary log event format (how to store an event in a file ):
Event generation time starttime
Relative position of the event (the start position of the event is the end position of the previous event) position

Binary log files have two types:
Index file: records the binary files that can be searched by the server.
Binary Log File

View the binary log file currently in use: show master status;
View the BINARY log file: show binary logs;
View EVENTS: show binlog events in 'binary log filename '[FROM specifies the start position];
Delete the log file before the specified BINARY file: purge binary logs to 'binary log file'
Scroll binary or relay LOGS: flush logs;
View the mysql binary file: mysqlbinlog [[---- start-datetime] [-stop-datetime] [[-- start-position] [-- stop-position]
-- Start-datetime: Specifies the start time.
-- Stop-datetime: Specifies the end time.
-- Start-position: Specifies the start position.
-- Stop-position: Specifies the end position.

Relay log
Events copied from the binary log file of the master server and saved as log files;
Transaction log:
Ib_logfile0
Ib_logfile1
ACID: converts random IO to sequential IO;
The transactional storage engine is used to ensure atomicity, consistency, isolation, and durability;
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;
Expire_logs_days = {0 .. 99}

 

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}

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.

 

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}

Innodb_log_buffer_size = {262144 .. 4294967295}

Innodb_log_file_size = {108576 .. 4294967295}
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

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 scope is global.
Used for Option files, which are dynamic variables.

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 lead to a binary log synchronization.

MyISAM:
Transactions are not supported.
Table lock
Foreign keys are not supported
B-tree index, FULLTEXT index, and spatial index
Supports table compression.
. Frm
. MYD
. MYI
InnoDB:
Transactions
Row-Level Lock
B-tree index, clustering index, and adaptive hash Index
Tablespace, raw disk device;
. Frm
. Ibd