其他数据库日志

MySQL支持的日志

• MySQL Server Logs

MySQL有不同类型的日志文件，用来存储不同类型的日志，分为 二进制日志 、 错误日志 、 通用查询日志 和 慢查询日志 ，这也是常用的4种。MySQL 8又新增两种支持的日志： 中继日志 和 数据定义语句日志 。使用这些日志文件，可以查看MySQL内部发生的事情。

• 慢查询日志：记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。
• 通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。
• 错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。
• 二进制日志：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复。
• 中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。
• 数据定义语句日志：记录数据定义语句执行的元数据操作。

除二进制日志外，其他日志都是 文本文件 。默认情况下，所有日志创建于 MySQL数据目录 中。

日志的弊端

• 降低MySQL数据库的性能。
• 日志会占用大量的磁盘空间。

通用查询日志（General query log）

• 通用查询日志用来 记录用户的所有操作 ，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等。当我们的数据发生异常时，查看通用查询日志，还原操作时的具体场景，可以帮助我们准确定位问题。
• 默认不开启

• 查看通用查询日志

  show variables like '%general_log%';

  

• 开启/关闭通用查询日志：set global general_log = on;/off

• 查看内容

  

错误日志（Error log）

• 记录了MySQL服务器启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示等。
• 通过错误日志可以查看系统的运行状态，便于及时发现故障、修复故障。
• 默认开启

• 查看错误日志相关信息

  show variables like 'log_error%';

  

• 查看错误日志

  

  每次启动都会生成一个日志，并且可以发现其实每个错误日志并不大

  

二进制日志（Binary log）

• binlog即binary log，二进制日志文件，也叫作变更日志（update log）。它记录了数据库所有执行的DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、 show等）。
• 二进制日志包含描述数据库更改的“事件”，例如表创建操作或表数据更改。
• 主要应用场景
  1. 数据恢复：如果MySQL意外停止，可以通过binlog查看用户执行了哪些操作，根据记录来恢复数据库服务器中的记录。
  2. 数据复制：主从复制通过binlog来实现数据的同步，保证数据的一致性。

• 查看参数

  

  log_bin：开启状态

  log_bin_basename：binlog基本文件名，真正的日志文件名后还需要添加标识

  log_bin_index：binlog文件的索引文件，这个文件管理了所有binlog文件的目录

  log_bin_trust_function_creators：限制存储过程。因为binlog用于主从复制的时候，存储函数可能会导致同一个SQL执行的结果不一样，导致主从数据不一致。比如now()，因为主从复制有延迟，导致数据不一致

  log_bin_use_v1_row_events：已被弃用

  

• 设置参数

  这里使用配置文件修改

  log-bin=日志目录，比如log-bin=/var/lib/mysql/mybinlog

  binlog_expire_logs_seconds=xxxx，binlog过期时间，单位s。

  max_binlog_size=100M，单个binlog最大大小，默认值为1G。并不是严格1GB一点也不能超过。因为可能碰到一个完整的事务还未执行完，但是已经达到最大值，此时依旧会接着写。

• 查看日志

  • mysqlbinlog命令

    由于binlog是二进制的，所以不能直接打开。

    mysqlbinlog -v "/var/lib/mysql/binlog/binlog.000002"，使用此命令打开binlog.000002的日志文件，-v表示以伪SQL的形式表现出来

    

  • show binlog events

    由于mysqlbinlog命令一次全部打开，不容易查看

    show binlog events以事件为单位进行记录增删改操作

    

    

使用binlog恢复数据

• Point-in-Time (Incremental) Recovery
• 语法如下：mysqlbinlog [option] filename|mysql –uuser -ppass;表示的意思是从filename中读取内容，然后使用mysql命令将这些内容恢复到数据库中。
  • filename：binlog文件
  • option：可选项
    • --start-date 和 --stop-date ：可以指定恢复数据库的起始时间点和结束时间点。
    • --start-position和--stop-position ：可以指定恢复数据的开始位置和结束位置。
