【MySQL】日志

schdut

已于 2023-02-04 16:49:47 修改

阅读量533

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

于 2023-01-28 09:54:11 首次发布

本文链接：https://blog.csdn.net/shengchaohua163/article/details/128775095

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

文章目录

https://www.cnblogs.com/myseries/p/10728533.html

在 MySQL 中，有多种不同的日志，包括错误日志、二进制日志、查询日志和慢查询日志，这些日志发挥着不同的作用。另外还有redo日志、undo日志和relay日志。

错误日志

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

错误日志是默认开启的，可以通过下面的命令查看错误日志的位置：

mysql> show variables like 'log_error%';
+---------------------+---------------------+
| Variable_name       | Value               |
+---------------------+---------------------+
| log_error           | /var/log/mysqld.log |
| log_error_verbosity | 3                   |
+---------------------+---------------------+
2 rows in set (0.03 sec)

二进制日志

https://www.jb51.net/article/273118.htm#_lab2_0_0

二进制日志（Binary Log，binlog）记录了二进制格式的 SQL语句，包括DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。

binlog主要用于主从复制，以及数据备份。具体地，MySQL使用binlog在主数据库和从数据库之间同步数据，保证数据一致性。

binlog有三种格式：

1）STATEMENT

该日志格式记录的是SQL语句（STATEMENT）。主从复制时，主数据库把binlog发送到从数据库，然后从数据库会把binlog解析为SQL语句，并重新执行。这里有一个问题，如果SQL中包含update_time=now()等类似的函数，该函数获取当前系统时间，直接执行会导致数据与原库的数据不一致。

2）ROW

该日志格式记录的是每一行的数据变更，而不是SQL语句。举例，对于UPDATE test_tab SET status=1;，如果是STATEMENT格式，在日志中会记录SQL语句；如果是ROW格式，在日志中会记录每一行的数据修改，因为该SQL语句是对全表进行更新，也就是每一行记录都会被修改。

3）MIXED

该日志格式混合了STATEMENT 和 ROW两种格式，是一种折中的方案。MIXED 格式能尽量利用两种模式的优点，而避开他们的缺点。MySQL会判断这条SQL语句是否可能导致数据不一致，如果可能导致数据不一致就用ROW格式，否则就用STATEMENT格式。

如何开启binlog

默认情况下，二进制日志是没有开启的。下面的命令可以用来查看是否开启二进制日志，

mysql> show variables like 'log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

默认情况下，二进制日志的格式为ROW。下面的命令可以用来查看二进制日志的格式，

mysql> show variables like 'binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

如果要设置开启binlog以及binlog的格式，需要在MySQL的配置文件设置log_bin和binlog_format，其中log_bin用于指定设置binlog的文件名，相当于开启binlog；binlog_format用于指定binlog的格式。

# 配置binlog文件名为mysqlbin，那么生成的文件如mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin
# 配置二进制日志的格式
binlog_format=STATEMENT/ROW/MIXED

如何查看binlog

由于日志以二进制方式存储，不能直接读取，mysqlbinlog工具可以用来解析和查看日志。mysqlbinlog的语法可以参考：https://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog.html。

写入磁盘

binlog的写入磁盘流程大致如下图所示：
在这里插入图片描述

解释：

在事务执行过程中，MySQL把binlog写到binlog cache。
当事务提交时，MySQL把binlog cache写入文件系统，有两种方式：（1）只写入文件系统缓存page cache，（2）先写入文件系统缓存page cache，接着再写入磁盘的binlog文件中。如果是前者，写入磁盘的时间将由操作系统决定。
write只是把binlog cache写入到文件系统缓存，并没有把数据持久化到磁盘，所以速度比较快，而 fsync是指把数据持久化到磁盘。

因为一个事务的binlog不能被拆开，无论事务多大，也要确保一次性写入，所以系统会给每个线程分配一块内存作为binlog cache，用于缓存事务的binlog。另外，通过设置binlog_cache_size参数可以控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存binlog cache到磁盘。

下面的命令可以用来查看binlog_cache_size参数：

mysql> show variables like 'binlog_cache_size%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |
+-------------------+-------+
1 row in set (0.00 sec)

另外，通过设置sync_binlog参数可以控制MySQL写入binlog cache到文件系统的方式。该参数可能为0、1或者一个正整数N，默认值为1，可以通过下面的命令查看：

mysql> show variables like 'sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.00 sec)

1）当sync_binlog=0时，表示每次提交事务都只执行write，由系统自行判断什么时候执行fsync。这种方式速度比较快，但是如果机器宕机，page cache中的binlog可能会丢失，如下图所示。
在这里插入图片描述

2）当sync_binlog=1时，表示每次提交事务都会执行write和fsync。虽然这种方式速度慢一些，但是更安全。

3）当sync_binlog=N时，这是一个折中方式，表示每次提交事务都write，但累积N个事务后才执行fsync。在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。同样地，如果MySQL服务器宕机，会丢失最近N个事务的binlog日志。

