澳门在线威尼斯官方 > 电脑数据库 > Mysql日志解析,详细分析MySQL的日志

原标题:Mysql日志解析,详细分析MySQL的日志

浏览次数:71 时间:2019-11-24

官方手册:https://dev.mysql.com/doc/refman/5.7/en/server-logs.html

修改Mysql配置

Mysql配置地址为:

C:Program Files (x86)MySQLMySQL Server 5.5

如果无法修改可以把my.ini拷贝出来,修改完后,再拷贝回去!

如果配置了Mysql的日志生成路径,但是该目录尚未创建,那么启动会报错!

不管是哪个数据库产品,一定会有日志文件。在MariaDB/MySQL中,主要有5种日志文件:
1.错误日志(error log):记录mysql服务的启停时正确和错误的信息,还记录启动、停止、运行过程中的错误信息。
2.查询日志(general log):记录建立的客户端连接和执行的语句。
3.二进制日志(bin log):记录所有更改数据的语句,可用于数据复制。
4.慢查询日志(slow log):记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。
5.中继日志(relay log):主从复制时使用的日志。

关于Mysql日志

splunk内置了两种mysql的日志,分别是mysqld以及mysql_error

除了这5种日志,在需要的时候还会创建DDL日志。本文暂先讨论错误日志、一般查询日志、慢查询日志和二进制日志,中继日志和主从复制有关,将在复制的章节中介绍。下一篇文章将介绍innodb事务日志,见:MySQL的事务日志。

mysqld_error

Standard mysql error log标准的mysql错误日志

050818 16:19:29 InnoDB: Started; log sequence number 0 43644 /usr/libexec/mysqld: ready for connections. Version: '4.1.10a-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution

1.日志刷新操作

以下操作会刷新日志文件,刷新日志文件时会关闭旧的日志文件并重新打开日志文件。对于有些日志类型,如二进制日志,刷新日志会滚动日志文件,而不仅仅是关闭并重新打开。

mysql> FLUSH LOGS;
shell> mysqladmin flush-logs
shell> mysqladmin refresh

mysqld

Standard MySQL query log; also matches the MySQL binary log following conversion to text标准的mysql查询日志;也能匹配Mysql二进制日志

53 Query SELECT xar_dd_itemid, xar_dd_propid, xar_dd_value FROM xar_dynamic_data WHERE xar_dd_propid IN (27) AND xar_dd_itemid = 2

2.错误日志

错误日志是最重要的日志之一,它记录了MariaDB/MySQL服务启动和停止正确和错误的信息,还记录了mysqld实例运行过程中发生的错误事件信息。

可以使用" --log-erroe=[file_name] "来指定mysqld记录的错误日志文件,如果没有指定file_name,则默认的错误日志文件为datadir目录下的 `hostname`.err ,hostname表示当前的主机名。

也可以在MariaDB/MySQL配置文件中的mysqld配置部分,使用log-error指定错误日志的路径。

如果不知道错误日志的位置,可以查看变量log_error来查看。

mysql> show variables like 'log_error';
+---------------+----------------------------------------+
| Variable_name | Value                                  |
+---------------+----------------------------------------+
| log_error     | /var/lib/mysql/node1.longshuai.com.err |
+---------------+----------------------------------------+

在MySQL 5.5.7之前,刷新日志操作(如flush logs)会备份旧的错误日志(以_old结尾),并创建一个新的错误日志文件并打开,在MySQL 5.5.7之后,执行刷新日志的操作时,错误日志会关闭并重新打开,如果错误日志不存在,则会先创建。

在MariaDB/MySQL正在运行状态下删除错误日志后,不会自动创建错误日志,只有在刷新日志的时候才会创建一个新的错误日志文件。

以下是MySQL 5.6.35启动的日志信息。

