MySQL中查询日志与慢查询日志的基本学习教程

2024-03-23 19:34:05 148

一、查询日志

查询日志记录MySQL中所有的query，通过"--log[=file_name]"来打开该功能。由于记录了所有的query，包括所有的select，体积比较大，开启后对性能也有比较大的影响，所以请大家慎用该功能。一般只用于跟踪某些特殊的sql性能问题才会短暂打开该功能。默认的查询日志文件名为：hostname.log.
----默认情况下查看是否启用查询日志：

[root@node4mysql5.5]#servicemysqlstart

StartingMySQL....[OK]

[root@node4mysql5.5]#mysql

WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis1
Serverversion:5.5.22-logSourcedistribution

Copyright(c)2000,2011,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

mysql>showvariableslike'%log';

+--------------------------------+-------+
|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|
+--------------------------------+-------+
8rowsinset(0.00sec)

----备注：log和general_log这两个参数是兼容的。而默认的情况下查询日志是不开启的
----使用下面的命令是开启查询日志
mysql>setgloballog=1;

QueryOK,0rowsaffected,1warning(0.03sec)

mysql>showvariableslike'%log';

+--------------------------------+-------+
|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|
+--------------------------------+-------+
8rowsinset(0.00sec)
----其中log参数是过时的，在启动选项中使用log参数的话，会在err日志中显示出来。
----修改my.cnf文件，添加log的参数设置

[root@node4mysql5.5]#vimy.cnf
[root@node4mysql5.5]#cat./my.cnf|grep'^log='
log=/tmp/mysqlgen.log
----清空err日志
[root@node4mysql5.5]#cat/dev/null>/tmp/mysql3306.err
[root@node4mysql5.5]#ll/tmp/mysql3306.err
-rw-rw----1mysqlroot0Jul3107:50/tmp/mysql3306.err
[root@node4mysql5.5]#servicemysqlstart

StartingMySQL...[OK]
----启动数据库后查看err日志的内容

[root@node4mysql5.5]#cat/tmp/mysql3306.err

13073107:51:32mysqld_safeStartingmysqlddaemonwithdatabasesfrom/opt/mysql5.5/data
1307317:51:32[Warning]Thesyntax'--log'isdeprecatedandwillberemovedinafuturerelease.Pleaseuse'--general-log'/'--general-log-file'instead.
1307317:51:33InnoDB:TheInnoDBmemoryheapisdisabled
1307317:51:33InnoDB:Mutexesandrw_locksuseInnoDB'sownimplementation
1307317:51:33InnoDB:Compressedtablesusezlib1.2.3
1307317:51:33InnoDB:Initializingbufferpool,size=128.0M
1307317:51:33InnoDB:Completedinitializationofbufferpool
1307317:51:33InnoDB:highestsupportedfileformatisBarracuda.
1307317:51:33InnoDB:Waitingforthebackgroundthreadstostart
1307317:51:34InnoDB:1.1.8started;logsequencenumber1625855
1307317:51:34[Note]EventScheduler:Loaded0events
1307317:51:34[Note]/opt/mysql5.5/bin/mysqld:readyforconnections.
Version:'5.5.22-log'socket:'/tmp/mysql.sock'port:3306Sourcedistribution
----使用最新的参数
----general_log和general_log_file。

[root@node4mysql5.5]#servicemysqlstop

ShuttingdownMySQL.[OK]

[root@node4mysql5.5]#vimy.cnf
[root@node4mysql5.5]#cat./my.cnf|grep'^general'

general_log=1
general_log_file=/tmp/mysqlgen.log

[root@node4mysql5.5]#servicemysqlstart

StartingMySQL...[OK]

[root@node4mysql5.5]#mysql

WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis1
Serverversion:5.5.22-logSourcedistribution

Copyright(c)2000,2011,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

mysql>showvariableslike'%log';

+--------------------------------+-------+
|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|
+--------------------------------+-------+
8rowsinset(0.04sec)

mysql>showvariableslike'%file';

+---------------------+-----------------------------------+
|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|
+---------------------+-----------------------------------+
7rowsinset(0.00sec)
----在上面的操作中可以看到已经启用查询日志，并且文件目录是/tmp/mysqlgen.log。
----查询日志记录了哪些东西？

进行下面的查询

mysql>showdatabases;

+--------------------+
|Database|
+--------------------+
|information_schema|
|mysql|
|performance_schema|
|test|
|test2|
+--------------------+
5rowsinset(0.08sec)

mysql>usetest;

Databasechanged

mysql>showtables;

Emptyset(0.00sec)

