前言
MySQL的日志在软件的运行过程中发挥中巨大作用,MySQL的数据恢复、还原、性能监控、问题排查都需要日志来协助。在开始介绍之前,先看下本篇文章的一个大概流程。
本文所有的日志介绍及配置都是基于MySQL5.7.21
版本的。
重做日志(redo log)
01作用
用来实现事务的持久性,由两部分组成:
- 一是内存中的重做日志缓冲(redo log buffer),其是易失的;
- 二是操作日志(redo log file),其是持久的。
redo log是InnoDB引擎层的物理日志。作用可分为两个:
- 一是减少磁盘IO操作,提升写效率:
若MySQL每一次更新操作都要写进磁盘,然后磁盘也要找到对应的那条记录,然后再更新,那么这整个过程
IO成本、查找成功会很高。为解决此问题,当有一条记录更新时,InnoDB引擎胡先把记录写到redo log(顺序追加写),
并更新内存。这个时候更新就算完成了,InnoDB会在适当的时候(系统比较空闲的时候)将这个操作记录更新到磁盘。
其实这里的redo log就是通常所说的WAL技术,基本上大部分数据库都会用到此技术。 - 二是保证数据持久化:
通过写效率的描述,我们知道InnoDB引擎会将更新记录先写入redo log,如果此时内存中的提交记录还未更新到磁盘而
数据库发生了异常重启,那等MySQL下次上电时就可以通过redo log恢复之前还未提交的数据到磁盘文件中。
02参数配置
通过命令SHOW VARIABLES LIKE '%innodb_log%'''
,我们可以得到以下参数信息:
-
innodb_log_buffer_size
用来设置缓存还未提交的事务的缓冲区大小,就是上文所说的redo log buffer大小。一般默认16MB,
如果事务之中还有blog/text等大字段,这个缓冲去会很快被填满而引起额外的IO负载。 -
innodb_log_files_in_group
redo log 文件的个数,默认2个,最大100个。命名方式为ib_logfile0、ib_logfile1…ib_logfileN。 -
innodb_log_file_size
就是上文所说的redo log file文件的大小,默认48MB,所有的redo log file文件的最大值是512GB,redo log file最小个数是2,所以单个redo log file最大值是256GB。这是一个全局的静态参数,不能动态修改。值不能设置的太小防止写日志文件频繁切换,值也不能太大,防止上电进行恢复时耗时太久。 -
innodb_log_write_ahead_size
每次追加写到redo log file中的数据大小,默认值8192Byte,最小值512Byte,最大值等于innodb_page_size。InnoDB以512字节一个block的方式对齐写入ib_logfile文件,但现代文件系统一般以4096字节为一个block单位。如果即将写入的日志文件块不在OS Cache时,就需要将对应的4096字节的block读入内存,修改其中的512字节,然后再把该block写回磁盘。
为解决此问题,MySQL 5.7引入了一个新参数:innodb_log_write_ahead_size。当前写入文件的偏移量不能整除该值时(即写入的数据总大小不是innodb_log_write_ahead_size倍数时),则补0,多写一部分数据。这样当写入的数据是以磁盘block size对齐时,就可以直接write磁盘,而无需read-modify-write这三步了。
回滚日志(undo log)
01作用
两个作用,提供数据回滚和多个行版本控制(MVCC)。
-
数据回滚
在对数据库进行修改时,InnoDB存储引擎不但会产生redo log,还会产生一定量的undo log,还会产生一定量的undo,这样用户如果执行事务或语句失败了,又或者用户用一条ROLLBACK语句请求回滚,这时就可用undo log将数据回滚到修改之前的样子。 -
MVCC
当用户读取某一行记录之前,会读取MVCC版本链中的一个版本,相当于一个快照(里面存储了针对该行操作的各个事务ID记录),
若该记录已经被其他事务占用,当前事务可以通过刚刚读取到的快照,去undo log中读取之前的行版本信息,以此实现非锁定读取。
undo log是采用undo segment段进行记录的,undo段位于共享表空间内(ibdata1文件)。另外undo log也会产生redo log,以此实现持久性保护。undo log不同于redo log(物理日志),它是逻辑日志。
02参数配置
通过命令SHOW GLOBAL VARIABLES LIKE '%undo%''
,我们可以得到以下参数信息:
- innodb_undo_directory
undo log文件存储路径。若未指定,则默认存放到mysql的data目录里。 - innodb_undo_logs
undo rollback segment 回滚段个数,默认值128,最大值也是128。
01)slot0 预留给系统表空间。
02)slot1~32 预留给临时表空间,每次数据库重启的时候,都会重建临时表空间。
03)slot33~127 如果有独立表空间,则预留给undo 独立表空间,若没有则预留给系统表空间。
记住,每个undo rollback segment内部由1024个undo segment组成。此参数值可动态调整,至少>=35,因为在MySQL 5.7中,第一个undo log永远在系统表空间中,另外32个undo log分配给了临时表空间,即ibtmp1,至少还有2个undo log才能保证2个undo表空间中每个里面至少有1个undo log,具体细节后续会求证,使用时最好默认128。 - innodb_undo_tablespaces
设置undo独立表空间个数,范围0~128。默认0,表示不开启独立undo表空间,此时undo日志存储在ibdata1文件中,设置该参数后,会在路径innodb_undo_directory看到undo为前缀的文件,该文件就代表rollback segment文件。该参数只能在最开始初始化MySQL实例的时候指定,如果实例已创建,这个参数是不能变动的。 - innodb_undo_log_truncate
(默认值NO)参数设置为ON,即开启在线回收undo日志文件,支持动态设置,当超过 innodb_max_undo_log_size 时被进行收缩,至少需要两个undo文件,即innodb_undo_tablespace>=2。 - innodb_max_undo_log_size
(默认1GB)当超过阈值时,会触发truncate回收动作,truncate后空间缩小到10MB。
二进制日志(binlog)
01作用
它是MySQL的Server层的日志,它是逻辑日志。记录了所有数据库表结构变更及表数据修改。
- 通过binlog,我们可以将数据恢复到指定时间点。
- 通过binlog,可以实现master/slave的主从复制。
- 通过binlog,可以保证存储引擎崩溃的数据得到恢复。
在开启binlog的情况下,为了保证binlog与redo的一致性,MySQL将采用事务的两阶段提交协议。
当MySQL系统发生崩溃时,事务在存储引擎内部的状态可能为prepared和commit两种。对于prepared状态的事务,是进行提交操作还是进行回滚操作,这时需要参考binlog,01如果事务在binlog中存在,那么将其提交;02如果不在binlog中存在,那么将其回滚,这样就保证了数据在主库和从库之间的一致性。
binlog是追加写的,文件写到一定大小会切换到下一个,并不会覆盖以前的日志。
02参数配置
-
log_bin
设置此参数表示启用binlog功能,并指定路径名称。 -
log_bin_index
指定二进制索引文件的路径与名称。这个文件里包含了所有binlog文件及文件路径。 -
expire_logs_days
删除超过这个日期的binlog文件,时间判断不是根据binlog数据内容的,而是根据binlog文件的最近操作系统修改时间来判断的。 -
max_binlog_size
binlog文件的最大大小,最大和默认值都是1GB。 -
binlog_cache_size
事务在内存中存储二进制日志的缓存大小。 -
max_binlog_cache_size
表示binlog缓存日志能使用的最大缓存大小。 -
binlog_cache_use
使用二进制日志缓存的事务数量。 -
binlog_cache_disk_use
使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量。 -
sync_binlog
控制cache中的事务数据commit多少次之后,要将cache数据刷新到磁盘。
01)默认为0,表示由文件系统自己控制它的缓存的刷新。此时数据库性能是最好的,但是风险也是最大的。因为一旦系统Crash,在cache中的所有binlog信息都会被丢失。
02)其它值表示事务进进行对应次数的commit之后,调用一次fsync之类的磁盘同步指令,强制将Cache中的binlog数据信息刷新到磁盘中。 -
binlog_format
binlog日志文件的格式。分三种:STATEMENT,ROW,MIXED。
1.STATEMENT模式:
每一条会修改数据的sql语句会记录到binlog中(也会记录每条sql语句的上下文信息)。优点是并不需要记录每一条sql语句和每一行的数据变化,减少了binlog日志量,节约IO,提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数, last_insert_id(),以及user-defined functions(udf)等会出现问题)。
2.ROW模式:
不记录每条sql语句的上下文信息,仅需记录哪条数据被修改了,修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志,尤其是alter table的时候会让日志暴涨。
3.MIXED模式:
以上两种模式的混合使用,一般的复制使用STATEMENT模式保存binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog,MySQL会根据执行的SQL语句选择日志保存方式。
错误日志(error log)
01作用
错误日志记录了mysqld在启动、关闭过程中的信息,也包括MySQL在运行过程中的一些错误信息。
注意:
错误日志中记录的并非全是错误信息,MySQL表的初始化信息也记录在里面。
MySQL服务器发生异常时,我们可以在错误日志中找到发生异常的时间、原因,然后根据这些信息来解决异常。
02参数配置
MySQL是否启用错误日志及错误日志的路径选择是通过配置文件来决定的。如果想修改这些,Windows环境下修改my.ini文件。通过命令SHOW VARIABLES LIKE '%log_error%'
,我们可以得到以下信息:
- log-error[=file_name]
选项用来启用错误日志,file_name指定错误日志路径及名称。 - log_warnings
选项指定错误日志中日志输出级别,测试过程中0
不记录告警信息,1
只记录告警信息,大于1
记录正常信息和告警信息。
03归档
错误日志如果不清理或删除,它会一直增长。 在mysql 5.7之后,只能人为的拷贝移动备份日志,操作步骤这里就不说了。
慢查询日志(slow query log)
01作用
慢查询日志用来记录超过指定执行时间的SQL语句,包括SELECT、INSERT、DELETE、UPDATE等DML语句。通过慢查询日志可以找到耗时的SQL语句并进行优化。默认情况下慢查询日志是不开启的,需要手动开启。
02参数配置
- long_query_time
慢查询超时阈值,默认10s。 - slow_query_log
ON表示开启慢查询日志,OFF则相反。 - slow_query_log_file
指定慢查询日志路径及名称。
一般查询日志(general log)
01作用
记录所有对MySQL数据库的请求命令,不论这些请求命令是否得到正确执行。
02参数
一般查询日志如果开启了会对系统开销比较大,MySQL默认不开启的。查询日志默认存储在MySQL数据库的数据文件夹下,通常文件名是hostname.log,其中hostname表示MySQL服务器的主机名。
通过命令SHOW VARIABLES LIKE "general_log%"
,得到以下信息:
- general_log
ON表示开启日志,OFF表示关闭日志。 - general_log_file
指明了日志存储路径及名称。
中继日志(relay log)
01作用
MySQL进行主主复制或主从复制的时产生的日志。
02参数
- max_relay_log_size
指定中继日志大小,若为0则默认为1GB。 - relay_log
定义中继日志位置,如果值为空,则默认位置在数据文件的目录。
日志的比较
这里主要说下redo log和bin log的区别:
- redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用。
- redo log 是物理日志,记录的是“在某个数据页上做了什么修改”;binlog 是逻辑日志,记录的是这个语句的原始逻辑,比如“给 ID=2 这一行的 c 字段加 1 ”。
- redo log 是循环写的,空间固定会用完;binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。
参考:
https://www.cnblogs.com/xingyunfashi/p/8431780.html
极客时间:《MySQL实战45讲》