数据库日志整理

Database : log, redolog, undolog, commandlog

幂等性

数据库日志文件中的操作记录应该具有幂等性，同一个操作执行多次，结果是一样的。因为日志在故障恢复过程中，可能会被回放多次。

重做日志/事务日志(Redo日志)

作用

它记录了InnoDB存储引擎的事务日志，用于数据库故障的恢复，以保证数据的完整性。

在事务Commit的时候一定会写入。系统一旦写入redo日志，则可以确认数据已经提交，之后，数据库才会选择在合适的时间更新磁盘数据记录。

redo log是指在回放日志的时候把已经commit的事务重做一遍，对于没有commit的事务按照abort处理。日志回放并不会处理任何没有commit的事务，

因此，在COMMIT日志持久化之前，不能将数据的修改持久化。因为如果数据在COMMIT之前持久化，那么在系统异常退出的情况下，这种部分修改的事务就会处于一种不一致状态。

同时，由于重做事务，因此事务日志中必须记录事务修改以后的值。redo log必须满足以下规则：

R1:在修改磁盘上的任何数据库元素X以前，要保证与X的这一修改相关的所有的日志记录，包括更新记录

内容

事务T开始

事务T提交，事务commit之后，日志管理器会执行flush命令将REDO日志强写回磁盘。

事务T终止

Redo log文件是循环写入的，在覆盖写之前，总是要保证对应的脏页已经刷到了磁盘。在非常大的负载下，Redo log可能产生的速度非常快，导致频繁的刷脏操作，进而导致性能下降，通常在未做checkpoint的日志超过文件总大小的76%之后，InnoDB 认为这可能是个不安全的点，会强制的preflush脏页，导致大量用户线程stall住。如果可预期会有这样的场景，我们建议调大redo log文件的大小。可以做一次干净的shutdown，然后修改Redo log配置，重启实例。

写入时机

*> 在页面修改完成之后，在脏页刷入磁盘之前，写入REDO日志

*> 日志先行，日志一定比数据页先写回磁盘。

*> 聚簇索引、二级索引、undo页面修改，均需要记录REDO日志。

删除时机

不删除，会循环写日志，最后一个redo日志写满，则回溯到第一个。

有几种场景可能会触发redo log写文件：

1). Redo log buffer空间不足时

2). 事务提交

3). 后台线程

4). 做checkpoint

5). 实例shutdown时

6). binlog切换时

存储位置

回滚日志(Undo日志)

作用

undo log就是把所有没有COMMIT的事务回滚到事务开始之前的状态(撤销事务在系统崩溃前可能还没有完成的影响来恢复数据库状态)，对于已经commit的事务不做任何处理。

undo日志必须满足如下两个规则：

U1:如果事务T改变了数据库元素X，那么形如

内容

生成时机

DML操作修改聚簇索引前，记录UNDO日志

二级索引记录的修改，不记录UNDO日志

注意：undo页面的修改，同样需要记录REDO日志。

删除时机

存储位置

Undo日志和Redo日志的区别

Undo日志：

在恢复时取消未完成事务，忽略已提交事务

先将修改后的数据写到磁盘。 写到磁盘

遵循Undo日志的U1和U2规则，恢复时需要的是数据库的旧值。

Redo日志：

忽略未完成事务，重做已经提价事务的改变。

先写到磁盘，然后数据进入缓冲区，选择合适时间在写入磁盘。

恢复时需要的是新值。

UNDO/REDO LOG（UNDO和REDO和合体）

单独使用的Undo/Redo日志的缺陷

Undo 日志要求数据在事务结束后立即写到磁盘，可能增加磁盘I/O数

Redo日志要求我们在事务提交和日志记录刷新以前将所有修改过的块保留在缓冲区中，可能增加事务需要的平均缓冲区数

如果数据库元素不是完整的块或块块集，在检测点处理过程中，Undo日志和Redo日志在如何处理缓冲区上存在矛盾。

而undo/redo log可以很好的解决这些问题。undo/redo log是指在日志回放的时候像undo log那样回滚所有没有commit的事务；

redo log一样redo所有已经commit的事务。由于同时要进行redo和undo，因此日志记录中必须同时记录修改前的值和修改后的值。

二进制日志(Binlog)

作用

记录了对数据库执行更改的所有操作，但是不包壳Select和Show这些对数据本身没有修改的操作。

如果一个操作本身并没有导致数据库变化，它也有肯能会被写入binglog。比如update的条件不存在。

数据恢复：某些数据的恢复需要二进制日志。用户可以通过二进制日志进行point-in-time的恢复。

