Analysis of Mysql Log Management 01/16 Update SLTechnology News&Howtos

Analysis of Mysql Log Management

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

The following content mainly brings you Mysql log management analysis, the knowledge here is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

I. brief introduction

Developed by MySQL AB, it is the most popular open source SQL database management system with the following main features:

1. It is a database management system.

2. It is an associated database management system.

3. It is a kind of open source software, and there are a lot of shared MySQL software available

4. MySQL database cloud servers are fast, reliable and easy to use.

5. MySQL server works in client / server mode, or in embedded system.

The InnoDB storage engine saves InnoDB tables in a tablespace that can be created by several files. In this way, the size of the table can exceed the maximum capacity of individual files. Tablespaces can include raw disk partitions, making large tables possible. The maximum capacity of the tablespace is 64TB.

II. MySQL log management

2.1. Log global variable

Mysql > SHOW GLOBAL VARIABLES LIKE'% log%'; # View global variables about log

+-+ +

| | Variable_name | Value |

+-+ +

| | back_log | 50 | |

| | binlog_direct_non_transactional_updates | OFF |

| | general_log general query log 1 | OFF | |

| | general_log_file general query log 2 | / mydata/data/lamp.log |

| | innodb_flush_log_at_trx_commit | 1 | |

| | innodb_locks_unsafe_for_binlog | OFF |

| | innodb_log_buffer_size | 8388608 | |

| | innodb_log_file_size | 5242880 | |

| | innodb_log_files_in_group | 2 | |

| | innodb_log_group_home_dir |. / | |

| | innodb_mirrored_log_groups | 1 | |

| | log general query log 3 | OFF | |

| | log_error error log 1 | / mydata/data/lamp.err |

| | log_output general query log 4 slow query log 1 | FILE |

| | log_queries_not_using_indexes slow query log 2 | OFF | |

| | log_slave_updates | OFF |

| | log_slow_queries | OFF |

| | log_warnings error log 2 | 1 | |

| | slow_query_log slow query log 3 | OFF | |

+-+ +

41 rows in set (0.00 sec)

2.2. Log classification

MySQL has several different log files that can help you find out what's going on inside mysqld:

1. Error logs record problems that occur when starting, running, or stopping

two。 General query logs record client connections established and statements executed

3. Slow log records all queries that take more than long_query_time seconds to execute or that do not use indexes

4. A statement that records all change data in a binary log. Mainly used for replication and instant point recovery

5. Relay log

6. Transaction logs record logs generated when transaction-enabled storage engines, such as InnoDB, execute transactions

7. Scroll log

By default, all logs are created in the mysqld data directory. By refreshing the log, you can force mysqld to close and reopen the log file (or, in some cases, switch to a new log). Log refresh occurs when you execute a FLUSH LOGS statement or mysqladmin flush-logs or mysqladmin refresh. If you are using the MySQL replication function, more log files, called replacement logs, will be maintained from the replication server.

2.3. Detailed explanation of log

2.3.1. Error log (on by default)

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

2.3.1.1. Record the error messages generated when the server is running

2.3.1.2. Record the information generated when the service is started and stopped

2.3.1.3. When the replication process is started from the server, the information of the replication process is also recorded

2.3.1.4. Record event event error logs and warning messages

Configure the main configuration file for mysql:

Log_error = / mydata/data/mysql.test.com.err

# specify 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}

# enabled by default, and the warning log of the server running will also be recorded in the error log

2.3.2. General query log (off by default)

Except for the query information recorded in the slow log, all the query information will be recorded, which will put a lot of pressure on the server.

Therefore, the busy server should turn off the variables related to the query log.

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 # specify the log location, 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, which 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 regardless of whether the logging feature is enabled or not

2.3.3. Slow log (off by default)

If the query time exceeds the set time by default, the query log will be recorded by the slow log.

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

Slow_query_log_file = / mydata/data/mysql-slow.log

# the storage location of slow query logs, which is located in the data directory by default

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

Log_query_not_using_indexes = {ON | OFF}

# whether to log query operations that do not use indexes to the slow query log

Log_output = {TABLE | FILE | NONE}

# define how to save general query logs and slow query logs. Default is FILE, or a combination of TABLE and FILE (separated by commas). If NONE appears in the combination, all other settings will become invalid, and no relevant log information will be recorded regardless of whether the log feature is enabled or not

2.3.4. Binary log (on by default)

Data objects that accurately record the commands and operations of users to operate on the data in the database, and any operations that cause or may cause changes in the database.

2.3.4.1. The purpose of binary log files:

1) provide incremental backup function

2) provide point-in-time recovery of data, which can be controlled by the user

3) provide the basis for the replication architecture of mysql. Data synchronization can be achieved by copying the binary logs of the master server to the slave server and performing the same operation.

2.3.4.2. Binary log format:

Based on statement statement

Line-based row

Mixed mode mixed

2.3.4.3. Binary log events:

Position is location-based

Datetime is based on time

2.3.4.4. How to view and delete binary logs:

Mysql > show master status; # View the binary logs currently in use

Mysql > show binlog events in 'mysql-bin.000012'

Mysql > SHOW BINLOG EVENTS IN 'mysql-bin.00011' FROM 107