过期日志删除

对于比较繁忙的系统，每天会生成大量日志。这些日志如果长时间不清除，将会占用大量的磁盘空间。删除日志有几种常用方法：

# 删除全部 binlog 日志，删除之后，日志编号将从 xxxx.000001 重新开始。
reset master;
# 删除 ****** 编号之前的所有日志
purge master logs to 'mysqlbin.******'
# 删除 某一时间 之前产生的所有日志
purge master logs before 'yyyy-mm-dd hh24:mi:ss'

另外，还有一种定期删除日志的方法，需要在MySQL配置文件中设置日志过期参数，如下所示：

# 设置日志过期时间，过期日志将会被自动删除
--expire_logs_days=xxx

普通查询日志

普通查询日志（General query log）用来记录服务器接收到的每一个查询或命令。无论查询或是命令是否正确，普通查询日志都会将其记录下来，记录的格式为{Time, Id, Command, Argument}。

普通查询日志默认是关闭的，因为开启普通查询日志会导致MySQL服务器不断地记录日志，系统开销比较大。下面的命令可以用来查看普通查询日志是否开启以及日志文件的位置，

mysql> show variables like 'general_log%';
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | OFF                             |
| general_log_file | /var/lib/mysql/9eca2f63e027.log |
+------------------+---------------------------------+
2 rows in set (0.00 sec)

如果要开启普通查询日志，可以在MySQL配置文件中添加如下的配置：

# 设置是否开启查询日志，0表示关闭，1表示开启
general_log=1
# 设置日志的文件名，如果没有指定，默认的文件名为 host_name.log
general_log_file=<file_name>

慢查询日志

慢查询日志（Slow query log）用来记录执行时间过长和没有使用索引的查询语句。

慢查询日志默认是关闭的。下面的命令可以用来查看是否开启慢查询日志以及日志文件的位置，

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/9eca2f63e027-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)

具体来说，慢查询日志记录了执行时间超过long_query_time参数并且扫描记录数不小于min_examined_row_limit参数的查询语句。下面的命令可以用来查看这两个参数的值，

mysql> show variables like 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)

mysql> show variables like 'min_examined_row_limit%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

如果需要开启慢查询日志，可以在MySQL配置文件中添加如下的配置：

# 设置是否开启慢查询日志， 0代表关闭，1代表开启
slow_query_log=1
# 设置慢查询日志的文件名
slow_query_log_file=slow_query.log
# 设置查询的时间限制，超过这个时间将认为是慢查询，需要进行日志记录，默认为10秒
long_query_time=10

和错误日志、查询日志一样，慢查询日志的内容也是纯文本，可以使用cat命令直接读取日志文件。如果慢查询日志比较多，查看文件比较麻烦，可以借助于MySQL自带的mysqldumpslow工具，来对慢查询日志进行分类和汇总。

redo log

https://www.jb51.net/article/273118.htm

redo log可以称为重做日志，是Innodb存储引擎自带的日志，记录了事务操作的变化。redo log主要用于MySQL的崩溃恢复，保证事务的持久性。简单来说，如果MySQL服务崩溃时仍然有脏页没有写入磁盘，MySQL服务重启之后可以根据事务期间产生的redo log进行重做，恢复到崩溃之前的状态，从而达到事务的持久性。

MySQL 中数据是以页为单位，查询一条数据时，会把一页的数据加载出来，加载出来的数据格式可以称为数据页，并放入到 Buffer Pool 中。
通常来说，查询都是先从 Buffer Pool 中找，没有找到再去硬盘加载，这样能够减少硬盘 IO 开销，提升性能。同理，在更新数据时，如果发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。

redo log是物理日志，记录的是数据修改之后的值，具体来说是“在哪个数据页做了什么修改”。在一个事务中，只要修改数据就会产生redo log并保存到文件中。换句话说，不管事务是否提交，redo log都会记录下来。

redo log和binlog的区别在于：

redo log是在InnoDB存储引擎层产生的。binlog和存储引擎没有关系，只要有数据更改，都会产生binlog。
日志内容不同。redo log是物理日志，记录的是数据修改之后的值；binlog是逻辑日志，记录的是SQL语句或数据修改。
写入磁盘的时间点不同。binlog只在事务提交完成后写入磁盘；redo log在事务进行中不断地被写入，而不是在事务提交后写入。
日志文件生成方式不同。binlog在一个文件写满或者MySQL重启之后，会创建一个新的binlog文件，用文件后缀标明日志文件的顺序。redo log使用了一个日志文件组，并循环使用每一个文件，如果日志文件组中的最后一个文件写满，会重新写入日志文件组的第一个文件。
用途不同。binlog主要用于主从复制，MySQL使用binlog来同步数据，保证主从数据库的数据一致性；redo log主要用于MySQL的崩溃恢复，MySQL重启之后使用redo log恢复到崩溃之前的状态。