集群复制：

主备和mysql集群都是通过复制binlog文件来进行数据实时同步的。

审计：

用户对二进制日志中的信息进行审计，判断是否对数据库进行了注入攻击。

内容

Mysql可以通过如下语句来查看到SQL执行状态。

mysql> show master status;
+------------------+-----------+--------------+--------------------------+-----------------------------------------------------------------------------------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set                                                                             |
+------------------+-----------+--------------+--------------------------+-----------------------------------------------------------------------------------------------+
| mysql-bin.000446 | 986824149 |              | mysql,information_schema | 59bd0562-cb49-11e7-85ca-286ed488cdb2:1-113908026,
c22aab64-cb49-11e7-85cd-286ed488cb3a:1-7711 |
+------------------+-----------+--------------+--------------------------+-----------------------------------------------------------------------------------------------+
1 row in set ( sec)
mysql> show binlgo events in 'mysql-bin.000446';
查看binlog。
           

Mysql的binlog支持三种记录格式：STATEMENT, ROW, MIXED

STATEMENT: 记录的是逻辑SQL语句

ROW: 记录表的行更改情况。该模式下，innoDB的事务隔离基本支持READ COMMITTED，这样可以获得更好的并发性能。

MIXED：混合模式，Mysql默认会采用statement进行日志记录，但是某些情况下会使用ROW格式，比如使用了uuid，user，current_user等不确定函数，用户自定义UDF函数，临时表，INSERT DELAY等语句场景。

生成时机

删除时机

存储位置

Redo和binlog的对比

1、binlog记录所有与数据库有关的日志记录，包含各种引擎的日志。InnoDB存储引擎的重做日志，只记录有关该存储引擎本身的事务日志。

2、binlog记录的是一个事务的具体操作内容，即逻辑日志。InnoDB的重做日志，记录每个页更改的物理情况。

3、写入时间不同，binlog在事务提交之前进行提交，只写磁盘一次，不论事务多大。在事务执行过程中，重做日志会不断写入。

重做日志先写入缓冲区，再写入磁盘，写入磁盘的时候，是按照512个字节，也就是一个扇区大小进行写入的，扇区是写入的最小单位，因此可以保证写入必定是成功的。因为重做日志的写入过程不需要write。

生成时机

Mysql InnoDB默认有两个文件，先写文件 1，到达文件的最后时，会切换到文件2，当文件2写满的时候，再切回1.

一些配置参数：

innodb_log_file_size: 每个重做日志文件大小，目前最大为512GB。

innodb_log_files_in_group: 重做日志文件的数量，默认为2

innodb_mirrored_log_groups: 日志镜像文件组的数量，默认1，表示只有一个日志文件组，没有镜像。如果使用了磁盘阵列或者其他高可用方案，可以不卡空气重做日志镜像的功能。

innodb_log_group_home_dir: 日志文件路径，默认在数据库的数据目录。

重做日志文件不能设置的太大，太大会导致恢复速度很慢。设置的太小，又会发生文件频繁切换，导致性能抖动。

错误日志(errorLog)

作用

记录Mysql启动，运行过程中的错误信息，其包含所有的错误信息，以及一些警告信息或者正确信息。

强制开启，无法被禁止

内容

