MySQL日志

MySQL中有六种日志文件，分别是事务日志（分为重做日志(redo log)和回滚日志(undo log)）、二进制日志(bin log)、错误日志(error log)、慢查询日志(slow query log)、一般查询日志(general log)、中继日志(relay log)。

事务日志

事务的隔离性是由锁来实现的，原子性、一致性、持久性是通过undo log和redo log来实现的

重做日志 redo log(Innodb特有的)

用于保证事务的原子性和持久性，有两部分组成，一是内存中的重做日志缓冲(redo log buffer)，二是重做日志文件(redo log file)

InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）

Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。

于是，redo log被引入来解决这个问题：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到磁盘，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求

事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中，当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖），在redo log中记录的是物理数据页面的修改信息

redolog 是物理日志，记录的就是数据页变更

redo log分为两部分：内存中的日志缓冲(redo log buffer) 和磁盘上的日志文件(redo log file)，先将记录写入redo log buffer，后续某个时间点在一次性将多个操作记录写到redo log file中，对于redo log buffer写入redo log file有三种选择

0 延迟写 事务提交时不会将redo log buffer中日志写入到os buffer中，而是每秒写入os buffer并调用fsync()写入到redo log file中，如果系统崩溃，将丢失1秒的数据
1 实时写，实时刷 事务每次提交都会将redo log buffer中的日志写入到os buffer并调用fsync()刷到redo log file中，虽然不会丢失数据，但是每次都写入磁盘，IO性能较差
2 实时写，延迟刷 每次提交将redo log buffer中的日志写入os buffer，但是每秒调用一次fsync()将os buffer中的日志写入到redo log file中

通过配置innodb_flush_log_at_trx_commit来配置

配置

默认是存储在data目录下的ib_logfile1和ib_logfile2文件中

innodb_log_group_home_dir 指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下
innodb_log_files_in_group 指定重做日志文件组中文件的数量，默认2

关于文件的大小和数量，由一下两个参数配置

innodb_log_file_size 重做日志文件的大小，redo log的文件大小是固定的，采用了循环写入的方式，当写到结尾时，会回到开头循环写入
innodb_mirrored_log_groups 指定了日志镜像文件组的数量，默认1

重做日志的大小影响Innodb的性能，重做日志不能设置太大，否则在恢复时可能会需要很长的时间；重做日志不能设置的太小，否则可能导致一个事务的日志需要多次切换重做日志文件。而且会导致频繁的发生async checkpoint，导致性能的抖动

作用

确保事务的持久性
防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性

回滚日志 undo log

用于保证事务的一致性

undo log主要记录数据的逻辑变化，比如一条insert语句，在undo log中对应一条delete语句，这样在发生错误时，就可以回滚到事务之前的数据状态

当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否有其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间

如果事务需要回滚，则直接利用undo log中的备份数据恢复到事务开始前的状态，根据undo log的内容做与之前相反的工作：对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去

undo log也会产生redo log，因为undo log也需要持久性的保护

除了回滚之外，undo的另一个作用是MVCC，当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取

配置

MySQL5.7之后的配置

innodb_undo_directory = /data/undospace/ undo独立表空间的存放目录
innodb_undo_logs = 128 回滚段为128KB
innodb_undo_tablespaces = 4 指定有4个undo log文件

作用

保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

二进制日志 bin log

事务提交的时候，一次性将事务中的sql语句（一个事务可能对应多个sql语句）按照一定的格式记录到binlog中，因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了bin_log的情况下，对于较大事务的提交，可能会变得比较慢一些，这是因为binlog是在事务提交的时候一次性写入的造成的。

binlog是逻辑日志（记录的是sql语句)

binlog在事务提交时才会记录，一开始binlog是记录在内存中，那么biglog是什么时候刷到磁盘中的呢？mysql通过sync_binlog参数控制biglog的刷盘时机，取值范围是0-N：

0：不去强制要求，由系统自行判断何时写入磁盘
1：每次commit的时候都要将binlog写入磁盘，5.7.7之后默认值
N：每N个事务，才会将binlog写入磁盘

binlog事件

binlog日志是由很多的binlog事件组成的，每个binlog事件有三部分组成

通用头包含有事件的基本信息，如事件类型和事件大小
提交头提交头与特定的事件类型有关，对于不同的事件类型，该字段存储的信息不同
事件体事件体的大小在通用头中给出，事件体存储事件的主要数据

配置