mysql>usetest2;

Databasechanged

mysql>showtables;

+-----------------+
|Tables_in_test2|
+-----------------+
|course|
|jack|
|sc|
|student|
|t|
|teacher|
+-----------------+
6rowsinset(0.07sec)

mysql>droptablet;

QueryOK,0rowsaffected(0.13sec)

mysql>select*fromsc;

Emptyset(0.04sec)

----可以看到上面的操作都记录在了mysqlgen.log里面。

[root@node4~]#tail-f/tmp/mysqlgen.log

/opt/mysql5.5/bin/mysqld,Version:5.5.22-log(Sourcedistribution).startedwith:
Tcpport:3306Unixsocket:/tmp/mysql.sock
TimeIdCommandArgument
1307317:55:411Queryshowdatabases
1307317:55:561QuerySELECTDATABASE()
1InitDBtest
1307317:55:591Queryshowtables
1307317:56:191QuerySELECTDATABASE()
1InitDBtest2
1307317:56:231Queryshowtables
1307317:56:271Querydroptablet
1307317:56:391Queryselect*fromsc

二、慢查询日志
顾名思义，慢查询日志中记录的是执行时间较长的query，也就是我们常说的slowquery，通过设--log-slow-queries[=file_name]来打开该功能并设置记录位置和文件名，默认文件名为hostname-slow.log，默认目录也是数据目录。
慢查询日志采用的是简单的文本格式，可以通过各种文本编辑器查看其中的内容。其中记录了语句执行的时刻，执行所消耗的时间，执行用户，连接主机等相关信息。MySQL还提供了专门用来分析满查询日志的工具程序mysqlslowdump，用来帮助数据库管理人员解决可能存在的性能问题。

----使用log_slow_queries参数打开慢查询，由于该参数已经过时，因此在err日志中将出现提示信息

----修改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~]#servicemysqlstart

StartingMySQL....[OK]

----查看err日志的信息


[root@node4data]#tail-f/tmp/mysql3306.err

02:26:28mysqld_safeStartingmysqlddaemonwithdatabasesfrom/opt/mysql5.5/data
2:26:28[Warning]Thesyntax'--log-slow-queries'isdeprecatedandwillberemovedinafuturerelease.Pleaseuse'--slow-query-log'/'--slow-query-log-file'instead.
2:26:28[Warning]Youneedtouse--log-bintomake--binlog-formatwork.
2:26:28InnoDB:TheInnoDBmemoryheapisdisabled
2:26:28InnoDB:Mutexesandrw_locksuseInnoDB'sownimplementation
2:26:28InnoDB:Compressedtablesusezlib1.2.3
2:26:28InnoDB:Initializingbufferpool,size=128.0M
2:26:28InnoDB:Completedinitializationofbufferpool
2:26:28InnoDB:highestsupportedfileformatisBarracuda.
2:26:28InnoDB:Waitingforthebackgroundthreadstostart
2:26:30InnoDB:1.1.8started;logsequencenumber3069452
2:26:30[Note]EventScheduler:Loaded0events
2:26:30[Note]/opt/mysql5.5/bin/mysqld:readyforconnections.
Version:'5.5.22-log'socket:'/tmp/mysql.sock'port:3306Sourcedistribution

----使用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'

slow_query_log=1
slow_query_log_file=/tmp/mysqlslow.log1

[root@node4~]#servicemysqlstart

StartingMySQL...[OK]

[root@node4~]#mysql

WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis1
Serverversion:5.5.22-logSourcedistribution

Copyright(c)2000,2011,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

mysql>showvariableslike'%slow%';

+---------------------+---------------------+
|Variable_name|Value|
+---------------------+---------------------+
|log_slow_queries|ON|
|slow_launch_time|10|
|slow_query_log|ON|
|slow_query_log_file|/tmp/mysqlslow.log1|
+---------------------+---------------------+
rowsinset(0.00sec)

----关于slow_launch_time参数，首先修改一下参数值
mysql>setgloballong_query_time=1;

QueryOK,0rowsaffected(0.00sec)

mysql>showvariableslike'%long_query%';

+-----------------+----------+
|Variable_name|Value|
+-----------------+----------+
|long_query_time|1.000000|
+-----------------+----------+
rowinset(0.00sec)

----进行一下相关操作，查看/tmp/mysqlslow.log1的内容

mysql>selectdatabase();

+------------+
|database()|
+------------+
|NULL|
+------------+
rowinset(0.00sec)

mysql>usetest;

Databasechanged

mysql>showtables;

Emptyset(0.00sec)

