Shulou(Shulou.com)05/31 Report--

This article will explain in detail all kinds of logs about how to understand mysql. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Mysql error log:

Events recorded in the error log:

A), information during server startup and shutdown

B), error messages during server operation

C) the event debugger runs information about the timing of an event

D) the information generated when the slave server process is started on the slave server

The log_error parameter specifies the path to the error log and the file name. If you do not specify a file, the default file name is [host_name] .err and the save path is% datadir%.

Mysql > show variables like "log_error%"

+-+

| | Variable_name | Value |

+-+

| | log_error | D:\ Quarkp2p\ mysql-5.6.19-winx64\ data\ QF-dbadmin-02.err |

+-+

1 row in set (0.00 sec)

Mysql > show variables like "datadir%"

+-- +

| | Variable_name | Value |

+-- +

| | datadir | D:\ Quarkp2p\ mysql-5.6.19-winx64\ data\ | |

+-- +

1 row in set (0.00 sec)

The information recorded in the error log is divided into three categories: [Note], [warning], and [Error]. You can control whether warnings are logged by setting log_warnings, with a default value of 1 for recording and 0 for disabled. If it is greater than 1, messages such as connection failure, new connection rejection, and so on are also written to the error log.

Mysql > show variables like "log_warning%"

+-+ +

| | Variable_name | Value |

+-+ +

| | log_warnings | 1 | |

+-+ +

1 row in set (0.00 sec)

Query log of mysql:

The query log records the query statement and startup time. It is recommended that you do not open the query log in a debug environment, because it will continue to occupy your disk space and generate a lot of IO.

There are two kinds of query logs, namely slow query log (Slow Query Log) and general query log (General Query Log). And the query log of mysql can not only be recorded in the file, but also automatically saved to the table object in the MySql database.

1. Slow query log:

All query statements take longer to execute than the parameter values specified by the system variable long_query_time (default is 10 seconds), and the number of records accessed exceeds the number of records accessed by the system variable min_examined_row_limit (default is 0). The execution time here does not include the overhead of initializing the table lock.

The record and order of statements in the slow query log may be different from the execution order, because mysqld will not write qualified sql statements to the slow query log until each statement is executed and the lock resources are released.

Enable and disable slow query logs through the following two system parameters, which can be dynamically modified:

Slow_query_log specifies whether to output the slow query log, 1 means output, 0 means no output, and the default is 0.

Slow_query_log_file specifies the log file storage path and file name, if not specified, the default file name is [host_name]-slow.log, and the save path is% datadir%.

Mysql > show variables like "slow_query_log%"

+-+

| | Variable_name | Value |

+-+

| | slow_query_log | OFF |

| | slow_query_log_file | D:\ Quarkp2p\ mysql-5.6.19-winx64\ data\ QF-dbadmin-02-slow.log |

+-+

2 rows in set (0.00 sec)

Disable / enable slow query logs globally:

SET GLOBAL slow_query_log='OFF'

SET GLOBAL slow_query_log='ON'

Other parameters:

Long_query_time

Log_short_format

Log_slow_admin_statements

Log_queries_not_using_indexes

Log_throttle_queries_not_using_indexes

Log_slow_slave_statements

The steps taken by mysqld to determine whether a sql statement needs to be recorded in the slow log are as follows:

1) determine whether the query statement or administrative statement is being executed

2) the execution time of the query statement reaches or exceeds the value of the long_query_time parameter, or meets the log_queries_not_using_indexes condition

3) the number of records queried has reached the min_examined_row_limit parameter value.

4) the query statement does not violate the value set by log_throttle_queries_not_using_indexes

If the slow query log is very large, you can use the mysqldumpslow command that comes with mysql or other third-party tools to view the analysis.

2. General query log:

This log records almost everything the mysqld process does, including sql statement execution, database object management, client connections and disconnections.

The biggest function of this log is auditing.

Enable and disable the generic query log through the following two system parameters, which can be dynamically modified:

General_log specifies whether to output the slow query log, 1 means output, 0 means no output, and the default is 0.

