【MySQL系列】- MySQL日志详解

【MySQL系列】- MySQL日志详解

文章目录

对于线上数据库应用系统，突然遭遇数据库宕机的情况下，可以查看数据库错误日志来定位具体的问题。因为日志中记录了数据库运行中的诊断信息，包含错误，警告与注解等信息。

数据日志除了可以发现错误，日志在数据复制，数据恢复，操作审计及确保数据的永久性和一致性等方面，都有着不可替代的作用。

日志作用非常大，很多奇怪的问题，都可以通过日志找到答案。所有在MySQL中要养成查看日志的习惯，提升数据开发运维的能力。

一、My日志分类

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

下面会结合场景详解讲解每个日志应用及其使用场景

二、重做日志（redo log）

redo 日志是为了在系统因奔溃而重启时恢复奔溃前的状态而提出的。lnnoDB 存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作从本质上来说都是在访问页面（包括读页面、写页面、创建新页面等操作）。在真正访问页面之前，需要先把在磁盘中的页加载到内存中的[[Buffer Pool]] 中，之后才可以访问。事务的持久性的特征要求对于一个己经提交的事务，在事务提交后即使系统发生了崩溃，这个事务对数据库所做的更改也不能丢失。
如果我们只在内存的 Buffer Pool 中修改了页面，假设在事务提交后突然发生了某个故障，导致内存中的数据都失效了，那么这个已经提交的事务在数据库中所做的更改也就跟着丢失了，这是我们所不能忍受的。那么，如何保证这个持久性呢？一个很简单的做法就是在事务提交完成之前，把该事务修改的所有页面都刷新到磁盘。不过这个简单粗暴的做法存在下面这些问题：

• 刷新一个完整的数据页太浪费了。有时我们仅仅修改了某个页面中的一个字节，但是由于 InnoDB 是以页为单位来进行磁盘 I/O 的，也就是说在该事务提交时不得不将一个完整的页面从内存中刷新到磁盘。一个页面的默认大小是 16 KB，因为修改了一个字节就要刷新 16KB 的数据到磁盘上，显然太浪费了。
• 随机 I/O 刷新起来比较慢。一个事务可能包含很多语句，即使是一条语句也可能修改许多页面，而且该事务修改的这些页面可能并不相邻。这就意味着在将某个事务修改的 Buffer Pool 中的页面刷新到磁盘时，需要进行很多的随机 I/O。随机 I/O 比顺序 I/O 要慢，尤其是对于传统的机械硬盘。

只是想让已经提交了的事务对数据库中的数据所做的修改能永久生效，即使后来系统崩溃，在重启后也能把这种修改恢复过来。所以没有必要在每次提交事务时就把该事务在内存中修改过的全部页面刷新到磁盘，只需要把修改的内容记录一下就好。这样在事务提交时，就会把上述内容刷新到磁盘中。即使之后系统崩溃了，重启之后只要按照上述内容所记录的步骤重新更新一下数据页，那么该事务对数据库中所做的修改就可以被恢复出来，这样也就意味着满足持久性的要求。

因为在系统因崩溃而重启时需要按照上述内容所记录的步骤重新更新一下数据页，所以上述内容也称为重做日志 （redo log）。 我们也可以将它称为 redo 日志。相较于在事务提交时将所有修改过的内存中的页面刷新到磁盘中，只将该事务执行过程中产生的 redo 日志刷新到磁盘具有下面这些好处：

• redo 日志占用的空间非常小；在存储表空间 ID、页号、偏移量以及需要更新的值时，需要的存储空间很小。
• redo 日志是顺序写入磁盘；在执行事务的过程中，每执行一条语句，就可能产生若干条 redo 日志，这些日志是按照产生的顺序写入磁盘的，也就是使用顺序 I/O。

redo 日志格式

redo 日志本质上只是记录了一下事务对数据库进行了哪些修改。InnoDB 针对事务对数据库的不同修改场景，定义了多种类型的 redo 日志，但是绝大部分类型的 redo 日志都有如下图所示的这种通用结构.

• type：这条 redo 日志的类型。
• space ID：表空间 ID。
• page number：页号。
• data：这条redo日志的具体内容。

简单日志格式

如果没有为某个表显式地定义主键，并且表中也没有定义不允许存储 NULL 值的 UNIQUE 键，那么 InnoDB 会自动为表添加一个名为 row_id 的隐藏列作为主键。为这个 row_id 隐藏列进行赋值的方式如下：

