MySQL Server Logs

日志记录存储方式

#日志记录存储方式
mysql> show variables like 'log_output';
mysql> set global log_output='FILE,TABLE';

1、错误日志

#1、错误日志
mysql> show variables like 'log_error';
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| log_error     | /usr/local/mysql/data/VMUest.err |
+---------------+----------------------------------+

2、普通查询日志

#2、普通查询日志
mysql> show variables like 'general_log%';
+------------------+----------------------------------+
| Variable_name    | Value                            |
+------------------+----------------------------------+
| general_log      | OFF                              |
| general_log_file | /usr/local/mysql/data/VMUest.log |
+------------------+----------------------------------+
#设置普通日志路径并开启(临时，重启后失效)
mysql> set global general_log_file='/usr/local/mysql/data/mysql-general.log';
mysql> set global general_log =1;
#测试语句
mysql> show databases;
mysql> use sakila;
mysql> select * from actor limit 10;
#查看普通日志
[root@VMUest data]# more /usr/local/mysql/data/mysql-general.log
/usr/local/mysql/bin/mysqld, Version: 5.6.35 (Source distribution). started with:
Tcp port: 3306  Unix socket: /usr/local/mysql/mysql.sock
Time                 Id Command    Argument
170312 16:14:25    10 Query     show databases
170312 16:14:52    10 Query     use sakila
170312 16:15:04    10 Query     select * from actor limit 10

3、慢查询日志

#3、慢查询日志
mysql> show variables like 'slow_query_log%';
+---------------------+---------------------------------------+
| Variable_name       | Value                                 |
+---------------------+---------------------------------------+
| slow_query_log      | OFF                                   |
| slow_query_log_file | /usr/local/mysql/data/VMUest-slow.log |
+---------------------+---------------------------------------+
#设置慢查询日志路径并开启(临时，重启后失效)
mysql> set global slow_query_log_file='/usr/local/mysql/data/mysql-slow.log';
mysql> set global slow_query_log=1;
#运行时间>long_query_time的记录到日志
mysql> show global variables like 'long_query_time';
mysql> set global long_query_time=2;
#测试语句(需在新窗口执行)
mysql> select sleep(3);

# 未使用索引的查询也被记录到慢查询日志中
mysql> show variables like 'log_queries_not_using_indexes';
mysql> set global log_queries_not_using_indexes=1;
#测试语句
mysql> select * from actor;
mysql> explain select * from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |  200 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
#查看慢查询日志
[root@VMUest data]# more /usr/local/mysql/data/mysql-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.6.35 (Source distribution). started with:
Tcp port: 3306  Unix socket: /usr/local/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 170312 16:43:11
# User@Host: mydba[mydba] @  [192.168.85.1]  Id:    12
# Query_time: 3.014669  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use test;
SET timestamp=1489308191;
select sleep(3);
# Time: 170312 16:46:44
# User@Host: mydba[mydba] @  [192.168.85.1]  Id:    10
# Query_time: 0.003183  Lock_time: 0.000320 Rows_sent: 200  Rows_examined: 200
use sakila;
SET timestamp=1489308404;
select * from actor;

#mysqldumpslow分析慢查询日志(配置好环境变量)
[root@VMUest data]# mysqldumpslow -s c -t 3 /usr/local/mysql/data/mysql-slow.log
Reading mysql slow query log from /usr/local/mysql/data/mysql-slow.log
Count: 39  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=4.0 (156), mydba[mydba]@[192.168.85.1]
  select * from actor limit N
Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=200.0 (400), mydba[mydba]@[192.168.85.1]
  select * from actor
Count: 1  Time=3.00s (3s)  Lock=0.00s (0s)  Rows=1.0 (1), mydba[mydba]@[192.168.85.1]
  select sleep(N)

# 多少条慢查询记录，可以使用系统变量
mysql> show global status like 'Slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 2     |
+---------------+-------+

日志文件备份
mysql> flush logs; 等效于 shell>mysqladmin flush-logs
flush logs可以添加具体的日志类型：flush error logs、flush general logs、flush binary logs、flush slow logs

A log-flushing operation does the following:
If general query logging or slow query logging to a log file is enabled, the server closes and reopens the general query log file or slow query log file.
If binary logging is enabled, the server closes the current binary log file and opens a new log file with the next sequence number.
If the server was started with the --log-error [406] option to cause the error log to be written to a file, the server closes and reopens the log file.
The server creates a new binary log file when you flush the logs. However, it just closes and reopens the general and slow query log files. To cause new files to be created on Unix, rename the current log files before flushing them. At flush time, the server opens new log files with the original names.

因此对于二进制日志之外的日志文件，通常是重命名当前的日志文件，然后flush-log来创建新日志文件

[root@VMUest ~]# cd /usr/local/mysql/data
[root@VMUest data]# mv mysql-general.log mysql-general.log.old
[root@VMUest data]# mv mysql-slow.log mysql-slow.log.old
mysql> flush general logs;
#执行mv命令后，日志记录到.old文件；执行flush命令后(flush命令记录在old文件)，创建"新"文件用于记录之后的记录