Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief description of what are the main logs of Mysql. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on the main logs of Mysql can bring you some practical help.

1 Overview

This article will introduce the related concepts of six kinds of logs of Mysql as follows

Query log: general_log

Slow log: log_slow_queries

Error log: log_error, log_warnings

Binary log: binlog

Relay log: relay_log

Transaction log: innodb_log

2 query log

It is not enabled by default. Considering that the database cloud server itself occupies the storage IO, the query log function is generally not used to prevent performance degradation to the mysql.

Record query statement, log storage location:

File: file

Table: table (mysql.general_log), in the mysql library

General_log= {ON | OFF}: general log, switch of log function

General_log_file=HOSTNAME.log: the location of the log output, there are two places, such as the file specified by general_log_file, and the following TABLE

Log_output= {FILE | TABLE | NONE}. When you start general_log, you should also specify log_output as file or table. If it is defined as file, the general_log table is useless. If you want to record both, it is defined as file,table. If you do not log, set it to none. Even if you set it to on, the path to log is also specified, and the log will not be recorded. The default is file.

By default, the log file is the current hostname .log (note that you need to reverse parse the ip locally to get the hostname to be the current hostname, otherwise it defaults to localhost.log), there are no parameters at the session level, only at 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 parameter can also take effect immediately, as follows

MariaDB [mysql] > set @ @ global.general_log=on

When enabled, after performing relevant operations in the database, the log file is under the current hostname .log. For example, here is CentOS7A.log. If the log file is a relative path, then by default the log is under the data directory, that is, under / var/lib/mysql/. The file here is / var/lib/mysql/CentOS7A.log. The file contains detailed records of database operations. Check below.

Cat / var/lib/mysql/CentOS7A.log

Change to TABLE, and all logs will be recorded in the mysql.general_log table, and the file / var/lib/mysql/CentOS7A.log will no longer be recorded.

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

3 slow query log

Slow query: a query that runs for more than a specified length of time, which generally refers to the length of time specified by long_query_time

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

You can view variables in two ways

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

MariaDB [mysql] > select @ @ global.long_query_time

It is not blocked by other statements or the query time of the statement itself is very long, and the query is very slow. It is necessary to enable slow query to record. This is generally enabled and is used to analyze problems related to the system, such as slow web page opening.

Storage location:

File: FILE

Table: TABLE,mysql.slog_log

Log_slow_queries= {ON | OFF}: enable slow query, slow_query_log and enable slow query. The parameters enabled in different mysql versions may be different, and there may be two parameters at the same time. If you want to ensure that it can be recorded, it is recommended to enable both parameters.

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 condition defined here is queried for more than 10 seconds before the slow query log is recorded. These are usually caused by the slowness of the sentence itself.

Log_slow_rate_limit

Log_slow_verbosity

Slow query is enabled. Note that the log does not record all operations, but only the conditions defined in the log_slow_filter field. The log is recorded in / var/lib/mysql/CentOS7A-slow.log.

MariaDB [mysql] > set @ @ global.slow_query_log=ON

Enabling slow_query_log Bool values does not require quotation marks, such as ON or off

The above definition is temporarily valid. If the global parameter restarts mysql, the session-level shutdown session will become invalid. To be effective for a long time, you need to configure it in the configuration file.

4 error log

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

(1) the information output during mysqld startup and shutdown

(2) error messages generated during the operation of mysqld

(3) the information generated by the event scheduler runtime; # is equivalent to the cycle plan within the mysql, which has the same effect as the system crontab.

(4) Log generated when the replication thread starts from the CVM in the master-slave replication architecture

Log_error=

/ var/log/mariadb/mariadb.log | OFF # specifies the file path, which means ON. If log_error is not enabled, use OFF to set it directly.

Log_warnings= {ON | OFF}: # ON indicates whether to record warning-level logs in log_error

Through the mysql installed by rpm, error logging is enabled by default, and the decompression installation may not be enabled. Generally, the error logging feature is enabled.

