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

This article mainly tells you how to achieve log management in MySQL. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on how to achieve log management in MySQL can bring you some practical help.

MySQL log management

SHOW GLOBAL VARIABLES LIKE'% log%'; view global variables about log

I. Log classification

1. Error log 2. General query log 3. Slow query log

4. Binary log 5. Relay log 6. Transaction log 7. 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 CVM

Records the information generated when the service is started and stopped

When the replication process is started from the cloud server, the information of the replication process will also be recorded.

Record event event error log

Configure the main configuration file for mysql:

12log_error = / mydata/data/mysql.test.com.err # specifies the location of the error log. By default, the location is under the data directory, where mysql users must have write permission log_warning = {0 | 1} # enabled by default, and the warning log of running CVM will also be recorded in the error log.

two。 General 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 CVM, so this log should be closed for busy CVM.

Variables related to the query log

1234log = {ON | OFF} # whether to enable query log. This instruction has been abandoned in mysq5.6. General_log = {ON | OFF} # starts or closes query log. The default is general_log_file = / mydata/data/mysql.log #, which specifies the location of query log. By default, log_output = {TABLE | FILE | NONE} # specifies the location of query log under the data directory, 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. 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.

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:

12345slow_query_log = {ON | OFF} # whether to enable slow query logs. The default is the location of slow query logs that are closed, and log_query_time = 10 # under the data directory to define the default duration. The default duration is 10 seconds log_query_not_using_indexes = {ON | OFF} # sets whether to log query operations that do not use indexes to slow log log_output = {TABLE | FILE | NONE} # defines how to save general query log and slow log, 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

Any actions that cause or may cause changes to the database are recorded in the binary log.

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 primary cloud server to the slave cloud 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:

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

Mysql > show binlog events in 'mysql-bin.000001'; to view binary logged events can also follow [from position] where to start mysql-bin.000001 is the binary file name

Usage:SHOW BINLOG EVENTS IN 'mysql-bin.00001' FROM 107 # View the event information of the mysql-bin.00001 log file and display it from the location of event 107

Mysql > flush logs; binary logs scroll mysql > show binary logs; to view all binary logs

Mysql > purge binary logs to 'mysql-bin.000003'; all log files before the binary log files are deleted.

Mysql > SHOW BINARY LOGS; # View all binary log files

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000001 | 1165 | |

| | mysql-bin.000002 | 126 |

| | mysql-bin.000003 | 126 |

| | mysql-bin.000004 | 884 |

| | mysql-bin.000005 | 126 |

| | mysql-bin.000006 | 126 |

| | mysql-bin.000007 | 126 |

| | mysql-bin.000008 | 126 |

| | mysql-bin.000009 | 206 | |

| | mysql-bin.000010 | 126 |

| | mysql-bin.000011 | 126 |

| | mysql-bin.000012 | 391 | |

| | mysql-bin.000013 | 7984 | |

| | mysql-bin.000014 | 286 |

| | mysql-bin.000015 | 1325 | |

| | mysql-bin.000016 | 390 |

| | mysql-bin.000017 | 371 |

+-+ +

17 rows in set (0.00 sec)

Mysql > PURGE BINARY LOGS TO 'mysql-bin.000003'; # all log files before binary log files are deleted. Check again, the binary log files, 000001 and 000002 log files have been deleted.

Query OK, 0 rows affected (0.01 sec)

Mysql > SHOW BINARY LOGS

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000003 | 126 |

| | mysql-bin.000004 | 884 |

| | mysql-bin.000005 | 126 |

| | mysql-bin.000006 | 126 |

| | mysql-bin.000007 | 126 |

| | mysql-bin.000008 | 126 |

| | mysql-bin.000009 | 206 | |

| | mysql-bin.000010 | 126 |

| | mysql-bin.000011 | 126 |

| | mysql-bin.000012 | 391 | |

| | mysql-bin.000013 | 7984 | |

| | mysql-bin.000014 | 286 |

| | mysql-bin.000015 | 1325 | |

| | mysql-bin.000016 | 390 |

| | mysql-bin.000017 | 371 |

+-+ +

15 rows in set (0.00 sec)

Command to view binary logs in the file system: mysqlbinlog

123456mysqlbinlog related options,-- start-position # start position-- stop-position # end position-- start-datetime 'yyyy-mm-dd hh:mm:ss'; # start time

-- stop-datetime'; # end time

Usage:

[root@lamp ~] # mysqlbinlog mysql-bin.000001 # View all the information about mysql-bin.000001 log events

[root@lamp ~] # mysqlbinlog-- start-datetime '2017-04-1809 start-datetime' 01V 13 'mysql-bin.000001 # View mysql-bin.000001 log events since 09:01 on April 18, 2017.

[root@lamp ~] # mysqlbinlog-- start-position 193 mysql-bin.000001 # View the information after the mysql-bin.000001 log time starts at position 193.

Configure the main configuration file for mysql:

123456789sql_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. The user can modify the value of this variable at the session level, but it must have SUPER permission binlog_cache_size = 32768 # default value 32768 Binlog Cache for use 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 use temporary files (binlog_cache_disk_use) to cache binlog_stmt_cache_size = 32768 # when non-transactional statements use binary log caching but exceed binlog_stmt_cache_size due to insufficient memory size Use a temporary file to store these statements log_bin = mysql-bin # to specify the location of the binlog. By default, binlog-format = {ROW | STATEMENT | MIXED} # specifies the type of binary log in the data directory, and the default is 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 or later only applies to transaction cache expire_log_days = {0.99} # sets the number of days for binary logs to expire, and binary log files exceeding 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 mysql CVM architecture. 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.

How to enable it: (open it only from the CVM)

Configure the main configuration file for mysql:

123456relay-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 the relay log relay-log-index = file_name # the location and name of the index file that specifies the name of the relay log, which defaults to host_name-relay-bin.indexrelay-log-info-file = file_name # in the data directory to set the file used by the relay service to record relay information The default is relay-log.inforelay_log_purge = {ON | OFF} # in the data directory to set whether relay logs that are no longer needed are automatically cleaned. The default value is ONrelay_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 digits of the system platform max_relay_log_size = {4096... 1073741824} # sets the upper limit of the size of the relay log from the CVM, and when it reaches this limit, it will automatically scroll 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. Transactional storage engine is used to ensure atomicity, consistency, isolation and persistence

The transaction log implements the following functions for the database cloud 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:

1234567

Innodb_flush_log_at_commit # defaults to 1 and has three levels: 0: synchronize per second and perform disk flush operations; 1: synchronize per transaction and perform disk flush operations; and 2: synchronize per transaction but do not perform disk flush operations.

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 the InnoDB and their size, separated by semicolons when there is more than one file.

Innodb_mirrored_log_groups # whether mirroring settings are enabled for transaction logs

Innodb_data_file_path = ibdata2:50M:autoextend # defines how the data size grows innodb_log_group_home_dir = / mydata/data # sets the directory where important InnoDB 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 size 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. The default is 2innodb_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 IUnio 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

Note: as long as it is scrolled for binary logs, a new corresponding log file is generated once for a certain type of log file, which ensures the specific size of the log file in this way, thus ensuring that the CVM has a high response ability to log file query.

The command to scroll the binary log:

1mysql > FLUSH LOGS; # to implement binary log scrolling

MySQL how to achieve log management will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.