• 多个文件进行恢复时，需要注意回复顺序，编号小的在前，编号大的在后。
• 演示

  先向表中插入3条记录，然后修改其中的一条记录

  

  然后一不小心，将记录全删除了，然后我们通过binlog来恢复数据

  

  首先，为了保证binlog不再继续进行写入，需要将当前binlog备份出来，后续的记录写入新的binlog中

  

  这里演示根据pos进行恢复，首先通过show binlog events查看

  可以分析出我们的操作对应的Postion

  

  恢复3条insert记录

  mysqlbinlog --start-position=25782 --stop-position=26615 /var/lib/mysql/binlog.000010 | mysql -v -t test_db2 -uroot -p

  

删除binlog

• binlog可以通过过期时间自动删除，也可以手动删除

• 删除指定的日志文件，语法如下

  PURGE {MASTER | BINARY} LOGS TO ‘指定日志文件名’   # 执行文件名之前的文件会被删除
  PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’  # 指定日期之前的文件会被删除
  

• 删除全部的binlog：RESET MASTER

binlog写入机制

• 事务执行过程中，先把日志写到**binlog cache**，事务提交的时候，再把**binlog cache**写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

  

• 刷盘策略：write和fsync时机，由参数 sync_binlog 控制，默认是 0

  • 0，为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。虽然性能得到提升，但是机器宕机，page cache里面的binglog 会丢失。如下图：

    

  • 1，表示每次提交事务都会执行write和fsync，就如同redo log 刷盘流程一样。

  • N(N > 1)，表示每次提交事务都write，但累积N个事务后才fsync。在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。同样的，如果机器宕机，会丢失最近N个事务的binlog日志。

    

binlog与redolog对比

• Redo Log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎层产生的。
• 而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。
• 虽然二者都属于持久性的保证，但是侧重点不同：
  • Redo Log让InnoDB存储引擎拥有了崩溃恢复能力
  • binlog保证了MySQL集群架构的数据一致性

两阶段提交

• 在执行更新语句过程中，会记录Redo Log与binlog两块日志，以基本的事务为单位，Redo Log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以Redo Log与binlog的 写入时机不一样。

  

• 由于两种日志写入的时机不同，可能导致一些问题

  比如，在执行过程中，事务提交，Redo Log成功保存到磁盘，但是binlog还未保存到磁盘就出现了故障

  

  由于binlog中没有故障前的修改记录。因此，从库使用binlog进行数据恢复的时候，就会少了这次更新。而主库使用RedoLog进行恢复，可以成功恢复。

  这就导致主从不一致的问题。

  

  为了解决两份日志之间的逻辑一致问题。InnoDB存储引擎使用两阶段提交方案。

• 两阶段提交方案

  将Redo Log的写入拆分成两个步骤，prepare和commit，这就是两阶段提交。

  这两个阶段分别处于写入binlog的前和后。

  

  使用两阶段提交后，写入binlog时发生异常也不会有影响，因为MySQL根据Redo Log进行恢复数据时，发现Redo Log还处于prepare阶段，并且没有对应的binlog日志，就会回滚事务。

  

  Redo Log的commit阶段发生异常，那会不会回滚事务呢？

  

  并不会回滚事务，它会执行上图框住的逻辑，虽然Redo Log是处于prepare阶段，但是能通过事务id找到对应的binlog日志，所以MySQL认为是完整的，就会提交事务恢复数据。

总结

• 二进制日志可以通过数据库的全量备份和二进制日志中保存的增量信息 ，完成数据库的无损失恢复 。但是，如果遇到数据量大、数据库和数据表很多（比如分库分表的应用）的场景，用二进制日志进行数据恢复，是很有挑战性的，因为起止位置不容易管理。
• 在这种情况下，一个有效的解决办法是配置主从数据库服务器 ，甚至是一主多从的架构，把二进制日志文件的内容通过中继日志，同步到从数据库服务器中，这样就可以有效避免数据库故障导致的数据异常等问题。

中继日志（Relay log）

• 中继日志只在主从服务器架构的从服务器上存在。

• 从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志 。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的 数据同步 。

  

• 搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。

• 文件名的格式是： 从服务器名 -relay-bin.序号 。中继日志还有一个索引文件： 从服务器名 -relay- bin.index ，用来定位当前正在使用的中继日志。
• 中继日志也是二进制格式的
• 中继日志中包含服务器名，所以如果使用中继日志进行恢复，需要确保服务器名一致。