MySQL(InnoDB剖析):11---文件之（日志文件：错误日志(error log)、慢查询日志(slow query log)、查询日志(query log)、二进制日志(bin log)）

• 日志文件记录了影响MySQL数据库的各种类型活动
• 常见的日志文件有：

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

一、错误日志

• 错误日志文件对MySQL的启动、运行、关闭过程进行了记录。遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息，也记录一些警告信息或正确的信息
• 用户可以通过下面命令来查看错误日志文件的位置：

show variables like 'log_error'\G;

• 当MySQL不能正常启动时，第一个必须查找的文件应该就是错误日志文件，该文件记录了错误信息。例如下面显示数据库不能启动的原因是找不到权限哭mysql，所以启动失败

• 例如用户可以直接在错误日志文件中得到优化的帮助，而不需要通过查看数据库状态来得知。例如，下面的错误文件中的信息高度用户需要增加InnoBD存储引擎的redo log

二、慢查询日志

• 慢查询日志的主要用途：慢查询日志可以帮助定位可能存在问题的SQL语句，从而进行SQL语句层面的优化

log_slow_queries参数

• 该参数控制着MySQL是否开启慢查询日志的功能
• 默认值为关闭

show variables like 'log_slow_queries'\G;

long_query_time参数

• 例如，可以在MySQL启动时设一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志中。DBS可以对日志文件进行检查，确认是否有SQL语句需要进行优化
• long_query_time参数就是这个阈值。默认值为10，代表10秒

show variables like 'long_query_time'\G;

• 备注：只有运行时间大于long_query_time的SQL语句才会被记录下来，小于或等于long_query_time的SQL语句不会被记录下来

log_queries_not_using_indexes参数

• 开启了该参数之后，如果运行的SQL语句没有使用索引，则MySQL数据库会将这条SQL语句记录到慢查询日志文件
• 该参数默认关闭

show variables like 'log_queries_not_using_indexes'\G;

log_throttle_queries_not_using_indexes参数

• 从MySQL 5.6开始新增了这个参数，用来表示每分钟允许记录到slow log的且未使用索引的SQL语句次数
• 该参数默认值为0，表示没有限制，意思为每分钟记录到slow log的且未使用索引的SQL语句次数不限

show variables like 'log_throttle_queries_not_using_indexes'\G;

• 在生产环境下，若没有使用索引，此类SQL语句会频繁地被记录到slow log，从而导致slow log文件的大小不断增加，故DBA可通过此参数进行配置

mysqldumpslow命令

• 该命令是为了慢查询日志文件进行设计的。例如如果慢查询日志文件的内容过多，不容易进行信息筛选，那么可以通过此命令进行信息筛选
• 例如下面以默认命令格式查看nh122-190-slow.log慢查询日志文件

mysqldumpslow nh122-190-slow.log

• 例如用户希望在david.log慢查询日志文件中得到执行时间最长的10条SQL语句，可以运行如下的命令：

mysqldump -s -al -n 10 david.log

slow_log表

• MySQL 5.1开始可以将慢查询日志记录放入一张表中，这使得用户的查询更加方便和直观
• 该参数为动态的，且是全局的
• 慢查询表在mysql架构下，名为slow_log。表的结构定义如下：

show create table mysql.slow_log\G;

• slow_log表的使用见下面的log_output参数

slow_log表的存储引擎更换

• 从上面我们可以看到slow_log表创建的时候默认使用的是CSV引擎，对大数据量下的查询效率可能不高
• 用户可以把slow_log表的存储引擎更换为MyISAM，并在start_time列上添加索引以进一步提高慢查询的效率
• 当然，将slow_log表的存储引擎更改为MyISAM后，还是会对数据库造成额外的开始。不过还在很多关于慢查询的参数都是动态的，可以方便地在线进行修改或设置
• 如果慢查询日志功能开启的情况下，修改slow_log表的存储引擎会显示失败

• 所以为了更改slow_log表的存储引擎，应该先关闭慢查询日志的功能再进行更改

