MySQL存储引擎InnoDB详解（一）日志文件

  本系列是《MySQL技术内幕 InnoDB 存储引擎 第2版》的学习笔记。

  MySQL包含的日志文件类型：

• 错误日志（error log）

• 慢查询日志（slow query log）

• 查询日志（log）

• 二进制日志（binlog）

一、错误日志

  记录了所有的错误信息

  遇到问题时，优先查看该文件定位问题。

  通过命令

	SHOW VARIABLES LIKE 'log_error'

  来定位文件。

  错误文件名称一般为主机名，文件格式.err。

二、慢查询日志

  慢查询日志帮助DBA定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。

2.1 SQL运行时间阈值

  可以在MySQL启动时设一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。DBA每天或每过一段时间对其进行检查，确认是否有SQL语句需要进行优化。 该阈值可以通过参数 long_query_time 来设置，默认值为10.代表10秒。

2.2 开启慢查询日志

  默认情况下，MySQL数据库并不启动慢查询日志，用户需要手工将这个参数设为ON:

	SHOW VARIABLES LIKE 'long_query_time' 

  其次，从MySQL5.1开始，开始以微秒记录SQL语句运行的时间。

2.3 记录未使用索引的SQL

  另一个和慢查询日志有关的参数是 log_queries_not_using_indexex，如果运行的SQL没有使用索引，则会记录到慢查询日志。使用时首先确认已打开该参数

	SHOW VARIABLES LIKE 'log_queries_not_using_indexex'  

  另外，MySQL5.6.5版本开始新增了一个参数log_throttle_queries_not_using_indexes，用来限制每分钟允许记录到slow log的且未使用索引的SQL语句条数。 该值默认未0，表示没有限制。 在生产环境下，未使用索引的SQL语句会频繁被记录到slow log，可以通过该参数进行配置限制。

2.4 日志文件分析

  随着运行时间的增加，慢查询日志文件会越来越大，直接分析不便。可以通过mysqldumpslow命令来辅助。 如，求执行时间最长的10条SQL语句

	mysqldumpslow -s al -n 10 david.log  

2.5 日志表分析

  MySQL5.1开始可以将慢查询的日志记录放入slow_log表中。 参数log_output指定了慢查询输出的格式，默认未FILE，可以将它设为TABLE.

  查看

	SHOW VARIABLES LIKE 'log_output'

  设置

	SET GLOBAL log_output='TABLE'

三、查询日志

  记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。默认文件名未：主机名.log

  同样的，从MySQL5.1开始，可以将查询日志输出为general_log表，使用方法同slow_log。

四、二进制日志

  记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作堆数据本身并没有修改。

  作用：

• 恢复：在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复

• 复制：通过复制和执行二进制日志与一台远程的MySQL数据库进行实时同步。

• 审计：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

  默认二进制日志文件名为主机名，后缀名为二进制日志的序列号，所在路径为数据库所在目录（datadir）

	show variables like 'datadir'  

  根据MySQL官方手册中的测试表明，开启二进制日志会使性能下降1%，但考虑到可以使用复制和point-in-time的恢复，这些性能损失是能被接受的。