mysql日志

在任何一种数据库中，都会有各种各样的日志，记录着数据库工作的方方面面，以帮助数据
库管理员追踪数据库曾经发生过的各种事件。MySQL 也不例外，在 MySQL 中，有 4 种不同
的日志，分别是错误日志、二进制日志（BINLOG 日志）、查询日志和慢查询日志，这些日志
记录着数据库在不同方面的踪迹。本章将详细介绍这几种日志的作用和使用方法，希望读者
能充分利用这些日志对数据库进行各种维护和调优。
 

26.1 错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在
运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，
可以首先查看此日志。
可以用--log-error[=file_name]选项来指定 mysqld（MySQL 服务器）保存错误日志文件的位置。
如果没有给定 file_name 值，mysqld 使用错误日志名 host_name.err（host_name 为主机名）
并默认在参数 DATADIR（数据目录）指定的目录中写入日志文件。
以下是 MySQL 正常启动和关闭的一段日志，不同的版本可能略有不同：
[root@localhost mysql]# more localhost.localdomain.err Linux
 
070727 04:24:46 mysqld started
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
070727 4:24:46 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
070727 4:24:46 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
070727 4:24:46 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
070727 4:24:47 InnoDB: Started; log sequence number 0 0
070727 4:24:47 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.41-community' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL
Community Edition (GPL)
070727 6:20:27 [Note] /usr/sbin/mysqld: Normal shutdown
070727 6:20:27 InnoDB: Starting shutdown...
070727 6:20:28 InnoDB: Shutdown completed; log sequence number 0 43655
070727 6:20:28 [Note] /usr/sbin/mysqld: Shutdown complete
070727 06:20:28 mysqld ended
 
 

26.2 二进制日志

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）
语句，但是不包括数据查询语句。语句以“事件”的形式保存，它描述了数据的更改过程。
此日志对于灾难时的数据恢复起着极其重要的作用。
26.2.1 日志的位置和格式
当用--log-bin[=file_name]选项启动时，mysqld将包含所有更新数据的SQL 命令写入日志文件。
如果没有给出 file_name 值，默认名为主机名后面跟“-bin”。如果给出了文件名，但没有包
含路径，则文件默认被写入参数 DATADIR（数据目录）指定的目录。
26.2.2 日志的读取
由于日志以二进制方式存储，不能直接读取，需要用 mysqlbinlog 工具来查看，语法如下：
shell> mysqlbinlog log-file;
mysqlbinlog 的用法在上一章中已经详细介绍过，因此这里不再赘述。下面的例子演示了二
 
 
 