2017-03-29 01:15:14 2362 [Note] Plugin 'FEDERATED' is disabled.
2017-03-29 01:15:14 2362 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-03-29 01:15:14 2362 [Note] InnoDB: The InnoDB memory heap is disabled
2017-03-29 01:15:14 2362 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-03-29 01:15:14 2362 [Note] InnoDB: Memory barrier is not used
2017-03-29 01:15:14 2362 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-03-29 01:15:14 2362 [Note] InnoDB: Using Linux native AIO
2017-03-29 01:15:14 2362 [Note] InnoDB: Using CPU crc32 instructions
2017-03-29 01:15:14 2362 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2017-03-29 01:15:14 2362 [Note] InnoDB: Completed initialization of buffer pool
2017-03-29 01:15:14 2362 [Note] InnoDB: Highest supported file format is Barracuda.
2017-03-29 01:15:14 2362 [Note] InnoDB: 128 rollback segment(s) are active.
2017-03-29 01:15:14 2362 [Note] InnoDB: Waiting for purge to start
2017-03-29 01:15:14 2362 [Note] InnoDB: 5.6.35 started; log sequence number 3911610
2017-03-29 01:15:14 2362 [Note] Server hostname (bind-address): '*'; port: 3306
2017-03-29 01:15:14 2362 [Note] IPv6 is available.
2017-03-29 01:15:14 2362 [Note]   - '::' resolves to '::';
2017-03-29 01:15:14 2362 [Note] Server socket created on IP: '::'.
2017-03-29 01:15:14 2362 [Warning] 'proxies_priv' entry '@ root@xuexi.longshuai.com' ignored in --skip-name-resolve mode.
2017-03-29 01:15:14 2362 [Note] Event Scheduler: Loaded 0 events
2017-03-29 01:15:14 2362 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.35'  socket: '/mydata/data/mysql.sock'  port: 3306  MySQL Community Server (GPL)

Mysql日志的配置

MySQL有以下几种日志:

  • 错误日志: -log-err
  • 查询日志: -log
  • 慢查询日志: -log-slow-queries
  • 更新日志: -log-update
  • 二进制日志: -log-bin

默认情况下,所有日志创建于mysqld数据目录中。通过刷新日志,你可以强制 mysqld来关闭和重新打开日志文件(或者在某些情况下切换到一个新的日志)。当你执行一个FLUSH LOGS语句或执行mysqladmin flush-logs或mysqladmin refresh时,出现日志刷新

3.一般查询日志

查询日志分为一般查询日志和慢查询日志,它们是通过查询是否超出变量 long_query_time 指定时间的值来判定的。在超时时间内完成的查询是一般查询,可以将其记录到一般查询日志中,但是建议关闭这种日志(默认是关闭的),超出时间的查询是慢查询,可以将其记录到慢查询日志中。

使用" --general_log={0|1} "来决定是否启用一般查询日志,使用" --general_log_file=file_name "来指定查询日志的路径。不给定路径时默认的文件名以 `hostname`.log 命名。

和查询日志有关的变量有:

long_query_time = 10 # 指定慢查询超时时长,超出此时长的属于慢查询,会记录到慢查询日志中
log_output={TABLE|FILE|NONE}  # 定义一般查询日志和慢查询日志的输出格式,不指定时默认为file

TABLE表示记录日志到表中,FILE表示记录日志到文件中,NONE表示不记录日志。只要这里指定为NONE,即使开启了一般查询日志和慢查询日志,也都不会有任何记录。

和一般查询日志相关的变量有:

general_log=off # 是否启用一般查询日志,为全局变量,必须在global上修改。
sql_log_off=off # 在session级别控制是否启用一般查询日志,默认为off,即启用
general_log_file=/mydata/data/hostname.log  # 默认是库文件路径下主机名加上.log

在MySQL 5.6以前的版本还有一个"log"变量也是决定是否开启一般查询日志的。在5.6版本开始已经废弃了该选项。

默认没有开启一般查询日志,也不建议开启一般查询日志。此处打开该类型的日志,看看是如何记录一般查询日志的。

首先开启一般查询日志。

mysql> set @@global.general_log=1;

[root@xuexi data]# ll *.log
-rw-rw---- 1 mysql mysql 5423 Mar 20 16:29 mysqld.log
-rw-rw---- 1 mysql mysql  262 Mar 29 09:31 xuexi.log

执行几个语句。

mysql> select host,user from mysql.user;
mysql> show variables like "%error%";
mysql> insert into ttt values(233);
mysql> create table tt(id int);
mysql> set @a:=3;

查看一般查询日志的内容。

