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等修改数据的语句,还是会记录所有行的变更
查看二进制日志
-- 查看二进制日志中的事件,不过该语句只能看到第一个日志的内容,如果需要看其他文件的需要进行指定文件
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来进行查看日志
# 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执行完成就可以发送下一个事件
查看中继日志
-- 查询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性能,建议不要开启
-- 查询当前日志的存储格式 可以是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