0%

MySQL日志

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来开启二进制日志,且设置文件名称
  • max_binlog_size 设置每个binlog文件的大小,当文件大小达到指定值后,会生成新的文件进行存储
  • 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 该配置上述有描述
  • 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(),以及user-defined 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等修改数据的语句,还是会记录所有行的变更

查看二进制日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 查看二进制日志中的事件,不过该语句只能看到第一个日志的内容,如果需要看其他文件的需要进行指定文件
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)

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

1
2
3
4
5
6
show variables like '%general%';

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

general_log的值为ON则为开启,为OFF则为关闭(默认情况下是关闭的)

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

错误日志(error log)

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

1
2
3
4
5
6
7
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