MySQL日志管理

在一般情况下（不包括复制和集群等情况），MySQL中会有四种类型的日志文件，如下：

• 错误日志：用于记录在数据库运行中的所有错误
• 一般查询日志：用于记录所有针对数据库的请求信息，不管该操作是否执行；
• 慢查询日志：用于记录一些对数据库很耗时的操作，以便之后进行SQL预计层面的优化；
• 二进制日志：记录所有针对数据的修改的操作，不管该操作是否更改了数据库的数据；

软件环境是Ubuntu 15.10下的 5.6.31-0ubuntu0.15.10.1

1. 错误日志

该日志对数据库实例的启动、运行和关闭都会进行记录，虽然说是错误日志，但也会记录一些警告或者一般的提示信息，比如启动信息和关闭信息,所以当数据库启动或者关闭出问题时首先要看这个日志文件，关于日志文件的位置可以通过查询mysql变量的方式来获得，如下：

show variables like '%log_error%'

结果如下：

log_error参数表示日志文件的位置，该位置默认情况下是不允许普通用户访问的，只能使用su用户访问，一般来说在该错误日志的名称默认情况下是主机名.err，但是如果在配置文件中指定了名称则会使用用户定义的，如下已经在配置文件中定义了：

日志中的内容如下：

2. 一般查询日志

一般查询日志中的记录是最全面的，记录了所有对数据库的请求，不管这些请求是否成功执行。默认情况下，该功能是不开启的，因为它需要记录所有的操作，会影响数据库的性能，所以不建议在生产环境中使用，但是在开发的过程中是十分有用的。
有两个属性是和一般查询日志有关的：

• general_log：用于标识是否开启一般查询日志；
• general_log_file：用于标识一般查询日志文件的位置，默认情况下该文件还没有生成，默认文件名是主机名.log；
• log_output:用于标识日志信息输出到什么地方，值得注意的是，该属性不只针对于general_log还会针对下面的要说的slow_log,所以选择的时候需要考虑周全；
  
  • FILE:默认值，输出到文件中；
  • TABLE:输出到mysql.general_log数据表中；
  • NONE:日志不输出，虽然设置设能一般查询日志，但是也不会起作用；

属性默认如下：

开启该功能指令如下：

set @@global.general_log = on

这种设置方法只能在当前数据库运行时起作用，如果数据库服务器重启，则仍然会去读取配置文件，此处的设置会被忽略，所以如果需要永久设置启动慢查询日志。则需要在配置文件中进行设置（但是不建议这么做）。

此时可以看见在lmy-PC.log文件中已经有记录的日志了，如下：

另外在MySQL 5.1版本之后查询日志的记录可以放到mysql.general_log表中，该表的结构如下：

修改log_output属性为table，指令如下：

set @@global.log_output = 'table';

查询mysql.general_log表如下：

3. 慢查询日志

什么叫慢查询？这个定义是因情况和场景而异的，但是可以通过定义一个属性标识查询时间，如果超过该时间就是慢查询，在MySQL中存在这样一个属性，为long_query_time，该属性就是充当这个阈值的作用，默认是10,代表10s。
默认情况下，慢查询日志是不会启动的，需要我们自己去设置，对应的属性如下：

set @@global.slow_query_log = on;

还有一个属性用来表明慢查询日志存储在什么地方，通过slow_query_log_file来设置，显示如下：

默认情况下，该文件是主机名-slow.log，并创建在数据库文件的目录中。

开启慢查询日志后，将long_query_time设置为2s，超过2s则认为是慢查询。发现一个问题，虽然我们更改了long_query_time参数，但是去查询的时候发现还是10s,如下：

此处的解决办法是重开一个会话，才会看到已经修改的long_query_time参数。

如果不想重开一个会话，也可以使用show global variables like 'log_query_time'查询修改的参数

测试慢查询如下：
使用select sleep(5)来测试是否将该条记录写入慢查询日志，日志如下：

慢查询不只是记录超过阈值时间的操作语句，通过属性log_queries_not_using_indexs可以将没有使用索引的语句，也记录到慢查询日志中，该属性默认情况是没开启的，开启如下set @@global.log_queries_not_using_indexes = 1。

通过查询select host, user from mysql.user表来测试没有索引的查询，日志如下：

