日志文件

MySQL数据库中常见的日志文件有：

  • 错误日志（error log）
  • 慢查询日志（slow query log）
  • 查询日志（log）
  • 二进制日志（binlog）

错误日志

SHOW VARIABLES LIKE "log_error";

当MySQL数据库不能启动时，可以查看该日志文件。

慢查询日志

MySQL启动时可以设置一个阀值，将运行时间大于该阀值的SQL语句都记录

到慢查询日志中。该阀值可以通过long_query_time来设置，默认值为10，即10秒。

MySQL 5.1开始long_query_time以微妙记录SQL语句运行的时间，之前是秒。

默认情况下MySQL不开启慢查询日志，需要手动将log_slow_queries设置为ON。

记录未使用索引的查询

log_queries_not_using_indexes，如果运行的SQL语句没有使用索引，则MySQL

同样会将这条SQL记录到慢查询日志文件。

MySQL 5.6.5版本开始新增一个参数log_throttle_queries_not_using_indexes，

用来表示每分钟允许记录到慢查询日志的未使用索引的SQL语句的次数，默认是0，

表示无限制。可以控制此类SQL写入慢查询日志的频率。

查看慢查询日志

MySQL数据库提供了 mysqldumpslow命令帮助查看慢查询日志。

-- 查看执行时间最长的10条 SQL
mysqldumpslow -s al -n 10 david.log
慢查询日志的格式

参数log_output指定了慢查询日志输出的格式，默认是FILE，可以设置为`TABLE，

之后的慢查询日志就输入到mysql.slow_log表中。

log_output参数是动态的，并且是全局的。

如果改为输入到表中，还可以改表的存储引擎，为表加索引等。

额外的参数

InnoDB版本加强了对SQL语句的捕获方式，在原版MySQL的基础上在slow_log

中增加对于逻辑读取和物理读取的统计。物理读取即从磁盘进行IO读取的次数，

逻辑读取包括所有读取，不管是磁盘还是缓冲池。

用户可以通过额外的参数long_query_io将超过指定逻辑IO次数的SQL记录到

slow_log中，默认值的100。

还添加了参数slow_query_type，用来表示启动slow log的方式

  • 0 表示不将SQL语句记录到slow log
  • 1 表示根据运行时间将SQL语句记录到slow log
  • 2 表示根据逻辑IO次数将SQL语句记录到slow log
  • 3 表示根据运行时间及逻辑IO次数将SQL语句记录到slow log

查询日志

查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。

默认文件名为：主机名.log。

从MySQL 5.1开始，可以将查询日志的记录放入mysql架构下的general_log表中，

与slow_log表一样。

二进制日志

二进制日志（binary log）记录了对MySQL数据库执行更改的所有操作，但是不包括

SELECT和SHOW这类操作，因为这类操作并不修改数据。但操作本身没有产生对数据

的修改也可能会写入二进制日志，如 

-- 不存储 a = 2 的数据
UPDATE t SET a = 1 WHERE a = 2
二进制日志的作用

  • 恢复（recovery）：point-in-time恢复

  • 复制（replication）：搭建主从

  • 审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库

    进行注入的攻击。

    咋操作？

二进制日志的设置

二进制日志文件在默认情况下没有启动，需要手动指定参数启动。

开启对数据库整体性能按照官方手册表明会下降1%。

一些影响二进制文件的配置

配置 作用
max_binlog_size 指定单个二进制日志文件的最大值，如果超过该值，则产生新的二进制文件
默认是1G
binlog_cache_size 当使用事务的表存储引擎时，所有未提交的二进制日志会被记录到一个缓存中，
等该事务提交时直接将缓冲中的二进制日志写入二进制日志文件，而该文件的
大小由binlog_cache_size决定，默认是32K。
binlog_cache_size是基于会话的，即一个线程开启一个事务时，会自动分配
这么一块内存。
可以通过SHOW GLOBAL STATUS查看binlog_cache_use、binlog_cache_disk_use 的状态，
判断当前binlog_cache_size大小是否合适。
sync_binlog 默认情况下，二进制日志不是在每次写的时候同步到磁盘。
参数sync_binlog=[N]表示每写缓冲多少次就同步到磁盘。
binlog-do-db
binlog-ignore-db		 表示写入或者忽略哪些库的日志，默认为空，表示需要同步所有库的日志到
二进制日志。
log-slave-update 如果当前数据库是slave角色，则不会将从master取得并执行的二进制日志写入
自己的二进制日志文件中去。如果需要写入，则设置log-slave-update。
如果需要搭建master->slave->slave架构的复制，则必须设置该参数。
binlog_format 影响了记录二进制日志的格式，是动态参数
STATEMENT：记录的是日志的的逻辑SQL语句，节省空间
ROW：记录表的行更改情况，不会有数据问题
MIXED：默认会使用STATEMENT，某些情况下会用ROW

binlog_format设置为MIXED格式下，写入ROW格式的情况

  • 表的存储引擎是NDB
  • 使用了UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数
  • 使用了INSERT DELAY语句
  • 使用了用户定义函数（UDF）
  • 使用了临时表（temporary table）
二进制日志的查看

因为格式为二进制，因此无法直接用cat、head、tail等直接查看。

需要通过MySQL提供的工具mysqlbinlog。

STATEMENT格式的二进制日志文件才有可读性，ROW的看不出啥。