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