Shulou(Shulou.com)06/01 Report--

Outline

I. Log classification

Second, detailed explanation of the log

Note: MySQL version, Mysql-5.5.32 (mysql variables vary from version to version)

I. Log classification

Error log

Query log

Slow query log

Binary log

Relay log

Transaction log

Scroll log

Second, detailed explanation of the log

1. Error log

Description: in the corresponding data directory, the type of information recorded by the error log for the file named with the hostname + .err:

Recorded the error messages generated during the operation of the server

Records the information generated when the service is started and stopped

When the replication process is started on the slave server, the information of the replication process will also be recorded.

Record event error log

Configure the main configuration file for mysql:

one

two

Log_error = / mydata/data/mysql.test.com.err # specifies the location of the error log. By default, it is under the data directory, where mysql users must have write permission.

Log_warning = {0 | 1} # is enabled by default, and the warning log while the server is running will also be recorded in the error log

two。 Query log

Note: except for the query information recorded in the slow check log, all the query information will be recorded, which will exert a lot of pressure on the server host, so this log should be closed for busy servers.

Variables related to the query log

one

two

three

four

Log = {ON | OFF} # whether to enable query log, this instruction has been abandoned in mysq5.6

General_log = {ON | OFF} # enables or closes the query log. It is off by default.

General_log_file = / mydata/data/mysql.log # specifies the location of the query log, which is under the data directory by default

Log_output = {TABLE | FILE | NONE} # specify the location to store the query log, which can be placed in a file or in a table in the database. It is easier to view in a table than in a file.

3. Slow query log

Description: if the query time exceeds the set time, the query log will be recorded by slow check log.

Configure the main configuration file for mysql:

one

two

three

four

five

Slow_query_log = {ON | OFF} # whether to enable slow log query. Default is off.

Slow_query_log_file = / mydata/data/mysql-slow.log # the location of the slow query log. By default, it is under the data directory.

Log_query_time = 10 # defines the default duration, which is 10 seconds

Log_query_not_using_indexes = {ON | OFF} # set whether to log query operations that do not use indexes to the slow query log

Log_output = {TABLE | FILE | NONE} # defines how to save general query logs and slow query logs, which can be TABLE, FILE, NONE, or a combination of TABLE and FILE (separated by commas). The default is FILE. If NONE appears in the combination, all other settings will be invalidated, and no relevant log information will be recorded, whether or not the logging feature is enabled.

4. Binary log

Description: on by default, the data object that accurately records the commands and operations of the user to operate on the data in the database.

The purpose of binary log files:

Provides the function of incremental backup

Provides a point-in-time recovery of data, which can be controlled by the user

To provide the basis for mysql's replication architecture, the data can be synchronized by copying the binary logs of the master server to the slave server and performing the same operation

Binary log format:

Based on statement statement

Line-based row

Mixed mode mixed

Binary log events:

Position is location-based

Datetime is based on time

How to view and delete binary logs:

one

two

three

four

five

Mysql > show master status; to view the binary log currently in use

Mysql > show binlog events in 'mysql-bin.000001'; to view binary logged events [from position]

Mysql > flush logs; binary log scrolling

Mysql > show binary logs; view all binary logs

Mysql > purge binary logs to 'mysql-bin.000003'; delete binary log

Command to view binary logs in the file system:

one

two

three

four

five

six

Mysqlbinlog

Related option

-- start-position # start position

-- stop-position # end position

-- start-datetime 'yyyy-mm-dd hh:mm:ss'; # start time

-- stop-datetime'; # end time

Configure the main configuration file for mysql:

one

two

three

four

five

six

seven

eight

nine

Sql_log_bin = {ON | OFF} # is used to control whether binary log information is recorded in the log file. The default is ON, which means that logging is enabled. Users can modify the value of this variable at the session level, but they must have SUPER permission

Binlog_cache_size = 32768 # the default value of 32768 Binlog Cache is used in environments where binary logging (binlog) recording is turned on. It is a memory area designed by MySQL to improve the recording efficiency of binlog and to temporarily cache binlog data for a short period of time. In general, if there are no big transactions in our database and writes are not particularly frequent, 2MB~4MB is an appropriate choice. However, if our database has a large number of transactions and a large number of writes, we can increase binlog_cache_size appropriately. At the same time, we can use binlog_cache_use and binlog_cache_disk_use to analyze whether the set binlog_cache_size is enough, and whether a large number of binlog_cache are cached using temporary files (binlog_cache_disk_use) due to insufficient memory size.

