Mysql总结系列 一 日志

我们都知道mysql更新数据的时候都是先更新日志，再写入数据，包括他的事物，主从复制，数据一致性都与日志有关，当然他的日志也包含了一般日志具备的功能，所以总结mysql首先从日志开始。

首先，说明mysql的主要的主要日志分类，包括重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog），以上三种日志均为辅助数据记录的日志；错误日志（errorlog）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log）以上日志主要为了数据库问题排查。

重做日志（redolog）

redolog的作用主要是保证即使数据库挂掉已经提交的事物可以准确的刷新到数据库中。

我们都知道mysql对数据的处理是先将数据读入到缓存中，随后在根据特定策略flash到磁盘(主要为了保证写入速度，现将数据写入缓存池，随后刷新)。但如果一个事务在提交之后未flash到磁盘之前mysql挂掉，如何保证数据正确写入呢？此时就需要用到redolog了

 

redolog会在事务开始时就产生，使用物理记录的方式，如在‘xxx’页的偏移'xxx'位置写入'xxx'. 准确记录数据的变化。为保证写入log的速度，一般是先将数据写入到redo log buffer，在redolog buffer中，日志按照块来存储，使用特定策略向磁盘中刷新，刷新时机可以分为以下几个时间：

1. 事物提交时把它对应的那些redo log写入到磁盘中去（这个动作可由相关参数控制，下文会说）
2. 当redo log buffer 使用量达到了参数innndb_log_buffer_size的一半时，会触发落盘。
3. 会有一个后台线程，每隔1秒就会将redo log block刷新到磁盘文件中去。
4. MySQL关闭时也会将其落盘。

具体的刷新策略可以通过配置【innodb_flush_log_at_trx_commit = 0/1/2】：

     1 ： 默认是1，表示事务提交时必须调用一次刷新操作，将redolog buffer中的数据刷新到磁盘。遵循ACID的持久性。

     0 ： commit不会写入日志，写的操作仅在master Thread中完，大概每秒写一次日志，并刷新到磁盘.在1秒内有数据库宕机丢数据的风险.

     2 ： commit时只将日志刷新到OS Cache中，根据系统的日志刷新策略将数据刷新到磁盘，也存在数据库宕机丢数据的风险。

注意：0和2能提高事务提交性能，但是这种情况丧失了事务的ACID特性.

redolog的日志存储位置也可以通过自己控制，一般位于数据库的data目录下ib_logfile1&ib_logfile2

• innodb_log_group_home_dir 指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下。
• innodb_log_files_in_group   指定redolog file 组中文件的数量，默认2
• 关于文件的大小和数量，由一下两个参数配置
• innodb_log_file_size     redolog file 的大小。
• innodb_mirrored_log_groups 指定了日志镜像文件组的数量，默认1

redolog文件的书写采用追加策略，在redolog文件写满之后会覆盖最原始的日志，因此redolog的日志文件需要足够大，一般设置为可以存储峰值情况下一个小时的事务数据。

个人理解：

为什么mysql要通过日志形式把数据记录下来写入磁盘不是一次性写入数据呢？

有以下几个原因： 1、redo并不是一条条直接写入磁盘中去的，redolog是按块，一块一块的写入到磁盘中去的，在写入数据时也不用查找位置，因此速度比直接写入数据快很多。2、在进行大事务的数据处理时一次性大量写入数据一定会影响性能，而redolog则不需要有这种考虑，刷新到磁盘的数据不一定已经提交，因此一个事务的数据可以分多次写入。

但此时出现一个问题，就是redolog是如何区分数据所属事务是否已经提交的呢，换句话说如果一个事务的部分操作已经写入到redolog，但当前事务没有被提交怎么办？

此时则需要涉及到下面要讲的undolog，事务未提交时执行了undolog，此时数据又被修改过来，则不会对真正数据有影响。

回滚日志（undo log）

undolog主要用于记录事务的回滚和MVCC（MVCC本系列后续会介绍）。

undo log 是逻辑日志，在事务回滚时对数据库进行一些补偿性的修改，以使数据在逻辑上回到修改前的样子，它并不幂等。可以认为执行了一条insert指令则生成一条对应的delete指令。当执行回滚操作或版本控制，通过执行相应指令获取到对应数据。

