MySQL日志介绍

在任何一种数据库中，都会有各种各样的日志，记录着数据库工作的各种记录，以帮助数据库管理员追踪数据库曾经发生过的各种事件

日志分类

1. 错误日志（error log）

• 错误日志是MySQL中最重要的日志之一，它记录了mysqld启动、停止相关信息，以及服务器启动、停止和在运行过程中发生的诊断信息，如错误、警告等。当数据库出现故障导致无法正常使用时，可以首先查看此日志
• 此日志是默认开启的，默认存放目录为MySQL的数据目录，默认的日志文件名为hostname.err（hostname是主机名）

日志位置查看指令

show variables like 'log_error%';

2. 查询日志（general log）

• 查询日志中记录了客户端的所有操作语句，默认关闭

日志状态查询指令

-- 查看MySQL是否开启了查询日志
SHOW VARIABLES LIKE 'general_log';

-- 开启查询日志
SET GLOBAL general_log = 1;

通过配置开启

# 开启查询日志，0表示关闭，1表示开启
general_log=1

# 设置日志的文件名，不指定则默认为host_name.log
general_log_file=file_name

4. 慢查询日志

• 慢查询日志记录了所有执行时间超过参数long_query_time值且扫描记录数不小于min_examined_row_limit的所有SQL语句记录
• long_query_time默认为10秒，最小为0，精度可以到微秒

-- 查看慢查询日志是否开启
SHOW VARIABLES LIKE 'slow_query_log%';

-- 开启慢查询日志
SET GLOBAL slow_query_log=1;

-- 查看慢查询时间阈值
SHOW VARIABLES LIKE 'long_query_time%';

-- 设置慢查询时间阈值
SET GLOBAL long_query_time=1;

-- 记录执行较慢的语句，1开启，默认0关闭
SET GLOBAL log_slow_admin_statements = 1;

-- 记录未使用索引的语句，1开启，默认0关闭
SET GLOBAL log_queries_not_using_indexes = 1;

# 设置慢查询日志开启或关闭，1开启，默认0关闭
slow_query_log=1

# 指定慢查询日志文件名
slow_query_log_file=slow_query.log

# 设置超时时间
long_query_time=10

2. 二进制日志（binlog）

说明：二进制日志记录了除查询语句（select、show）外所有DDL（create、akter、drop）语句和DML（select，update，insert，delete）语句记录
作用： ①灾难时数据库恢复 ②实现MySQL的主从复制

• MySQL8.0默认开启二进制日志，低版本的MySQL需要通过配置文件开启，并配置MySQL日志格式
• 配置文件位置：Windows：my.ini； Linux：my.cnf

# 开启binlog日志
log_bin=mysqlbin

# 配置二进制日志的格式
binlog_format=STATEMENT

日志格式

• 通过MySQL提供的mysqlbinlog工具，可以查看每条语句的文本

  • STATEMENT：记录的都是SQL语句，对数据库进行的所有非查询SQL操作都会记录在日志文件中
  • ROW：记录的是每一行的数据变更，而不是SQL语句
  • MIXED：混合了MTATEMENT和ROW两种格式

binlog写入策略

• 在事务过程中，首先会写入到bunlog cashe中，事务提交后再把binlog写入到磁盘中；写入时机则有sync_binlog配置来决定

  • sync_binlog为0表示事务提交后binlog不会马上写入到磁盘，而是先写到page cache，相对于磁盘写会快很多，不过有日志丢失风险
  • sync_binlog为1表示每次事务提交后都会执行fsync写入到磁盘
  • sync_binlog大于1表示每次事务提交后都先写page cache，只有等到积累了N个事务之后才fsync写入到磁盘，此设置同样有丢失日志风险

日志查看指令

-- 查询MySQL是否开启了binlog日志
SHOW VARIABLES LIKE 'log_bin';

-- 查询binlog日志的格式
SHOW VARIABLES LIKE 'binlog_format';

-- 查询所有日志
SHOW BINLOG EVENTS;

-- 查询最新的日志
SHOW MASTER STATUS;

-- 查询指定的binlog日志
SHOW BINLOG EVENTS IN 'binlog.000008';

-- 从指定的binlog的指定位置开始查询日志
SHOW BINLOG EVENTS IN 'binlog.000008' FROM 7623208;

-- 从指定的binlog的指定位置开始查询指定条数日志
SHOW BINLOG EVENTS IN 'binlog.000008' FROM 7623208 LIMIT 5;
SHOW BINLOG EVENTS IN 'binlog.000008' FROM 7623208 LIMIT 1, 5;