[[email protected] data]#  tail -20 stu18.magedu.com.err
  ::  InnoDB: Starting shutdown...
  ::  InnoDB: Shutdown completed;  log sequence number 
 ::  [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
 ::  mysqld_safe mysqld from pid file /mydata/data/stu18.magedu.com.pid ended
 ::  mysqld_safe Starting mysqld daemon with databases from /mydata/data
 ::  InnoDB: The InnoDB memory heap is disabled     #禁用了InnoDB memory的堆功能。
 ::  InnoDB: Mutexes and rw_locks use GCC atomic builtins #Mutexes（互斥量）和rw_locks（行级锁）是GCC编译的是InnoDB内置的。
 ::  InnoDB: Compressed tables use zlib      #默认压缩工具是zlib
 ::  InnoDB: Initializing buffer pool, size = M    #InnoDB引擎的缓冲池（buffer pool）的值大小
 ::  InnoDB: Completed initialization of buffer pool
 ::  InnoDB: highest supported file format is Barracuda.
  ::  InnoDB: Waiting for the  background threads to start
 ::  InnoDB:  started; log sequence number 
 ::  [Note] Server hostname (bind-address): ''; port: 
 ::  [Note]   - '' resolves to  '';  #0.0.0.0会反解主机名，这里反解失败
 ::  [Note] Server socket created on IP: ''.
 ::  [Note] Event Scheduler: Loaded  events    #事件调度器没有任何事件，因为没有装载。
 ::  [Note] /usr/local/mysql/bin/mysqld: ready for connections. #mysql启动完成等待客户端的请求。
Version:  '-log'  socket:  '/tmp/mysql.sock'  port:   Source distribution  #创建一个本地sock用于本地连接。
           

生成时机

Mysql启动，运行，关闭过程中，发生错误的时候。

删除时机

只能重命名原来错误日志文件，抽共冲洗日志创建一个新的日志文件。

存储位置

mysql数据目录，${hostname}.err

慢查询日志(slow query log)

作用

记录可能存在问题的SQL语句，从而对该SQL语句进行优化。

可以在Mysql启动时设定一个阈值，将运行时间超过该值得所有SQL语句都记录在该文件中。

Mysql该日志默认情况下是关闭的。

如果打开了log_queries_not_using_iindexes，则会将没有使用索引的SQL语句也记录在慢查询日志中。

还可以通过long_query_io将超过指定逻辑IO次数的SQL记录到慢查询日志中，默认值为100.

内容

可以直接查看该文件内容

可以通过mysqldumpslow命令来查看

Mysql 5.1开始，可以将慢查询日志放入一张表中，名称为slow_log

生成时机

配置参数打开之后，如果查询时间大于等于阈值，则记录，Mysql阈值默认时间10秒

删除时机

Mysql有一个log_throttle_queries_not_using_indexes，表示每分钟允许记录到慢查询日志中且未使用索引的SQL语句次数，默认值为0，表示没有限制。该参数能够避免慢查询日志不断增加。

存储位置

一般查询日志(general log)

作用

记录了所有对数据库请求组的信息，不论这些请求是否需已经得到了正确的执行。对于未能正确执行的语句，也会记录下来。

默认关闭，在高并发场景下回产生大量信息从而导致不必要的磁盘IO，会影响Mysql性能

内容

生成时机

删除时机

存储位置

中继日志(relay log)

作用

从高层来看，复制分成三步：

(1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；

(2) slave将master的binary log events拷贝到它的中继日志(relay log)；

(3) slave重做中继日志中的事件，将改变反映它自己的数据。

内容

中继日志, 像binary log,有一组文件组成包含events 描述数据库的修改,和一个index文件包含所有使用过的relay log文件的名字

生成时机

数据复制过程中产生

删除时机

存储位置

WAL

事务提交的时候，先写重做日志，再修改页。当由于宕机二导致数据丢失时，通过重做日志来完成数据的恢复。

WAL的中心概念是数据文件（存储着表和索引）的修改必须在这些动作被日志记录之后才被写入，即在描述这些改变的日志记录被刷到持久存储以后。如果我们遵循这种过程，我们不需要在每个事务提交时刷写数据页面到磁盘，因为我们知道在发生崩溃时可以使用日志来恢复数据库：任何还没有被应用到数据页面的改变可以根据其日志记录重做（这是前滚恢复，也被称为REDO）

在使用WAL的系统中，所有的修改都先被写入到日志中，然后再被应用到系统状态中。通常包含redo和undo两部分信息。

CheckPoint

解决如下问题：

1、缩短数据库恢复时间，

2、缓冲池不够用时，将脏页刷新到磁盘。

3、重做日志不可用时，刷新脏页。

当数据库宕机的时候，数据库不需要重做所有的日志，因为checkpoint之前的也都已经刷新会磁盘，所以数据库只需要对checkpoint后的重做日志进行恢复，这样就大大缩短的恢复的时间。

innodb内部有俩还各种checkpoint：

1、sharp checkpint

数据库关闭时，将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数innodb_fast_shutdown=1

2、fuzzy checkpint

刷新一部分脏页到磁盘，而不是刷新所有脏页。

生成checkpoint的过程为：

1）、 记录START_CKPT

Double Write

doublewrite应用场景：

我们知道，innodb的数据页一般大小是16KB，MySQL存取数据的最小单位也是页，而操作系统并不能保障一个数据页的原子性，也就是说当写入数据时，有可能在一个页中写入一半时（比如8K）数据库宕机，这种情况称为部分写失效（partial page write），从而导致数据丢失。

大家也许会问，难道我不可以根据redo log进行数据恢复吗？答案是肯定的也是否定的，要分为两种情况：1、数据库宕机，物理文件完好无损，是可以通过redo log进行崩溃恢复。2、数据库宕机，正在刷新到磁盘的页发生partial page write，而正好在磁盘上的这个数据页由于宕机发生损坏，这时就无法通过redo log进行数据恢复了，为什么？我们必须要清楚的认识到，redo log里记录的是对页的物理操作！比如一条redo记录”page number xx，偏移量 800 写记录 “this is abc””，那当页损坏时，这条redo记录还有意义吗？于是在这种特殊情况下，doublewrite就派上用场啦！

实现机制

解决大数据块写入的时候，写入一半故障，数据不完整的问题。

doublewrite由两部分组成，一部分为内存中的doublewrite buffer，其大小为2MB，另一部分是磁盘上共享表空间（ibdata x）中连续的128个页，即2个区（extent），大小也是2M。doublewrite工作流程如下：

1、当一系列机制（main函数触发、checkpoint等）触发数据缓冲池中的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的doublewrite buffer,之后通过doublewrite buffer再分两次、每次1MB顺序写入共享表空间的物理磁盘上。

2、马上调用fsync函数，同步脏页进磁盘. 由于在这个过程中，doublewrite页的存储时连续的，因此写入磁盘为顺序写，性能很高；完成doublewrite后，再将脏页写入实际的各个表空间文件，这时写入就是离散的了。

相当于数据记录会写入两个文件中，一个是共享表空间的douuble wrirte中，另外一个是数据文件中，所以叫double write。

如果操作系统在将页写入磁盘的过程中发生崩溃，如上图，在恢复过程中，innodb存储引擎可以从共享表空间的doublewrite中找到该页的一个最近的副本，将其复制到表空间文件，再应用redo log，就完成了恢复过程。因为有副本所以也不担心表空间中数据页是否损坏。

redo日志中一般记录对数据页的物理修改，比如偏移量800，，写入aaa记录，如果这个页已经损坏，则写入是无意义的。

double write文件对应物理磁盘上ibdata系统表中间中的独立文件，每个大小2MB，共128个连续page。做两个分区，其中120个用于批量脏写，另外9个用于Single page flush。做区分的原因是批量刷脏是后台线程做的，不影响前台线程。而Single page flush是用户线程发起的，需要尽快的刷脏并替换出一个空闲页出来。

Oracle 闪回特性实现

Oracle的闪回技术提供了一组功能，可以访问过去某一时间的数据并从人为错误中恢复。闪回技术是Oracle 数据库独有的，支持任何级别的恢复，包括行、事务、表和数据库范围。使用闪回特性，您可以查询以前的数据版本，还可以执行更改分析和自助式修复，以便在保持数据库联机的同时从逻辑损坏中恢复。

闪回技术包括以下特性：

(1) 闪回查询允许用户查询过去某个时间点的数据，以重新构建由于意外删除或更改而丢失的数据。

(2) 闪回版本查询提供了一种查看行级数据库随时间变化的方法。

(3) 闪回事务查询提供了一种查看事务级数据库变化的方法。

(4) 闪回数据库是进行时间点恢复的新策略。它能够快速将Oracle 数据库恢复到以前的时间，正确更正由于逻辑数据损坏或用户错误而引起的任何问题。

(5) 闪回表功能使 DBA 可以非常快速、轻松地将一个表或一组表恢复至过去特定的某一时间点。

(6) 闪回删除在删除对象时提供了一个安全网，您可以非常快速、轻松地取消对一个表及其相关对象对象的删除。

和其他文件对比

引用

http://blog.51cto.com/pangge/1319304

https://blog.csdn.net/ggxxkkll/article/details/7616739

https://www.cnblogs.com/geaozhang/p/7241744.html

http://www.innomysql.com/innodb-doublewrite%E4%B8%8E%E9%87%8D%E5%81%9A%E6%97%A5%E5%BF%97%E7%9A%84%E5%85%B3%E7%B3%BB/

http://www.ywnds.com/?p=8334

http://blog.itpub.net/29018063/viewspace-2086098/

https://www.cnblogs.com/xinysu/p/6555082.html

https://www.jianshu.com/p/46515825366c

https://www.cnblogs.com/chenpingzhao/p/5003881.html

http://mysql.taobao.org/monthly/2015/05/01/

http://www.ztloo.com/2017/08/16/mysq-%E3%80%8Binnodb-%E6%97%A5%E5%BF%97%E5%9B%9E%E6%BB%9A%E6%AE%B5%E5%B4%A9%E6%BA%83%E6%81%A2%E5%A4%8D%E5%AE%9E%E7%8E%B0%E8%AF%A6%E8%A7%A3/

https://www.devbean.net/2016/05/how-database-works-8/

《MySQL技术内幕：InnoDB存储引擎（第2版）》