MariaDB [mysql] > select @ @ global.log_error

5 binary log

At the CVM level, it has nothing to do with the engine and can only take effect by changing the configuration parameters. However, it is supported to disable or take effect at the session level. Generally, the feature is not disabled. Only when replay is used, that is, when the recovery is done, the feature is disabled. Changes need to be made in the configuration file to take effect. However, you can control whether to record the corresponding session binary log at the session level.

Modification operations during the operation of mysql (operations that cause or may cause data changes) are recorded here before they are saved. The binaries, which have nothing to do with the engine, are not relevant to the engine and cannot be viewed using text tools such as cat, which may cause the files to be corrupted because they are saved in non-binary format. Each record is saved without event,event recording according to the location (determined according to the offset, such as the first event 0mur16, but the last 16 is the end of the first event, is not temporarily used by the first event, and is the beginning of the second event, such as the second event is 16Muk 36, where 36 is the start of the third event), which is recorded in binary code here. The function is to be accurate enough to record the start and end of each event in bytes.

As a recording event, record the time and action of the event

Binary log files are used for playback and can be used to restore the system when the system crashes. It is recommended to put the log in a separate location, use absolute logs, and ensure that mysql users have read and write permissions. The configuration file needs to be modified to take effect

There are three formats for binary logging

Binlog_format= {STATEMENT | ROW | MIXED}

STATEMENT: statement, that is, record operation commands

ROW: OK

MIXED: mixed compilation

Used to record statements that cause or potentially cause changes in data (STATEMENT) or results after changes (ROW), or a combination of the two

Record rows get more accurate results, but the quantity is relatively large, and the record "statement" is relatively light. If the definition asks MIXED, the system will decide to record it in the statement or line format. Default real-time statement

Note that if you execute a statement, the inserted field is generated by function execution, such as the now () function, the time will be different every time, and the result will be different the next time you execute it again. So what is recorded is the execution result, so that when the execution is re-executed, the result is the same. This recording method is called record row.

View the current format as follows:

MariaDB [mysql] > show global variables like 'binlog_format'

CVM variables:

Log_bin=/PATH/TO/BIN_LOG_FILE

Read-only variable; it is recommended not to use the data directory, that is, / var/lib/mysql, because the binary log is the method used to recover the data in the future, which is very important. If the binary log and the data directory are placed on the same disk, all the contents will not be recovered if the disk fails in the future, so it is recommended to set another path.

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

[root@CentOS7A mysql] # mkdir-pv / mydata/ {data,log}

[root@CentOS7A mysql] # chown-R mysql.mysql / mydata/*

[root@CentOS7A 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 and exiting

[root@CentOS7A mysql] # systemctl restart mariadb

A binary file master-log.000001 is generated under the path / mydata/log, which scrolls, restarts the service, or scrolls with the flush logs command, and the file suffix changes automatically. There is also a file master-log.index under the path, which is not a data index, but a log file index, in a text format. For example, when there are many files like master-log.000001, the files identified by mysql are recorded in master-log.index.

Master-log.000001 is a binary file, which cannot be viewed directly with text viewing tools such as cat. The way to view the binary file master-log.000001 is as follows

Go to mysql

To see how many binaries there are, as follows, this command is the same as looking at the contents of master-log.index directly

MariaDB [sunny] > show binary logs

+-+ +

| | Log_name | File_size |

+-+ +

| | master-log.000001 | 245 |

+-+ +

1 row in set (0.00 sec)

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

MariaDB [sunny] > flush logs

View a list of binary log files:

SHOW MASTER | BINARY LOGS

MariaDB [sunny] > show master logs

MariaDB [sunny] > show binary logs

View the binary log files that are currently in use:

SHOW MASTER STATUS

To view the binary file currently in use, Position refers to the location of the byte to which it is currently written, which generally does not start from 0, because the log has self-describing information, that is, the file header, recording which version of the log file is, which version is compatible, and so on. The default is 245.

MariaDB [sunny] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | master-log.000003 | 245 |

+-+

View the events in the binary log file:

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

To view the contents of the binary file record, Pos is the start position, End_log_pos is the end location, Event_type event type, Info event description information, in which xid=16 is the identifier of the mysql record

Server_id is the only information that records the CVM. If you do not work together, the default id is 1. The id number in the same cluster cannot be the same. You need to modify this parameter manually.

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)

