MySQL日志分类及简介

来自Wikioe
Eijux（讨论 | 贡献）2021年4月27日 (二) 11:37的版本 （→‎binlog 相关参数）
跳到导航 跳到搜索


关于

MySQL中有六种日志文件，分别是：

  1. 事务日志：
    1. 重做日志（redo log）
    2. 回滚日志（undo log）
  2. 二进制日志（binlog）
  3. 错误日志（errorlog）
  4. 慢查询日志（slow query log）
  5. 一般查询日志（general log）
  6. 中继日志（relay log）
  • 其中，查询日志中的“查询”，是广义的查询（涉及到数据库查找的诸多操作），而非特指“SELECT”语句。

事务日志

重做日志（redo log）

回滚日志（undo log）

二进制日志（binlog）

binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。

  • 写入性操作：包括，数据库表结构变更（CREATE、ALTER、DROP等）及表数据修改（INSERT、UPDATE、DELETE等），但不包括“SELECT”、“SHOW”等操作。
  • binlog 是 mysql 的逻辑日志，并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。

作用

  1. 恢复（recovery）：某些数据的恢复需要二进制日志。例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复。【备份】
  2. 复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为slave或者standby）与一台MySQL数据库（一般称为master或者primary）进行实时同步。【主从复制】
  3. 审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。【？】
  4. 事务：【分布式事务】
    在开启 binlog 的情况下，为了保证 binlog 与 redo 的一致性，MySQL将采用事务的两阶段提交协议。当MySQL系统发生崩溃时，事务在存储引擎内部的状态可能为“prepared”和“commit”两种。对于“prepared”状态的事务，是进行提交操作还是进行回滚操作，这时需要参考 binlog：
    1. 如果事务在 binlog 中存在，那么将其提交；
    2. 如果不在 binlog 中存在，那么将其回滚，这样就保证了数据在主库和从库之间的一致性。

binlog 索引文件

为了管理所有的 binlog 文件，MySQL额外创建了一个“base-name.index”文件，它按顺序记录了 MySQL 使用的所有 binlog 文件。如果你想自定义 index 文件的名称，可以设置“log_bin_index=file”参数。

  • 千万不要在 mysqld 运行的时候手动修改 index 文件的内容，这样会使 mysqld 产生混乱。

binlog 开启

如果想开启 binlog（默认关闭），可以在 MySQL 配置文件中通过配置参数“log-bin = [base-name]”启动二进制日志。如果不指定“base-name”，则默认二进制日志文件名为主机名，并以自增的数字作为后缀，例如“mysql-bin.000001”，所在目录为数据库所在目录（datadir）。


当满足下面三种情况时，会创建新的二进制文件（文件后缀会自增）：

  1. 文件大小达到“max_binlog_size”参数设置值时。
  2. 执行“flush logs”命令。
  3. 重启 mysqld 进程。
  • 当文件后缀从 000001 增长到 999999 时，又会回到 000001。

binlog 格式

binlog格式分为三种：

  1. STATEMENT：
    基于语句的复制，记录的是数据库上执行的原生SQL语句。
    • 优点：
      1. 简单直观地记录和执行这些语句，能够让主备保持同步，在主服务器上执行的SQL语句，在从服务器上执行同样的语句。（通过 mysqlbinlog 工具容易读懂其中的内容）
      2. 二进制日志里的时间更加紧凑，所以相对而言，基于语句的复制模式不会使用太多带宽，同时也节约磁盘空间。
    • 缺点：
      1. 同一条SQL在主库和从库上执行的时间可能稍微或很大不相同，因此在传输的二进制日志中，除了查询语句，还包括了一些元数据信息，如当前的时间戳。即便如此，还存在着一些无法被正确复制的SQL。
        例如，使用“INSERT INTO TB1 VALUE(CUURENT_DATE())”这一条使用函数的语句插入的数据复制到当前从服务器上来就会发生变化。
        存储过程和触发器在使用基于语句的复制模式时也可能存在问题。
      2. 基于语句的复制必须是串行化的。这要求大量特殊的代码，配置，例如 InnoDB 的 next-key 锁等。
  2. ROW：
    基于行的复制。将实际数据记录在二进制日志中，也就是基于数据的复制，基于行的更改。（从 MySQL5.1 开始支持）
    • 优点：可以正确地复制每一行数据，几乎没有基于行的复制模式无法处理的场景，对于所有的SQL构造、触发器、存储过程等都能正确执行。
    • 缺点：二进制日志可能会很大，而且不直观。（不能使用 mysqlbinlog 来查看二进制日志）
    • 但由于其优点远大于缺点，对于ROW格式的二进制日志基本是标配了，在数据恢复、不同数据库之间数据同步等方面十分重要。
  3. MIXED：【MySQ L默认使用的二进制日志记录方式】
    （STATEMENT + ROW）默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。
    比如用到“UUID()”、“USER()”、“CURRENT_USER()”、“ROW_COUNT()”等无法确定的函数。

