深入浅出MySQL之MySQL 日志

2019-10-15 本文已影响0人安静点就睡吧

MySQL 日志

错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。

可以用 --log-error[=file_name] 选项来指定 mysqld（MySQL 服务器）保存错误日志文件的位置。如果没有给定 file_name 值，mysqld 使用错误日志名 host_name.err（host_name 为主机名）并默认在参数 DATADIR（数据目录）指定的目录中写入日志文件。

二进制日志

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言） 语句和 DML（数据操纵语言） 语句，但是不包括数据查询语句。语句以“事件”的形式保存，它描述了数据的更改过程。

此日志对于灾难时的数据恢复起着极其重要的作用。

日志的位置和格式

当用 --log-bin[=file_name] 选项启动时，mysqld 将包含所有更新数据的 SQL 命令写入日志文件。如果没有给出 file_name 值，默认名为主机名后面跟 “-bin”。如果给出了文件名，但没有包含路径，则文件默认被写入参数 DATADIR（数据目录）指定的目录。

日志的读取

由于日志以二进制方式存储，不能直接读取，需要用 mysqlbinlog 工具来查看，语法如下：

shell> mysqlbinlog log-file;

日志的删除

对于比较繁忙的 OLTP（在线事务处理）系统，由于每天生成日志量大，这些日志如果长时间不清除，将会对磁盘空间带来很大的浪费。因此，定期删除日志是 DBA 维护 MySQL 数据库的一个重要工作内容。下面将介绍几种删除日志的常见方法。

方法1

执行“RESET MASTER;“|命令，该命令将删除所有 BINLOG 日志，新日志编号从 ”00001“开始。下例中删除了当前的所有日志。

查看删除前日志

mysql> system ls -ltr localhost-bin*
-rw-rw---- 1 mysql mysql 145 Aug 10 04:04 localhost-bin.000006
-rw-rw---- 1 mysql mysql 144 Aug 10 04:04 localhost-bin.index
-rw-rw---- 1 mysql mysql 98 Aug 10 04:04 localhost-bin.000009
-rw-rw---- 1 mysql mysql 145 Aug 10 04:04 localhost-bin.000008
-rw-rw---- 1 mysql mysql 145 Aug 10 04:04 localhost-bin.000007

在测试环境中，二进制日志格式为“hostname-bin.”后跟日志序号。而测试机的 hostname 设置为“localhost”，因此这里用“localhost-bin.”来表示所有的日志。结果中的 “localhost-bin.index”是日志的索引文件，记录了最大的日志序号，本例中可以将此文件忽略。

用 RESET MASTER 命令进行日志删除。

mysql> reset master;
Query OK, 0 rows affected (0.08 sec)

查看看删除后的日志
```
mysql> system ls -ltr localhost-bin*
-rw-rw---- 1 mysql mysql 36 Aug 10 04:04 localhost-bin.index
-rw-rw---- 1 mysql mysql 98 Aug 10 04:04 localhost-bin.000001
```
可以发现，以前的日志全部被清空。新日志重新从“000001”开始编号。

方法2

执行“PURGE MASTER LOGS TO 'mysql-bin.******'”命令，该命令将删除“******”编号之前的所有日志。下例中删除了“localhost-bin.000006”之前编号的所有日志。

查看删除前日志。

mysql> system ls -ltr localhost-bin*
-rw-rw---- 1 mysql mysql 145 Aug 10 04:00 localhost-bin.000004
-rw-rw---- 1 mysql mysql 98 Aug 10 04:00 localhost-bin.000006
-rw-rw---- 1 mysql mysql 145 Aug 10 04:00 localhost-bin.000005
-rw-rw---- 1 mysql mysql 108 Aug 10 04:00 localhost-bin.index

用 PURGE 命令进行删除

mysql> purge master logs to 'localhost-bin.000006';
Query OK, 0 rows affected (0.04 sec)

查看删除后的日志

mysql> system ls -ltr localhost-bin*
-rw-rw---- 1 mysql mysql 98 Aug 10 04:00 localhost-bin.000006
-rw-rw---- 1 mysql mysql 36 Aug 10 04:03 localhost-bin.index

从结果中发现，编号“000006”之前的所有日志都已经被删除。

方法3

执行“PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh24:mi:ss'”命令，该命令将删除日期为 “yyyy-mm-dd hh24:mi:ss” 之前产生的所有日志。下例中删除了日期在“2007-08-10 04:07:00” 之前的所有日志。

查看删除前的日志