mysql>createtabletasselect*frominformation_schema.tables;

QueryOK,85rowsaffected(0.38sec)
Records:85Duplicates:0Warnings:0

mysql>insertintotselect*fromt;

QueryOK,85rowsaffected(0.05sec)
Records:85Duplicates:0Warnings:0

mysql>insertintotselect*fromt;

QueryOK,170rowsaffected(0.03sec)
Records:170Duplicates:0Warnings:0

mysql>insertintotselect*fromt;

QueryOK,340rowsaffected(0.05sec)
Records:340Duplicates:0Warnings:0

mysql>insertintotselect*fromt;

QueryOK,680rowsaffected(0.08sec)
Records:680Duplicates:0Warnings:0

mysql>insertintotselect*fromt;

QueryOK,1360rowsaffected(0.29sec)
Records:1360Duplicates:0Warnings:0

mysql>insertintotselect*fromt;

QueryOK,2720rowsaffected(1.49sec)
Records:2720Duplicates:0Warnings:0

----在这里已经超过1s了，查看/tmp/mysqlslow.log1

[root@node4data]#tail-f/tmp/mysqlslow.log1

#Time:1308012:36:25
#User@Host:root[root]@localhost[]
#Query_time:2.274219Lock_time:0.000322Rows_sent:0Rows_examined:5440
usetest;
SETtimestamp=1375295785;
insertintotselect*fromt;

----log_queries_not_using_indexes参数实验

mysql>showvariableslike'%indexes%';

+-------------------------------+-------+
|Variable_name|Value|
+-------------------------------+-------+
|log_queries_not_using_indexes|OFF|
+-------------------------------+-------+
rowinset(0.00sec)

mysql>setlog_queries_not_using_indexes=1;

ERROR1229(HY000):Variable'log_queries_not_using_indexes'isaGLOBALvariableandshouldbesetwithSETGLOBAL

mysql>setgloballog_queries_not_using_indexes=1;

QueryOK,0rowsaffected(0.01sec)

mysql>showvariableslike'%indexes%';

+-------------------------------+-------+
|Variable_name|Value|
+-------------------------------+-------+
|log_queries_not_using_indexes|ON|
+-------------------------------+-------+
rowinset(0.00sec)

mysql>desct;

+-----------------+---------------------+------+-----+---------+-------+
|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||||
+-----------------+---------------------+------+-----+---------+-------+
rowsinset(0.05sec)

----下面的命令是查看索引的
mysql>showindexfromt;

Emptyset(0.01sec)

mysql>select*fromtwhereengine='xxx';

Emptyset(0.18sec)

#Time:1308012:43:43
#User@Host:root[root]@localhost[]
#Query_time:0.185773Lock_time:0.148868Rows_sent:0Rows_examined:5440
SETtimestamp=1375296223;
select*fromtwhereengine='xxx';

PS：slowquerylog相关变量

命令行参数：

--log-slow-queries

指定日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log

系统变量

log_slow_queries

指定日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log

slow_query_log

slowquerelog的开关，当值为1的时候说明开启慢查询。

slow_query_log_file

指定日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log

long_query_time

记录超过的时间，默认为10s

log_queries_not_using_indexes

log下来没有使用索引的query，可以根据情况决定是否开启

三、Mysqldumpslow

如果日志内容很多，用眼睛一条一条看会累死，mysql自带了分析的工具，使用方法如下：

[root@node4data]#mysqldumpslow--help

Usage:mysqldumpslow[OPTS...][LOGS...]

ParseandsummarizetheMySQLslowquerylog.Optionsare

--verboseverbose
--debugdebug
--helpwritethistexttostandardoutput

-vverbose
-ddebug
-sORDERwhattosortby(al,at,ar,c,l,r,t),'at'isdefault
al:averagelocktime
ar:averagerowssent
at:averagequerytime
c:count
l:locktime
r:rowssent
t:querytime
-rreversethesortorder(largestlastinsteadoffirst)
-tNUMjustshowthetopnqueries
-adon'tabstractallnumberstoNandstringsto'S'
-nNUMabstractnumberswithatleastndigitswithinnames
-gPATTERNgrep:onlyconsiderstmtsthatincludethisstring
-hHOSTNAMEhostnameofdbserverfor*-slow.logfilename(canbewildcard),
defaultis'*',i.e.matchall
-iNAMEnameofserverinstance(ifusingmysql.serverstartupscript)
-ldon'tsubtractlocktimefromtotaltime

MySQL中查询日志与慢查询日志的基本学习教程

热门推荐

随机推荐