InnoDB对undolog的管理方式为按段（segment）管理，在以前老版本，只支持1个rollback segment，这样就只能记录1024个undo log segment。后来MySQL5.5可以支持128个rollback segment，即支持128*1024个undo操作，还可以通过变量 innodb_undo_logs (5.6版本以前该变量是 innodb_rollback_segments )自定义多少个rollback segment，默认值为128。

当事务提交的时候，InnoDB不会立即删除undo log，因为后续还可能会用到undo log，如隔离级别为repeatable read时，事务读取的都是开启事务时的最新提交行版本。但是在这些事务提交的时候，会将该事务对应的undo log放入到删除列表中，未来通过purge来删除。并且提交事务时，还会判断undo log分配的页是否可以重用，如果可以重用，则会分配给后面来的事务，避免为每个独立的事务分配独立的undo log页而浪费存储空间和性能。

通过undo log记录delete和update操作的结果发现：(insert操作无需分析，就是插入行而已)

• delete操作实际上不会直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的。
• update分为两种情况：update的列是否是主键列。
  • 如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。
  • 如果是主键列，update分两部执行：先删除该行，再插入一行目标行。

二进制日志（binlog）

binlog主要用于保存数据，可以用于主从复制，特定时间点的数据恢复

binlog主要是记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的，对支持事务的引擎如InnoDB而言，必须要提交了事务才会记录binlog。binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关。

• 如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新；
• 如果设置为不为0的值，则表示每 sync_binlog 次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。
• 设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响。

很明显可以得出结论，在sync_binlog设置为0或大于1的数值时可能存在丢失数据的问题。

当前的binlog一共有三种模式：

• STATEMENT：基于SQL语句的复制（statement-based replication, SBR），会精准的记录所有的引起数据变更的sql，占用空间小，但随着mysql功能越来越多，为保证每条mysql可以正确的被执行，还需要记录sql的上下文信息。但仍然可能存在部分语句无法执行的情况。
• ROW：基于行的复制（row-based replication, RBR）记录每一行数据的变化，但明显数据量会比较大。5.1.5 版本开始支持。
• MIXED：混合模式复制（mixed-based replication, MBR）会混合的存储数据变更sql和变更数据。并不是所有的修改都会以 row 模式来记录，比如遇到表结构变更的时候就会以 statement 模式来记录，如果 SQL 语句确实就是 update 或者 delete 等修改数据的语句，那么还是会记录所有行的变更。

binlog文件包括两类，

• 二进制日志索引文件（文件名后缀为.index）用于记录所有有效的的二进制文件
• 二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML语句事件

可以通过 max_binlog_size 配置每个日志文件的大小，在执行flush logs命令、master宕机和超过max_binlog_size设置的大小时会重新生成一个新的日志文件。

binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

错误日志（errorlog）

会准确记录mysql在启动和停止，以及在运行过程中发生的错误的相关信息。在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出。 
　　指定日志路径两种方法:
　　　　编辑my.cnf 写入 log-error=[path] 
　　　　通过命令参数错误日志 mysqld_safe –user=mysql –log-error=[path] &

慢查询日志（slow query log）

是应用较为频繁的日志，可以记录下每条执行时间超过 long_query_time 配置时间和没有使用索引的执行成功的查询语句。

以下是几个重要的查询指令

查看慢查询时间： 
　　show variables like “long_query_time”;默认10s

查看慢查询日志路径： 
　　show variables like “%slow%”;

一般查询日志（general log）

记录了服务器接收到的每一个查询或是命令，无论这些查询或是命令是否正确甚至是否包含语法错误，general log 都会将其记录下来 ，记录的格式为 {Time ，Id ，Command，Argument }。也正因为mysql服务器需要不断地记录日志，开启General log会产生不小的系统开销。 因此，Mysql默认是把General log关闭的。

中继日志（relay log）

relay log日志文件具有与bin log日志文件相同的格式，从上边MySQL主从复制的流程可以看出，relay log起到一个中转的作用，slave先从主库master读取二进制日志数据，写入从库本地，后续再异步由SQL线程读取解析relay log为对应的SQL命令执行。