• 服务器会在内存中维护一个全局变量，每当向某个包含 row_id 隐藏列的表中插入一条记录时，就会把这个全局变量的值当作新记录的 row_id 列的值，并且把这个全局变量自增 1。
• 每当这个全局变量的值为 256 的倍数时，就会将该变量的值刷新到系统表空间页号为 7 的页面中一个名为 Max Row ID 的属性中 （之所以不是每次自增该全局变量时就将该值刷新到磁盘，是为了避免频繁刷盘）。
• 当系统启动时， 会将这个 Max Row ID 属性加载到内存中，并将该值加上 256 之后赋值给前面提到的全局变量（因为在系统上次关机时，该全局变量的值可能大于磁盘页面中 Max Row ID 属性的值）。

Max Row ID 属性占用的存储空间是 8 字节。当某个事务向某个包含 row id 隐藏列的
表插入一条记录，并且为该记录分配的 row_id 值为 256 的倍数时，就会向系统表空间页号为
7 的页面的相应偏移量处写入 8 字节的值。这个写入操作实际上是在 Buffer Pool 中完成的，我们需要把这次对这个页面的修改以 redo 日志的形式记录下来。这样在事务提交之后，即使系统崩溃了，也可以将该页面恢复成崩溃前的状态。在这种对页面的修改是极其简单的情况下，redo 日志中只需要记录一下在某个页面的某个偏移量处修改了几个字节的值、具体修改后的内容是啥就好了。InnoDB 把这种极其简单的 redo 日志称为物理日志，并且根据在页面中写入数据的多少划分了几种不同的 redo 日志类型。

• MLOG_1BYTE（type 字段对应的十进制数字为 1）：表示在页面的某个偏移量处写入 1 字节的 redo 日志类型。
• MLOG_2BYTE（type 字段对应的十进制数字为 2）：表示在页面的某个偏移量处写入 2 字节的 redo 日志类型。
• MLOG_4BYTE（type 字段对应的十进制数字为 4）：表示在页面的某个偏移量处写入 4 字节的 redo 日志类型。
• MLOG_8BYTE（type 字段对应的十进制数字为 8）：表示在页面的某个偏移量处写入 8 字节的 redo 日志类型。
• MLOG_WRITE_STRlNG（type 字段对应的十进制数字为 30）：表示在页面的某个偏移处写入一个字节序列。

Max Row ID 属性实际占用 8 字节的存储空间，所以在修改页面中的这个属性时，会记录一条类型为 MLOG_8BYTE 的 redo 日志。

【插入日志格式图1】

其余的 MLOG_1BYTE、MLOG_2BYTE、MLOG_4BYTE 类型的 redo 日志结构与 MLOG_8BYTE 的日志结构类似，只不过具体数据中包含的字节数量不同罢了。MLOG_WRITE_STRlNG 类型的 redo 日志表示写入一个字节序列，但是因为不能确定写入的具体数据占用多少字节，所以需要在日志结构中添加一个 len 字段。

【插入日志格式图2】

复杂的 redo 日志格式

• MLOG_REC_INSERT（type 字段对应的十进制数字为 9）：表示在插入一条使用非紧凑行格式 （REDUNDANT）的记录时，redo 日志的类型。
• MLOG_COMP_REC_INSERT（ type 字段对应的十进制数字为 38） ：表示在插入一条使用紧凑行格式 （COMPACT、DYNAMIC、COMPRESSED ） 的记录时，redo 日志的类型。
• MLOG_COMP_PAGE_CREATE（type 字段对应的十进制数字为 58）：表示在创建一个存储紧凑行格式记录的页面时，redo 日志的类型。
• MLOG_COMP_REC_DELETE（type 字段对应的十进制数字为 42 ）：表示在删除一条使用紧凑行格式记录时，redo 日志的类型。
• MLOG_COMP_LIST_START_DELETE（type 字段对应的十进制数字为 44）：表示在从某条给定记录开始删除页面中一系列使用紧凑行格式的记录时，redo 日志的类型。
• MLOG_COMP_LIST_END_DELETE（type 字段对应的十进制数字为 43）：与 MLOG_COMP_LIST_START_DELETE 类型的 redo 日志呼应，表示删除一系列记录，直到 MLOG_COMP_LIST_END_DELETE 类型的 redo 日志对应的记录为止。数据页中的记录按照索引列大小的顺序组成单向链表。有时，我们需要删除索引列的值在某个区间内的所有记录，这时如果每删除一条记录就写一条 redo 日志，效率可能有点低。MLOG_COMP_LUST_START_DELETE 和 MLOG_COMP_LIST_END_DELETE 类型的 redo 日志可以很大程度上减少 redo 日志的条数。
• MLOG_ZlP_PAGE_COMPRESS（type 字段对应的十进制数字为 51）:表示在压缩一个数据页时， redo 日志的类型。