mysql> system ls -ltr localhost-bin*
-rw-rw---- 1 mysql mysql 145 Aug 10 04:06 localhost-bin.000001
-rw-rw---- 1 mysql mysql 145 Aug 10 04:06 localhost-bin.000002
-rw-rw---- 1 mysql mysql 145 Aug 10 04:06 localhost-bin.000003
-rw-rw---- 1 mysql mysql 145 Aug 10 04:06 localhost-bin.000004
-rw-rw---- 1 mysql mysql 145 Aug 10 04:06 localhost-bin.000005
-rw-rw---- 1 mysql mysql 252 Aug 10 04:07 localhost-bin.index
-rw-rw---- 1 mysql mysql 98 Aug 10 04:07 localhost-bin.000007
-rw-rw---- 1 mysql mysql 145 Aug 10 04:07 localhost-bin.000006

用 PURGE 命令删除 “2007-08-10 04:07:00”之前的所有日志。

mysql> purge master logs before '2007-08-10 04:07:00';
Query OK, 0 rows affected (0.04 sec)

查看删除后日志，果然，系统只保留了两个指定删除日期后的日志。

mysql> system ls -ltr localhost-bin*
-rw-rw---- 1 mysql mysql 98 Aug 10 04:07 localhost-bin.000007
-rw-rw---- 1 mysql mysql 145 Aug 10 04:07 localhost-bin.000006
-rw-rw---- 1 mysql mysql 72 Aug 10 04:08 localhost-bin.index

方法4

设置参数 --expire_logs_days=#，此参数的含义是设置日志的过期天数，过了指定的天数后日志将会被自动删除，这样将将有利于减少 DBA 管理日志的工作量。下例中通过手工更改系统日期来测试此参数的使用。

查看删除前日志

mysql> system ls -ltr localhost-bin.*
-rw-rw---- 1 mysql mysql 145 Dec 25 00:00 localhost-bin.000041
-rw-rw---- 1 mysql mysql 145 Dec 25 00:06 localhost-bin.000042
-rw-rw---- 1 mysql mysql 144 Dec 25 00:06 localhost-bin.index
-rw-rw---- 1 mysql mysql 98 Dec 25 00:06 localhost-bin.000044
-rw-rw---- 1 mysql mysql 145 Dec 25 00:06 localhost-bin.000043

在 my.cnf 的[mysqld]中加入 “expire_logs_day=3"，然后重启 MySQL 服务。

[root@localhost zzx]# more /etc/my.cnf
[mysqld]
ndbcluster
log-bin
expire_logs_day=3
[root@localhost log]# service mysql restart
Shutting down MySQL.[ OK ]
Starting MySQL[ OK ]

将系统时间改为1天后

[root@localhost mysql]# date
Tue Dec 25 01:14:19 CST 2007
[root@localhost mysql]# date -s '20071226 14:00:00'
Wed Dec 26 14:00:00 CST 2007

用“flush logs”触发日志文件更新，这是由于没有到 3 天，所有日志将不会被删除。

[root@localhost mysql]# mysqladmin flush-log
[root@localhost mysql]# ls -ltr |grep 'localhost-bin*'
-rw-rw---- 1 mysql mysql 145 Dec 25 00:00 localhost-bin.000041
-rw-rw---- 1 mysql mysql 145 Dec 25 00:06 localhost-bin.000042
-rw-rw---- 1 mysql mysql 145 Dec 25 00:06 localhost-bin.000043
-rw-rw---- 1 mysql mysql 145 Dec 26 14:00 localhost-bin.000044
-rw-rw---- 1 mysql mysql 216 Dec 26 14:00 localhost-bin.index
-rw-rw---- 1 mysql mysql 98 Dec 26 14:00 localhost-bin.000046
-rw-rw---- 1 mysql mysql 145 Dec 26 14:00 localhost-bin.000045

将日期改为 3 天以后，再次执行“flush logs”触发日志文件更新。

[root@localhost mysql]# date -s '20071228 14:00:00'
Fri Dec 28 14:00:00 CST 2007
[root@localhost mysql]# mysqladmin flush-log
[root@localhost mysql]# ls -ltr |grep 'localhost-bin*'
-rw-rw---- 1 mysql mysql 145 Dec 26 14:00 localhost-bin.000044
-rw-rw---- 1 mysql mysql 145 Dec 26 14:00 localhost-bin.000045
-rw-rw---- 1 mysql mysql 144 Dec 28 14:00 localhost-bin.index
-rw-rw---- 1 mysql mysql 98 Dec 28 14:00 localhost-bin.000047
-rw-rw---- 1 mysql mysql 145 Dec 28 14:00 localhost-bin.000046

从结果中可以看出，3 天前日志 localhost-bin.000041-- localhost-bin.000043 都已经被删除。

其它选项

