0%

MySQL日志

MySQL日志

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

事务日志

重做日志 redo log

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

配置

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

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

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

  • innodb_log_file_size 重做日志文件的大小
  • innodb_mirrored_log_groups 指定了日志镜像文件组的数量,默认1
作用
  • 确保事务的持久性
  • 防止在发生故障的时间点,尚有脏页未写入磁盘,在重启mysql服务的时候,根据redo log进行重做,从而达到事务的持久性这一特性

回滚日志 undo log

事务开始之前,将当前的版本生成undo log,undo 也会产生 redo 来保证undo log的可靠性

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

如果事务需要回滚,则直接利用undo log中的备份数据恢复到事务开始前的状态

配置

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的默认是保持时间由参数expire_logs_days配置,也就是说对于非活动的日志文件,在生成时间超过expire_logs_days配置的天数之后,会被自动删除

  • 配置文件的路径为log_bin_basename,binlog日志文件按照指定大小,当日志文件达到指定的最大的大小之后,进行滚动更新,生成新的日志文件

  • 对于每个binlog日志文件,通过一个统一的index文件来组织

日志录入格式

binlog录入有三种格式,Statement、Row、Mixedlevel

Statement

每一条会修改数据的sql都会记录在binlog中

优点:不需要记录每一行的变化,减少了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

不记录sql语句上下文相关信息,仅保存哪条记录被修改

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

Mixedlevel

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

作用

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