[root@xuexi data]# cat xuexi.log 
/usr/local/mysql/bin/mysqld, Version: 5.6.35-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /mydata/data/mysql.sock
Time                Id Command    Argument
180421 20:04:41     13 Query      select user,host from mysql.user
180421 20:06:06     13 Query      show variables like "%error%"
180421 20:07:28     13 Query      insert into ttt values(233)
180421 20:11:47     13 Query      create table tt(id int)
180421 20:12:29     13 Query      set @a:=3

由此可知,一般查询日志查询的不止是select语句,几乎所有的语句都会记录。

1. 错误日志

--log-error[=file_name]选项来指定mysqld保存错误日志文件的位置。如果没有给定file_name值,mysqld使用错误日志名 host_name.err并在数据目录中写入日志文件。如果你执行FLUSH LOGS,错误日志用-old重新命名后缀并且mysqld创建一个新的空日志文件。(如果未给出--log-error选项,则不会重新命名)。
如果不指定--log-error,或者(在Windows中)如果你使用--console选项,错误被写入标准错误输出stderr。通常标准输出为你的终端。

4.慢查询日志

查询超出变量 long_query_time 指定时间值的为慢查询。但是查询获取锁(包括锁等待)的时间不计入查询时间内。

mysql记录慢查询日志是在查询执行完毕且已经完全释放锁之后才记录的,因此慢查询日志记录的顺序和执行的SQL查询语句顺序可能会不一致(例如语句1先执行,查询速度慢,语句2后执行,但查询速度快,则语句2先记录)。

注意,MySQL 5.1之后就支持微秒级的慢查询超时时长,对于DBA来说,一个查询运行0.5秒和运行0.05秒是非常不同的,前者可能索引使用错误或者走了表扫描,后者可能索引使用正确。

另外,指定的慢查询超时时长表示的是超出这个时间的才算是慢查询,等于这个时间的不会记录。

和慢查询有关的变量:

long_query_time=10 # 指定慢查询超时时长(默认10秒),超出此时长的属于慢查询
log_output={TABLE|FILE|NONE} # 定义一般查询日志和慢查询日志的输出格式,默认为file
log_slow_queries={yes|no}    # 是否启用慢查询日志,默认不启用
slow_query_log={1|ON|0|OFF}  # 也是是否启用慢查询日志,此变量和log_slow_queries修改一个另一个同时变化
slow_query_log_file=/mydata/data/hostname-slow.log  #默认路径为库文件目录下主机名加上-slow.log
log_queries_not_using_indexes=OFF # 查询没有使用索引的时候是否也记入慢查询日志

现在启用慢查询日志。

mysql> set @@global.slow_query_log=on;

因为默认超时时长为10秒,所以进行一个10秒的查询。

mysql> select sleep(10);

查看慢查询日志文件。这里看到虽然sleep了10秒,但是最后查询时间超出了847微秒,因此这里也记录了该查询。

[root@xuexi data]# cat xuexi-slow.log 
/usr/local/mysql/bin/mysqld, Version: 5.6.35-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /mydata/data/mysql.sock
Time                 Id Command    Argument
# Time: 170329  9:55:58
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 10.000847  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use test;
SET timestamp=1490752558;
select sleep(10);

威尼斯澳门在线 ,随着时间的推移,慢查询日志文件中的记录可能会变得非常多,这对于分析查询来说是非常困难的。好在提供了一个专门归类慢查询日志的工具mysqldumpslow。

[root@xuexi data]# mysqldumpslow --help
  -d           debug  
  -v           verbose:显示详细信息
  -t NUM       just show the top n queries:仅显示前n条查询
  -a           don't abstract all numbers to N and strings to 'S':归类时不要使用N替换数字,S替换字符串
  -g PATTERN   grep: only consider stmts that include this string:通过grep来筛选select语句。

该工具归类的时候,默认会将同文本但变量值不同的查询语句视为同一类,并使用N代替其中的数值变量,使用S代替其中的字符串变量。可以使用-a来禁用这种替换。如:

[root@xuexi data]# mysqldumpslow xuexi-slow.log 
Reading mysql slow query log from xuexi-slow.log
Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select sleep(N)

[root@xuexi data]#  mysqldumpslow -a xuexi-slow.log   
Reading mysql slow query log from xuexi-slow.log
Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select sleep(10)

