MySQL - 日志

2.2k
MySQL / 日志篇
SQL

日志分类 ​

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

MySQL日志主要包括六种：

  1. 重做日志（redo log）
  2. 回滚日志（undo log）
  3. 归档日志（binlog）（二进制日志）
  4. 错误日志（errorlog）
  5. 慢查询日志（slow query log）
  6. 一般查询日志（general log）
  7. 中继日志（relay log）

错误日志 ​

错误日志是 MySQL 中最重要的日志之一，记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志

该日志是默认开启的，默认位置是：/var/log/mysql/error.log

查看指令：

sql
SHOW VARIABLES LIKE 'log_error%';

查看日志内容：

sh
tail -f /var/log/mysql/error.log

归档日志 ​

基本介绍 ​

归档日志（BINLOG）也叫二进制日志，是因为采用二进制进行存储，记录了所有的 DDL（数据定义语言）语句和 DML（数据操作语言）语句，但不包括数据查询语句，在事务提交前的最后阶段写入

作用：灾难时的数据恢复和 MySQL 的主从复制

归档日志默认情况下是没有开启的，需要在 MySQL 配置文件中开启，并配置 MySQL 日志的格式：

sh
cd /etc/mysql
vim my.cnf

# 配置开启binlog日志， 日志的文件前缀为 mysqlbin -----> 生成的文件名如: mysqlbin.000001
log_bin=mysqlbin
# 配置二进制日志的格式
binlog_format=STATEMENT

日志存放位置：配置时给定了文件名但是没有指定路径，日志默认写入MySQL 的数据目录

日志格式：

  • STATEMENT：该日志格式在日志文件中记录的都是 SQL 语句，每一条对数据进行修改的 SQL 都会记录在日志文件中，通过 mysqlbinlog 工具，可以查看到每条语句的文本。主从复制时，从库会将日志解析为原语句，并在从库重新执行一遍

    缺点：可能会导致主备不一致，因为记录的 SQL 在不同的环境中可能选择的索引不同，导致结果不同

  • ROW：该日志格式在日志文件中记录的是每一行的数据变更，而不是记录 SQL 语句。比如执行 SQL 语句 update tb_book set status='1'，如果是 STATEMENT，在日志中会记录一行 SQL 语句； 如果是 ROW，由于是对全表进行更新，就是每一行记录都会发生变更，ROW 格式的日志中会记录每一行的数据变更

    缺点：记录的数据比较多，占用很多的存储空间

  • MIXED：这是 MySQL 默认的日志格式，混合了STATEMENT 和 ROW 两种格式，MIXED 格式能尽量利用两种模式的优点，而避开它们的缺点

日志刷盘 ​

事务执行过程中，先将日志写（write）到 binlog cache，事务提交时再把 binlog cache 写（fsync）到 binlog 文件中，一个事务的 binlog 是不能被拆开的，所以不论这个事务多大也要确保一次性写入

事务提交时执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache

write 和 fsync 的时机由参数 sync_binlog 控制的：

  • sync_binlog=0：表示每次提交事务都只 write，不 fsync
  • sync_binlog=1：表示每次提交事务都会执行 fsync
  • sync_binlog=N(N>1)：表示每次提交事务都 write，但累积 N 个事务后才 fsync，但是如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志

日志读取 ​

日志文件存储位置：/var/lib/mysql

由于日志以二进制方式存储，不能直接读取，需要用 mysqlbinlog 工具来查看，语法如下：

sh
mysqlbinlog log-file;

查看 STATEMENT 格式日志：

  • 执行插入语句：

    sql
    INSERT INTO tb_book VALUES(NULL,'Lucene','2088-05-01','0');

  • cd /var/lib/mysql：

    sh
    -rw-r-----  1 mysql mysql      177 5月  23 21:08 mysqlbin.000001
-rw-r-----  1 mysql mysql       18 5月  23 21:04 mysqlbin.index

    mysqlbin.index：该文件是日志索引文件 ， 记录日志的文件名；

    mysqlbing.000001：日志文件

  • 查看日志内容：

    sh
    mysqlbinlog mysqlbing.000001;
    image-20250320013217813

    日志结尾有 COMMIT