log_output参数

• 该参数指定了慢查询输出的格式

• 默认为FILE(代表为输出到文件)
• 可以设置为TABLE，这样之后慢查询的内容就会被记录到mysql架构下的slow_log表了

• 例如下面修改慢查询日志的输出到表中，修改之后，我们就可以通过查看slow_log表来查看慢查询日志中的结果了

-- 显示慢查询输出的格式 show variables like 'log_output'\G; -- 设置慢查询日志输出为表格形式(到log_output) set global log_output='table';

• 因为慢查询日志的功能是关闭的，所以需要先打开慢查询日志的功能，然后再进行实验

-- 开启慢查询日志的功能 set global log_slow_queries=ON; -- 查看slow_log表中的内容 select * from mysql.slow_log\G; -- 休眠11秒，该语句会被记录到慢查询日志中 select sleep(11); -- 再次查看slow_log表格 select * from mysql.slow_log\G;

long_query_io参数、slow_query_type参数

• MySQL的慢查询日志通过运行时间来对SQL语句进行捕获，这是一个非常有用的优化技巧。但是当数据库的容量较小时，可能因为数据库刚建立，此时非常大的可能是数据全部被缓存在缓冲池中，SQL语句运行的时间可能都是非常短的，一般都是0.5秒
• InnoSQL版本加强了对于SQL语句的捕获方式。在原版MySQL的基础上在慢查询日志中增加了对于逻辑读取（logical reads）和物理读取（physical reads）的统计：

• 物理读取：是指从磁盘进行IO读取的次数
• 逻辑读取：包含所有的读取，不管是磁盘还是缓冲池

• 例如，下面的例子可以看到该子查询的逻辑读取次数是91584次，物理读取为19次。从逻辑读与物理读的比例来看，该SQL语句可进行优化

• 用户可以通过“long_query_io”参数将超过指定逻辑IO次数的SQL语句记录到慢查询日志中。该值的默认值为100，即表示对于逻辑读取次数大于100的SQL语句记录到慢查询日志中

-- 查看该参数的值 show variables like 'long_query_io'\G;

• 而为了兼容原MySQL数据库的运行方式，还添加了slow_query_type参数，用来表示启用慢查询的方式，可选值为：

• 0：表示不讲SQL语句记录到慢查询日志
• 1：表示根据运行的时间将SQL语句记录到慢查询日志
• 2：表示根据逻辑IO次数将SQL语句记录到慢查询日志
• 3：表示根据运行时间及逻辑IO次数将SQL语句记录到慢查询日志

三、查询日志

• 查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行
• 默认文件名：主机名.log
• 从MySQL 5.1开始，可以将查询日志的记录放入mysql架构下的general_log表，该表的使用方法和上面提到的slow_log表的使用方式一样（见上）
• 如查看一个查询日志，通过下图发现，查询日志甚至记录了对Access denied的请求，即对于未能正确执行的SQL语句

四、二进制日志

• 二进制日志记录了对MySQL数据库执行更改的所有操作，若操作本身没有导致数据库发生变化，该操作可能也会写入二进制文件。但是不包括select和show这类操作（因为这些操作对数据本身不会进行修改）
• 二进制日志的几种作用：

• 恢复（recovery）：某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制文件进行point-in-time的恢复
• 复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为slave或standby）与一台MySQL数据库（一般称为master或primary）进行实时同步
• 审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击

• binlog在复制与集群中的应用请参阅：

• ​​javascript:void(0)​​
• ​​javascript:void(0)​​

log-bin参数

• 该参数用来控制是否开启二进制日志，默认为关闭
• 如果想要开启二进制日志的功能，可以在MySQL的配置文件中指定如下的格式：

• “name”为二进制日志文件的名称
• 如果不提供name，那么数据库会使用默认的日志文件名（文件名为主机名，后缀名为二进制日志的序列号），且文件保存在数据库所在的目录（datadir下）

-- 启用/设置二进制日志文件(name可省略) log-bin=name;

• 例如，下面查看二进制日志文件：

