MySQL log introduction to the database

1 Overview

This article will cover the following six types of MySQL logs related concepts

Query log: General_log

Slow query log: log_slow_queries

Error log: Log_error, log_warnings

Binary log: Binlog

Relay log: Relay_log

Transaction log: Innodb_log

2 Query Log

Default is not turned on, considering that the database server itself occupies the storage Io, so the function of query log will not be used to prevent performance degradation to MySQL

Log query statements, where the logs are stored:

Files: File

Tables: Table (Mysql.general_log), in MySQL library

general_log={on| OFF}: General log, log function switch

General_log_file=hostname.log: The location of the log output, there are two places, such as General_log_file the specified file, and the following table,

log_output={file| table| NONE}, when starting General_log, also specify Log_output as file or table, if defined as file, then general_log This table is useless, if you want to record both, it is defined as file,table, If the log is not logged, it is set to none, even if it is set to on, the path of log is specified, the log is not logged, and the default is file.

By default, the log file is the current hostname. log (note that the host name can be obtained by the native reverse resolution IP, otherwise the default is Localhost.log), there is no session-level parameter, only the global level, see the following

MariaDB [mysql]> show global variables like ' general% ';

Modified at the global level, but this parameter does not support the session level, so the parameters can also take effect immediately, set the following

MariaDB [mysql]> SET @ @global. General_log=on;

After opening, after performing the related operation in the database, the log file under the current hostname. Log, if this is CentOS7A.log, if the log file is a relative path, then the default log is in the data directory, that is/var/lib/mysql/below, where the file is/var/lib/ Mysql/centos7a.log, the file has a detailed record of the database operation, see the following

Cat/var/lib/mysql/centos7a.log

Change to table type, all logs will be recorded in Mysql.general_log this table, file/var/lib/mysql/centos7a.log will no longer be recorded

MariaDB [mysql]> SET @ @global. log_output= ' table ';

3 Slow query log

Slow query: Run time is longer than the specified length of the query, generally refers to the length of the long_query_time specified;

Long_query_time: Long execution time, default unit is seconds, default is 10s

View variables can be viewed in the following two ways

MariaDB [mysql]> show global variables like ' long% ';

MariaDB [mysql]> SELECT @ @global. long_query_time;

is not blocked by other statements or the statement itself query time is very long, the query is very slow, you need to enable slow query to record, this is generally enabled to analyze the system related issues, such as Web page open slow problem

Storage location:

Files: File

Table: Table,mysql.slog_log

log_slow_queries={on| OFF}: Enable slow query, Slow_query_log and is enabled slow query, different MySQL version of the parameters may not be the same, there may be two parameters, if you want to ensure that can be recorded, it is recommended that both parameters are enabled

slow_query_log={on| OFF}

slow_query_log_file=

log_output={file| table| NONE}

Log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp _table_on_disk: The criteria defined here are more than 10s before logging to the slow query log. These are usually caused by the slow statement itself.

Log_slow_rate_limit

Log_slow_verbosity

Slow query is enabled, note that the log does not log all operations, only the conditions defined by the Log_slow_filter field are recorded, the log is recorded in/var/lib/mysql/centos7a-slow.log

MariaDB [mysql]> SET @ @global. Slow_query_log=on;

Enable Slow_query_log bool Type value does not need to be quoted, such as on or off

The above definition is temporary valid, the global parameters restart MySQL after the failure, session-level shutdown session will expire, to long-term effective, need to be configured in the configuration file

4 error log

Record the following four types of information: including three non-error logs, as follows

(1) Mysqld The information that is output during startup and shutdown;

(2) Error information generated by MYSQLD operation;

(3) Information generated by the event scheduler operation; #相当于是mysql内部的周期计划, plan for the same effect as the system crontab

(4) in the master-slave replication schema, the log generated from the server copy thread when it is started;

Log_error=

/var/log/mariadb/mariadb.log| Off #指定文件路径, that is, on, if Log_error is not enabled, it is set directly with off

log_warnings={on| OFF}: #ON表示是否将warning级别的日志记录到log_error里面

MySQL installed through RPM, by default, the error log is enabled, the decompression of the installation may not be enabled, generally to enable the error logging feature.

MariaDB [mysql]> SELECT @ @global. log_error;

52 Binary Log

Server level, with the engine does not matter, can only change the configuration parameters to take effect, but the support at the session level is specified to close or take effect, generally do not turn off the function, only to use replay, that is, when the recovery is done, the function is turned off. Changes in the configuration file are required to take effect. However, you can control whether you want to log binary files for the corresponding session at the session level log