显然,这里归类后的结果只是精确到0.01秒的,如果想要显示及其精确的秒数,则使用-d选项启用调试功能。

[root@xuexi data]#  mysqldumpslow -d xuexi-slow.log   
Reading mysql slow query log from xuexi-slow.log
[[/usr/local/mysql/bin/mysqld, Version: 5.6.35-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /mydata/data/mysql.sock
Time                 Id Command    Argument
# Time: 170329  9:55:58
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 10.000847  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use test;
SET timestamp=1490752558;
select sleep(10);
]]
<<>>
<<# Time: 170329  9:55:58
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 10.000847  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use test;
SET timestamp=1490752558;
select sleep(10);
>> at /usr/local/mysql/bin/mysqldumpslow line 97, <> chunk 1.
[[# Time: 170329  9:55:58
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 10.000847  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use test;
SET timestamp=1490752558;
select sleep(10);
]]
{{  select sleep(N)}}

Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select sleep(N)

慢查询在SQL语句调优的时候非常有用,应该将它启用起来,且应该让慢查询阈值尽量小,例如1秒甚至低于1秒。就像一天执行上千次的1秒语句,和一天执行几次的20秒语句,显然更值得去优化这个1秒的语句。

2. 通用查询日志

--log[=file_name]-l [file_name]选项启动它。如果没有给定file_name的值,默认名是host_name.log

5.二进制日志

3. 慢速查询日志

--log-slow-queries[=file_name]选项启动时,mysqld 写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件.如果没有给出file_name值,默认未主机名,后缀为 -slow.log。如果给出了文件名,但不是绝对路径名,文件则写入数据目录。

5.1 二进制日志文件

二进制日志包含了引起或可能引起数据库改变(如delete语句但没有匹配行)的事件信息,但绝不会包括select和show这样的查询语句。语句以"事件"的形式保存,所以包含了时间、事件开始和结束位置等信息。

二进制日志是以事件形式记录的,不是事务日志(但可能是基于事务来记录二进制日志),不代表它只记录innodb日志,myisam表也一样有二进制日志。

二进制日志只在事务提交的时候一次性写入(基于事务的innodb二进制日志)

MariaDB/MySQL默认没有启动二进制日志,要启用二进制日志使用 --log-bin=[on|off|file_name] 选项指定,如果没有给定file_name,则默认为datadir下的主机名加"-bin",并在后面跟上一串数字表示日志序列号,如果给定的日志文件中包含了后缀(logname.suffix)将忽略后缀部分。

威尼斯澳门在线 1

或者在配置文件中的[mysqld]部分设置log-bin也可以。注意:对于mysql 5.7,直接启动binlog可能会导致mysql服务启动失败,这时需要在配置文件中的mysqld为mysql实例分配server_id。

[mysqld]
# server_id=1234
log-bin=[on|filename]

mysqld还创建一个二进制日志索引文件,当二进制日志文件滚动的时候会向该文件中写入对应的信息。所以该文件包含所有使用的二进制日志文件的文件名。默认情况下该文件与二进制日志文件的文件名相同,扩展名为'.index'。要指定该文件的文件名使用 --log-bin-index[=file_name] 选项。当mysqld在运行时不应手动编辑该文件,免得mysqld变得混乱。

当重启mysql服务或刷新日志或者达到日志最大值时,将滚动二进制日志文件,滚动日志时只修改日志文件名的数字序列部分。

二进制日志文件的最大值通过变量 max_binlog_size 设置(默认值为1G)。但由于二进制日志可能是基于事务来记录的(如innodb表类型),而事务是绝对不可能也不应该跨文件记录的,如果正好二进制日志文件达到了最大值但事务还没有提交则不会滚动日志,而是继续增大日志,所以 max_binlog_size 指定的值和实际的二进制日志大小不一定相等。

因为二进制日志文件增长迅速,但官方说明因此而损耗的性能小于1%,且二进制目的是为了恢复定点数据库和主从复制,所以出于安全和功能考虑,极不建议将二进制日志和datadir放在同一磁盘上

4. 更新日志

--log-update[=file_name]选项启动,不推荐使用.

本文由澳门在线威尼斯官方发布于电脑数据库,转载请注明出处:Mysql日志解析,详细分析MySQL的日志

关键词:

上一篇:SQL语句大全

下一篇:没有了