In any kind of database, there are various kinds of logs that record all aspects of the database work to help the database administrator keep track of the various events that have occurred in the database. MySQL is no exception. In MySQL, there are four different types of logs, namely error logs, binary logs, query logs, and slow query logs. These logs record the traces of the database in different aspects.
1. Error logs
The error log is one of the most important logs in MySQL, recording information about when mysqld is started and stopped, and when any serious errors occur during the server’s run. You can view this log when the database cannot be used properly due to any fault.
This log file is enabled by default and stored in the mysql data directory (var/lib/mysql). The default log file name is hostname. Err (hostname is the hostname).
View log location directive:
show variables like 'log_error%';
Copy the code
View log content:
tail -f /var/lib/mysql/xaxh-server.err
Copy the code
2. Binary logs
2.1 an overview of the
The binary log records all DDL (Data Definition Language) statements and DML (Data Manipulation Language) statements, but not the data query statements. This log is very important for data recovery in the event of a disaster. The master/slave replication of MySQL is implemented through this log.
Binary logs are disabled by default. You need to enable binary logs in the MySQL configuration file and configure the format of MySQL logs. CNF Configuration file location: /usr/my. CNF Log location: If the file name is specified but the path is not specified, logs are written to the Mysql data directory by default.
Mysqlbin ----- = mysqlbin -----
mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin
Configure the format of binary logs
binlog_format=STATEMENT
Copy the code2.2 Log Format
STATEMENT
In this log format, all SQL statements are recorded inlog files. Each SQL statement that modifs data is recorded inlog files. You can use the mysqlBinlog tool provided by Mysql to view the text of each statement. In master/slave replication, the slave library parses the log into the original text and executes it again in the slave library.
ROW
This log format records data changes for each row in a log file, rather than logging SQL statements. For example, run the following SQL STATEMENT: update tb_book set status=’1′. In the STATEMENT log format, a row of SQL files will be recorded. In the case of ROW, data changes for each ROW are recorded in the ROW format log.
MIXED
This is the default MySQL log format, which is a mixture of STATEMENT and ROW. Statements are used by default, but rows are used for recording in some special cases. MIXED formats take advantage of the best of both modes while avoiding their worst.
2.3 Reading Logs
Since logs are stored in binary mode, they cannot be read directly. You need to use the mysqlbinlog tool to view logs. The syntax is as follows:
Mysqlbinlog log file;Copy the codeView STATEMENT logs
insert into tb_book values(null.'Lucene'.'2088-05-01'.'0');
Copy the codeTo view the log file:
Mysqlbin. index: this file is the log index file and the name of the log file. Mysqlbing.000001: Log file
View log content:
Mysqlbinlog mysqlbing. 000001;Copy the code
View logs in ROW format
Mysqlbin ----- = mysqlbin -----
mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin
Configure the format of binary logs
binlog_format=ROW
Copy the codeInsert data:
insert into tb_book values(null.'SpringCloud practical'.'2088-05-05'.'0');
Copy the codeIf the log format is ROW, you cannot view the data directly. You can add -vv to mysqlbinlog
mysqlbinlog -vv mysqlbin.000002
Copy the code
2.4 Deleting Logs
For a busy system, a large number of logs are generated every day. If logs are not cleared for a long time, they occupy a large amount of disk space. Here are some common ways to delete logs:
Methods a
Run the Reset Master command to delete all binlog logs. After deletion, the log number will start from XXXX.000001. Before querying logs, check log files:
Execute delete log command:
Reset Master
Copy the code
Way 2
Run the purge master logs to ‘mysqlbin.******’ command, which will delete all logs up to ******.
Methods three
Purge master logs before ‘YYYY-MM-DD HH24 :mi:ss’ Run the purge master logs before’ YYYY-MM-DD HH24 :mi:ss’ command to delete all logs generated before “YYYY-MM-DD HH24 :mi:ss”.
Methods four
Set the parameter –expire_logs_days=#. This parameter specifies the expiration days of logs. After the expiration days, logs are automatically deleted, which helps reduce the workload of the DBA.
The configuration is as follows:
3. Query logs
Query logs record all operation statements of the client, while binary logs do not contain SQL statements for querying data. By default, log query is disabled. To enable log query, set the following parameters:
# This option is used to enable query logs. The value can be 0 or 1. 0 means off, 1 means on
general_log=1
If the file name is not specified, the default file name is host_name
general_log_file=file_name
Copy the code/usr/my.cnf /usr/my.cnf /usr/my.cnf /usr/my.cnf
After the configuration is complete, perform the following operations in the database:
select * from tb_book;
select * from tb_book where id = 1;
update tb_book set name = 'Lucene Guide to Getting Started' where id = 5;
select * from tb_book where id < 8;
Copy the codeAfter execution, query log file again:
4. Slowly query logs
Slow query logs record all SQL statements whose execution time exceeds the value of long_QUERy_time and the number of scanned records is not less than min_examined_ROW_limit. Long_query_time the default value is 10 seconds, the minimum value is 0, and the accuracy can be up to microseconds.
4.1 File location and format
Slow query logs are disabled by default. Slow query logging can be controlled using two parameters:
# This parameter is used to control whether slow query logs are enabled. Possible values are 1 and 0, where 1 indicates that slow query logs are enabled and 0 indicates that slow query logs are 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 set the time limit for the query. After this time, the query will be considered slow and will be logged. The default is 10 seconds
long_query_time=10
Copy the code4.2 Reading Logs
Like error logs and query logs, slow query logs are in plain text format and can be read directly. 1) Query the value of long_query_time.
2) Perform query operations
select id, title,price,num ,status from tb_item where id = 1;
Copy the code
This statement is not recorded in the slow query log because it takes a short time of 0s.
select * from tb_item where title like '% Alcatel (OT-927) Carbon Black Unicom 3G Mobile phone dual card dual waiting 165454%' ;
Copy the code
The execution time of this SQL statement is 26.77 seconds and exceeds 10 seconds, so it is recorded in the slow query log file.
3) View the slow query log file
Run the cat command to query the log file directly:
If the slow query logs contain a large number of files, you can use the mysqlDumpSlow tool provided by mysql to classify and summarize the slow query logs.
