Mysql Log Management Detailed _mysql

Log files are very important for a server, it records the operation of the server information, many operations will be written to log files, through the log file can monitor the running state of the server and view the performance of the server, but also to the server to troubleshoot and fault handling, MySQL has six different types of logs.

I. Type of journal

——— –> 1, error log: Log a problem that occurs when you start, run, or stop, and generally log a warning message.
——— –> 2, General query log: Records established client connections and executed statements.
——— –> 3, slow query log: Records all queries that run longer than long_query_time seconds, or queries that do not use indexes, can help us locate server performance issues.
——— –> 4, binary log: Any operations that cause or are likely to cause database changes are primarily used for replication and point-in-time recovery.
——— –> 5, Relay log: Event copied from the primary server's binary log file and saved as a log file.
——— –> 6, transaction log: Logs that are generated when transactions are performed by the storage engine that supports transactions, such as InnoDB.

There are a lot more environments in MySQL for log files than variables, and you can use the following command to view:

Mysql> show global variables like '%log% '; +-----------------------------------------+-----------------------------------------+
| variable_name |
Value | +-----------------------------------------+-----------------------------------------+
| Back_log | 50 | | Binlog_cache_size | 32768 | | Binlog_direct_non_transactional_updates | Off | | Binlog_format | MIXED | | Binlog_stmt_cache_size | 32768 | | Expire_logs_days | 0 | | General_log | Off | | General_log_file | /mydata/data1/localhost.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 | Off | | Log_bin | On | | | log_bin_trust_function_creators | Off | | Log_error | /mydata/data1/localhost.localdomain.err | | Log_output | FILE | | log_queries_not_using_indexes | Off | | Log_slave_updates | Off | | log_slow_queries | Off | | log_warnings | 1 | | Max_binlog_cache_size | 18446744073709547520 | | Max_binlog_size | 1073741824 | | Max_binlog_stmt_cache_size | 18446744073709547520 | | Max_relay_log_size | 0 | |                     Relay_log | |
| relay_lOg_index | |
| Relay_log_info_file | Relay-log.info | | Relay_log_purge | On | | | Relay_log_recovery | Off | | Relay_log_space_limit | 0 | | Slow_query_log | Off | | Slow_query_log_file | /mydata/data1/localhost-slow.log | | Sql_log_bin | On | | | Sql_log_off | Off | | Sync_binlog | 0 | | Sync_relay_log | 0 | | Sync_relay_log_info |
0 |

 +-----------------------------------------+-----------------------------------------+ rows in Set (0.00 sec)

Second, the log function

1, error log
The error log mainly records the following types of logs:
——— information during –> server startup and shutdown
——— error messages during the –> server run
——— the information generated when an event is run by the –> event Scheduler
——— –> The information generated when the server process is started from the server
Error log Definition:
You can use the –log-error [= file_name] option to specify where MYSQLD saves the error log file. If the file_name value is not given, MYSQLD uses the error log name Host_name.err and writes to the log file in the data directory. If you perform flush LOGS, the error log renames the suffix with-old and mysqld creates a new empty log file. (If the –LOG-ERROR option is not given, it will not be renamed).
Error logs typically have more than two variables to define:

Error log file: Log_error
Enable warning message: Log_warnings (default enabled)

Mysql> show global variables like ' log_error ';
+---------------+-----------------------------------------+
| variable_name | Value                  |
+---------------+-----------------------------------------+
| log_error   |/mydata/data1/ Localhost.localdomain.err |
+---------------+-----------------------------------------+
1 row in Set (0.00 sec)
mysql> Show Global Variables like ' log_warnings ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| log_warnings | 1   |
+---------------+-------+
1 row in Set (0.00 sec)

2. General Query Log

Start switch: general_log={on| OFF}
Log file variable: general_log_file [=/path/to/file]
Global Log switch: log={on| OFF} All logs will be enabled when the switch is turned on
Record type: log_output={table| file| NONE}
Log_output defines the output format of the log, which can be a table, a file, and if set to none, the log is not enabled, so to enable the common query log, you need to configure at least general_log=on,log_output={table| FILE}. and general_log_file if not specified, the default name is Host_name.log. Because the general query usage is large, enabling write log files, the server more I/O operations, will greatly reduce the performance of the server, so the default is closed.

Mysql> show global variables like ' general_log ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| General_log  | Off  |
+---------------+-------+
1 row in Set (0.00 sec)
mysql> show global variables like ' general_log_file ';
+------------------+-----------------------------+
| Variable_name  | Value            |
+------------------+-----------------------------+
| general_log_file |/mydata/data1/localhost.log
| +------------------+-----------------------------+
1 row in Set (0.01 sec)