写入磁盘

需要注意的是，redo log并不会直接写入日志文件，而是先写入重做日志缓存（redo log buffer），接着再把缓存中的数据写入到磁盘的日志文件中（也可以称为刷盘）。写入磁盘的时机由innodb_flush_log_at_trx_commit 参数控制，该参数可能为0、1或2：

innodb_flush_log_at_trx_commit=0，表示每次事务提交时不进行写入磁盘的操作。
innodb_flush_log_at_trx_commit=1，表示每次事务提交时都进行写入磁盘的操作，是默认值。
innodb_flush_log_at_trx_commit=2，表示每次事务提交时都只把 redo log buffer 内容写入文件系统缓存page cache。

通过下面的命令查看：

mysql> show variables like 'innodb_flush_log_at_trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

另外，InnoDB 存储引擎有一个后台线程，每隔1 秒会把 redo log buffer 中的内容写到文件系统缓存page cache，然后调用 fsync 刷入到磁盘。换句话说，无论产生redo log的事务是否提交，redo log都会定期写入到磁盘。

如下图所示，
在这里插入图片描述

除了后台线程每秒1次的轮询之外，当 redo log buffer 占用的空间即将达到 innodb_log_buffer_size参数一半时，后台线程也会主动把redo log buffer 中的内容写入到磁盘。

日志文件组

磁盘上的 redo log 日志文件不只一个，而是以一个日志文件组的形式工作的，每个的redo log文件大小都是一样的。

在这里插入图片描述

如上图所示，redo log的磁盘有固定的空间，一个文件写满后，就会使用下一个文件。

两阶段提交

binlog保证了MySQL集群架构的数据一致性，而redo log让InnoDB存储引擎拥有了崩溃恢复能力。虽然二者都属于持久化的保证，但是侧重点不同。

另外，redo log与binlog的写入时机不一样。以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入。

正是由于redo log与binlog的写入时机不一样，二者可能产生不一致的问题。以UPDARE T SET c=1 where id=2;为例，

1）假设执行过程中写完redo log日志后，binlog日志写磁盘期间发生了异常。如下图所示，
在这里插入图片描述

由于binlog没写完发生了异常，binlog里面没有对应的修改记录，但是redo log可能已经记下了这次修改。当MySQL服务器重启后，主数据库使用redo log可以恢复到正确的状态，c列的值为1；而从数据库使用binlog恢复数据时，就会缺少这次修改，导致数据不一致。

为了解决redo log和binlog之间的逻辑不一致问题，InnoDB存储引擎使用两阶段提交的方法。简单来说，InnoDB将redo log的写入拆成了prepare和commit两个阶段，如下图所示。
在这里插入图片描述

具体地，

在prepare阶段，MySQL写入redo log，并把此时的redo log标记为prepare阶段。
在commit阶段，MySQL先写入binlog，然后把redo log标记为commit阶段。

使用两阶段提交方法后，即使写入binlog时发生异常也不会有影响。因为在MySQL重启之后，根据redo log恢复数据时，可以发现redo log还处于prepare阶段，而且没有对应binlog，就会回滚该事务。

再看另一个场景，如果写入redo log在commit阶段发生异常，那MySQL会不会回滚事务呢？答案是不会。因为此时binlog已经完全写入，虽然redo log是处于prepare阶段，但是MySQL能通过事务ID找到完整的binlog，所以MySQL认为是redo log和binlog是一致的，就会提交事务恢复数据。

undo log

https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-logs.html

undo log可以称为回滚日志，也是Innodb存储引擎自带的日志。undo log是一个与读写事务相关的回滚日志记录的集合。一个undo log记录包含了一些信息，用于撤消一个事务在一个聚簇索引的最新更改。如果另一个事务需要将原始数据视为一致读取操作的一部分，则会从undo log记录中检索未修改的数据。

An undo log is a collection of undo log records associated with a single read-write transaction. An undo log record contains information about how to undo the latest change by a transaction to a clustered index record. If another transaction needs to see the original data as part of a consistent read operation, the unmodified data is retrieved from undo log records.

undo log的一个用途是事务回滚。如果一个事务的执行过程中发生错误，MySQL可以利用 undo log中的信息回滚事务，将数据回滚到事务之前的状态。另外，undo log会先于数据持久化到磁盘上。这保证了即使事务执行过程中遇到数据库突然宕机等极端情况，在重启之后，MySQL还能够通过查询undo log来回滚还没有完成的事务。

undo log的另一个用途是在MVCC的实现中，保存一条数据的历史版本。在一个快照读中，InnoDB 首先通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如果不可见，则通过数据行的 DB_ROLL_PTR 在undo log 中查找数据的历史版本。

总结，InnoDB 存储引擎使用 redo log 保证事务的持久性，使用 undo log 来保证事务的原子性。