binlog的默认的保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除
配置文件的路径为log_bin_basename，binlog日志文件按照指定大小，当日志文件达到指定的最大大小后，进行滚动更新，生成新的日志文件
配置文件中配置log-bin来开启二进制日志，且设置文件名称
binlog_format 可以设置日志格式，Statement、Row、Mixed
对于每个binlog日志文件，通过一个统一的index文件来组织，在配置文件中使用log-bin-index来进行设置文件名称，默认与log-bin基本名称相同
log_bin_trust_function_creators 信任函数的创建者，默认是需要super权限才可以在slave上创建函数的，如果该选项为on，则不需要super权限就可以创建
binlog_cache_size 在事务过程中内存容纳二进制日志sql语句的缓存大小，当事务缓存的大小超过该值时，剩余数据将进入磁盘，如果大型事务较多，可以增大该值来提高性能
innodb_locks_unsafe_for_binlog
max_binlog_cache_size binlog所能够使用的最大cache内存大小，使用该值来限制在二进制日志中每个事务的大小
max_binlog_size binlog日志最大值，不过并不能严格控制binlog大小，在当到达binlog比较靠近尾部而又遇到一个较大事务时，系统为了保证事务完整性，不会做切换日志的动作，只能将该事务的所有sql记录在当前日志
sync_binlog 控制biglog的刷盘时机，该配置上述有描述
read-only 防止任务客户端进程(除slave进程外)更新服务器上的任何数据

日志录入格式

binlog录入有三种格式，Statement、Row、Mixed

Statement

5.7.7之前的默认值

基于sql语句的记录，每一条会修改数据的sql都会记录在binlog中，记录的是原始的sql语句

优点： 不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该根据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题)
缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql的复制，像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及用户自定义的 functions(udf)会出现问题)
使用以下函数的语句也无法被复制：

LOAD_FILE()
UUID()
USER()
FOUND_ROWS()
SYSDATE() (除非启动时启用了 —sysdate-is-now 选项)

Row

5.7.7之后的默认值

基于行的记录，不记录sql语句上下文相关信息，仅保存哪条记录被修改，记录的是原始数据

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row level的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题
缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中

Mixed

以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。新版本的MySQL中对row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更

查看二进制日志

-- 查看二进制日志中的事件,不过该语句只能看到第一个日志的内容,如果需要看其他文件的需要进行指定文件
show binlog events;
-- 查看指定二进制日志文件中的事件
-- show binlog events [in logfile] [from pos] [limit offset,row count]
show binlog events in 'mysql-bin.000019';

-- 查看当前状态 其中的file字段表示当前正在写的二进制文件
show master status;
-- 查看所有的binlog日志文件以及大小
show BINARY logs;

-- 执行该语句可以进行binlog日志轮换，开启一个新的日志文件来保存binlog(有四种方式进行binlog日志轮换   1.服务器停止之后再次启动 2.binlog达到最大尺寸  3.使用flush logs来进行显示刷新 4.服务器事故)
flush logs;

-- 手动清除binlog日志   清除给定的时间之前的所有文件
purge binary logs before datetime;
-- 清除在给定文件之前的所有文件
purge binary logs to 'filename';

除了使用sql语句来进行查看binlog日志之外，还可以使用mysqlbinlog来进行查看日志

1 2	# mysqlbinlog [options] log-files mysqlbinlog --short-form --start-datetime='2021-12-13 10:00:00' /usr/local/var/mysql/mysql-bin.000020

作用

用于主从复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。
用于数据恢复，数据库的基于时间点的还原，使用mysqlbinlog进行数据恢复

中继日志(relay log)

中继日志是复制的核心，在复制过程中slave中的IO线程会将来自master的时间存储到中继日志中，将中继日志作为缓冲，使master不需要等待slave执行完成就可以发送下一个事件

查看中继日志

1
2
3

-- 查询slave上的中继日志
-- show relaylog events [in logfile] [from pos] [limit offset,row count]
show relaylog events;

慢查询日志(slow query log)

慢查询日志之前写过一篇文章是写慢查询日志的，可以直接去该链接查看

慢查询日志详解

一般查询日志(general log)

记录建立的客户端连接和执行的语句

show variables like '%general%';

---
Variable_name      Value
general_log        OFF
general_log_file    /var/lib/mysql/general.log

general_log的值为ON则为开启，为OFF则为关闭（默认情况下是关闭的），会记录所有操作，在并发操作下会产生大量信息从而导致大量IO操作，会影响mysql性能，建议不要开启

1
2
3

-- 查询当前日志的存储格式   可以是FILE(存储在general_log_file中)  也可以是TABLE(存储在mysql.general_log表中)，如果配置为FILE,TABLE则同时存入文件和表中
show variables like '%log_output%';

错误日志(error log)

默认是开启的，错误日志对MySQL的启动、运行、关闭过程进行了记录

show variables like 'log_er%' \G
*************************** 1. row ***************************
Variable_name: log_error
        Value: /var/log/mysql/error.log
*************************** 2. row ***************************
Variable_name: log_error_verbosity      可选值 1 错误信息 2  错误信息和告警信息  3：错误信息、告警信息和通知信息
        Value: 3