About MySQL Logs

The logs in MySQL are mainly divided into the following categories:

Query log

Slow query log

Error log

Binary log

Relay Log

Transaction log

Description

The Linux system supported in this experiment is the CENTOS7 version, the database used is base originated with the MARIADB, the storage engine used by the database uses the default InnoDB

1. Query log

Record query statements, log storage locations

The log is stored in two places: one is stored in the specified file and one is stored in the specified table. Considering I/O pressure, it is not common for both to record

MariaDB [mysql]> SHOW VARIABLES like ' general% '; +------------------+-------------+| variable_name | Value |+------------------+-------------+| General_log | On | | General_log_file | Centos7.log |+------------------+-------------+2 rows in Set (0.00 sec) #general_log默认是关闭的

As can be seen from the above code, the query log has two variables;

Below to view the details of the General_log table:

mariadb [mysql]> desc  general_log;+--------------+------------------+------+-----+---- ---------------+-----------------------------+| field        |  type             | null |  key | default           | extra                         |+--------------+------------------+------+-----+-------------------+----------- ------------------+| event_time   | timestamp (6)      |  no   |     | current_timestamp | on update  current_timestamp | |  user_host    | mediumtext       | no   |     | null               |                              | |  thread_id    | int (one)            | NO   |     | NULL               |                               | |  server_id    | int (Ten)  unsigned | NO   |      | NULL               |                              | |  command_type | varchar (      | no   |)      | NULL               |                              | |  argument     | mediumtext       |  no   |     | null               |                              |+--------------+------------------+------+-----+-------------------+-----------------------------+6  rows in set  (0.00 SEC)

Event time

User Host

The process ID of the event

Service ID

Command type

Parameters

The above information constitutes the main content of General_log, that is, the contents of the logged query log can be displayed by the above information.

SET @ @global. General_log=on; #默认此变量是全局的, but can take effect immediately after modification

After opening the query log, we then perform some query operations such as:

SELECT User,host,password from Mysql.user;

These query operations are logged to the log file Centos7.log file, which is the default file that can be modified. This file is stored in a relative path that is/var/lib/mysql/

Cat/var/lib/mysql/centos7.log #能看到记录的查询操作了

The above shows that the log information is recorded in the file, the log can also be recorded in the specified table, as long as a variable can be modified:

SET @ @global. log_output=table; #默认是FILE

Log records about tables are similar to file records and no longer demonstrate

2. Slow query log

Slow query: A query instruction run time beyond a certain length of operation, this operation will be greatly discounted user experience, should try to avoid

It is generally recommended to start this slow query log feature

MariaDB [mysql]> SELECT @ @global. long_query_time;+--------------------------+|                @ @global. long_query_time |+--------------------------+| 10.000000 |+--------------------------+1 row in Set (0.00 sec)

The default slow query time is 10 seconds

SELECT @ @global. slow_query_log; #默认关闭慢查询日志SELECT @ @global. slow_query_log_file;#  The default file name is called hostname-slow.log, such as Centos7-slow.log SELECT @ @global. log_output; #默认是文件记录

Not all command executions take longer than 10 seconds to be recorded, which is a filter

SELECT @ @global. log_slow_filter; #此变量指定的指令才会使用慢查询

The usage of the slow query log is basically the same as the general_log, no longer repeating

3. Error log

As the name implies, the error message is logged and the following types of information are mainly recorded:

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

(2) Error information generated by MYSQLD operation;

(3) Information generated when the event scheduler is run;

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

If the error log is open, you can use the following command to view

SELECT @ @global. log_error; #默认是开启的 that specifies the specific log file path, such as/var/log/mariadb/mariadb.log #如果未开启, the result of the query is off

4. Binary Log

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;

Role:

Replay (replay), that is, a binary log can be used to re-operate the instructions before a failure occurs

Imagine, after a full-scale backup of the database, a day after the primary database device suddenly failed, while we can use a full-scale backup to recover the database, but the data before the previous date has not time to back up, that is to say

One day less data, in which case you can use the binary log to recover the missing day information;

Here is an example of the important role of binary logs in data recovery:

• Create a database, table, insert data
  

CREATE DATABASE RDBMS; Use Rdbms;create TABLE tbl (id int primary Key,name char (a) not null,age int.); INSERT into TBL VALUES (1, ' Xiao WA Ng ', (2, ' Xiao Li ', 22);

• Check Information

mariadb [rdbms]> desc tbl;         +-------+--- -------+------+-----+---------+-------+| field | type     | null  | key | default | extra |+-------+----------+------+-----+---------+------ -+| id    | int (one)   | no   | pri |  null    |       | |  name  | char ()  | NO   |     |  null    |       | |  age   | int (one)   | NO   |      | null    |       |+-------+----------+----- -+-----+---------+-------+3 rows in set  (0.00 sec) mariadb [rdbms]> select * from tbl;+----+-----------+-----+| id | name       | age |+----+-----------+-----+|  1 | xiao wang  |  20 | |   2 | xiao li   |  22 |+----+-----------+-----+2  rows in set  (0.00 SEC)

• Modify the MySQL service configuration file to turn on the binary logging feature
  

RPM-QL mariadb-server# can find the location of the configuration file Vim/etc/my.cnf.d/server.cnf[server]log_bin =/app/logs/master-log# Specify the storage path of the binary log, The reason why the configuration file is modified without a command line is because the command line does not support the ability to turn on binary logging

• viewing binary log information

SHOW master| BINARY LOGS; #查看二进制日志文件列表SHOW MASTER STATUS; #查看当前正在使用的二进制日志文件SHOW BINLOG EVENTS in ' master-log.000001 '; #查看二进制日志文件的事件信息

• Make a full backup of the current database
  

Mysqlbinlog-uroot-p master-log.000001 >/root/binlog.sql#binlog.sql is the backed up database file #mysqlbinlog is a mysql-brought binary logging tool

When the row is restored on a new backup server

MySQL </root/binlog.sql

Recover as described above

Binary files under/app/logs/cannot be viewed directly using cat or less, so you need to use the Mysqlbinlog special tool

Mysqlbinlog-uroot-pcentos master-log.000007-j 430--stop-position=874#-j Specify start position #--stop-position Specify end position

The content that you view contains the following information:

Start 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

The above is a brief introduction to MySQL logs

This article from "A_pan" blog, declined reprint!

About MySQL Logs