You can use the following command to open General_log:

mysql> set global general_log=1;
Query OK, 0 rows Affected (0.00 sec)
mysql> show global variables like ' general_log ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| General_log  | On  |
+---------------+-------+
1 row in Set (0.00 sec)

3, slow query log

Query timeout time: long_query_time
Start slow log: Log_slow_queries={yes|no}
Start Slow log: Slow_query_log
Log file: Slow_query_log_file [= file_name]
MySQL if the Slow_query_log=on option is enabled, queries that run longer than Long_query_time are logged (the time at which the table is locked initially does not count as execution time). If the log file does not give a file_name value, the default is the host name and the suffix is-slow.log. If a file name is given, but not an absolute pathname, the file is written to the data directory.

Mysql> show global variables like '%slow_query_log% ';
+---------------------+----------------------------------+
| Variable_name    | Value              |
+---------------------+----------------------------------+
| Slow_query_log   | Off               | |
slow_query_log_file |/mydata/data1/localhost-slow.log
| +---------------------+----------------------------------+
2 rows in Set (0.00 sec)

Slow queries are not enabled by default and are recommended for server tuning.

Mysql> show global variables like ' long_query_time ';
+-----------------+-----------+
| Variable_name  | Value   |
+-----------------+-----------+
| long_query_time | 10.000000
| +-----------------+-----------+
1 row in Set (0.00 sec)

Ultra-long time defaults to 10 seconds, more than a slow query.

4, binary log

Binary log start switch: log-bin [= file_name]
must be manually specified in version 5.6 and above. 5.6 The following versions default file_name to $datadir/mysqld-binlog, which is used to record all statements that change data, primarily for replication and point-in-time recovery. The primary purpose of binary logging is to have a database failure the database is most likely to be updated at restore time (that is, point-in-time recovery) because the binary log contains all the updates made after the backup, and the binary logs are used to record all statements that will be sent to the server from the primary replication server.
The tool for viewing binary logs is: mysqlbinlog
Binary log format:
-->  based on statement: statement
-->  based on rows: Row
-->  Blending mode: Mixed
The binary log file used by MySQL is a mixed binary log because of its own benefits based on statements and row based log formats, and built-in policies automatically choose the best format.
Binary Log event:
-->  time generated: starttime
-->  relative position: position
Binary log file:
-->  index file
-- >  binary log file
A mysql-bin.index in the data directory is the index file, which begins with a mysql-bin and ends with a digital file as a binary log file.
Log scrolling:
MySQL Scrolls the same way as other logs, and when scrolling creates a new number 1 log that records the latest log, and the original log name is not changed. Every time you restart the MySQL service, the log will scroll automatically.
In addition, if manual scrolling is required, use the command:

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> Show master status;  #查看当前正在使用的二进制文件
+------------------+----------+--------------+------------------+
| File       | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000033 |   the | | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

The default position is starting at 107.

Mysql> show binary logs; #查看所有的二进制文件 +------------------+-----------+ | Log_name |
File_size | +------------------+-----------+
|    mysql-bin.000020 | 107 | |    mysql-bin.000021 | 107 | |   mysql-bin.000022 | 50676 | |    mysql-bin.000023 | 150 | |    mysql-bin.000024 | 621 | |    mysql-bin.000025 | 107 | |   mysql-bin.000026 | 4509 | |    mysql-bin.000027 | 150 | |    mysql-bin.000028 | 150 | |    mysql-bin.000029 | 150 | |    mysql-bin.000030 | 357 | |    mysql-bin.000031 | 107 | |    mysql-bin.000032 | 150 | |    mysql-bin.000033 |
107 |
+------------------+-----------+ rows in Set (0.00 sec) mysql> use Mysql_shiyan;
Database changed mysql> INSERT INTO department (dpt_name) VALUES (' Feiyu ');
Query OK, 1 row Affected (0.00 sec) mysql> Show master status; +------------------+----------+--------------+------------------+
| File | Position | binlog_do_db |
binlog_ignore_db | +------------------+----------+--------------+------------------+
| mysql-bin.000033 |   331 |         |
|
 +------------------+----------+--------------+------------------+ 1 row in Set (0.00 sec)

The

position has changed since the data was inserted.