General_log_file specifies the log file storage path and file name, if not specified, the default file name is [host_name] .log, and the save path is% datadir%.

Mysql > show variables like "general_log%"

+-+

| | Variable_name | Value |

+-+

| | general_log | OFF |

| | general_log_file | D:\ Quarkp2p\ mysql-5.6.19-winx64\ data\ QF-dbadmin-02.log |

+-+

2 rows in set (0.00 sec)

Disable / enable generic logs globally:

SET GLOBAL general_log='OFF'

SET GLOBAL general_log='ON'

Session-level disable / enable generic logging:

SET sql_log_off='OFF'

SET sql_log_off='ON'

The order in which statements appear in the generic query log file is in the order in which mysqld is received.

3. Configure query log:

Specify the-- log-output option when the mysql service starts to determine whether the query log is saved in a file in the operating system or in a dedicated table in the database system.

There are three optional values for log-output: table, which is output to a table, and the corresponding tables are general_log and slow_log

File, exporting to log file

None, do not output query log

-- log-output can set multiple values.

By default, the log table uses the CSV engine, and after 5.1, the log table can also be modified to the MyIsam engine.

Log tables (general_log and slow_log) support create table,alter table,drop table,truncate table,rename,check. Lock tables,insert,update,delete operation is not supported. Additions, deletions, modifications and queries of log tables are carried out by the mysql service itself.

Flush tables with read lock and setting the global system variable read_only are not valid for log tables.

Log table writes are not counted in the binary log, and if there is a replication environment, the contents of the log table will not be copied to other Slaves nodes.

Use flush tables or flush logs to refresh log tables or log files.

The log table does not allow the creation of partitions.

Binary log file:

With binary log files, you can achieve two important functions: for replication and for recovery.

Enable the binary log file and add the parameter-- log-bin= [base _ name] when the mysql service starts. If you do not specify a parameter value, the default file name is [host_name]-bin.log and the save path is% datadir%.

Mysql > show variables like "log_bin%"

+-+

| | Variable_name | Value |

+-+

| | log_bin | ON |

| | log_bin_basename | D:\ Quarkp2p\ mysql-5.6.19-winx64\ data\ mysql-bin |

| | log_bin_index | D:\ Quarkp2p\ mysql-5.6.19-winx64\ data\ mysql-bin.index |

| | log_bin_trust_function_creators | ON |

| | log_bin_use_v1_row_events | OFF |

| | sql_log_bin | ON |

+-+

6 rows in set (0.00 sec)

There will be more than one binary log file. Starting with serial number 1, each time you start the mysql service or refresh the log, a new binary log file is generated. And a single log file cannot grow indefinitely, and when it rises to the size specified by the parameter max_binlog_size, a new binary log file is created. However, it is also possible that the log file is larger than the value specified by max_binlog_size. If the transaction executed is very large, all transaction information must be written to a log file.

To track the status of binary log files, the mysql service creates a binary log index file with the same name as the binary log file, but with the extension .index. This file contains all the binary log files available.

If a user with super privileges executes the set sql_log_bin=0 command before performing the operation, the statements he executes are prohibited from producing binary logs.

-- binlog-do-db and-- binlog-ignore-db two options to specify which library records or not to record binary logs. These two options can only be set one value at a time. If there are multiple libraries, set multiple entries repeatedly.

There are three formats of binary log files: row format record (row-based logging), statement based record (statement-based logging), and mixed mode record (mixed-based logging).

Because mysql has both a storage engine that supports transactions and a storage engine that does not support transactions, binary logs are handled differently when operating on different storage engine objects.

For non-transactional tables, statements are written to the binary log file as soon as they are executed. For transaction tables, it is not written to the binary log file until there is currently no locked or uncommitted information, to ensure that the logs are always recorded in the order in which they are executed.

Use the mysqlbinlog command line tool to view binary log files.

Binlog_cache_use shows the number of transactions using the binlog_cache_size variable, including temporary files

Binlog_cache_size specifies how much memory space is allocated for threads

