MySQL的监控原理

MySQL的监控通过日志文件来实现。

那么MySQL的日志文件有哪些类型呢？ MySQL使用以下这几种类型的日志来记录服务器活动信息。

Error log错误日志:关于启动、关闭和异常情况的诊断消息（排查错误）

General query log通用查询日志:服务器从客户端接收到的所有语句（全局监控）

Slow query log慢速查询日志:执行时间较长的查询（性能优化）

Audit log审计日志:针对企业版的策略审计(主从搭配)

Binary log二进制日志:用于修改数据（备份用的到）

这5个类型的日志文件涵盖了所有MySQL的运行记录，是将来排查问题，优化性能的重要依据。很多经验丰富的DBA遇到问题就是先从这些日志文件下手分析的。重点之重，要考的。

日志文件的特点：占用大量的磁盘空间，存储在文件中，也可以存储在表中，但只有Slow query log慢速查询日志和General query log通用查询日志是可以存储在表中。可以用文本格式书写，但Binary log二进制日志除外。（因为Binary log中都是二进制格式啊！）

Error log错误日志:设置文件名set- -log- Error =filename来记录错误到给定的文件。或者在 UNIX或基于UNIX的系统中使用- -log-syslog服务器选项将错误消息发送到syslog系统日志上。

General query log通用查询日志:设置文件名set- -general_log_file=filename为日志查询。全局general_log和general_log_file服务器变量提供了对通用查询日志的运行时控制。将general_log设置为0(或OFF)以禁用日志，或设置为1(或ON)以启用日志。

Slow query log慢速查询日志:设置文件名set- -slow_query_log_file=filename以提供对慢速查询日志的运行时控制。设置slow_query_log为0禁用日志，设置为1启用日志，如果日志文件已经打开，则关闭该日志文件并打开新文件。

Binary log二进制日志:设置Set --log-bin以启用二进制日志记录。

服务器使用该选项值作为基本名称，在创建新日志文件时向基本名称添加一个递增的连续数字后缀。这些日志文件以二进制格式而不是文本格式存储。

Audit log审计日志:审计日志作为Enterprise Edition插件提供，当加载时，您可以使用该插件将事件记录到audit_log_file选项指定的文件中。审计不断写入审计日志，直到您删除插件，或者使用audit_log_policy=NONE选项设置关闭审计。通过在服务器启动时使用audit_log=FORCE_PLUS_PERMANENT作为选项来防止删除插件。

接下来我们逐个讲解

General query log通用查询日志可以使用general_log服务器选项启用，它是用来记录所执行语句的连接信息和详细信息。包括记录每个连接的时间和类型以及所有操作的进程ID，记录对所有表执行的所有语句，但不包括存储在二进制日志的更新操作行变化，因为这些不是更新操作以语句形式被存储的。而且General query log通用查询日志扩张的很快，但是如果以一个短时间内启用它，可以收集这些时间内所有活动的完整记录。

具体记录了些什么，我们在此举例说明，请看下面：

1424 Query /* mem dbpool.default */ update

`mem__events`.events set lastUpdateTime=1397633565708 where id=7 1429 Query /* mem dbpool.default */ update `mem__events`.events set lastUpdateTime=1397633565708 where id=4970 1424 Query /* mem dbpool.default */ commit 1429 Query /* mem dbpool.default */ commit 150526 7:32:46 1434 Connect root@localhost on mysql 1434 Query SET NAMES latin1 1434 Query SET character_set_results = NULL

里面包含事件类型和查询内容，示例类型:查询、连接

上面代码显示每个连接和查询的详细信息，记录每次连接的时间和所有操作的进程号。

Slow query log慢速查询日志可以通过使用slow_query_log服务器选项启用。用于记录执行时间超过指定阈值的语句。一般设定10秒(默认)，可以使用long_query_time server选项更改此持续时间。可以指定微秒精度的秒数。可以记录不使用索引的语句，甚至是long_query_time下面的语句。比如用log_queries_not_using_indexes来实现。可以使用mysqldumpslow命令行程序查看Slow query log慢速查询日志。

Slow query log慢速查询日志是记录管理和复制语句。但默认情况下，慢速查询日志不记录管理语句。可以通过启用log_slow_admin_statements服务器选项来记录这些语句。默认情况下，从复制主机复制的语句不会出现在慢速查询日志中，即使它们超过了long_query_time服务器选项指定的时间。此时可以要记录这些语句，请启用log_slow_slave_statements服务器选项来实现。

如何过滤慢速查询日志事件？这有两个额外的服务器选项，您可以使用来过滤缓慢的查询日志输出:

第一个min_examined_row_limit：指定语句必须检查的最低行数，以便慢速查询日志记录该语句。

第二个log_throttle_queries_not_using_indexes：它指定慢速查询在60秒内记录的查询数量，因为它们不使用索引。慢速查询日志记录了这些查询的数量后，它汇总了在该时间段内花费在其余语句上的数量和总时间。默认情况下，该服务器选项的值为0，表示它记录所有此类查询。

举例说明：

# Time: date-and-time # User@Host: root[root] @ localhost [127.0.0.1] Id: 7694 # Query_time: 1.010099 Lock_time: 0.000023 Rows_sent: 0 Rows_examined: 1 SET timestamp=timestamp; /* mem dbpool.default */ update `mem__inventory`.`MysqlServer` set `timestamp`=timestamp where hid=x'08310BA6528CBF5783BBD95CE5B4561F';

例子包括服务器日期和时间，连接和查询信息，运行查询和持有锁的时间。

我们使用mysqldumpslow查看慢速查询日志，mysqldumpslow命令行程序总结了慢速查询日志的内容。它将类似的查询分组在一起，例如将数值参数更改为N，将字符串参数更改为'S'，显示此类查询的数量以及运行查询所需的平均时间和总时间。使用-g选项提供一个搜索词，仅显示匹配搜索项的语句的摘要信息。也可以通过设定log_output服务器选项来指定表或文件日志输出。。

log_output服务器选项是用来配置慢速查询日志和通用查询日志。它包含一个或多个FILE、TABLE或NONE值(以逗号分隔)。默认情况下，它的值为FILE。NONE代表日志既不写文件，也不写表。NONE的存在会导致忽略FILE或TABLE。FILE表示日志分别写入由slow_query_log_file和general_log_file MySQL服务器选项指定的文件。在MySQL数据目录文件中的缺省值为hostname-slow.log和hostname.log。TABLE表示在mysql数据库中，慢速查询日志写到slow_log表，常规日志写到general_log表。

随着时间的推移，日志文件会占用更大的空间。定期备份和删除旧的日志文件，并重新记录到新的日志文件是DBA的日常工作之一。注意，如果使用二进制日志进行复制，请谨慎使用。备份后，刷新日志。刷新日志需要注意

1. 创建新的二进制日志文件

2. 关闭并重新打开常规和慢速查询日志文件。在创建新日志时，必须在刷新之前重命名当前日志文件。

可以创建自己的脚本或使用提供的mysql-log-rotate脚本来实现定期安排日志文件的循环。备份日志文件后，刷新日志以强制MySQL服务器开始写入新的日志文件。可以通过执行FLUSH LOGS SQL语句或执行mysqladmin flush-logs来实现。在刷新二进制日志会导致二进制日志记录顺序中的下一个文件重新开始。刷新普通和慢速查询日志时，这将关闭日志文件，重新打开它们，然后在相同的文件名下重新进行日志记录，要启动新的日志，请在刷新之前重命名现有的日志文件。