View the log from the specified location, which must be the starting position, not any number in the middle

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

View the log from the specified location, offset by 2 lines

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

Session.sql_log_bin= {ON | OFF}

Controls whether write operation statements in a session are recorded in the log file

The shutdown statement is as follows, after which the record of the session will not be recorded in the binary file. It is recommended that you do not close it, but only turn it off when you replay it.

MariaDB [sunny] > set @ @ session.sql_log_bin=off

Max_binlog_size=1073741824 # writes to the configuration file and defines the maximum value of the binary file, in bytes byte,1073741824 of 1G, but the actual size of the file may not be accurate. If you scroll beyond this value, the file may not scroll to this value, and the size will differ by a little.

Sync_binlog= {1 | 0} # synchronizes logs from memory to the hard disk, which is usually cached in the memory area and then synchronized to the hard disk after a period of time. This advantage is high efficiency, but it may cause abnormal data to be lost in memory. There is an extreme situation here, if the lost data is commint, so the data cannot be recovered. If it is set to 1, that is, the current operation is commit, it will be recorded to disk immediately. Data playback is more secure, but the performance will be degraded. You need to decide whether to enable the function or not according to the actual situation.

The main function of mysqlbinlog:#, a command line tool, is to view the contents of the log. Generally, the beginning of the 245 bytes is the header of the file, and the information is all after this header, and the general information is recorded in a specific format. The first # at begins with the header information, and the operation record of each event begins with # at and ends with end_log_poss. In general, add, delete, change and search operations are to be queried first, so they are all Query. The execution time exec_time that is less than 1 second is recorded as 0.error_code if there are no errors, it is 0. Each connection is a thread thread_id.

Related options:

-- start-datetime=

-- stop-datetime=

Note that the time format here is: YYYY-MM-DD hh:mm:ss

Example:

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

-j,-- start-position=# indicates where to start

Where does the stop-position=# end?

Example: where to start viewing

[root@CentOS7A log] # mysqlbinlog / mydata/log/master-log.000001-j 314

-user,-host, password

This command is a client command and can be executed remotely. Note that the file cannot be quoted.

However, when testing, the prompt file does not exist

[root@CentOS7E] # mysqlbinlog-uroot-pPass123456-h 192.168.1.71 / mydata/log/master-log.000001

Example: no options

[root@CentOS7A log] # mysqlbinlog / mydata/log/master-log.000001

Attach 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

/ *! * /

The start of the event: # at 553

Date and time of the event: # 160831 9:56:08

Id:server id 1 of the CVM where the event occurred

Where the event ends: end_log_pos 624

Type of event: Query

ID: thread_id=2 of the thread in which the event was executed on the cloud server where the event occurred

The time stamp of the statement and the time difference between writing it to the binary log file: exec_time=0

Error code: error_code=0

Set the timestamp of the event: SET timestamp 1472608568

Event content: BEGIN

6 Relay Lo

Events synchronized from the binary log files of the master CVM are recorded from the CVM. When mysql master-slave replication, the binary is the log recorded on the master CVM. By relaying the logs from the CVM, the events are replayed locally from the CVM to get the same binary file. To make the master-slave copy the same.

Another function of binary log is to do point-in-time restore, not necessarily for master-slave replication, this recovery method is very effective, because even if it is a backup operation, when the backup does not have a backup to the time period from the beginning of the backup to the crash of the system, you can use this binary log to restore data that is not backed up based on time.

7 transaction log

Log files used by the transactional storage engine innodb to guarantee transaction characteristics:

Redo log

Undo log

What are the main logs of Mysql to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.