前言

MySQL的日志在软件的运行过程中发挥中巨大作用，MySQL的数据恢复、还原、性能监控、问题排查都需要日志来协助。在开始介绍之前，先看下本篇文章的一个大概流程。

主重mysql binlog日志删除 mysql重做日志作用_mysql 日志

本文所有的日志介绍及配置都是基于MySQL5.7.21版本的。

重做日志（redo log）
01作用

用来实现事务的持久性，由两部分组成：

  1. 一是内存中的重做日志缓冲(redo log buffer)，其是易失的；
  2. 二是操作日志(redo log file),其是持久的。

redo log是InnoDB引擎层的物理日志。作用可分为两个：

  1. 一是减少磁盘IO操作，提升写效率：
    若MySQL每一次更新操作都要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，那么这整个过程
    IO成本、查找成功会很高。为解决此问题，当有一条记录更新时，InnoDB引擎胡先把记录写到redo log（顺序追加写），
    并更新内存。这个时候更新就算完成了，InnoDB会在适当的时候(系统比较空闲的时候)将这个操作记录更新到磁盘。
    其实这里的redo log就是通常所说的WAL技术，基本上大部分数据库都会用到此技术。
  2. 二是保证数据持久化：
    通过写效率的描述，我们知道InnoDB引擎会将更新记录先写入redo log，如果此时内存中的提交记录还未更新到磁盘而
    数据库发生了异常重启，那等MySQL下次上电时就可以通过redo log恢复之前还未提交的数据到磁盘文件中。
02参数配置

通过命令SHOW VARIABLES LIKE '%innodb_log%'''，我们可以得到以下参数信息：

主重mysql binlog日志删除 mysql重做日志作用_MySQL_02

  • 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%''，我们可以得到以下参数信息：

主重mysql binlog日志删除 mysql重做日志作用_redlog_03

  • 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层的日志，它是逻辑日志。记录了所有数据库表结构变更及表数据修改。

  1. 通过binlog，我们可以将数据恢复到指定时间点。
  2. 通过binlog，可以实现master/slave的主从复制。
  3. 通过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%'，我们可以得到以下信息：

主重mysql binlog日志删除 mysql重做日志作用_redlog_04

  • 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%"，得到以下信息：

主重mysql binlog日志删除 mysql重做日志作用_主重mysql binlog日志删除_05

  • general_log
    ON表示开启日志，OFF表示关闭日志。
  • general_log_file
    指明了日志存储路径及名称。
中继日志（relay log）
01作用

MySQL进行主主复制或主从复制的时产生的日志。

02参数
  • max_relay_log_size
    指定中继日志大小，若为0则默认为1GB。
  • relay_log
    定义中继日志位置，如果值为空，则默认位置在数据文件的目录。
日志的比较

这里主要说下redo log和bin log的区别：

  1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
  2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
  3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

参考：

极客时间：《MySQL实战45讲》