# View the event information of the log file and display it from the location of event 107

Mysql > flush logs; # binary logs scroll mysql > show binary logs; # View all binary logs

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

# all log files 000001 and 000002 before this log is deleted

Usage:

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.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';# deletes all previous log files 001 and 002

Mysql > SHOW BINARY LOGS

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000003 | 126 |

| | mysql-bin.000004 | 884 |

| | mysql-bin.000005 | 126 |

...

| | mysql-bin.000015 | 1325 | |

| | mysql-bin.000016 | 390 |

| | mysql-bin.000017 | 371 |

+-+ +

15 rows in set (0.00 sec)

2.3.4.5. Command to view binary logs in the file system: mysqlbinlog

Mysqlbinlog 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 log events

[root@lamp ~] # mysqlbinlog-- start-datetime '2017-04-1809 start-datetime' 01V 13 'mysql-bin.000011 # View the information of log events from a certain point in time

[root@lamp] # mysqlbinlog-- start-position 193 mysql-bin.000011

# View the information after the log time starts at position 193.

2.3.4.6. Configure the main configuration file for mysql:

Sql_log_bin = {ON | OFF}

# is used to control whether binary log information is recorded in a 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 is turned on. It is a memory area designed by MySQL to improve the recording efficiency of binlog, which is used to temporarily cache binlog data in 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 sufficient, 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 the binlog_stmt_cache_size is exceeded, use a temporary file to hold the statements

Log_bin = mysql-bin

# specify the location of the binlog, which is under the data directory by default

Binlog-format = {ROW | STATEMENT | MIXED}

# specify the type of binary log. 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

# set 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 determines the size of log cache space. Versions 5.5.9 and later are only applicable 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}

# set the number of days for binary logs to expire, and binary log files exceeding this number of days will be deleted automatically. 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!

2.3.5. Relay log

It is mainly used on the slave server in the slave architecture of the mysql server. when the master and slave server carries out data synchronization, 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 SQL thread to execute according to the binary log file in the relay log file to achieve data synchronization.

Opening method: can only be opened on the slave server.

Configure the main configuration file for mysql:

Relay-log = host_name-relay-bin

# specify the location and name of the relay log. Default is host_name-relay-bin. You can also use an absolute path to specify a non-data directory to store relay logs

Relay-log-index = host_name-relay-bin.index

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

Relay-log-info-file = relay-log.info

# set the file used by the relay service to record relay information. Default is relay-log.info in the data directory.

Relay_log_purge = {ON | OFF}

# set whether to automatically clean up relay logs that are no longer needed. The default value is ON

Relay_log_space_limit = 0

# 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}

# the upper limit of the size of the relay log from the server, when this limit is reached, the relay log is scrolled automatically. When this value is 0, the max_binlog_size parameter is used, and the log file size limit is set for both binary and relay logs.

2.3.6. Transaction log

Record in detail when what transactions took place, at which time which data were changed, can achieve the replay of events, generally only record the operations to change the data, generally do not record the read operations. The transactional storage engine is used to ensure atomicity, consistency, isolation, and persistence.

2.3.6.1. Main function

2.3.6.1.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.3.6.1.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.

2.3.6.2. 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:

Configure the main configuration file for mysql: (take the Innodb storage engine that supports transactions as an example)

Innodb_flush_log_at_commit

# the default value is 1, which has three levels:

0: synchronize every second and perform disk flush operation

1: synchronize each transaction and perform disk flush operations

2: every transaction is synchronized, but the disk flush operation is not performed, and the flush; IO is reduced but not safe by the operation

Define the synchronization method from the memory space to the log file, while the synchronization from the log file to the generated data file (on the hard disk) is automatically carried out by the background service thread of mysql, and can also be controlled; first cached to the sub-user space in the memory space, and then cached to the sub-kernel space, when the value is 0, it will not be cached to the sub-kernel space, but will be synchronized directly to the log file.

Innodb_data_home_dir = / mydata/data

# InnoDB the directory path of all shared tablespace data files. Default is under the data directory.

Innodb_data_file_path = ibdata1:1024M

# specify the data files of InnoDB and their sizes. When the files are larger than one, they are separated by semicolons.

Innodb_mirrored_log_groups

# whether the mirror setting is enabled for the transaction log

Innodb_data_file_path = ibdata2:50M:autoextend

# define the growth mode of data size

Innodb_log_group_home_dir = / mydata/data

# set the storage directory for important InnoDB log files. 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_group_home_dir = / mydata/data

Innodb_log_files_in_group = {2.. 100}

# set 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}

# set the size of each log file in the log group in bytes. The default value is 5MB. It is recommended that you do not exceed 10MB. 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}

# set the size of the log buffer used by InnoDB to assist in writing log files, 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

2.3.7. Scroll log

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 to the log file query.

The command to scroll the binary log:

Mysql > FLUSH LOGS; # to implement binary log scrolling

For the above analysis on Mysql log management, if you need to know more, you can continue to pay attention to the innovation of our industry, if you need to get professional answers, you can contact the pre-sale and after-sale on the official website. I hope this article can bring you some knowledge updates.

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.