Binlog_stmt_cache_size = 32768 # when non-transactional statements use binary log caching, but when binlog_stmt_cache_size is exceeded, a temporary file is used to hold the statements

Log_bin = mysql-bin # specifies the location of the binlog, which is under the data directory by default

Binlog-format = {ROW | STATEMENT | MIXED} # specifies the type of binary log, which defaults to MIXED. If the binary log is formatted but not enabled, a warning log message is generated when MySQL starts and is recorded in the error log.

Sync_binlog = 10 # sets how often the binary log is synchronized to the disk file, 0 means out of sync, and any positive value indicates that the binary is synchronized after every number of writes. When the value of autocommit is 1, the execution of each statement will cause binary log synchronization, otherwise, the commit of each transaction will cause binary log synchronization.

Max_binlog_cache_size = {4096.. 18446744073709547520} # binary log cache size, version 5.5.9 and later applies only to transaction cache, and the upper limit is determined by max_binlog_stmt_cache_size.

Max_binlog_stmt_cache_size = {4096.. 18446744073709547520} # binary log cache size, version 5.5.9 and later only apply to transaction cache

Expire_log_days = {0.99} # sets the number of days for binary logs to expire, and binary log files that exceed this number of days will be automatically deleted. The default is 0, which means that the automatic deletion of expiration is not enabled. If this feature is enabled, automatic deletion usually occurs when MySQL starts or when FLUSH logs

Note: it is generally recommended to store binlog logs and data files separately, which can not only improve mysql performance, but also increase security!

5. Relay log

Description: it is mainly used on the slave server in the slave architecture of the mysql server. when the slave server wants to synchronize data with the master server, the slave server copies the binary log file of the master server to its own host and puts it in the relay log, and then calls the SQL thread to execute according to the binary log file in the copy relay log file so that the data synchronization can be achieved.

Method to enable it: (open it only from the server)

Configure the main configuration file for mysql:

one

two

three

four

five

six

Relay-log = file_name # specifies the location and name of the relay log, which defaults to host_name-relay-bin. You can also use an absolute path to specify a non-data directory to store relay logs

Relay-log-index = file_name # specifies the location and name of the index file of the name of the relay log, which defaults to host_name-relay-bin.index in the data directory

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

Relay_log_purge = {ON | OFF} # sets whether relay logs that are no longer needed are automatically cleaned. The default value is ON

Relay_log_space_limit = 0 # 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.

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 is 0, mysqld will use the max_binlog_size parameter to set the log file size limit for both binary and relay logs.

6. Transaction log

Description: it records in detail when and when, which data has been changed, and can realize the replay of the event. Generally, it only records the operation of changing the data, and generally does not record the read operation.

The transaction log performs the following functions for the database server:

(1)。 Converting random IO to sequential IO greatly improves the performance of the database, and the stored data may exist in different locations of the disk, which reduces the performance of data reading and operation. The principle of converting to sequential IO is that the data is first stored in the log file, and then the data in the log is stored on disk by the background of RDBSM, which ensures that the stored data is continuous.

(2)。 To provide the basis for event playback, the transaction log records in detail the time of occurrence and the data object of the operation, and the transaction process can replay the time according to this information.

There are two default transaction log files, which are located in the number ending in ibdata+number in the data directory. We can define the location, file size and growth mode of the transaction log as follows:

Here is an example of using an Innodb storage engine that supports transactions

Configure the main configuration file for mysql:

one

two

three

four

five

six

seven

Innodb_data_home_dir = / mydata/data # InnoDB the directory path of all shared tablespace data files, which is under the data directory by default

Innodb_data_file_path = ibdata1:1024M # specifies the individual data files of InnoDB and their size, separated by semicolons when there is more than one file

Innodb_data_file_path = ibdata2:50M:autoextend # defines how the data size increases

Innodb_log_group_home_dir = / mydata/data # sets the directory where InnoDB important 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

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. Default value is 2

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 IWeiO operations are required, but this can also lead to slower failure recovery.

Innodb_log_buffer_size = {262144.. 4294967295} # sets the log buffer size, in bytes, that InnoDB uses to assist in log file writes. 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

7. Scroll log

Description: as long as it is scrolling for binary logs, a new corresponding log file is generated by scrolling a certain type of log file once, which ensures the specific size of the log file in this way. so as to ensure that the server has a high response ability to the log file query.

The command to scroll the binary log:

one

Mysql > FLUSH LOGS

III. Summary

Through the above study, you should know something about mysql log management, ^ _ ^ …!

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.