Binlog_cache_disk_use shows the number of transactions using temporary files

Max_binlog_cache_size is used to limit the maximum cache area that a transaction can use (the default is 4G, which is also the maximum, and the minimum is 4096). If the transaction exceeds this limit, the execution error occurs and the transaction rolls back.

Transaction log:

The transaction log files are named "ib_logfile0" and "ib_logfile1" and are stored in the same directory as the tablespace by default

Variables related to the transaction log:

Innodb_log_group_home_dir=/PATH/TO/DIR # sets the directory where InnoDB redo log files are stored. When all variables related to InnoDB logs are used by default, two log files named ib_logfile0 and ib_logfile1 with the size of 5MB are created in the data directory by default. The scope is at the global level, can be used in option files, and is a non-dynamic variable.

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 from 1MB to 1 of the cache pool volume, where n represents the number of log files in the log group. The larger the log file, the fewer checkpoint brushing operations that need to be performed in the cache pool, which means that fewer Ibino operations are required, but this can also lead to slower failure recovery. The scope is at the global level, can be used in option files, and is a non-dynamic variable.

Innodb_log_files_in_group= {2.. 100} # sets the number of log files in the log group. InnoDB uses these log files in a circular manner. The default value is 2. The scope is at the global level, can be used in option files, and is a non-dynamic variable.

Innodb_log_buffer_size= {262144.. 4294967295} sets the size of the log buffer that InnoDB uses to assist in log file writes, in bytes. The default is 8MB. Larger transactions can make use of larger log buffers to avoid writing data from log buffers to log files before the transaction is completed, so as to reduce IBO operations and improve system performance. Therefore, in application scenarios with large transactions, it is recommended that you set a larger value for this variable. The scope is at the global level, can be used in option files, and is a non-dynamic variable.

Innodb_flush_log_at_trx_commit = 1 # means that after a transaction is committed, the transaction will not be written to the buffer first and then synchronized to the transaction log file, but will be written to the transaction log immediately as soon as there is a transaction commit, and the data in the buffer will be synchronized to the file every 1 second, so the IO consumption is high, and the default value is "1", which can be changed to "2".

The variable innodb_locks_unsafe_for_binlog = OFF # is recommended to keep the OFF state. The detailed principle is not clear.

Innodb_mirrored_log_groups = 1 # number of mirrors saved by the transaction log group

Relay log:

Log information produced in a replication environment

Variables related to the relay log:

Log_slave_updates # is used to set whether the slave server in the replication scenario logs updates received from the master server in the local binary log. The effective setting of this parameter requires the binary logging feature to be enabled on the slave server.

Relay_log=file_name # sets the file name of the relay log, default to host_name-relay-bin. You can also use an absolute path to specify a non-data directory to store relay logs. The scope is at the global level, can be used in option files, and is a non-dynamic variable.

Relay_log_index=file_name # sets the index file name of the relay log. It defaults to host_name-relay-bin.index in the data directory, and its scope is at the global level. It can be used for option files and is a non-dynamic variable.

Relay-log-info-file=file_name # sets the file used by the relay service to record relay information, which defaults to relay-log.info in the data directory. The scope is at the global level, can be used in option files, and is a non-dynamic variable.

Relay_log_purge= {ON | OFF} # sets whether relay logs that are no longer needed are automatically cleaned. The default is ON. The scope is at the global level, can be used in option files, and is a dynamic variable.

Relay_log_space_limit= # sets the amount of free space used to store all relay log files. The default is 0, which means that there is no limit. The maximum value depends on the number of platform bits in the system. The scope is at the global level, can be used in option files, and is a non-dynamic variable.

Max_relay_log_size= {4096... 1073741824} # sets the upper limit of the size of the relay log on the server, and when it reaches this limit, it automatically rolls the relay log. When this parameter value is 0, mysqld uses the max_binlog_size parameter to set the log file size limit for both binary and relay logs. The scope is at the global level, can be used for configuration files, and is a dynamic variable.

All kinds of logs about how to understand mysql are shared here. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.