MySQL Log Management details

Outline
I. Log Classification
Ii. Log details

Note: MySQL version, Mysql-5.5.32 (different versions of mysql variables are different)

I. Log Classification
Error Log
Query logs
Slow query log
Binary log
Relay log
Transaction Log
Rolling logs

Ii. Log details
1. Error Log
Note: In the corresponding data directory, the file named after the host name +. err has the error log information type:
The error message generated during server running is recorded.

Records the information generated when the service is started and stopped.

If the replication process is started on the slave server, the information of the replication process is also recorded.

Event error logs

Configure the mysql master configuration file:
Log_error =/mydata/data/mysql.test.com. err # specify the location of the error log. The default location is under the data directory, and the mysql user must have the write permission.
Log_warning = {0 | 1} # enabled by default. Warning logs in server running are also recorded in error logs.

2. query logs
Note: All query information recorded in logs except slow query will be recorded, which will put a lot of pressure on the server host, so the log should be disabled for busy servers.
Variables related to log query,
1234 log = {ON | OFF} # Whether to enable log query. This command is obsolete in mysq5.6.
General_log = {ON | OFF} # Start or close the query log, which is disabled by default.
General_log_file =/mydata/data/mysql. log # specify the log query location, which is in the data directory by default.
Log_output = {TABLE | FILE | NONE} # specifies the location where the query log is stored. It can be stored in a FILE or in a database TABLE, which is easier to view than in a FILE.

3. Slow query logs
Note: The status is disabled by default. If the query time exceeds the set duration, the query logs are recorded slowly.
Configure the mysql master configuration file:
Slow_query_log = {ON | OFF} # Whether to enable slow log query, Which is disabled by default.
Slow_query_log_file =/mydata/data/mysql-slow.log # the location where the slow query log is stored, which is in the data directory by default
Log_query_time = 10 # define the default duration. The default duration is 10 seconds.
Log_query_not_using_indexes = {ON | OFF} # Set whether to record query operations without indexes to slow query logs.
Log_output = {TABLE | FILE | NONE} # defines the storage mode for general query logs and slow query logs, which can be TABLE, FILE, or NONE, it can also be a combination of tables and files (separated by commas). The default value is FILE. If NONE appears in the combination, other settings will be invalid. At the same time, no log information is recorded whether or not the log function is enabled.

4. binary log
Note: This function is enabled by default and accurately records the Data Objects for commands and operations on the data in the database.
Roles of binary log files:
Provides the Incremental backup function.

Provides data recovery based on time points, which can be controlled by users.

It provides the foundation for mysql replication architecture. You can copy the binary logs of the master server to the slave server and perform the same operation to synchronize the data.

Binary log format:
Statement-based statement

Row-based

Mixed

Binary log event:
Position based on location

Datetime is based on time

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.000001 '; view binary log recorded 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 logs

Command for viewing binary logs in the file system:
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

Configure the mysql master configuration file:
123456789 SQL _log_bin = {ON | OFF} # used to control whether binary log information is recorded in the 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.
Binlog_cache_size = 32768 # default value: 32768 Binlog Cache is used in an environment where binary log (binlog) logging is enabled, mySQL is a memory region designed for temporarily caching binlog data in a short period of time to improve the record efficiency of binlog. In general, if there are no large transactions in our database, writing is not very frequent, 2 MB ~ 4 MB is a suitable choice. However, if there are many large transactions in our database and the write volume is large, you can increase the binlog_cache_size accordingly. 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 files are cached due to insufficient memory size (binlog_cache_disk_use ).
Binlog_stmt_cache_size = 32768 # When non-transaction statements use binary log cache but exceed binlog_stmt_cache_size, a temporary file is used to store these statements.
Log_bin = mysql-bin # specify the location of the binlog, which is in the data directory by default.
Binlog-format = {ROW | STATEMENT | MIXED} # specify the binary log type. The default value is MIXED. If the binary log format is set but the binary log is not enabled, the warning log is generated and recorded in the error log at MySQL startup.
Sync_binlog = 10 # Set how often binary logs are synchronized to the disk file. 0 indicates that the binary data is not synchronized. Any positive value indicates that the binary data is synchronized after each write operation. When the autocommit value 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 space size. 5.5.9 and later versions are only applied to transaction cache. The maximum value is determined by max_binlog_stmt_cache_size.
Max_binlog_stmt_cache_size = {4096 .. 18446744073709547520} # binary log cache space size. Versions 5.5.9 and later are only used in transaction cache.
Expire_log_days = {0 .. 99} # Set the Expiration days of the binary log. The binary log files exceeding the specified days will be automatically deleted. The default value is 0, indicating that the expired automatic deletion function is not enabled. If this function is enabled, automatic deletion usually occurs at MySQL startup or FLUSH log.