MySQL operation in the process of modifying the class operation (causing data changes, or may cause data changes in the operation), not saved, will be recorded here to the binary files, server level, and engine-independent, binary files, can not use cat and other text tools to view, may lead to file corruption, Since it is saved in the binary format, each record is saved without event,event according to the location (depending on the offset, such as the first event 0--16, however, the last 16 is the end of the first event, not taken up by the first event, is the start of the second event, such as the second event is 16- -36, here 36 is the third event start), here with the binary encoding to record, the function is accurate to the byte to record to record each event's starting and ending position

As a record event, record the time and action at which the event occurred

Binary log file, the role is used to replay, when the system crashes, can be used to restore the system, it is recommended to put the log in a separate location, with absolute logs, while ensuring that the MySQL user has read and write permissions. The configuration file needs to be modified before it takes effect

Binary logging is available in three formats

Binlog_format={statement| row| MIXED}

STATEMENT: statement, which is the Record action command

Row: line;

MIXED: Mixed;

A statement (STATEMENT) or a modified result (ROW) that records the potential for data changes or the possibility of a change in data, or a mixture of the two;

The record line gets more accurate results, but the volume is relatively large, the "statement" is comparatively light, and if the definition asks mixed, the system decides to record it in the form of a statement or line. Default real-time statement

Note that when a statement is executed, the inserted field is generated by the function, such as the Now () function, and the time is different for each execution, and the result is different the next time the execution occurs. Therefore, the result of the execution is recorded, so that the results are the same when re-executing. This recording mode becomes the record row

View the current format as follows:

MariaDB [mysql]> show global variables like ' Binlog_format ';

Server variables:

Log_bin=/path/to/bin_log_file

Read-only variables; it is recommended that you do not use the data directory, that is,/var/lib/mysql, because the binary log is the way to recover data in the future, it is important that if the binary log and data directory on the same disk, in the future, if the disk failure, all the content is lost can not be restored, so it is recommended to set the path

Example: Create two directories, separate the data from the log, mount it on separate disks, and change the array and owner, modify the configuration file, specify the path

[[email protected] MySQL] #mkdir-pv/mydata/{data,log}

[[email protected] MySQL] #chown-R mysql.mysql/mydata/*

[[email protected] MySQL] #vim/etc/my.cnf.d/server.cnf

Log_bin =/mydata/log/master-log# definition log file cannot be suffixed

Restart MySQL service after saving exit

[[email protected] MySQL] #systemctl restart mariadb

A binary file master-log.000001 is generated under path/mydata/log, the binaries are scrolled, the service is restarted, or the flush Logs command scrolls, and the file suffix is automatically changed. This path also has a file Master-log.index, which is not a data index, but a log file index, text format, For example, when master-log.000001 is a lot of files, the files that are recognized by MySQL are recorded in Master-log.index.

MASTER-LOG.000001 is a binary file and cannot be viewed directly with a text-viewing tool such as cat, and the master-log.000001 method for viewing binary files is as follows

to MySQL.

See how many binaries, such as the one below, are the same as the direct view master-log.index content

MariaDB [sunny]> show binary logs;

+-------------------+-----------+

| Log_name | File_size |

+-------------------+-----------+

|       master-log.000001 | 245 |

+-------------------+-----------+

1 row in Set (0.00 sec)

To manually scroll through the log, you can use the following command

MariaDB [sunny]> flush logs;

To view a list of binary log files:

SHOW master| BINARY LOGS;

MariaDB [sunny]> show master logs;

MariaDB [sunny]> show binary logs;

To view the binary log files that are currently in use:

SHOW MASTER STATUS;

View the currently in use of the binary file, position refers to which byte is currently written to the location, generally not starting from 0, because the log has a self-describing information, that is, the file header, the record of course the log file is which version, compatible with which version, and so on, the default is 245 from the location of the start

MariaDB [sunny]> Show Master status;

+-------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+-------------------+----------+--------------+------------------+

|      master-log.000003 |              245 |                  | |

+-------------------+----------+--------------+------------------+

To view the events in a binary log file:

SHOW BINLOG EVENTS [in ' Log_name '] [from POS] [LIMIT [offset,] row_count]

View the contents of a binary file record, POS is the start position, End_log_pos is the end position, Event_type event type, info event description information, information, XID=16 is the identifier of the MySQL record

, server_id is the only information that records the server, if do not work together, the default ID is 1, the same cluster ID number can not be the same, you need to manually modify the parameter,

MariaDB [sunny]> show Binlog events in ' master-log.000003 ';

+-------------------+-----+-------------+-----------+-------------+-------------------------------------------- -----+

| Log_name | Pos | Event_type | server_id | End_log_pos | Info |