如果开启慢查询并且使能无索引查询记录则会记录很多的语句，会严重影响数据库的性能，所以有一个属性log_throttle_queries_not_using_indexes来表明每分钟允许记录到慢查询日志并且没有使用索引的SQL语句的次数，默认为0，表示没有限制。

如果在慢查询日志变得越来越大的时候，查看文件变得不方便的时候，可以使用mysqldumpslow命令来查看，如下：

更具体的使用可以参考mysqldumpslow — Summarize Slow Query Log Files

上面所说的查询日志全都输出到一个文件中，但是该日志也可以输出到一个数据表中，如下：

改变日志输出目的地为数据表,使用set @@global.log_output = 'table',该属性可以动态修改，然后测试select sleep(5)，返回如下：

4. 二进制日志

二进制日志记录所有针对数据库的更改操作，注意此处是更改操作，不涉及查询，所以对于select和show等操作是不会记录的，但是对于执行的修改操作没有影响数据库也会被记录下来，比如你要删除的记录数据库中不存在，虽然你删除成功执行，但是对数据库并没有造成影响，

二进制日志相对于其他日志有什么作用？

• 恢复：用于恢复数据库的数据；
• 复制：当使用MySQL构建集群或者搭建主从复制架构时会使用该功能完成服务器之间数据的复制；

默认情况下并没有开启二进制日志，需要手动启动，通过配置参数log-bin[=name]启动二进制日志，该参数是静态参数，也就是在整个服务运行期间，该参数是只读的不能修改，所以在配置文件中修改完配置需要重启服务才能生效，不指定name，默认情况下日志文件名为主机名，后缀名是二进制日志的序号，该文件保存在数据库目录中，与datadir相同，在开启二进制后还会产生一个与二进制文件名相同但是后缀名是index的文件，该文件是二进制的索引文件，其中存储的是二进制日志的序列号，修改如下：

之后重启服务就可以开启二进制日志。此时查看二进制日志内容共就不会是空了，如下：

并且对应的属性也变化了，如下：

对于影响二进制日志记录的行为的参数有如下常用的几种：

• max_binlog_size：指定单个二进制文件的最大值，如果超过该值则会生成新的文件，后缀名+1并更新到索引文件中，在该版本下默认是104857600B，也就是100MB；
• binlog_cache_size：当使用事务时，没有提交事务时，二进制日志是保存在缓存中的，等事务提交了才会写入磁盘中，而缓存的大小是由该属性决定的，默认大小32K，另外该属性是基于会话的，每个线程开启一个事务时，mysql服务都会为它分配32K大小的缓存，当使用的超过该数值则会将二进制文件写入临时文件；
  
  • 可以通过查看全局状态观察缓存使用情况：
    
  • Binlog_cache_use：记录使用缓存写入二进制日志的次数；
  • Binlog_cache_disk_use：记录使用临时文件写入二进制日志的次数；
• sync_binlog：该属性是用于标识将缓存数据写到磁盘上的方式，sync_binlog=[n]标识每写缓冲n次同步磁盘一次。默认情况下二进制日志不是在每次写的时候都会同步到磁盘，在该版本下默认值是0，是让文件系统自行决定什么时候来做同步，如果设置为1的话，即每写一次缓冲立刻同步到磁盘，这是同步写入的方式，会提高数据库服务的可用性，但是会给IO性能造成压力，所以设置这个值时需要进行权衡；
• binlog_format:记录二进制日志的格式，
  
  • 基于语句(statement):二进制文件中记录的是SQL语句；
  • 基于行(row):二进制日志记录的是表的行更改情况；
  • 基于混合(mixed):混合使用statement和row
  • 该参数是动态参数，可以在运行时进行修改，并可以在会话级别和全局级别上进行修改；
  • 当使用这种方式进行记录时，日志文件占用空间的大小会大大增加相对于基于语句的形式；
  • 该版本默认值是基于语句的形式；

二进制文件不能直接读取，如果想要查看二进制文件，可以使用

可以通过show binlog event来查询二进制日志，参考SHOW BINLOG EVENTS Syntax，如下：

也可以使用MySQL提供的工具mysqlbinlog，参考如下链接mysqlbinlog — Utility for Processing Binary Log Files

---

相关文章：

• Mysql 查询日志slow_launch_time和Long_query_time
• 为什么mysql的binlog-do-db选项是危险的
• SHOW BINLOG EVENTS
• mysql日志设置优化