进制日志的读取过程。
（1）往测试表 emp 中插入两条测试记录。
[root@localhost mysql]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.41-community-log MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into emp values(1,'z1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into emp values(1,'z2');
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye
 
（2）使用 mysqlbinlog 工具进行日志查看，粗体字显示了步骤（1）中所做的操作。
[root@localhost mysql]# mysqlbinlog localhost-bin.000007
……
use test/*!*/;
SET TIMESTAMP=1186691584/*!*/;
SET
@@session.foreign_key_checks=1,
@@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
/*!\C gbk *//*!*/;
SET
@@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_serv
er=28/*!*/;
insert into emp values(1,'z1')/*!*/;
# at 191
#070810 4:33:07 server id 1 end_log_pos 284 Query thread_id=8 exec_time=0
error_code=0
SET TIMESTAMP=1186691587/*!*/;
insert into emp values(1,'z2')/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;Linux
 
 
26.2.3 日志的删除
对于比较繁忙的 OLTP（在线事务处理）系统，由于每天生成日志量大，这些日志如果长时
间不清除，将会对磁盘空间带来很大的浪费。因此，定期删除日志是 DBA 维护 MySQL 数据
库的一个重要工作内容。下面将介绍几种删除日志的常见方法。
1．方法 1
执行“RESET MASTER;”命令，该命令将删除所有 BINLOG 日志，新日志编号从“000001”
开始。下例中删除了当前的所有日志。
（1）查看删除前日志。
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”是日志的索引文件，记录了最大的日志序号，本例中可以将此文件忽
略。
（2）用 RESET MASTER 命令进行日志删除。
mysql> reset master;
Query OK, 0 rows affected (0.08 sec)
（3）查看删除后的日志。
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．方法 2
执行“PURGE MASTER LOGS TO 'mysql-bin.******'”命令，该命令将删除“******”编号之前
的所有日志。下例中删除了“localhost-bin.000006”之前编号的所有日志。
（1）查看删除前日志。
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
（2）用 PURGE 命令进行删除。
mysql> purge master logs to 'localhost-bin.000006';
Query OK, 0 rows affected (0.04 sec)
（3）查看删除后日志。
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．方法 3
执行“PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh24:mi:ss'”命令，该命令将删除日期为
“yyyy-mm-dd hh24:mi:ss”之前产生的所有日志。下例中删除了日期在“2007-08-10 04:07:00”
之前的所有日志。
（1）查看删除前日志。
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
（2）用 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)
（3）查看删除后日志，果然，系统只保留了两个指定删除日期后的日志。
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．方法 4
设置参数--expire_logs_days=#，此参数的含义是设置日志的过期天数，过了指定的天数后日
志将会被自动删除，这样将将有利于减少 DBA 管理日志的工作量。下例中通过手工更改系
统日期来测试此参数的使用。
（1）查看删除前日志。
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
（2）在 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 ]
（3）将系统时间改为 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
（4）用“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
（5）将日期改为 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 都已经被删除。
26.2.4 其他选项
二进制日志由于记录了数据的变化过程，对于数据的完整性和安全性起着非常重要的作用。
因此，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 权限的客户端可以通过此语句禁止将自己的语句记入二进制记录。这个选项在
某些环境下是有用的，但是使用时一定要小心，因为它很可能造成日志记录的不完整或者在
复制环境中造成主从数据的不一致。
 
 

26.3 查询日志

查询日志记录了客户端的所有语句，而二进制日志不包含只查询数据的语句。
26.3.1 日志的位置和格式
当用--log[=file_name]或-l [file_name]选项启动 mysqld（MySQL 服务器）时，查询日志开始被
记录。和其他日志一样，如果没有给定 file_name 的值，日志将写入参数 DATADIR（数据目
录）指定的路径下，默认文件名是 host_name.log。
26.3.2 日志的读取
因为查询日志记录的格式是纯文本，因此可以直接进行读取。下面是一个读取查询日志的例
子。
（1）首先在客户端对数据库做一些简单操作，包括查询和插入。
[root@localhost mysql]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.41-community-log MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from dept;
Empty set (0.00 sec)
mysql> insert into emp values(1,'z2');
Query OK, 1 row affected (0.01 sec)
（2）然后查看查询日志中记录的客户端的所有操作，对应的内容如下：
[root@localhost mysql]# more localhost.log
……
070810 6:52:02 9 Query select * from dept
 
070810 6:52:27 9 Query insert into emp values(1,'z2')
070810 6:53:50 9 Quit
注意：log 日志中记录了所有数据库的操作，对于访问频繁的系统，此日志对系统性能的影响较
大，建议一般情况下关。
 
 

26.4 慢查询日志

慢查询日志记录了包含所有执行时间超过参数 long_query_time（单位：秒）所设置值的 SQL
语句的日志。获得表锁定的时间不算作执行时间。
26.4.1 文件位置和格式
当用--log-slow-queries[=file_name]选项启动 mysqld（MySQL 服务器）时，慢查询日志开始被
记录。和前面几种日志一样，如果没有给定 file_name 的值，日志将写入参数 DATADIR（数
据目录）指定的路径下，默认文件名是 host_name-slow.log。
26.4.2 日志的读取
和错误日志、查询日志一样，慢查询日志记录的格式也是纯文本，可以被直接读取。下例中
演示了慢查询日志的设置和读取过程。
（1）首先查询一下 long_query_time 的值。
mysql> show variables like 'long%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| long_query_time | 10 |
+-----------------+-------+
1 row in set (0.00 sec)
（2）为了方便测试，将修改慢查询时间为 2 秒。
mysql> set long_query_time=2;
Query OK, 0 rows affected (0.02 sec)
（3）依次执行下面两个查询语句。
第一个查询因为查询时间低于 2 秒而不会出现在慢查询日志中：
mysql> select count(1) from emp;
+----------+
| count(1) |
+----------+
| 131075 |
+----------+
1 row in set (0.00 sec)
第二个查询因为查询时间大于 2 秒而应该出现在慢查询日志中：
mysql> select count(1) from emp t1,dept t2 where t1.id=t2.id;
 