+-------------------+-----+-------------+-----------+-------------+-------------------------------------------- -----+

|   master-log.000003 | 4 |         Format_desc |         1 | 245 | Server ver:5.5.56-mariadb, Binlog ver:4 |

| master-log.000003 | 245 |         Query |         1 | 314 | BEGIN |

| master-log.000003 | 314 |         Query |         1 | 412 | Use ' sunny '; Delete from students where id=1001 |

| master-log.000003 | 412 |         Xid |         1 | 439 | COMMIT/* xid=15 */|

+-------------------+-----+-------------+-----------+-------------+-------------------------------------------- -----+

4 rows in Set (0.00 sec)

To view log from the specified location, the position value must be the starting position and cannot be any number in the middle

MariaDB [sunny]> show Binlog events in ' master-log.000003 ' from 314;

View log from the specified position, offset by 2 rows

MariaDB [sunny]> show Binlog events in ' master-log.000003 ' from 245 limit 2;

session.sql_log_bin={on| OFF}

Controls whether the "write" action statement in a session is recorded in the log file;

Close the statement as follows, after which the record of the session will not be recorded in the binary file, it is recommended not to close, only when the replay to turn off the function

MariaDB [sunny]> SET @ @session. Sql_log_bin=off;

max_binlog_size=1073741824 #写入配置文件, defines the maximum value of the binary file, in bytes byte,1073741824 is 1G, but the actual size of the file may not be accurate, more than the value of scrolling, the file may not be accurate to this value after scrolling, The size will vary a little.

Sync_binlog={1|0} #把日志从内存同步到硬盘, usually cached in the memory area, after a period of time in the synchronization to the hard disk, the benefit is high efficiency, but it is possible to cause abnormal condition data is also lost in memory. Here is an extreme situation is, if the lost data is commint, so that the data will not recover, if set to 1, is the current operation is a commit, immediately logged to the disk, the data replay more secure, but the performance will be reduced, you need to decide whether to turn on the function according to the actual situation

Mysqlbinlog: #这个命令行工具的主要作用就是用来查看日志的内容, the general beginning of the 245 bytes is the file header, the information is in the file header, the general information has a specific format record, the first # at the beginning of the header information, each event operation record to # At the beginning, end with End_log_poss. General additions and deletions to check the operation are to first query, so are all query. Execution times of less than 1s exec_time are recorded as 0.error_code if no errors are 0. Each connection is a thread thread_id.

Related options:

--start-datetime=

--stop-datetime=

Note Here the time format is: Yyyy-mm-dd hh:mm:ss

Example:

[[email protected] log] #mysqlbinlog/mydata/log/master-log.000003--start-datetime= "2018-01-14 11:27:41"

-j,--start-position= #表示从哪个位置开始

--stop-position= #到哪个位置结束

Example: from which location to start viewing

[[email protected] log] #mysqlbinlog/mydata/log/master-log.000001-j 314

--user,--host,--password

The command is a client command that can be executed remotely, noting that the file cannot be quoted

, however, the hint file does not exist when testing

[[Email protected] ~] #mysqlbinlog-uroot-ppass123456-h 192.168.1.71/mydata/log/master-log.000001

Example: no option

[[email protected] log] #mysqlbinlog/mydata/log/master-log.000001

Enclose the binary log event format:

# at 553

#160831 9:56:08 Server ID 1 end_log_pos 624 Query thread_id=2 exec_time=0 error_code=0

SET timestamp=1472608568/*!*/;

BEGIN

/*!*/;

Starting position of event: # at 553

Date when the event occurred: #160831 9:56:08

event occurs for server Id:server ID 1

End of event location: End_log_pos 624

Type of event: Query

The id:thread_id=2 of the thread where the event occurred when the server executed the event

Time difference between the timestamp of the statement and the write to the binary log file: exec_time=0

Error code: error_code=0

Set the timestamp when the event occurred: set timestamp=1472608568/*!*/;

Event Content: BEGIN

6 Trunk Log

Events from the server that are logged from the binary log file of the primary server; MySQL master-slave replication, the binary is the log on the primary server, from the server through the relay log, from the server on-premises replay, get the same binary files. Make the winner copy the same.

The binary log also has another role is to do a point-in-time restore, not necessarily used for master-slave replication, this recovery method is very effective, because even if the backup operation, when the backup does not have the time to start the backup to the time period of the system crash, you can use this binary log based on time to recover the time period of no backup data

7 Transaction log

Transactional storage Engine InnoDB the log file used to guarantee transaction characteristics:

Redo Log

Undo Log

MySQL log introduction to the database