MySQL Log File Classification
Official documentation:
https://dev.mysql.com/doc/refman/5.7/en/server-logs.html
Log files record various types of activities affecting MySQL databases. Common logs are as follows:
1. Error log; 2. Slow query log; 3. Binlog; 4. Query log (log);
Error Log
1. Official introduction document:
https://dev.mysql.com/doc/refman/5.7/en/error-log.html
2. The MySQL error log is to record the more serious warnings and errors during the operation of MySQL, as well as the detailed information of each startup and shutdown of MySQL.
The MySQL error log file location can be viewed with the command:
mysql> show variables like "log_error"\G;
Check it out below:
Slow Query Log
1. Official introduction document:
https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html
2. MySQL's slow query log is a log record provided by MySQL, which is used to record the statement whose response time exceeds the threshold in MySQL, specifically refers to the running time exceeding the threshold.long_query_time
The default value of long_query_time is 10, meaning a statement that runs for more than 10s.
By default, the MySQL database does not start the slow query log, and we need to manually set this parameter. Of course, if it is not required for tuning, it is generally not recommended to start this parameter, because turning on the slow query log will bring more or less certain benefits. performance impact.
The slow query log supports writing log records to files, and also supports writing log records to database tables.
3. You can check the default threshold and whether it is enabled by the following command:
mysql> show variables like "long_query_time"\G;
mysql> show variables like "slow_query_log"\G;
4. Enable slow query log:
mysql> set global slow_query_log="ON";
5. The default slow query log file directory
mysql> show variables like "slow_query_log_file"\G;
It can be seen that the default slow query log is named after the host name and '-slow', the default: host_name-slow.log
6. View the slow query log
The usemysqldumpslow command is viewed as follows：
xuliugen@xuliugen:~$ sudo mysqldumpslow /var/lib/mysql/xuliugen-slow.log
For more information on how to use mysqldumpslow, use man mysqldumpslow to view：
7. Change the storage method of slow queries
First, check the default storage method of logs by command:
mysql> show variables like "log_output"\G;
It can be known that the default storage method is FILE, that is, stored in/var/lib/mysql/xuliugen-slow.log 。
Check out the official documentation forlog_output：
mysql> set global log_output="TABLE";
After changing to TABLE, the slow query log will be stored in the form of a table.mysql slow_log：
mysql> show create table mysql.slow_log\G;
8. Test the slow query log
Simulate a slow query with the command:
mysql> select sleep(10);
View slow query log:
mysql> select * from mysql.slow_log\G;
9. There are two more properties in MySQL 5.7 version:
log_slow_admin_statements
log_queries_not_using_indexes
By default, administrator commands to operate the database (including: ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE.) and queries without indexes are not logged.
These two properties can be set in the development environment. In the actual production environment, because the index is not used, such SQL statements will be frequently recorded in the slow log, making the size of the slow log file larger. Keep increasing, therefore, make reasonable settings due to improvements.