《Mysql实例MySQL中查询日志与慢查询日志的基本学习教程》要点：
本文介绍了Mysql实例MySQL中查询日志与慢查询日志的基本学习教程，希望对您有用。如果有疑问，可以联系我们。
MYSQL数据库一、查询日记
MYSQL数据库 查询日志记录MySQL中所有的query,通过"--log[=file_name]"来打开该功能.由于记录了所有的query,包含所有的select,体积比较大,开启后对性能也有比较大的影响,所以请大家慎用该功能.一般只用于跟踪某些特殊的sql性能问题才会短暂打开该功能.默认的查询日志文件名为：hostname.log.
----默认情况下查看是否启用查询日志：
MYSQL数据库
[root@node4 mysql5.5]# service mysql start
MYSQL数据库
Starting MySQL.... [ OK ]
MYSQL数据库
[root@node4 mysql5.5]# mysql
MYSQL数据库
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MYSQL数据库
mysql> show variables like '%log';
MYSQL数据库
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| back_log | 50 |
| general_log | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| log | OFF |
| relay_log | |
| slow_query_log | OFF |
| sync_binlog | 0 |
| sync_relay_log | 0 |
+--------------------------------+-------+
8 rows in set (0.00 sec)
MYSQL数据库 ----备注：log和general_log这两个参数是兼容的.而默认的情况下查询日志是不开启的
----使用下面的命令是开启查询日志
mysql> set global log=1;
MYSQL数据库
Query OK, 0 rows affected, 1 warning (0.03 sec)
MYSQL数据库
mysql> show variables like '%log';
MYSQL数据库
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| back_log | 50 |
| general_log | ON |
| innodb_locks_unsafe_for_binlog | OFF |
| log | ON |
| relay_log | |
| slow_query_log | OFF |
| sync_binlog | 0 |
| sync_relay_log | 0 |
+--------------------------------+-------+
8 rows in set (0.00 sec)
----其中log参数是过时的,在启动选项中使用log参数的话,会在err日志中显示出来.
----修改my.cnf文件,添加log的参数设置
MYSQL数据库
[root@node4 mysql5.5]# vi my.cnf
[root@node4 mysql5.5]# cat ./my.cnf |grep '^log='
log=/tmp/mysqlgen.log
----清空err日志
[root@node4 mysql5.5]# cat /dev/null > /tmp/mysql3306.err
[root@node4 mysql5.5]# ll /tmp/mysql3306.err
-rw-rw---- 1 mysql root 0 Jul 31 07:50 /tmp/mysql3306.err
[root@node4 mysql5.5]# service mysql start
MYSQL数据库
Starting MySQL... [ OK ]
----启动数据库后查看err日志的内容
MYSQL数据库
[root@node4 mysql5.5]# cat /tmp/mysql3306.err
MYSQL数据库
130731 07:51:32 mysqld_safe Starting mysqld daemon with databases from /opt/mysql5.5/data
130731 7:51:32 [Warning] The syntax '--log' is deprecated and will be removed in a future release. Please use '--general-log'/'--general-log-file' instead.
130731 7:51:33 InnoDB: The InnoDB memory heap is disabled
130731 7:51:33 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
130731 7:51:33 InnoDB: Compressed tables use zlib 1.2.3
130731 7:51:33 InnoDB: Initializing buffer pool, size = 128.0M
130731 7:51:33 InnoDB: Completed initialization of buffer pool
130731 7:51:33 InnoDB: highest supported file format is Barracuda.
130731 7:51:33 InnoDB: Waiting for the background threads to start
130731 7:51:34 InnoDB: 1.1.8 started; log sequence number 1625855
130731 7:51:34 [Note] Event Scheduler: Loaded 0 events
130731 7:51:34 [Note] /opt/mysql5.5/bin/mysqld: ready for connections.
Version: '5.5.22-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution
----使用最新的参数
----general_log和general_log_file.
MYSQL数据库
[root@node4 mysql5.5]# service mysql stop
MYSQL数据库
Shutting down MySQL. [ OK ]
MYSQL数据库
[root@node4 mysql5.5]# vi my.cnf
[root@node4 mysql5.5]# cat ./my.cnf |grep '^general'
MYSQL数据库
general_log = 1
general_log_file = /tmp/mysqlgen.log
MYSQL数据库
[root@node4 mysql5.5]# service mysql start
MYSQL数据库
Starting MySQL... [ OK ]
MYSQL数据库
[root@node4 mysql5.5]# mysql
MYSQL数据库
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MYSQL数据库
mysql> show variables like '%log';
MYSQL数据库
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| back_log | 50 |
| general_log | ON |
| innodb_locks_unsafe_for_binlog | OFF |
| log | ON |
| relay_log | |
| slow_query_log | OFF |
| sync_binlog | 0 |
| sync_relay_log | 0 |
+--------------------------------+-------+
8 rows in set (0.04 sec)
MYSQL数据库
mysql> show variables like '%file';
MYSQL数据库
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| ft_stopword_file | (built-in) |
| general_log_file | /tmp/mysqlgen.log |
| init_file | |
| local_infile | ON |
| pid_file | /tmp/mysql3306.pid |
| relay_log_info_file | relay-log.info |
| slow_query_log_file | /opt/mysql5.5/data/node4-slow.log |
+---------------------+-----------------------------------+
7 rows in set (0.00 sec)
----在上面的操作中可以看到已经启用查询日志,而且文件目录是/tmp/mysqlgen.log.
----查询日志记录了哪些东西?
MYSQL数据库进行下面的查询
MYSQL数据库
mysql> show databases;
MYSQL数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test2 |
+--------------------+
5 rows in set (0.08 sec)
MYSQL数据库
mysql> use test;
MYSQL数据库
Database changed
MYSQL数据库
mysql> show tables;
MYSQL数据库
Empty set (0.00 sec)
MYSQL数据库
mysql> use test2;
MYSQL数据库
Database changed
MYSQL数据库
mysql> show tables;
MYSQL数据库
+-----------------+
| Tables_in_test2 |
+-----------------+
| course |
| jack |
| sc |
| student |
| t |
| teacher |
+-----------------+
6 rows in set (0.07 sec)
MYSQL数据库
mysql> drop table t;
MYSQL数据库
Query OK, 0 rows affected (0.13 sec)
MYSQL数据库
mysql> select * from sc;
MYSQL数据库
Empty set (0.04 sec)
----可以看到上面的操作都记录在了mysqlgen.log里面.
MYSQL数据库
[root@node4 ~]# tail -f /tmp/mysqlgen.log
MYSQL数据库
/opt/mysql5.5/bin/mysqld, Version: 5.5.22-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
130731 7:55:41 1 Query show databases
130731 7:55:56 1 Query SELECT DATABASE()
1 Init DB test
130731 7:55:59 1 Query show tables
130731 7:56:19 1 Query SELECT DATABASE()
1 Init DB test2
130731 7:56:23 1 Query show tables
130731 7:56:27 1 Query drop table t
130731 7:56:39 1 Query select * from sc
MYSQL数据库二、慢查询日志
顾名思义,慢查询日志中记录的是执行时间较长的query,也就是我们常说的slow query,通过设--log-slow-queries[=file_name]来打开该功能并设置记录位置和文件名,默认文件名为hostname-slow.log,默认目录也是数据目录.
慢查询日志采用的是简单的文本格式,可以通过各种文本编辑器查看其中的内容.其中记录了语句执行的时刻,执行所消耗的时间,执行用户,连接主机等相关信息.MySQL还提供了专门用来分析满查询日志的工具程序mysqlslowdump,用来帮助数据库管理人员办理可能存在的性能问题.
MYSQL数据库----使用log_slow_queries参数打开慢查询,由于该参数已经过时,因此在err日志中将呈现提示信息
MYSQL数据库
----改动my.cnf文件,添加log_slow_queries参数
[root@node4 ~]# vi /opt/mysql5.5/my.cnf
[root@node4 ~]# cat /opt/mysql5.5/my.cnf |grep '^log_slow'
log_slow_queries = /tmp/mysqlslow.log
----清空err日志内容：
[root@node4 ~]# cat /dev/null > /tmp/mysql3306.err
[root@node4 ~]# service mysql start
MYSQL数据库
Starting MySQL.... [ OK ]
MYSQL数据库
----查看err日志的信息
[root@node4 data]# tail -f /tmp/mysql3306.err
MYSQL数据库
02:26:28 mysqld_safe Starting mysqld daemon with databases from /opt/mysql5.5/data
2:26:28 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
2:26:28 [Warning] You need to use --log-bin to make --binlog-format work.
2:26:28 InnoDB: The InnoDB memory heap is disabled
2:26:28 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2:26:28 InnoDB: Compressed tables use zlib 1.2.3
2:26:28 InnoDB: Initializing buffer pool, size = 128.0M
2:26:28 InnoDB: Completed initialization of buffer pool
2:26:28 InnoDB: highest supported file format is Barracuda.
2:26:28 InnoDB: Waiting for the background threads to start
2:26:30 InnoDB: 1.1.8 started; log sequence number 3069452
2:26:30 [Note] Event Scheduler: Loaded 0 events
2:26:30 [Note] /opt/mysql5.5/bin/mysqld: ready for connections.
Version: '5.5.22-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution
MYSQL数据库
----使用slow_query_log和slow_query_log_file
[root@node4 ~]# vi /opt/mysql5.5/my.cnf
[root@node4 ~]# cat /opt/mysql5.5/my.cnf |grep '^slow_query'
MYSQL数据库
slow_query_log = 1
slow_query_log_file = /tmp/mysqlslow.log1
MYSQL数据库
[root@node4 ~]# service mysql start
MYSQL数据库
Starting MySQL... [ OK ]
MYSQL数据库
[root@node4 ~]# mysql
MYSQL数据库
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MYSQL数据库
mysql> show variables like '%slow%';
MYSQL数据库
+---------------------+---------------------+
| Variable_name | Value |
+---------------------+---------------------+
| log_slow_queries | ON |
| slow_launch_time | 10 |
| slow_query_log | ON |
| slow_query_log_file | /tmp/mysqlslow.log1 |
+---------------------+---------------------+
rows in set (0.00 sec)
MYSQL数据库
----关于slow_launch_time参数,首先改动一下参数值
mysql> set global long_query_time=1;
MYSQL数据库
Query OK, 0 rows affected (0.00 sec)
MYSQL数据库
mysql> show variables like '%long_query%';
MYSQL数据库
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
row in set (0.00 sec)
MYSQL数据库----进行一下相关操作,查看/tmp/mysqlslow.log1的内容
MYSQL数据库
mysql> select database();
MYSQL数据库
+------------+
| database() |
+------------+
| NULL |
+------------+
row in set (0.00 sec)
MYSQL数据库
mysql> use test;
MYSQL数据库
Database changed
MYSQL数据库
mysql> show tables;
MYSQL数据库
Empty set (0.00 sec)
MYSQL数据库
mysql> create table t as select * from information_schema.tables;
MYSQL数据库
Query OK, 85 rows affected (0.38 sec)
Records: 85 Duplicates: 0 Warnings: 0
MYSQL数据库
mysql> insert into t select * from t;
MYSQL数据库
Query OK, 85 rows affected (0.05 sec)
Records: 85 Duplicates: 0 Warnings: 0
MYSQL数据库
mysql> insert into t select * from t;
MYSQL数据库
Query OK, 170 rows affected (0.03 sec)
Records: 170 Duplicates: 0 Warnings: 0
MYSQL数据库
mysql> insert into t select * from t;
MYSQL数据库
Query OK, 340 rows affected (0.05 sec)
Records: 340 Duplicates: 0 Warnings: 0
MYSQL数据库
mysql> insert into t select * from t;
MYSQL数据库
Query OK, 680 rows affected (0.08 sec)
Records: 680 Duplicates: 0 Warnings: 0
MYSQL数据库
mysql> insert into t select * from t;
MYSQL数据库
Query OK, 1360 rows affected (0.29 sec)
Records: 1360 Duplicates: 0 Warnings: 0
MYSQL数据库
mysql> insert into t select * from t;
MYSQL数据库
Query OK, 2720 rows affected (1.49 sec)
Records: 2720 Duplicates: 0 Warnings: 0
----在这里已经跨越1s了,查看/tmp/mysqlslow.log1
MYSQL数据库
[root@node4 data]# tail -f /tmp/mysqlslow.log1
MYSQL数据库
# Time: 130801 2:36:25
# User@Host: root[root] @ localhost []
# Query_time: 2.274219 Lock_time: 0.000322 Rows_sent: 0 Rows_examined: 5440
use test;
SET timestamp=1375295785;
insert into t select * from t;
----log_queries_not_using_indexes参数实验
MYSQL数据库
mysql> show variables like '%indexes%';
MYSQL数据库
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
row in set (0.00 sec)
MYSQL数据库
mysql> set log_queries_not_using_indexes = 1;
MYSQL数据库
ERROR 1229 (HY000): Variable 'log_queries_not_using_indexes' is a GLOBAL variable and should be set with SET GLOBAL
MYSQL数据库
mysql> set global log_queries_not_using_indexes = 1;
MYSQL数据库
Query OK, 0 rows affected (0.01 sec)
MYSQL数据库
mysql> show variables like '%indexes%';
MYSQL数据库
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
row in set (0.00 sec)
MYSQL数据库
mysql> desc t;
MYSQL数据库
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(32) | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(2048) | NO | | | |
+-----------------+---------------------+------+-----+---------+-------+
rows in set (0.05 sec)
MYSQL数据库
----下面的命令是查看索引的
mysql> show index from t;
MYSQL数据库
Empty set (0.01 sec)
MYSQL数据库
mysql> select * from t where engine='xxx';
MYSQL数据库
Empty set (0.18 sec)
# Time: 130801 2:43:43
# User@Host: root[root] @ localhost []
# Query_time: 0.185773 Lock_time: 0.148868 Rows_sent: 0 Rows_examined: 5440
SET timestamp=1375296223;
select * from t where engine='xxx';
MYSQL数据库PS：slow query log相关变量
MYSQL数据库敕令行参数：
MYSQL数据库 --log-slow-queries
MYSQL数据库 指定日志文件寄存位置,可以为空,系统会给一个缺省的文件host_name-slow.log
MYSQL数据库体系变量
MYSQL数据库 log_slow_queries
MYSQL数据库 指定日志文件寄存位置,可以为空,系统会给一个缺省的文件host_name-slow.log
MYSQL数据库 slow_query_log
MYSQL数据库 slow quere log的开关,当值为1的时候阐明开启慢查询.
MYSQL数据库 slow_query_log_file
MYSQL数据库 指定日志文件寄存位置,可以为空,系统会给一个缺省的文件host_name-slow.log
MYSQL数据库 long_query_time
MYSQL数据库 记录跨越的时间,默认为10s
MYSQL数据库 log_queries_not_using_indexes
MYSQL数据库 log下来没有使用索引的query,可以依据情况决定是否开启
MYSQL数据库三、Mysqldumpslow
MYSQL数据库 如果日志内容很多,用眼睛一条一条看会累死,mysql自带了分析的工具,使用办法如下：
MYSQL数据库
[root@node4 data]# mysqldumpslow --help
MYSQL数据库
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and su妹妹arize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
欢迎参与《Mysql实例MySQL中查询日志与慢查询日志的基本学习教程》讨论，分享您的想法，维易PHP学院为您提供专业教程。
