mysql 系列：innodb 日志管理，带你高效快速理解
1、恢复：用户可以通过二进制文件的 point-in-time 进行恢复 2、复制：通过执行二进制的文件在远程的机器上恢复数据 3、审计：可以对二进制日志文件进行审计，判断是否有对数据库进行注入攻击
1.5.2、查看二进制文
1.使用 mysqlbinlog 工具。2.使用 show 显示对应的信息。SHOW {BINARY | MASTER} LOGS # 查看使用了哪些日志文件 SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] # 查看日志中进行了哪些操作 SHOW MASTER STATUS # 显示主服务器中的二进制日志信息
mysqlbinlog 使用格式：
mysqlbinlog [option] log-file1 log-file2...
常用选项：
-d,--database=name：只查看指定数据库的日志操作-o,--offset=#：忽略掉日志中的前 n 个操作命令-r,--result-file=name：将输出的日志信息输出到指定的文件中，使用重定向也一样可以。-s,--short-form：显示简单格式的日志，只记录一些普通的语句，会省略掉一些额外的信息如位置信息和时间信息以及基于行的日志。可以用来调试，生产环境千万不可使用--set-charset=char_name：在输出日志信息到文件中时，在文件第一行加上 set names char_name--start-datetime,--stop-datetime：指定输出开始时间和结束时间内的所有日志信息--start-position=#,--stop-position=#：指定输出开始位置和结束位置内的所有日志信息-v,-vv：显示更详细信息，基于 row 的日志默认不会显示出来，此时使用-v 或-vv 可以查看
使用 mysqlbinlog 命令对 mysql-bin.000001 日志进行查看，如下：
[root@lgh3 mysql]# mysqlbinlog /data/binlog/mysql-bin.000001/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;DELIMITER /!/;
at 4
#190908 21:18:32 server id 1000 end_log_pos 123 CRC32 0x052f8ffd Start: binlog v 4, server v 5.7.24-log created 190908 21:18:32 at startupROLLBACK/!/;BINLOG 'aKh1XQ/oAwAAdwAAAHsAAAAAAAQANS43LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABoqHVdEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQAAf2PLwU='/!/;
at 123
#190908 21:18:32 server id 1000 end_log_pos 154 CRC32 0x927edf95 Previous-GTIDs
[empty]
at 154
#190908 21:19:15 server id 1000 end_log_pos 219 CRC32 0xb05f419c Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=noSET @@SESSION.GTID_NEXT= 'ANONYMOUS'/!/;
at 219
#190908 21:19:15 server id 1000 end_log_pos 313 CRC32 0xaafce462 Query thread_id=2 exec_time=0 error_code=0SET TIMESTAMP=1567991955/!/;SET @@session.pseudo_thread_id=2/!/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/!/;SET @@session.sql_mode=1075838976/!/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/!/;/!\C utf8mb4 //!/;SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/!/;SET @@session.lc_time_names=0/!/;SET @@session.collation_database=DEFAULT/!/;create database test/!/;
at 313
#190908 21:19:53 server id 1000 end_log_pos 378 CRC32 0xdccfd8ec Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=noSET @@SESSION.GTID_NEXT= 'ANONYMOUS'/!/;
at 378
#190908 21:19:53 server id 1000 end_log_pos 497 CRC32 0x57a45727 Query thread_id=2 exec_time=0 error_code=0use test/!/;SET TIMESTAMP=1567991993/!/;create table gxt1(id int , name varchar(128))/!/;
at 497
#190908 21:20:17 server id 1000 end_log_pos 562 CRC32 0x316b41e9 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes/!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED//!/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/!/;
at 562
#190908 21:20:17 server id 1000 end_log_pos 634 CRC32 0x693ee549 Query thread_id=2 exec_time=0 error_code=0SET TIMESTAMP=1567992017/!/;BEGIN/!/;
at 634
#190908 21:20:17 server id 1000 end_log_pos 684 CRC32 0xde906285 Table_map: test.gxt1 mapped to number 108
at 684
#190908 21:20:17 server id 1000 end_log_pos 734 CRC32 0xa0fc1d34 Write_rows: table id 108 flags: STMT_END_F
BINLOG '0ah1XRPoAwAAMgAAAKwCAAAAAGwAAAAAAAEABHRlc3QABGd4dDEAAgMPAgACA4VikN4=0ah1XR7oAwAAMgAAAN4CAAAAAGwAAAAAAAEAAgAC//wBAAAACAB6aGFuZ3NhbjQd/KA='/!/;
at 734
#190908 21:20:17 server id 1000 end_log_pos 765 CRC32 0xc9208948 Xid = 10COMMIT/!/;
at 765
#190908 21:30:18 server id 1000 end_log_pos 812 CRC32 0xda645def Rotate to mysql-bin.000002 pos: 4SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog / /!*/;DELIMITER ;
End of log file
/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/;/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0/;
上面蓝色字体表示位置，红色表示时间。
SHOW BINARY LOGS ;
SHOW MASTER LOGS ;
show binlog events in 'mysql-bin.000001';
SHOW MASTER STATUS;
表示二进制文件已经记录到了第四个，位置为 418.
1.5.3、二进制文件的删除
1.reset master 将会删除所有日志，并让日志文件重新从 000001 开始。mysql> reset master;
2.PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }purge master logs to "binlog_name.00000X" 将会清空 00000X 之前的所有日志文件。例如删除 000006 之前的日志文件。mysql> purge master logs to "mysql-bin.000006";mysql> purge binary logs to "mysql-bin.000006";master 和 binary 是同义词
purge master logs before 'yyyy-mm-dd hh:mi:ss' 将会删除指定日期之前的所有日志。但是若指定的时间处在正在使用中的日志文件中，将无法进行 purge。
3.使用--expire_logs_days=N 选项指定过了多少天日志自动过期清空
1.5.4、相关参数
log-bin=on|off|file_name #或者指定路径 log-bin=/data/binlog/mysql-bin 表示开启并设定路径，msyql5.7 开启该参数必须分配 server_id,否则 mysql 不能启动 server_id=1000 #指定 server_idmax_binlog_size: #(默认 1G)指定一个二进制文件的最大值，如果超过该值，则产生一个新的二进制日志文件（考虑到事务不可能跨两个 binlog 文件，所以有时 binlog 文件的实际大小不会等于设定值）binlog_cache_size: #（默认 32k）存储所有未提交的二进制日志记录，用于缓存，当该事务提交了，则会写入到二进制日志文件中，如果该参数太小，可能）存储所有未提交的二进制日志记录，用于缓存，当该事务提交了，则会写入到二进制日志文件中，如果该参数太小，可能 #会写入到磁盘，影响性能，可以通过查看参数 binlog_cache_disk_use,表示写磁盘的次数 sync_binlog: #默认情况下，二进制日志不是每次写都会写入二进制日志文件中，（可以理解为缓冲）如果数据库异常宕机，就会存在二进制日志文件缺少记录的情况，可以通过参数 sync_binlog=[N]来进行配置，当 N=1 时表示同步写磁盘的方式写二进制文件，这是不使用操作系统的缓冲来写日志。#N=0（默认），虽然设置 sync_binlog=1 理论上保证了同步，但是当使用 innodb 存储引擎时，会存在二进制记录了操作，但是事务由回滚了的情况，#可以通过参数 innodb_support_xa=1 来解决该问题，innodb_support_xa=1 保证了二进制文件和存储引擎文件同步 binlog_do-db: #记录该数据库 binlog_ingnore-db: #忽略该数据库 log_slave-update: #如果当前数据库时复制中的 slave 角色，不会把从 master 获取到的二进制日志写入到自己的二进制文件中，如果需要，则需要开启 #这个参数，如果搭建 master=>slave=>slave 的架构复制，则必须开启此参数 binlog_format: #可以设置为如下的值 #statement：二进制日志文件都是记录的逻辑 sql 语句 #row：记录表行更改的情况，如果设置为该参数，可以将 innodb 存储引擎的事务隔离级别基本设置为 READ COMMITTED,获得更好的并发，一般设置为 row，虽然对磁盘和网络开销大一些，格式为二进制格式。使用 mysqlbinlog 工具查看 #mixed：mixed 模式，默认使用 statement 配置
expire_logs_days ： #指定自动删除二进制日志的时间，即日志过期时间 log_bin_index ： #指定 mysql-bin.index 文件的路径 max_binlog_cache_size ： #指定二进制日志缓存最大大小，硬限制。默认 4G，够大了，建议不要改 binlog_cache_use：使用缓存写二进制日志的次数(这是一个实时变化的统计值)binlog_cache_disk_use:使用临时文件写二进制日志的次数，当日志超过了 binlog_cache_size 的时候会使用临时文件写日志，如果该变量值不为 0，则考虑增大 binlog_cache_size 的值
1.5.5、日志还原
方式一：直接还原 mysqlbinlog --stop-datetime="2014-7-2 15:27:48" /tmp/mysql-bin.000008 | mysql -u user -p password
方式二：先导入到一个文件然后恢复 mysqlbinlog mysql-bin.000001 > /tmp/a.sqlmysql -u root -p password -e "source /tm
二、事务日志
innodb 事务日志包括 redo log 和 undo log。redo log 是重做日志，提供前滚操作，undo log 是回滚日志，提供回滚操作。
undo log 不是 redo log 的逆向过程，其实它们都算是用来恢复的日志：1.redo log 通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。2.undo 用来回滚行记录到某个版本。undo log 一般是逻辑日
志，根据每行记录进行记录
2.1、redo log
redo log 主要由重做日志缓冲（redo log buffer）和重做日志文件（redo log file）构成。缓冲数据存于内存，当机器异常或者服务异常会导致缓冲中的数据丢失，日志文件的数据存于磁盘，是持久化文件。
重做日志就是通过先写入缓冲，然后刷新到磁盘的操作，那怎么能够保证数据不会丢失呢？
我们可以通过参数 innodb_flush_log_at_trx_commit 进行控制，该参数有三个值，分别为 0,1,2，在详细了解这个参数之前，我们先了解下重做日志缓冲和重做日志文件的是如何同步的：确保每次日志都能写入到事务日志文件中，在每次将 log buffer 中的日志写入日志文件的过程中都会调用一次操作系统的 fsync 操作(即 fsync()系统调用)。因为 MySQL 是工作在用户空间的，MySQL 的 log buffer 处于用户空间的内存中。要写入到磁盘上的 log file 中(redo:ib_logfile0 和 ib_logfile1 文件,undo:share tablespace 或.ibd 文件)，中间还要经过操作系统内核空间的 os buffer，调用 fsync()的作用就是将 OS buffer 中的日志刷到磁盘上的 log file 中。如下图所示：
接下来继续看 innodb_flush_log_at_trx_commit 的值的意义：
当设置为 1 的时候，事务每次提交都会将 log buffer 中的日志写入 os buffer 并调用 fsync()刷到 log file on disk 中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO 的性能较差。（推荐）当设置为 0 的时候，事务提交时不会将 log buffer 中日志写入到 os buffer，而是每秒写入 os buffer 并调用 fsync()写入到 log file on disk 中。也就是说设置为 0 时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失 1 秒钟的数据。当设置为 2 的时候，每次提交都仅写入到 os buffer，然后是每秒调用 fsync()将 os buffer 中的日志写入到 log file on disk。
redo log 是用来数据的 crash recovery 的，这是数据库保障数据安全的重要功能之一，保存着 innodb 表中的修改记录，所以也叫日志文件，在 innodb 存储引擎中，默认有两个日志文件，新建数据库后，会有名为 ib_logfile0 和 ib_logfile1 两个文件，如果在启动数据库时，两个文件不存在，则 innodb 会根据参数创建。
日志文件是以类似循环的方式使用的，当第一个日志文件写满，会往第二个继续写，当所有日志文件写满，则会在第一个日志文件中清空一些空间出来，提供写空间，如下图所示：
innodb 中通过日志组来管理日志文件，是一个逻辑定义，它包含若干个日志文件，一组中的日志文件大小相等，大小通过参数设定。参数 innodb_log_files_group 决定，默认值为 2，决定每个组有多少个日志文件。参数 innodb_log_group_home_dir，默认文 mysql 的 database 目录，决定日志文件存放的目录。
日志组中的每个文件都有自己的格式，内部也是按照大小相同的页面切分，这里的页面大小是 512 字节，则是机械硬盘块的大小。这样的设计是为了提高数据写入的吞吐量和效率。并且日志将逻辑事务对数据库的分散随机写入转换为 512 字节整数倍的顺序写入，大大提高数据库写的性能，这也是数据库管理系统区别于文件管理系统的重要因素。
既然说每个日志有很多个 512 字节的块组成，那么我们了解下每个日志文件的构成，如下图所示：
如上图所示，一个日志文件包含很多个 512 字节的块，而每个块又分为三部分组成，分别为日志头，日志尾，和具体日志内容。其中日志头占用 12 字节，日志尾占用 8 字节，所以剩余的 492 字节为日志内容占用。
日志头包含 4 部分：log_block_hdr_no：(4 字节)该日志块在 redo log buffer 中的位置 ID。log_block_hdr_data_len：(2 字节)该 log block 中已记录的 log 大小。写满该 log block 时为 0x200，表示 512 字节。log_block_first_rec_group：(2 字节)该 log block 中第一个 log 的开始偏移位置。lock_block_checkpoint_no：(4 字节)写入检查点信息的位置
日志尾只有一个部分： log_block_trl_no ，该值和块头的 log_block_hdr_no 相等
接下来我们看看文件组：在每个组的第一个 redo log file 中，前 2KB 记录 4 个特定的部分，从 2KB 之后才开始记录 log block。除了第一个 redo log file 中会记录，log group 中的其他 log file 不会记录这 2KB，但是却会腾出这 2KB 的空间。如下：
我们知道重做日志的构成以及其作用之后，那重做日志是什么时间点从缓冲中刷新到日志文件的呢？接下来看看日志刷盘的时机。
日志刷盘时机主要有：
1、log buffer 空间用完，2、master 主线程后台每秒刷新一次 3、在 DML 操作时，会检查日志空间是否足够，如果已经达到预设值，则刷新日志到磁盘 4、checkpoint 时，保证所有要刷的数据页中的 LSV 最小值已经刷入到磁盘，5、提交逻辑事务时，由参数 innodb_flush_log_at_trx_commit 控制
上面提到的 LSN 称为日志的逻辑序列号(log sequence number)，在 innodb 存储引擎中，lsn 占用 8 个字节。LSN 的值会随着日志的写入而逐渐增大。根据 LSN，可以获取到几个有用的信息：1.数据页的版本信息。2.写入的日志总量，通过 LSN 开始号码和结束号码可以计算出写入的日志量。3.可知道检查点的位置。实际上还可以获得很多隐式的信息。LSN 不仅存在于 redo log 中，还存在于数据页中，在每个数据页的头部，有一个 fil_page_lsn 记录了当前页最终的 LSN 值是多少。通过数据页中的 LSN 值和 redo log 中的 LSN 值比较，如果页中的 LSN 值小于 redo log 中 LSN 值，则表示数据丢失了一部分，这时候可以通过 redo log 的记录来恢复到 redo log 中记录的 LSN 值时的状态
可以通过：mysql> show engine innodb status\G;查看
其中：
log sequence number 就是当前的 redo log(in buffer)中的 lsn；log flushed up to 是刷到 redo log file on disk 中的 lsn；pages flushed up to 是已经刷到磁盘数据页上的 LSN；last checkpoint at 是上一次检查点所在位置的 LSN
2.2、undo log
undo log 有两个作用：提供回滚和多个行版本控制(MVCC)
在数据修改的时候，不仅记录了 redo，还记录了相对应的 undo，如果因为某些原因导致事务失败或回滚了，可以借助该 undo 进行回滚。undo log 和 redo log 记录物理日志不一样，它是逻辑日志。可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录。
当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过 undo log 来实现的：当读取的某一行被其他事务锁定时，它可以从 undo log 中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。undo log 是采用段(segment)的方式来记录的，每个 undo 操作在记录的时候占用一个 undo log segment。另外，undo log 也会产生 redo log，因为 undo log 也要实现持久性保护
在事务提交的时候，会将该事务对应的 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 区别：
1、记录对象不一样：redo log 是在记录存储引擎（innodb）的数据，而二进制文件时记录 mysql 的数据库更新数据相关的操作，可以是 innodb，mymyisam 等存储引擎。
2、记录内容不一样：binlog 记录的一个事务的具体操作内容，是逻辑日志，redo log 记录的是每个页的修改情况，是物理日志。
3、记录时间不一样：binlog 仅仅在事务提交前 jin 进行提交，即只写磁盘一次，而 redo log 在事务的过程中不断的记录操作，写入 redo log 中。
4、记录格式不一样：binlog 的记录方式和事务的提交顺序有关，而 redo log 中是记录的物理页的修改，redo log 文件中同一个事务可能多次记录，最后一个提交的事务记录会覆盖所有未提交的事务记录。
5、事务日志记录的是物理页的情况，它具有幂等性，因此记录日志的方式极其简练。幂等性的意思是多次操作前后状态是一样的。
binlog 和事务日志的先后顺序及 group commit：
评论