复杂Redo日志既包含物理层面的意思，也包含逻辑层面的意思：

• 从物理层面看，这些日志都指明了对哪个表空间的哪个页进行修改；
• 从逻辑层面看，在系统崩溃后重启时，并不能直接根据这些日志中的记载，在页面内的某个偏移量处恢复某个数据，而是需要调用一些事先准备好的函数，在执行完这些函数后才可以将页面恢复成系统崩溃前的样子；

三、回滚日志（undo log）

回滚日志，保存了事务发生之前的数据的一个版本，用于事务的回滚操作，同时也是实现多版本并发控制（MVCC）下读操作（快照读）的关键技术。

undo log的作用

• 事务发生错误时回滚rollback，数据更新之前，会把原始数据保存在回滚日志中，保证事务出错回滚或者我们手动回滚的时候，能够在回滚日志中找到最初的数据。
• 提供了MVCC的非锁定读（快照读），依赖undo log实现。

undo log数据结构

• DB_ROW_ID：和MVCC的关系不大，这个是我们创建表的时候，如果没有加PRIMARY KEY，那么InnoDB就自动生成主键列id（DB_ROW_ID），通过这个id作为主键创建索引树，在B+树的叶子节点上存放数据。由于InnoDB的数据和索引是存放在一起的，如果我们没有设置主键，InnoDB会自动生成主键。
• DB_TRX_ID：事务ID，我们每打开一个客户端session，输入begin，向服务器请求开启一个事务。事务开启请求发到MySQL server上，MySQL server为每个事务都会分配一个全局的，不冲突的事务ID（InnoDB存储引擎分配的，因为它才支持事务）。当某个事务修改数据时，DB_TRX_ID放的就是该事务的ID，同一个事务无论怎么更改数据，这个事务ID都不会改变。
• DB_ROLL_PTR：回滚指针，我们看到，存放的是地址，这个地址表示的是一个数据的内存的位置。

四、二进制日志（bin log）

4.1 什么是 Bin Log

Bin Log是一个二进制格式的文件，是MySQL最重要的日志，它记录了所有的DDL及DML语句（除了数据查询select、show等），以事件形式记录，还包含语句所执行的消耗时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

4.2 应用场景

1. MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
2. 数据恢复：通过使用 mysqlbinlog工具来使恢复数据。

4.3 开启Binlog

一般来说开启binlog日志大概会有1%的性能损耗。启用binlog，通过配置 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件的 log-bin 选项在配置文件中加入 log-bin配置，表示启用binlog，如果没有给定值，写成 log-bin=，则默认名称为主机名。（注：名称若带有小数点，则只取第一个小数点前的部分作为名称）

[mysqld]
log-bin=my-binlog-name

也可以通过SET SQL_LOG_BIN = 1命令来启用Bin Log，通过SET　SQL_LOG_BIN =0 命令停用Bin Log

启用 binlog 之后须重启MySQL才能生效。

五、错误日志（error log）

rror log记录中MySQL启动和关闭服务的时候详细的日志信息，包括在MySQL实例在运行过程中遇到的错误、警告等信息也会记录在error log中。需要注意的是，这里的错误和警告并不是指我们在执行某些SQL语句的时候遇到的错误信息和警告信息，而是MySQL底层进程级别的错误和警告信息。

对于error log，我们平时使用的最多的场景是这样的：在为了满足某些需求的时候，我们需要添加或修改my.cnf配置文件中参数，然后重试重启MySQL实例，如果此时MySQL启动失败，我们会经常去看下error log中的错误提示信息，然后根据错误提示去做对应的调整。最后再次尝试重启MySQL服务。此时的error log会给我们提供很大的帮助。

error log的参数:

• log_error 这个参数控制着错误日志到底输出到哪里。如果是输出到控制台中，则该参数的值stderr，这表示是standard error，标准错误输出，也就是会把error log写到当前的命令行控制台中。如果不是把错误日志输出到stderr，则需要配置一个具体的文件路径和名称。例如把错误日志输出到/tmp/mysql_error.log文件中。此时我们定义了路径为/tmp，错误日志的文件名称为mysql_error.log。一般情况下，在使用yum和apt-get方式安装MySQL数据库的时候，MySQL的error log一般都是默认输出到/var/log/mysql目录下。
• log_error_verbosity：这个参数控制着error log的输出级别。参数取值为1、2、3中的其中一个值，默认值为3。建议使用默认值3。三个参数所代表的日志级别如下： - 1：只输出error级别的日志到error log中。- 2：输出error和warning级别的日志到error log中。- 3：输出error和warning还有note级别的日志到error log中。
• log_timestamps：这个参数控制着日志文件中输出的每一行日志中的时间戳是什么样的。这个参数的默认值为UTC，如果发现日志文件中时间戳和当前数据库服务器的系统时间不一致（这里说一个我经常遇到的问题，就是发现所有的日志信息，都是和服务器的时间差8个小时，就是因为log_timestamps参数的值为UTC，把它改为system之后，发现日志中时间戳就和当前系统时间一致了。），通常需要修改这个参数为SYSTEM，修改后，此时的日志总的时间戳就和服务器的系统时间一致了。这个参数不仅仅适用于error log，像前面我们介绍的slow query log慢查询日志中时间戳也是受这个参数控制。

六、慢查询日志（Slow Query Log）

日志用来记录在 MySQL 中执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率低，以便进行优化。

通俗的说，MySQL 慢查询日志是排查问题的 SQL 语句，以及检查当前 MySQL 性能的一个重要功能。如果不是调优需要，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

默认情况下，慢查询日志功能是关闭的。可以通过以下命令查看是否开启慢查询日志功能。命令和执行过程如下：

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

参数说明如下:

• slow_query_log：慢查询开启状态
• slow_query_log_file：慢查询日志存放的位置（一般设置为 MySQL 的数据存放目录）
• long_query_time：查询超过多少秒才记录

七、一般查询日志（general log）

在默认情况下，MySQL是不会打开general log的，这个log里面会记录MySQL所有的SQL语句，不管是查询语句，还是DML语句，还是DDL语句，还是DCL语句，这些语句统统都会被记录在general log文件中。就连我们连接和断开MySQL数据库的这些语句。

MySQL会把它收到的所有SQL语句按照接收的顺序依次记录在general log中。我们需要注意的是，这里接受的SQL语句的顺序，并不等于SQL语句就是按照这个接受的顺序来执行，因为有的时候，一些SQL可能需要等待其他锁被释放后才会被真正的执行，SQL语句的执行顺序是和binlog中的顺序是相匹配的。

General log默认不开启的原因有两个：

1. 日志将会非常大，对磁盘是一个很大的压力。因为所有的操作都会被记录下来
2. 对MySQL数据的性能有一定的影响。

如何开启 general log：

• 配置文件中永久开启：[mysqld]general_log=1general_log_file=mysql_general_log.loglog_output=/var/lib/mysql

• SQL命令行中临时开启show variables like 'general_log'; -- 查看日志是否开启show variables like 'general_log_file'; -- 看看日志文件保存位置show variables like 'log_output'; -- 看看日志输出类型 table或fileset session general_log=on; -- 这是一个全局参数，不能只针对当前session启用。 set global general_log=on; -- 对全局开启general log

八、一般查询日志（general log）

中继日志（relay log）只在主从服务器架构的从服务器上存在。从服务器（Slave）为了与主服务器(Master)保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步，起到了中介作用。

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

文件名的格式是：从服务器名 - relay-bin.序号。中继日志还有一个索引文件：从服务器名 - relay-bin.index，用来定位当前正在使用的中继日志。

中继日志的作用

中继日志用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。

中继日志是连接mastert(主服务器)和slave(从服务器)的信息，它是复制的核心，I/O线程将来自master的binlog存储到中继日志中，中继日志充当缓冲，这样master不必等待slave执行完成就可以发送下一个binlog。

查看中继日志

中继日志文件的格式与二进制日志文件相同，并且可以使用mysqlbinlog进行读取。