Note: We recommend that you store binlog and data files separately, which not only improves mysql performance, but also increases security!
5. Relay logs
Note: It is mainly used on the slave server in the mysql server 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 host and stores it in the relay log, then the SQL thread is called to execute the binary log file in the relay log file to synchronize data.
How to enable: (only enable on the slave server)
Configure the mysql master configuration file:

Relay-log = file_name # specifies the location and name of the relay log, 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.
Relay-log-index = file_name # specifies the location and name of the index file for the relay log name, which defaults to the host_name-relay-bin.index IN THE DATA DIRECTORY
Relay-log-info-file = file_name # sets the file that the relay service uses to record relay information, which defaults to the relay-log.info IN THE DATA DIRECTORY
Relay_log_purge = {ON | OFF} # Sets whether to automatically clean relay logs that are no longer needed. The default value is ON.
Relay_log_space_limit = 0 # 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.
Max_relay_log_size = {4096 .. 1073741824} # sets the maximum size of the relay log on the slave server. When this limit is reached, the relay log is automatically rolled. When this parameter is set to 0, mysqld uses the max_binlog_size parameter to set the maximum size of log files for both binary logs and relay logs.

6. Transaction logs

Note: records in detail when and at which time the data has been changed and events can be replayed, generally, only operations that change data are recorded, and read operations are not recorded.
Transaction logs provide the following functions for the database server:
(1) Converting random I/O to sequential I/O greatly improves database performance. Stored data may exist in different locations on disks, reducing Data Reading and operation performance. The principle of converting to sequential IO is to first store the data in the log file, and then store the data in the log on the disk in the RDBSM background, this ensures that the stored data is continuous.
(2). It provides the foundation for event replay. The transaction log records the time when the event occurred and the Data Objects of the operation in detail. The transaction process can replay the time based on the information.
The default transaction log file contains two numbers ending with ibdata + number in the data directory. we can define the location, file size, and growth mode of the transaction log, the method is as follows:
Taking the Innodb Storage engine that supports transactions as an example,
Configure the mysql master configuration file:
Innodb_data_home_dir =/mydata/data # directory path of all InnoDB table space data files, which is in the data directory by default
Innodb_data_file_path = ibdata1: 1024 M # specify the data files and their sizes of InnoDB. When there are more than one file, they are separated by semicolons.
Innodb_data_file_path = ibdata2: 50 M: autoextend # define the data size growth mode
Innodb_log_group_home_dir =/mydata/data # sets the directory for storing 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 a size of 5 MB are created in the data directory by default.
Innodb_log_files_in_group = {2 .. 100} # set the number of log files in the log group. InnoDB uses these log files cyclically. The 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 5 MB. The wise value range is from 1 MB to 1/n of the cache pool size, where n indicates the number of log files in the log group. The larger the log file, the fewer checkpoint write operations that need to be performed in the cache pool, which means that fewer I/O operations are required. However, this will lead to slow fault recovery.
Innodb_log_buffer_size = {262144 .. 4294967295} # Set the log buffer size used by InnoDB to assist in log file write operations. The unit is byte. The default value is 8 MB. Large transactions can use a larger log buffer to avoid writing data in the log buffer before the transaction is completed, so as to reduce I/O operations and improve system performance. Therefore, we recommend that you set a larger value for this variable in scenarios with large transactions.

7. Rolling logs
Note: As long as binary logs are rolled, a new corresponding log file is generated when a log file of a certain type is rolled. This method ensures the specific size of the log file, this ensures that the server has a high response capability for log file query.
Command for Rolling binary logs:
1 mysql> flush logs;

Iii. Summary
Through the above learning, you should have some knowledge about mysql Log Management, ^ _ ^ ......!

Recommended reading:

MySQL InnoDB manages and backs up binary logs