binlog 相关参数

  • max_binlog_size：限定单个 binlog 文件的大小（默认1 G）
    如果当前 binlog 文件的大小达到了参数指定的阈值，会创建一个新的 binlog 文件作为当前活跃的 binlog 文件，后续所有对数据库的修改都会记录到新的 binlog 文件中。
    • 注意：binlog 文件可能会大于 max_binlog_size 参数设定的阈值。【因为一个事务所产生的所有事件必须记录在同一个 binlog 文件中，所以可能导致文件大小大于阈值】
  • binlog_cache_size：二进制日志缓冲大小（默认32 K）
    当使用事务的表存储引擎（如InnoDB存储引擎）时，所有未提交（uncommitted）的二进制日志会被记录到一个缓存中去，等该事务提交（committed）时直接将缓冲中的二进制日志写入二进制日志文件。
    • 此外，binlog_cache_size 是基于会话（session）的，也就是说，当一个线程开始一个事务时，MySQL 会自动分配一个大小为 binlog_cache_size 的缓存，因此该值的设置需要相当小心，不能设置过大。当一个事务的记录大于设定的binlog_cache_size时，MySQL会把缓冲中的日志写入一个临时文件中，因此该值又不能设得太小。
    通过“SHOW GLOBAL STATUS”命令查看“binlog_cache_use”（使用缓冲写二进制日志的次数）、“binlog_cache_disk_use”（使用临时文件写二进制日志的次数）的状态，可以判断当前binlog_cache_size的设置是否合适。
  • sync_binlog：“sync_binlog=[N]”中的 N 表示每提交多少个事务就进行 binlog 刷新到磁盘。
    1. “sync_binlog=0”，表示刷新 binlog 时间点由操作系统自身来决定，操作系统自身会每隔一段时间就会刷新缓存数据到磁盘；
    2. “sync_binlog=1”，表示采用同步写磁盘的方式来写二进制日志，每次事务提交时就会刷新 binlog 到磁盘；
    • 如果使用 Innodb 存储引擎进行复制，并且想得到最大的高可用性，需要将此值设置为 1。不过该值为 1 时，确时会对数据库I/O系统带来一定的开销。
      但是，即使将sync_binlog设为1，还是会有一种情况导致问题的发生：当使用 InnoDB 存储引擎时，在一个事务发出 COMMIT 动作之前，由于 sync_binlog 为 1，因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志，但是提交还没有发生，并且此时发生了宕机，那么在 MySQL 数据库下次启动时，由于 COMMIT 操作并没有发生，这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，不能被回滚。
      对于这个问题，MySQL 使用了两阶段提交来解决的，简单说就是对于已经写入到 binlog 文件的事务一定会提交成功， 而没有写入到 binlog 文件的事务就会进行回滚，从而保证二进制日志和 InnoDB 存储引擎数据文件的一致性，保证主从复制的安全。
  • binlog-do-db & binlog-ignore-db：表示需要写入或者忽略写入哪些库的二进制日志。
    默认为空，表示需要同步所有库的日志到二进制日志。
  • log-slave-update：【主从复制中，从服务器的二进制文件是否更新】
    如果当前数据库是复制中的 slave 角色，则它不会将 master 取得并执行的二进制日志写入自己的二进制日志文件中去。如果需要写入，要设置 log-slave-update。
    • 如果需要搭建 master–>slave–>slave 架构的复制，则必须设置该参数。
  • binlog-format：设置二进制日志的记录格式。
  • log_bin_trust_function_creators：
    默认为 OFF，这个参数开启会限制存储过程、Function、触发器的创建。

错误日志（errorlog）

慢查询日志（slow query log）

一般查询日志（general log）

中继日志（relay log）

取自“http://wiki.eijux.com/index.php?title=MySQL日志分类及简介&oldid=2766”