• bin_log.00001即为二进制日志文件，使我们在配置文件中指定的名称，所以没有偶使用默认的文件名
• bin_log.index为二进制的索引文件，用来存储过往产生的二进制日志序号，通常情况下，不建议手动修改这个文件

• 二进制日志文件在默认情况下并没有启动，需要手动指定参数来启动。开启这个选项会对MySQL的性能造成影响，但是性能损失十分有限。根据MySQL官方手册中的测试指明，开启二进制日志会使性能下降1%。但考虑到可以使用复制和point-in-time的恢复，这些性能损失是可以被接受的

• 以下配置文件的参数影响着二进制日志记录的信息和行为：

• max_binlog_size
• binlog_cache_size
• sync_binlog
• binlog-do-db
• binlog-ignore-db
• log-slave-update
• binlog_format

max_binlog_size参数

• 该参数指定了单个二进制日志文件的最大值
• 如果超过该值，则产生新的二进制日志文件，后缀名+1，并记录到.index文件
• 从MySQL 5.0开始的默认值为1073741824，代表1G（在之前版本中的默认值为1.1G）

show variables like 'max_binlog_size'\G;

binlog_cache_size参数

• 当使用事务的表存储引擎（如InnoDB存储引擎）时，所有未提交的二进制日志会被记录到一个缓冲中去，等待事务提交时直接将缓冲中的二进制日志写入二进制日志文件，该缓冲的大小由该参数决定。默认值为32K

show variables like 'binlog_cache_size'\G;

• 此参数是基于会话的，因此该值的设置需要小心，不能设置过大。当一个事务的记录大于设定的binlog_cache_size时，MySQL会把缓冲中的日志写入一个临时文件中，因此该值又不能设置的太小
• 通过show global status可以查看binlog_cache_use和binlog_cache_disk_use的状态，判断当前binlog_cache_size的设置是否适合与当前会话，如果binlog_cache_disk_use次数过多，需要调整binlog_cache_size的值

• binlog_cache_use：记录了使用缓冲写二进制日志的次数
• binlog_cache_disk_use：记录了使用临时文件写二进制日志的次数

show global status like 'binlog_cache%';

sync_binlog参数

• 在默认的情况下，二进制日志并不是在每次写的时候同步到磁盘。因此，当数据库所在操作系统发生宕机时，可能会有最后一部分数据没有写入二进制日志文件，这会给恢复和复制带来问题
• 这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性
• 参数取值如下：

• 0（默认值）：当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘
• N：当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘

show variables like 'sync_binlog'\G;

innodb_support_xa参数

• 即使把sync_binlog设置为1，还是会有一种情况导致问题的发生：

• 当使用InnoDB存储引擎时，在一个事务发出commit动作之前，由于sync_binlog为1，因此会将二进制日志立即写入磁盘。
• 如果这时已经写入了二进制日志，但是提交还没有发出，并且此时发生了宕机，那么MySQL在下次启动时，由于commit还没有提交，这个事务会被回滚。但是二进制日志已经记录了该事务信息，不能被回滚

• 这个问题可以通过参数innodb_support_xa设为1来解决，虽然innodb_support_xa与事务有关，但它同时也确保了二进制日志和InnoDB存储引擎数据库文件的同步

show variables like 'innodb_support_xa'\G;

binlog-do-db参数、binlog-ignore-db参数

• 这两个参数分别表示需要写入或忽略写入哪些库的日志
• 默认为都为空：表示需要同步所有库的日志到二进制日志
• 如果需要设置多个数据库，可以将库名用逗号隔开，或者按照格式每个一行
• 例如：

​​# 需要同步liting数据库 binlog-do-db=liting # 不同步mysql系统数据库 binlog-ignore-db=mysql​​

log-slave-update参数

• 如果当前数据库是复制中的slave角色，则它不会将从master取得并执行的二进制日志写入自己的二进制日志文件中去
• 如果需要写入，需要设置log-slave-update
• 如果需要搭建master->slave->slave架构的复制，则必须设置该参数