二进制日志由于记录了数据的变化过程，对于数据的完整性和安全性起着非常重要的作用。因此，MySQL 还提供了一些其它参数来进行更小粒度的管理，具体介绍如下。

--binlog-do-db=db_name

该选项告诉主服务器，如果当前的数据库（即 USE 选定的数据库）是 db_name，应将更新记录到二进制日志中。其它所有没有显式指定的数据库更新将被忽略，不记录在日志中。
--binlog-ignore-db=db_name

该选项告诉主服务器，如果当前的数据库（即 USE 选定的数据库）是 db_name，不应该将更新保存到二进制日志中，其它没有显式忽略的数据库都将进行记录。

如果想记录或忽略多个数据库，可以对上面两个选项分别使用多次，即对每个数据库指定相应的选项。例如，如果只想记录数据库 db1 和 db2 的日志，可以在参数文件中设置两行：
```
--binlog-do-db=db1
--binlog-do-db=db2
```
--innodb-safe-binlog

此选项经常和 --sync-binlog=N （每写 N 次日志同步磁盘）一起配合使用，使得事务在日志中的记录更加安全。
SET SQL_LOG_BIN=0

具有 SUPER 权限的客户端可以通过此语句禁止将自己的语句记录二进制记录。这个选项在某些环境下是有用的，但是使用时一定要小心，因为它很可能造成日志记录的不完整或者在复制环境中造成主从数据的不一致。

查询日志

日志的位置和格式

当用--log[=file_name]或-l [file_name]选项启动 mysqld（MySQL 服务器）时，查询日志开始被记录。和其他日志一样，如果没有给定 file_name 的值，日志将写入参数 DATADIR（数据目录）指定的路径下，默认文件名是 host_name.log。

日志的读取

因为查询日志记录的格式是纯文本，因此可以直接进行读取。

注意：log 日志中记录了所有数据库的操作，对于访问频繁的系统，此日志对系统性能的影响较大，建议一般情况下关。

慢日志

慢查询日志记录了包含所有执行时间超过参数 long_query_time（单位：秒）所设置值的 SQL 语句的日志。获得表锁定的时间不算作执行时间。

文件位置和格式

当用--log-slow-queries[=file_name]选项启动 mysqld（MySQL 服务器）时，慢查询日志开始被记录。和前面几种日志一样，如果没有给定 file_name 的值，日志将写入参数 DATADIR（数据目录）指定的路径下，默认文件名是 host_name-slow.log。

日志的读取

和错误日志、查询日志一样，慢查询日志记录的格式也是纯文本，可以被直接读取。如果慢查询日志中记录内容很多，可以使用 mysqldumpslow 工具（MySQL 客户端安装自带）来对慢查询日志进行分类汇总。

对于 SQL 文本完全一致，只是变量不同的语句，mysqldumpslow 将会自动视为同一个语句进行统计，变量值用 N 来代替。这个统计结果将大大增加用户阅读慢查询日志的效率，并迅速定位系统的 SQL 瓶颈。

注意：慢查询日志对于我们发现应用中有性能问题的 SQL 很有帮助，建议正常情况下，打开此日志并经常查看分析。

其它选项

在 MySQL 5.1 中，通过--log-slow-admin-statements 服务器选项，可以请求将慢管理语句，例如 OPTIMIZE TABLE、ANALYZE TABLE 和 ALTER TABLE 写入慢查询日志。

总结

本章主要介绍了 MySQL 最常用的 4 种日志类型：错误日志、二进制日志、查询日志和慢查询日志。

这 4 种日志各有不同的用途。

系统故障时，建议首先查看错误日志，以帮助用户迅速定位故障原因。
如果要记录数据的变更、数据的备份、数据的复制等操作时，二进制日志必须打开，以帮助用户进行数据恢复等操作。默认不记录此日志，建议通过 --log-bin 选项将此日志打开。
如果希望记录数据库发生的任何操作，包括 SELECT，则需要用 --log 将查询日志打开，此日志默认关闭，一般情况下建议不要打开此日志，以免影响系统整体性能。
如果希望查看系统的性能问题，希望找到有性能问题的 SQL 语句，则需要用 --log-slow-queries 打开慢查询日志。对于大量的慢查询日志，建议使用 mysqldumpslow 工具来进行汇总查看。

深入浅出MySQL之MySQL 日志

MySQL 日志

错误日志

二进制日志

日志的位置和格式

日志的读取

日志的删除

方法1

方法2

方法3

方法4

其它选项

查询日志

日志的位置和格式

日志的读取

慢日志

文件位置和格式

日志的读取

其它选项

总结

猜你喜欢

热点阅读