Mysql> INSERT INTO Department (dpt_name) VALUES (' feiyu1 ');   #再插入一条数据 Query OK, 1 row Affected (0.00 sec) mysql> Show Binlog events in ' mysql-bin.000033 '; #查看二进制文件中记录的内容 +------------------+-----+-------------+-----------+-------------+------------------------------- ------------------------------------------+
| Log_name | Pos | Event_type | server_id | End_log_pos |
Info | +------------------+-----+-------------+-----------+-------------+--------------------------------------------- ----------------------------+
|  mysql-bin.000033 | 4 |     Format_desc |     1 | 107 | Server ver:5.5.36-log, Binlog ver:4 | | mysql-bin.000033 | 107 |     Query |     1 | 183 | BEGIN | | mysql-bin.000033 | 183 |     Query |     1 | 304 | Use ' Mysql_shiyan '; INSERT INTO department (dpt_name) VALUES (' Feiyu ') | | mysql-bin.000033 | 304 |     Xid |     1 | 331 |                         COMMIT/* xid=12 * *  |
| mysql-bin.000033 | 331 |     Query |     1 | 407 | BEGIN | | mysql-bin.000033 | 407 |     Query |     1 | 529 | Use ' Mysql_shiyan '; INSERT INTO department (dpt_name) VALUES (' feiyu1 ') | | mysql-bin.000033 | 529 |     Xid |     1 | 556 |
COMMIT/* XID=14 * | +------------------+-----+-------------+-----------+-------------+--------------------------------------------- ----------------------------+ 7 rows in Set (0.00 sec) mysql> Show Binlog events in ' mysql-bin.000033 ' from 40   7; #也可以从某个位置查看二进制文件 +------------------+-----+------------+-----------+-------------+------------------------------ -------------------------------------------+
| Log_name | Pos | Event_type | server_id | End_log_pos |
Info | +------------------+-----+------------+-----------+-------------+---------------------------------------------- ---------------------------+
| mysql-bin.000033 | 407 | Query |     1 | 529 | Use ' Mysql_shiyan '; INSERT INTO department (dpt_name) VALUES (' feiyu1 ') | | mysql-bin.000033 | 529 |     Xid |     1 | 556 |
COMMIT/* XID=14 * | +------------------+-----+------------+-----------+-------------+----------------------------------------------  ---------------------------+ 2 rows in Set (0.00 sec) mysql> purge binary logs to ' mysql-bin.000025 ';
#删除某个序号之前的日志文件 Query OK, 0 rows affected (0.04 sec) mysql> Show binary logs; +------------------+-----------+
| Log_name |
File_size | +------------------+-----------+
|    mysql-bin.000025 | 107 | |   mysql-bin.000026 | 4509 | |    mysql-bin.000027 | 150 | |    mysql-bin.000028 | 150 | |    mysql-bin.000029 | 150 | |    mysql-bin.000030 | 357 | |    mysql-bin.000031 | 107 | |    mysql-bin.000032 | 150 | |    mysql-bin.000033 |
556 |
 +------------------+-----------+ 9 rows in Set (0.00 sec)

To view binary log content using the command mysqlbinlog:
Basic syntax:

Mysqlbinlog [Options] Log-files
Common options (similar to byte offset):
--start-position: Start position
--stop-position: End Position
--start-datetime ' Yyyy-mm-dd hh:mm:ss ': Start time
--stop-datetime ' Yyyy-mm-dd hh:mm:ss ': End time

← #4 #root@localhost ~→mysqlbinlog--start-position 407--stop-position 556 mysql-bin.000033/*!40019
SET @@ session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE =@ @COMPLETION_TYPE, completion_type=0*/;
DELIMITER/*!*/;
Mysqlbinlog:file ' mysql-bin.000033 ' not Found (errcode:2)
DELIMITER;
# End of log file
ROLLBACK/* Added by Mysqlbinlog * *;
/*!50003 SET completion_type= @OLD_COMPLETION_TYPE * *;

5, Relay Log

I'll talk about it later.

6, transaction log

The transactional storage engine is used to guarantee (ACID) atomicity, consistency, isolation, and persistence, and it is not immediately written to the data file, but is written to the transaction log.
Innodb_flush_log_at_trx_commit:
——— –> 0: Sync per second, and perform disk flush operations;
——— –> 1: Synchronize each transaction and perform disk flush operations;
——— –> 2: Synchronize each transaction, but do not perform disk flush operations;

Mysql> show global variables like ' innodb_flush_log_at_trx_commit ';
+--------------------------------+-------+
| Variable_name         | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1   |
+--------------------------------+-------+
1 row in Set (0.00 sec)

Three, MySQL in the log-related commonly used server variable description:

expire_logs_days={0..99}

Sets the number of days that the binary log expires, and the binary log files that exceed this number of days are automatically deleted. The default is 0, which means that the Expiration automatic deletion feature is not enabled. If this feature is enabled, automatic deletion usually occurs when MySQL starts or flush logs. Scope is global and can be used for configuration files, which belong to dynamic variables.

general_log={on| OFF}

Determines whether the query log is enabled, and the default value depends on whether the –general_log option is used when starting mysqld. If this is enabled, its output location is defined by the –log_output option, and if the Log_output value is set to none, the query log is enabled and no log information is logged. Scope is global and can be used for configuration files, which belong to dynamic variables.

General_log_file=file_name

The log file name of the query log, which defaults to "Hostname.log". Scope is global and can be used for configuration files, which belong to dynamic variables.

binlog-format={row| Statement| MIXED}

Specifies the type of binary log, defaults to statement, and recommends to mixed. If the binary log format is set, but the binary log is not enabled, the MySQL startup generates warning log information and is logged in the error log. Scope is global or session, available for configuration files, and belongs to dynamic variables.

Log={yes|no}

Log information that records all statements is enabled in the General query log, which is usually off by default. MySQL 5.6 has deprecated this option.

Log-bin={yes|no}

Whether to enable binary logging, if the –log-bin option is set for MYSQLD, its value is on, or off. It is only used to show whether binary logs are enabled, and does not reflect the Log-bin set value. The scope is a global level and is a non dynamic variable.

log_bin_trust_function_creators={true| FALSE}

This parameter is only valid when binary logging is enabled, and is used to control whether a stored function is prevented from creating a stored function if it causes an unsafe event record binary log condition. The default value is 0, which means that unless the user has Super permissions in addition to the create routing or alter routine permissions, the stored function is prevented from being created or modified, and the deterministic property must be used for the function when it is created. Or else it would be a reads SQL data or no SQL attribute. Setting the value to 1 does not enable these restrictions. Scope is global and can be used for configuration files, which are dynamic variables.

Log_error=/path/to/error_log_filename

Define error log files. Scope is global or session level, available for configuration files, and is not a dynamic variable.

log_output={table| file| NONE}

Defines how the general query log and the slow query log are saved, either as a table, file, NONE, or as a combination of table and file (separated by commas), and the default is table. If none is present in the group, the other settings are invalidated, and no related log information is logged, regardless of whether the log feature is enabled or not. Scope is global and can be used for configuration files, which are dynamic variables.

log_query_not_using_indexes={on| OFF}

Sets whether query operations that do not use indexes are logged to the slow query log. Scope is global and can be used for configuration files, which are dynamic variables.

Log_slave_updates

Used to set the update operations in the replication scenario that are received from the server from the primary server into the native binary log. This parameter is set to take effect by enabling the binary logging feature from the server.

Log_slow_queries={yes|no}

Whether to record a slow query log. A slow query means that the execution time of a query exceeds an event that is longer than the Long_query_time parameter set. MySQL 5.6 Modifies this parameter to Slow_query_log. Scope is global and can be used for configuration files, which are dynamic variables.

log_warnings=#

Sets whether the warning message is logged in the error log. The default setting of 1, which is enabled, can be set to 0 to disable, and its value greater than 1 indicates that the error message for the failed connection and deny access classes that originated when the new connection was initiated is also logged in the error log.

long_query_time=#

Set the difference between a slow query and a general query's statement execution time length. The statements here are executed at actual execution time rather than on the CPU, so slower queries are more likely to occur on heavier servers. The minimum value is 0, the default value is 10, and the unit is seconds. It also supports the resolution of the millisecond level. Scope is global or session level and can be used for configuration files, which are dynamic variables.

max_binlog_cache_size{4096.18446744073709547520}

Binary log cache space size, 5.5.9 and later versions apply only to transaction caching, and the upper limit is determined by max_binlog_stmt_cache_size. Scope is global and can be used for configuration files, which are dynamic variables.

max_binlog_size={4096.1073741824}

Set the binary log file upper limit, in bytes, the minimum value is 4K, the maximum is 1G, the default is 1G. The log information generated by a transaction can only be written to a binary log file, so the actual binary log file may be larger than this specified limit. Scope is global and can be used for configuration files, which are dynamic variables.

max_relay_log_size={4096..1073741824}

Sets the upper volume limit of the relay log from the server, and automatically scrolls the relay log when it reaches this limit. When this parameter value is 0 o'clock, Mysqld will use the Max_binlog_size parameter to set the log file volume limit for both the binary and the relay logs. Scope is global and can be used for configuration files, which are dynamic variables.

innodb_log_buffer_size={262144.4294967295}

Sets the size of the log buffer used to assist in the completion of log file writes, in bytes and by default of 8MB. \ innodb Larger transactions can rely on larger log buffers to avoid writing log buffer data to the log file before the transaction completes to reduce I/O operations and thereby improve system performance. Therefore, in a scenario with a larger transaction, it is recommended that you set a larger value for this variable. Scope is global and can be used for option files, which are non dynamic variables.

innodb_log_file_size={108576.4294967295}

Set 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 the 1/n from 1MB to the volume of the cache pool, where n represents the number of log files in the log group. The larger the log file, the fewer checkpoint brush writes you need to perform in the cache pool, which means fewer I/O operations are required, but this can also lead to slower failure recovery rates. Scope is global and can be used for option files, which are non dynamic variables.

Innodb_log_files_in_group={2.100}

Sets the number of log files in the log group. InnoDB use these log files in a circular fashion. The default value is 2. Scope is global and can be used for option files, which are non dynamic variables.

Innodb_log_group_home_dir=/path/to/dir

Sets the storage directory for InnoDB redo log files. When all the variables associated with the InnoDB log are used by default, the default is to create two log files in the data directory, named Ib_logfile0 and Ib_logfile1, with a size of 5MB. Scope is global and can be used for option files, which are non dynamic variables.

innodb_support_xa={true| Flase}

Storage engine transactions are given acid properties within the storage engine, and distributed (XA) transactions are a high-level transaction that extends the ACID properties outside the storage engine, even outside the database, using the "prepare" and "submit" (Prepare-then-commit) two-segment approach. However, the "prepare" phase results in additional disk brush writes. XA requires a transaction coordinator, which notifies all participants to commit the transaction (phase 1). When the coordinator receives "ready" information from all participants, it instructs all participants to make a true "submit" action.
This variable is precisely the distributed transaction that defines whether InnoDB supports two-segment commits and is enabled by default. In fact, all MySQL servers that have binary logs enabled and that support multiple threads to write data to the binary log need to enable distributed transactions, otherwise, multiple threads writing to the binary log may be done in a different way from the original order, This will result in the creation of different raw data from the recovery operation based on the binary log or from the server. Therefore, you should not disable this feature except for a single thread that can change other scenarios than the data. In an application where only one thread can modify data, disabling this feature is safe and can enhance the performance of the InnoDB table. Scope is global and session level, available for option files, and is a dynamic variable.

Relay_log=file_name

Sets the file 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. Scope is global and can be used for option files, which are non dynamic variables.

Relay_log_index=file_name

Sets the index file name for the relay log, which defaults to Host_name-relay-bin.index in the data directory. Scope is global and can be used for option files, which are non dynamic variables.

Relay-log-info-file=file_name

Sets the file that the relay service uses to record relay information by default to the Relay-log.info in the data directory. Scope is global and can be used for option files, which are non dynamic variables.

relay_log_purge={on| OFF}

Sets whether to automatically clean up the relay logs that are no longer needed. The default value is on. Scope is global and can be used for option files, which are dynamic variables.

relay_log_space_limit=#

Sets the amount of free space used to store all relay log files. The default is 0, which means no qualification. The maximum value depends on the number of system platform bits. Scope is global and can be used for option files, which are non dynamic variables.

slow_query_log={on| OFF}

Sets whether slow query logging is enabled. 0 or off means disabled, 1 or on indicates enabled. The output location of the log information depends on the definition of the log_output variable, and if the value is None, no slow query information is logged, even if Slow_query_log is on. Scope is global and can be used for option files, which are dynamic variables.

Slow_query_log_file=/path/to/somefile

Set the name of the slow query log file. The default is Hostname-slow.log, but can be modified by the –slow_query_log_file option. Scope is global and can be used for option files, which are dynamic variables.

sql_log_bin={on| OFF}

Used to control whether binary log information is logged into the log file. The default is on, which means logging is enabled. The user can modify the value of this variable at the session level, but it must have the super permission. The scope is global and session level, and is a dynamic variable.

sql_log_off={on| OFF}

Used to control whether General query log class information is prevented from being logged into the query log file. The default is off, which means that the logging feature is not disabled. The user can modify the value of this variable at the session level, but it must have the super permission. The scope is global and session level, and is a dynamic variable.

sync_binlog=#

How long does it take to sync a binary log to a disk file, 0 means no sync, and any positive value indicates that the binary is synchronized once for every number of writes. When the value of autocommit is 1 o'clock, the execution of each statement causes a binary log synchronization, or the commit of each transaction causes a binary log synchronization. The recommended setting is 1.