+----------+
| count(1) |
+----------+
| 33555200 |
+----------+
1 row in set (11.31 sec)
（4）查看慢查询日志。
[root@localhost mysql]# more localhost-slow.log
/usr/sbin/mysqld, Version: 5.0.41-community-log (MySQL Community Edition (GPL)). started
with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 070810 23:43:55
# User@Host: root[root] @ localhost []
# Query_time: 297 Lock_time: 0 Rows_sent: 0 Rows_examined: 26214
use test;
# Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 512
select count(1) from emp t1,dept t2 where t1.id=t2.id;
从上面日志中，可以发现查询时间超过 2 秒的 SQL，而小于 2 秒的则没有出现在此日志中。
如果慢查询日志中记录内容很多，可以使用 mysqldumpslow 工具（MySQL 客户端安装自带）
来对慢查询日志进行分类汇总。下例中对日志文件 bj37-slow.log 进行了分类汇总，只显示汇
总后摘要结果：
[zzx@bj37 data]$ mysqldumpslow bj37-slow.log
[root@localhost mysql]# mysqldumpslow localhost-slow.log
Reading mysql slow query log from localhost-slow.log
Count: 1 Time=297.00s (297s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
select count(N) from emp t1,emp t2 where t1.id<>t2.id
Count: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhost
select count(N) from emp t1,dept t2 where t1.id=t2.id
Count: 1 Time=9.00s (9s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
select count(N) from emp t1,emp t2 where t1.id=t2.id
Count: 2 Time=3.00s (6s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhost
select count(N) from emp t1,dept t2 where t1.id=t2.id and t1.id=N
对于 SQL 文本完全一致，只是变量不同的语句，mysqldumpslow 将会自动视为同一个语句进
行统计，变量值用 N 来代替。这个统计结果将大大增加用户阅读慢查询日志的效率，并迅
速定位系统的 SQL 瓶颈。
注意：慢查询日志对于我们发现应用中有性能问题的 SQL 很有帮助，建议正常情况下，打开此
日志并经常查看分析。
 
 
26.4.3 其他选项
 
在 MySQL 5.1 中，通过--log-slow-admin-statements 服务器选项，可以请求将慢管理语句，例
如 OPTIMIZE TABLE、ANALYZE TABLE 和 ALTER TABLE 写入慢查询日志。
26.5 小结
日志是数据库中很重要的记录内容，它可以帮助我们诊断数据库出现的各种问题。本章主要
介绍了 MySQL 最常用的 4 种日志类型：错误日志、二进制日志、查询日志和慢查询日志。
这 4 种日志各有不同的用途。
 系统故障时，建议首先查看错误日志，以帮助用户迅速定位故障原因。
 如果要记录数据的变更、数据的备份、数据的复制等操作时，二进制日志必须打开，以
帮助用户进行数据恢复等操作。默认不记录此日志，建议通过--log-bin 选项将此日志打开。
 如果希望记录数据库发生的任何操作，包括 SELECT，则需要用--log 将查询日志打开，
此日志默认关闭，一般情况下建议不要打开此日志，以免影响系统整体性能。
 如果希望查看系统的性能问题，希望找到有性能问题的 SQL 语句，则需要用
--log-slow-queries 打开慢查询日志。对于大量的慢查询日志，建议使用 mysqldumpslow 工具
来进行汇总查看。
posted @ 2020-12-01 19:15  wq9  阅读(79)  评论(0)  编辑  收藏  举报