binlog_format参数

• 该参数影响了记录二进制日志的格式。在MySQL 5.1版本之前没有这个参数，MySQL 5.1开始引入了这个参数
• 所有二进制文件的格式都是基于SQL语句级别的，因此基于这个格式的二进制日志文件的复制和Oracle的逻辑Standby有点相似
• 该参数对于复制有一定的影响

• 如在主服务器运行rand、uuid等函数，又或者使用触发器等操作，这些都可能导致主从服务器上表中数据的不一致
• 另一个影响是，会发现InnoDB的默认事务隔离级别是REPEATABLE READ。这其实也是因为二进制日志文件格式的关系，如果使用READ COMMITTED的事务隔离级别，会出现类似丢失更新的现象，从而出现主从数据库上的数据不一致

• 该参数设的值如下：

• STATEMENT，基于SQL语句的复制（statement-based replication, SBR）：

• STATEMENT格式和之前的MySQL版本一样，二进制日志文件记录的是日志的逻辑SQL语句
• 也就是说，在主服务器上执行的SQL语句，在从服务器上执行同样的SQL语句

• ROW，基于行的复制（row-based replication，RBR）：

• 二进制日志记录的不再是简单的SQL语句了，而是把改变的内容复制过去，不是把SQL语句在从服务器上执行一遍，记录表的行更改情况（下面有演示案例）
• 基于ROW格式的复制类似于Oracle的物理Standby（当然，还是有些区别）。同时，对上述体积的Statement格式下复制的问题予以解决
• 从MySQL 5.1版本开始，如果设置了binlog_format为ROW，可以将InnoDB的事务隔离基本设为READ COMMITED，以获得更好的并发性

• MIXED，混合类型的复制（mixed-based replication，MBR）：

• 以上两种模式的混合使用，默认采用基于SQL语句的复制，一旦发现基于语句的无法精确复制时，就会采用基于行的复制
• 在MIXED格式下，MySQL默认采用STATEMENT格式进行二进制日志文件的记录，但是在一些情况下会使用ROW格式，可能的情况有

• 表的存储引擎为NDB，这时对表的DML操作都会以ROW格式记录
• 使用了UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数
• 使用了INSERT DELAY语句
• 使用了用户定义函数（UDF）
• 使用了临时表

• binlog_format参数还有对于存储引擎的限制，如下表所示：

• binlog_format参数是动态的，因此可以在运行时进行修改

• 演示案例：

• 下面图1：当前会话的binlog_format格式为STATEMENT，当我们更改数据后，二进制日志大小只增加了200字节（306-106）
• 下面图2：我们把当前会话的binlog_format设置为ROW格式，同时对t2表进行了更新操作，可以看到在ROW格式下竟然需要13782094字节，二进制日志大小差不多增加了13MB，而且执行时间也有所增加（这里设置了sync_binlog=1）。因为这时MySQL不再将逻辑的SQL操作记录到二进制日志中，而是记录对于每行的更改
• 因此，将参数binlog_format设置为ROW后，会对磁盘空间要求有一定的增加，由于复制时采用传输二进制日志方式实现的，因此复制的网络开销也会有所增加

mysqlbinlog工具

• 二进制日志文件采用的是二进制方式存储，因此不容易进行查看。MySQL提供了mysqlbinlog工具用来对二进制日志文件进行查看
• 对STATEMENT格式的二进制日志文件查看，会显示出执行的逻辑SQL语句，如下所示（下图为裁剪过的）：

• 在这种情况下，mysqlbinlog与Oracle LogMiner类似

• 但是如果对ROW格式的记录进行查看，会使得结果变为“非常难读”，如下所示：

• 因此为了方便查看ROW格式的，需要加上参数-v或-vbv即可看到执行的具体信息（-vv会比-v多显示处更新的类型），如下所示：

• 通过查看我们知道依据简单的update操作记录了对于整个行更改的信息，这也解释了上面的演示案例为什么更新了10W行的数据，在ROW格式下，二进制日志文件会增大13MB