A simple tutorial on how to use the mysql log system

Preface

MySQL is a relational database management system developed by Swedish company MySQL AB and is a product of Oracle. MySQL is one of the most popular relational database management systems. In terms of WEB applications, MySQL is one of the best RDBMS (Relational Database Management System) application software.

In any database, there will be various logs that record all aspects of database work to help database administrators track various events that have occurred in the database.
In MySQL, there are 4 different logs, namely

• Error Log
• Binary log (BINLOG log)
• Query log
• Slow query log

These logs record traces of different aspects of the database. Learning to use MySQL logs is very helpful for us to use and maintain the database.

1. Error log

The error log is one of the most important logs in MySQL. It records information about when mysqld is started and stopped, and when any serious errors occur during the operation of the server. When the database fails and cannot be used normally, you can check this log first.
This log is enabled by default. The default storage directory is the mysql data directory (var/lib/mysql), and the default log file name is hostname.err (hostname is the host name).

1. Check the log location

show variables like 'log_error%';

2. View log content

tail -f /var/lib/mysql/xxx.err 

2. Binary Log

The binary log (BINLOG) records all DDL (data definition language) statements and DML (data manipulation language) statements, but does not include data query statements. This log plays an extremely important role in data recovery in the event of a disaster. MySQL's master-slave replication is achieved through this binlog.

1. Enable binary logging

The binary log is not enabled by default. You need to enable it in the MySQL configuration file and configure the format of the MySQL log.

• Configuration file location: /etc/my.cnf
• Log storage location: When configuring, if a file name is given but no path is specified, the log will be written to the MySQL data directory by default.

#Configure binlog logging. The log file prefix is ​​mysqlbin -----> The generated file names are as follows: mysqlbin.000001,mysqlbin.000002 
log_bin=mysqlbin
 
#Configure the binary log format binlog_format=STATEMENT

2. Binary log format

(1) STATEMENT

This log format records SQL statements in the log file. Each SQL statement that modifies the data will be recorded in the log file. The text of each statement can be clearly viewed through the mysqlbinlog tool provided by MySQL. During master-slave replication, the slave will parse the log into the original text and re-execute it on the slave.

(2) ROW

This log format records the data changes of each row in the log file instead of recording SQL statements. For example, when executing the SQL statement: update tb_book set status='1', if the log format is STATEMENT, one line of SQL file will be recorded in the log; if it is ROW, since the entire table is updated, that is, every row of records will be changed, the ROW format log will record the data changes of each row.

(3) MIXED

This is the current default log format for MySQL, which is a mixture of STATEMENT and ROW formats. By default, STATEMENT is used, but in some special cases ROW is used for recording. The MIXED format can make the best use of the advantages of both modes while avoiding their disadvantages.

3. Log reading

Since the log is stored in binary format and cannot be read directly, you need to use the mysqlbinlog tool to view it. The syntax is as follows

mysqlbinlog log-file;

4. View STATEMENT format log

(1) Execute the insert statement

insert into tb_book values(null,'Lucene','2088-05-01','0');

(2) Find the binary file in the mysql data file directory

mysqlbin.index is the log index file, which records the log file name. mysql-bin.000001 is the log file

(3) View log files

5. View ROW format logs

(1) Configure in the configuration file

#Configure binlog logging. The log file prefix is ​​mysqlbin -----> The generated file names are as follows: mysqlbin.000001,mysqlbin.000002 
log_bin=mysqlbin
 
#Configure the binary log format binlog_format=ROW

Note: Remember to restart the MySQL service every time you modify the configuration file

service mysql restart

(2) Insert data

If the log format is ROW, you cannot understand the data directly. You can add the parameter -vv after mysqlbinlog. Use the --no-defaults parameter to avoid the impact of the character set.

6.

6. Log deletion

For busy systems, a large amount of logs are generated every day. If these logs are not cleared for a long time, they will take up a lot of disk space. Here are some common ways to delete logs:

(1) Method 1

Delete all binlog logs through the Reset Master command. After deletion, the log number will restart from xxxx.000001.

Before deleting, query the log file:

Execute the delete log command

Reset Master

Check again

The last remaining 000001 is an empty configuration file

(2) Method 2

purge master logs to 'mysqlbin.******

This command will delete all logs before the ****** number.

(3) Method 3

purge master logs vefore 'yyyy-mm-dd hh24:mi:ss'

This command will delete all logs generated before yyyy-mm-dd hh24:mi:ss

(4) Method 4

Set the parameter expire_logs_days=# . This parameter means setting the expiration days of the logs. After the specified number of days, the logs will be automatically deleted. This helps reduce the workload of DBA in managing logs.

3. Query log

The query log records all operation statements of the client, while the binary log does not contain SQL statements for querying data.

By default, query logging is not enabled. If you need to enable query log, you can set the following configuration

#This option is used to enable query log, optional value: 0 or 1; 0 means off, 1 means on general_log=1
 
#Set the log file name. If not specified, the default file name is host_name.log 
general_log_file=file_name

1. Enable query log

2. Perform a set of operations

3. View query log

4. Slow query log

The slow query log records all SQL statements whose execution time exceeds the setting value of the parameter long_query_time and the number of scanned records is not less than min_examined_row_limit. The default value of long_query_time is 10 seconds, the minimum value is 0, and the precision can be microseconds.

1. File location and format

The slow query log is disabled by default. Slow query log can be controlled by two parameters

# This parameter is used to control whether the slow query log is enabled. The possible values ​​are: 1 and 0. 1 means enabled, and 0 means disabled. slow_query_log=1 
 
# This parameter is used to specify the file name of the slow query log slow_query_log_file=slow_query.log
 
# This option is used to configure the query time limit. If the query time exceeds this limit, it will be considered a slow query and logging will be required. The default is 10s 
long_query_time=10 

Generally, it is sufficient to set it to about 2 seconds. In my experiment, in order to complete the slow query, I set it to 0.5 seconds.

The current long_query_time can be queried in the MySQL client

2. Execute query operations

(1) Take a look at the slow query log before querying

(2) Execute a faster query action

(3) Execute a slow query action

If the slow query log contains a lot of content, it is troublesome to view the file directly. At this time, you can use the mysqldumpslow tool that comes with MySQL to classify and summarize the slow query log.

Since there is only one slow query record here, the effect of mysqldumpslow is not reflected, but if there are many slow query records, they can be classified and summarized

Summarize

This is the end of this article about the simple usage tutorial of the MySQL log system. For more relevant MySQL log system content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!