查看 ROW 格式日志：

  • 修改配置：

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

  • 插入数据：

    sql
    INSERT INTO tb_book VALUES(NULL,'SpringCloud实战','2088-05-05','0');

  • 查看日志内容：日志格式 ROW，直接查看数据是乱码，可以在 mysqlbinlog 后面加上参数 -vv

    sql
    mysqlbinlog -vv mysqlbin.000002
    image-20250320013258023

日志删除 ​

对于比较繁忙的系统，生成日志量大，这些日志如果长时间不清除，将会占用大量的磁盘空间，需要删除日志

  • Reset Master 指令删除全部 binlog 日志，删除之后，日志编号将从 xxxx.000001重新开始

    sql
    Reset Master	-- MySQL指令

  • 执行指令 PURGE MASTER LOGS TO 'mysqlbin.***，该命令将删除 *** 编号之前的所有日志

  • 执行指令 PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss' ，该命令将删除日志为 yyyy-mm-dd hh:mm:ss 之前产生的日志

  • 设置参数 --expire_logs_days=#，此参数的含义是设置日志的过期天数，过了指定的天数后日志将会被自动删除，这样做有利于减少管理日志的工作量，配置 my.cnf 文件：

    sh
    log_bin=mysqlbin
binlog_format=ROW
--expire_logs_days=3

数据恢复 ​

误删库或者表时，需要根据 binlog 进行数据恢复

一般情况下数据库有定时的全量备份，假如每天 0 点定时备份，12 点误删了库，恢复流程：

  • 取最近一次全量备份，用备份恢复出一个临时库
  • 从日志文件中取出凌晨 0 点之后的日志
  • 把除了误删除数据的语句外日志，全部应用到临时库

跳过误删除语句日志的方法：

  • 如果原实例没有使用 GTID 模式，只能在应用到包含 12 点的 binlog 文件的时候，先用 –stop-position 参数执行到误操作之前的日志，然后再用 –start-position 从误操作之后的日志继续执行
  • 如果实例使用了 GTID 模式，假设误操作命令的 GTID 是 gtid1，那么只需要提交一个空事务先将这个 GTID 加到临时实例的 GTID 集合，之后按顺序执行 binlog 的时就会自动跳过误操作的语句

查询日志 ​

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的 SQL 语句

默认情况下，查询日志是未开启的。如果需要开启查询日志，配置 my.cnf：

sh
# 该选项用来开启查询日志，可选值0或者1，0代表关闭，1代表开启 
general_log=1
# 设置日志的文件名，如果没有指定，默认的文件名为host_name.log，存放在/var/lib/mysql
general_log_file=mysql_query.log

配置完毕之后，在数据库执行以下操作：

sql
SELECT * FROM tb_book;
SELECT * FROM tb_book WHERE id = 1;
UPDATE tb_book SET name = 'lucene入门指南' WHERE id = 5;
SELECT * FROM tb_book WHERE id < 8

执行完毕之后， 再次来查询日志文件：

image-20250320013318003

慢日志 ​

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

慢查询日志默认是关闭的，可以通过两个参数来控制慢查询日志，配置文件 /etc/mysql/my.cnf：

sh
# 该参数用来控制慢查询日志是否开启，可选值0或者1，0代表关闭，1代表开启 
slow_query_log=1 

# 该参数用来指定慢查询日志的文件名，存放在 /var/lib/mysql
slow_query_log_file=slow_query.log

# 该选项用来配置查询的时间限制，超过这个时间将认为值慢查询，将需要进行日志记录，默认10s
long_query_time=10

日志读取：

  • 直接通过 cat 指令查询该日志文件：

    sh
    cat slow_query.log
    image-20250320013338559

  • 如果慢查询日志内容很多，直接查看文件比较繁琐，可以借助 mysql 自带的 mysqldumpslow 工具对慢查询日志进行分类汇总：

    sh
    mysqldumpslow slow_query.log
    image-20250320013349361
Q.E.D.
MySQL - 表
MySQL - 主从