-- 查询binlog写入策略
SHOW VARIABLES LIKE 'sync_binlog'

-- 查询binlog过期删除开关状态
SHOW VARIABLES LIKE 'binlog_expire_logs_auto_purge';

-- 查询binlog过期时间
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

-- 清空所有的binlog日志文件
RESET MASTER

5.中继日志（relaylog）

• 在从服务器（Slaver）中暂存从主服务器（master）同步过来的二进制日志（binlog）数据，它的作用主要体现在以下几个方面：

  • 存储二进制日志内容：从服务器 的I/O线程将 主服务器 的二进制日志读取过来，并记录到从服务器本地的中继日志文件中
  • 数据同步的中介：中继日志作为临时的日志文件，它的内容包括了从主服务器节点同步过来的二进制日志内容。从服务器的SQL线程会读取这些内容，并将其应用到从服务器上，从而使得从服务器和主服务器的数据保持一致
  • 文件命名与存储：搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。文件名的格式通常是“从服务器名-relay-bin.序号”，并且还有一个索引文件“从服务器名-relay-bin.index”用来定位当前正在使用的中继日志
  • 保障数据一致性：通过读取和应用中继日志的内容，从服务器可以根据主服务器的变化来更新自己的数据，完成数据同步的过程
  • 主从复制过程中的角色：在主主复制或主从复制的过程中，会在从服务器的home目录下面产生相应的中继日志文件，这些文件包含了描述数据库变化的事件的集合，以及一个索引文件

6.回滚日志（undo log）

• undo log属于InnoDB引擎日志，记录数据库的历史版本信息，用来保证事务操作的原子性和实现MVCC多版本读写

在MySQL数据每次修改前，都会先将修改之前的数据、最后一次操作该数据的事务ID（DB_TRX_ID）、上一份数据的版本（DB_ROLL_PTR）等信息保存到undo log作为备份，当事务回滚时可根据此事务ID进行

当我们对表数据进行修改时，首先获取一个事务ID；然后将改之前数据记录一份undo log；开始修改数据；将数据库事务ID改为当前最新事务ID，并把DB_TRX_ID地址指向undo log数据地址

7.预写日志（redo log）

• redo log 属于InnoDB引擎日志，为了支持InnoDB事务特性而设计的；redo log的核心职责就是保证数据不因宕机等异常造成数据错误或丢失从而保证数据的持久性和一致性

redo log写入策略

• redo log占用的空间大小是固定的，因其是顺序写所以性能较高。当空间占满后又会从头开始覆盖式写入

在写redo log的时候也有一个redo log buffer，日志在什么时候刷盘是通过innodb_flush_log_at_trx_commit参数决定

• innodb_flush_log_at_trx_commit为0表示每次事务提交时都只是把redo log留在redo log buffer中
• innodb_flush_log_at_trx_commit为1表示每次事务提交时都将redo log直接持久化到磁盘
• innodb_flush_log_at_trx_commit为2表示每次事务提交都只是把redo log写到page cashe

除了上面几种机制外，还有其它两种情况会把redo log buffer中的日志刷到磁盘
1.定时处理：有线程会定时（每秒）把redo log buffer中的数据刷盘
2.根据空间处理：redo log buffer占用到了一定程度（innodb_log_buffer_size设置的值一半）时也会把redo log buffer中的数据刷盘

日志查看指令

-- 查看redo log刷盘策略
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'

MySQL日志生成流程及数据恢复

当我们执行将小李改为小王时，数据库实际流程大致如下：

1. 从磁盘中读取存储小李的这条记录并保存到内存中
2. 记录undo log日志
3. 记录redo log日志，但此时日志状态记录为prepare
4. 修改内存数据
5. 记录binlog
6. 提交事务并将redo log状态改为commit

当数据库执行SQL过程出现异常，来分析一下数据恢复步骤

1. 在执行1-3步时发生异常，数据库实际数据未发生改变，无影响
2. 在执行4-5步时发生异常，此时事务未提交，使用undo log进行回滚操作即可恢复数据
3. 在执行第6步时发生异常，则要看binlog是否已经刷盘写入文件
   3-1. 如果已写入binlog文件，则数据库可以根据binlog及redo log日志匹配继续后续流程
   3-2. 如果未写入binlog文件，则使用undo log进行回滚操作即可恢复数据