Mysql技术内幕（二）--Mysql文件和日志

  • Mysql技术内幕系列文章
  • 一.文件
  • 1.1 参数文件
  • 1.2 日志文件
  • 1.2.1 错误日志
  • 1.2.2 慢查询日志
  • 慢查询测试：
  • 1.2.3 二进制日志☆
  • 1.3 其他类型文件（了解）
  • 二.InnoDB存储引擎文件☆
  • 2.1 表空间文件
  • 2.2 重做日志文件☆
  • 2.2.1 重做日志块（log block）
  • 2.2.2 重做日志组（log group）
  • 2.2.3 重做日志格式和LSN
  • 2.2.4 redo
  • 2.2.5 undo
  • 三.总结

一.文件

上一篇文章说过，Mysql数据库其实可以看做是一个存放了多个文件的容器，其中的每一种、每一个文件都有着非常重要的作用，那么接下来就先从文件的角度来一一细说，虽然这块部分可能比较枯燥，不过我觉得能理解下Mysql的日志还是很不错的。（至少你能知道，各个种类的日志又怎样的作用，又去哪里查看）

1.1 参数文件

我们知道，Mysql实例启动后，会按照一定的顺序在指定的位置进行读取，输入命令：

mysql --help | grep my.cnf

结果如下，意思是按照/etc/my.cnf——>/etc/mysql/my.cnf——> /usr/etc/my.cnf ~/.my.cnf的顺序进行读取（以读取到的最后一个文件为准）

mysql的密码插件有哪些 mysql秘密_二进制日志


而读取这些文件有什么用呢？当然是为了加载对应的配置，其实数据库参数相当于一个键值对，而具体的参数可以由命令show variables来完成，部分结果如下：

mysql的密码插件有哪些 mysql秘密_二进制日志_02


图中，前面的参数如tablespace_definition_cache作为键，后面的256则作为值。Mysql中的参数可以分为两类：

  1. 动态参数：运行在运行时进行更改。
  2. 静态参数：在实例整个生命周期内都不得进行更改。（只读）

我们可以通过Set命令对动态参数的值进行修改，同时，动态参数还有两个作用范围：

  • global：作用于整个实例的生命周期，如binlog_cache_size参数。
  • session：作用于当前会话，如autocommit参数。

使用格式：

set [global | session] name=value

例如：修改一个静态变量时会报错。

mysql的密码插件有哪些 mysql秘密_mysql的密码插件有哪些_03


注意：通过set命令修改了任何类型的参数后，在下一次启动Mysql实例的时候，值会失效。 想想为啥？

回答：原因很简单，因为启动一个Mysql实例，他会从指定的配置文件my.cnf中读取配置， 如果要让配置永久生效，那么必须去修改参数文件。

1.2 日志文件

Mysql很多功能的实现都离不开日志，而日志文件当然是记录影像Mysql的各类型活动的文件，其中常见的日志包括：

  • 错误日志（error log）
  • 二进制日志（binlog）
  • 慢查询日志（slow query log）
  • 查询日志（log）：记录所有对Mysql数据库请求的信息，无论这些请求是否得到了正确的执行。（后文不展开叙述）

1.2.1 错误日志

错误日志主要针对Mysql的启动、运行、关闭过程。虽然名字上说是错误日志，但是他还记录一些警告或者正确的信息。 其实错误日志这一块没什么好说的，这里就教大家怎么辨别哪个日志是错误日志，输入命令：

show variables like 'log_error';

结果：会显示错误日志对应的存储地址，当大家遇到一些数据库的相关报错问题时，请第一时间来看看错误日志上怎么说，对症下药比较快。

mysql的密码插件有哪些 mysql秘密_二进制日志_04

1.2.2 慢查询日志

慢查询日志（slow log）主要是用来定位可能存在问题的sql语句，从而进行SQL语句层面的优化。

使用过程：

  1. 在Mysql启动的时候设置一个阈值：long_query_time，默认值是10秒，但是默认不启动。
  2. 那么运行时间超过该值的所有SQL语句都会记录到慢查询日志当中。

这里先把慢查询的3个最重要的参数放出来：

  • slow_query_log：慢查询开启状态。
  • slow_query_log_file：慢查询日志存放的位置。
  • long_query_time：慢查询的阈值。
慢查询测试：

1.执行命令（或者修改my.cnf文件，靠谱点）

set global slow_query_log='ON'; 
set global long_query_time=1;
select sleep(2);

2.查看对应的慢查询日志，如果能够多出一个文件则代表成功了。

mysql的密码插件有哪些 mysql秘密_二进制日志_05


此外，和慢查询相关的参数还有：

  • log_queries_not_using_indexes：如果运行的SQL语句没有使用索引，那么Mysql同样会把这条语句记录到慢查询日志文件中，打开此参数可以关闭该功能。
  • log_throttle_queries_not_using_indexes：表示每分钟允许记录到慢查询日志的且没有使用索引的SQL语句次数（默认是10，表示无限制）
  • log_output：指定慢查询输出的格式，默认是FILE（文件），可以将它设置为TABLE，然后即可查询mysql库中的slow_log表了。

举个栗子🌰~：

set global log_output='TABLE';
show variables like 'log_output';
select sleep(2);
select * from mysql.slow_log;

对应数据库中的表就有了数据：（注意，设置完输出格式为Table后，一定要执行一次慢SQL，否则表中是没有数据的）

mysql的密码插件有哪些 mysql秘密_二进制日志_06

1.2.3 二进制日志☆

二进制日志记录了对Mysql执行更改的所有操作，不包括select、show等不影响数据的操作。 二进制日志的作用有：

  1. 恢复：某些数据的恢复需要二进制日志。
  2. 复制：一般用于主从复制。
  3. 审计：用户通过二进制日志的信息来进行审计，判断是否有对数据库进行SQL注入。

查看二进制文件的存放目录：

show variables like 'datadir';

结果：

mysql的密码插件有哪些 mysql秘密_mysql的密码插件有哪些_07


二进制文件的格式为：（后面为标志性的二进制日志序列号）

mysql的密码插件有哪些 mysql秘密_mysql的密码插件有哪些_08


接下来说一下和二进制日志有关的几个重要参数：

  • max_binlog_size：指定单个二进制日志文件的最大值，若超过该值，则产生新的二进制文件，后缀名+1。
  • binlog_cache_size☆

1.使用InnoDB存储引擎时，所有未提交的（uncommitted）的二进制日志会被记录到一个缓存中，等到该事务提交的时候直接将缓冲中的二进制日志写入到二进制文件中。 而binlog_cache_size则控制了这个缓存的大小，默认32K。
2.此外，binlog_cache_size基于会话（session），即当一个线程开始一个事务时，Mysql会自动分配一个大小为binlog_cache_size的缓存。
3.当一个事务的记录大小大于阈值的话，会把缓冲的日志写入到一个临时文件中。（即这个值不能太小）建议使用show global status like 'binlog_cache%';来判断当前的binlog_cache_size是否合适，

mysql的密码插件有哪些 mysql秘密_sql_09

  • sync_binlog

1.参数sync_binlog=【N】表示每写缓冲多少次就同步到磁盘，若该值为1，则代表采用同步写磁盘的方式来写二进制日志。
2.那么为什么有这个参数，是发生了什么问题吗？
----回答：因为默认情况下，二进制日志并不是在每次写的时候同步到磁盘，而发生数据库宕机的时候，可能会有最后一部分数据没有写入到文件中，那么这个时候会给恢复和复制带来问题。而默认情况下该值为0（我的是1，不知道为啥😅😅）简而言之，该值设置为1的时候，相对来说能够保证数据都能够写到磁盘。
3.为什么说相对来说，我来解释下：
----因为如果二进制日志同步写到磁盘，我一个事务发生commit之前，若sync_binlog=1，此时会将二进制日志立即写入磁盘，但是后来事务回滚了，那么这时候写入磁盘的二进制日志会存在脏数据。

  • binlog_do_db：表示需要写入xxx库中的日志，默认为空，表示需要同步所有库的日志到二进制日志文件中。
  • binlog_ignore_db：表示需要忽略xxx库中的日志。
  • log_slave_update：该参数一般用于主从架构。如果当前数据库是复制中的从节点，则他不会将从主节点中取得并且执行的二进制日志写入到自己的二进制日志中，若需要写入，则需要开启该参数。
  • binlog_format☆

这个参数就非常重要了，该参数可以设置3种类型：

  • STATEMENT：若设置为该值，则二进制日志文件记录的是日志的逻辑SQL语句。
  • ROW：二进制日志文件记录的是表的行更改情况。 （这种模式下，将事务隔离级别设置为Read Committed会获得更好的并发性）
  • MIXED：Mysql默认采用STATEMENT格式进行二进制日志文件的记录，但是在以下几个情况下会转成ROW模式：

1.表的存储引擎为NDB，这时候对表的DML操作都会以ROW格式记录。
2.使用了UUID()，USER()，CURRENT_USER()，FOUND_ROWS()，ROW_COUNT()等不确定函数。
3.使用了insert delay语句。
4.使用了用户定义函数（UDF）。
5.使用了临时表。

binlog_format作为动态参数，可以在数据库运行环境下进行更改：

set @@session.binlog_format='ROW';
select @@session.binlog_format;

验证：

mysql的密码插件有哪些 mysql秘密_重做日志_10


使用ROW格式的话，会对磁盘空间要求有一定的增加，又因为复制是采用传输二进制日志的方式实现的，也因此复制的网络开销也有所增加。二进制文件查看的注意事项：

二进制文件的查看必须使用Mysql提供的mysqlbinlog工具来查看，例如：对于STATEMENT格式的二进制日志文件，在使用mysqlbinlog后，看到的就是执行的逻辑SQL语句。

比如：

1.我先执行一条语句：

mysql的密码插件有哪些 mysql秘密_sql_11


2.然后查看二进制文件：

# 参数-vv可以显示更具体的执行信息，最好要加上，否则看不懂的
mysqlbinlog -vv binlog.000026

部分结果：

mysql的密码插件有哪些 mysql秘密_mysql的密码插件有哪些_12

1.3 其他类型文件（了解）

套接字文件
在UNIX系统下本地连接Mysql可以采用UNIX域套接字方式，而这种方式则需要一个套接字文件（socket），名为mysql.socket。

SHOW VARIABLES LIKE 'SOCKET';

结果：输出套接字文件存放路径

mysql的密码插件有哪些 mysql秘密_sql_13


pid文件

当Mysql实例启动的时候，会将自己的进程Id写入一个文件中，而这个文件就是pid文件。该参数由参数pid_file控制，默认位于数据库目录下。

SHOW VARIABLES LIKE 'pid_file';

mysql的密码插件有哪些 mysql秘密_二进制日志_14


表结构定义文件

Mysql数据的存储是根据表进行的，每个表都有一个与之对应的文件，无论使用哪一种存储引擎，Mysql都有一个以frm为后缀名的文件，该文件记录该表的表结构定义。

二.InnoDB存储引擎文件☆

上面介绍的文件都是Mysql数据库本身的文件，和我们的存储引擎无关。那么存储引擎也有属于自己的独特文件。

2.1 表空间文件

InnoDB采用将存储的数据按照表空间（tablespace）进行存放的设计。 在默认配置下会有一个初始大小为10MB、名为ibdata1的文件，该文件就是默认的表空间文件。

mysql的密码插件有哪些 mysql秘密_二进制日志_15


此外，用户可以通过参数innodb_data_file_path对表空间路径进行设置，举例如下：

# 这里将/db/ibdata1和/db/ibdata2两个文件一起来组成表空间。（若俩文件在不同磁盘上，还可以做到负载均衡）
# 两个文件后跟的属性含义：100M，代表ibdata1的大小为100M。
# 200M:autoextend代表超过200MB时，这个文件的大小可以自动增长。
innodb_data_file_path =/db/ibdata1:100M;/db/ibdata2:200M:autoextend

若设置了innodb_data_file_path参数，那么所有基于InnoDB存储引擎的表中的数据都会记录到该共享表空间中。 此外，若设置了参数innodb_file_per_table，用户可以将每个基于InnoDB的表都产生一个独立的表空间，命名规则为：表名.ibd.

mysql的密码插件有哪些 mysql秘密_二进制日志_16


可以看到每张表都有一个对应的ibd文件。（注意：这些单独的表空间文件仅存储该表的数据、索引、插入缓冲BitMap等信息，而其余的信息还是存放在默认的表空间中）

mysql的密码插件有哪些 mysql秘密_sql_17

2.2 重做日志文件☆

默认情况下，每个InnoDB存储引擎至少有一个重做日志文件组（group），每个文件组下至少有2个重做日志文件。如默认的ib_logfile0和ib_logfile1。

mysql的密码插件有哪些 mysql秘密_重做日志_18


重做日志也称为redo log，用来保证事务的原子性和持久性，重做日志中还包含着一种日志，叫undo log，也就是回滚日志，用来帮助事务回滚以及MVCC的功能，再讲具体的日志之前，我觉得有必要先讲一下相关的概念。

2.2.1 重做日志块（log block）

InnoDB存储引擎中，重做日志都是以512个字节进行存储的，也就意味着重做日志缓存、重做日志文件都是以块（block）的方式进行保存的，称之为重做日志块。

一个块有512个字节这么大，其结构除了日志本身外，还包括：

  • 日志块头（log block header）12字节
  • 日志块尾（log block tailer）8字节

也因此，每个重做日志块实际可以存储的大小为（512-12-8=492字节），其结构图为下：

mysql的密码插件有哪些 mysql秘密_sql_19

2.2.2 重做日志组（log group）

log group为重做日志组，包含多个重做日志文件。每个log group中的日志文件大小是相同的。而每个重做日志文件中存储的就是上面的log block块，因此也是根据块的方式进行物理存储的管理。在InnoDB存储引擎运行过程中，log buffer会根据一定的规则将内存中的block刷新到磁盘，规则如下：

  1. 事务提交时。
  2. 当log buffer有一半的内存空间已经被使用时。
  3. log Checkpoint时。

仅仅对于一个重做日志组的第一个重做日志文件（其他部分不保存），它前2KB的部分会保存4个512字节的block，保存信息如下：

mysql的密码插件有哪些 mysql秘密_二进制日志_20


log group和redo log file之间的关系如下：

mysql的密码插件有哪些 mysql秘密_二进制日志_21

2.2.3 重做日志格式和LSN

重做日志格式：

mysql的密码插件有哪些 mysql秘密_二进制日志_22

  • redo_log_type：重做日志的类型。
  • space：表空间Id。
  • page_no：页的偏移量。（InnoDB的存储管理是基于页的，因此重做日志格式也是基于页的）

而后续的body会根据type的不同存储不同的内容。

再说下什么是LSN（了解）
LSN（Log Sequence Number），代表日志序列号，占用8字节，单调递增，其表示的含义有：

  • 重做日志写入的总量。
  • Checkpoint的位置。
  • 页的版本。

我们可以通过命令来查看LSN的情况：

SHOW ENGINE INNODB STATUS

部分结果：

---
LOG
---
# 表示当前的LSN
Log sequence number 4732508
# 表示刷新到重做日志文件的LSN
Log flushed up to   4732508
# 在生产环境中，这3个值一般是不同的，因为一个事务中从日志缓冲刷新到重做日志文件并不是只是在事务提交的时候发生。
# 因为比如master线程，每秒都会有从日志缓冲刷新到重做日志文件的动作发生。
Pages flushed up to 4732508
# 表示刷新到磁盘的LSN
Last checkpoint at  4732499
0 pending log flushes, 0 pending chkp writes
15 log i/o's done, 0.00 log i/o's/second

讲完这么几个概念后，我们再来看一看啥事redo log

2.2.4 redo

重做日志用来实现事务的持久性，由两个部分组成：

  • 内存中的重做日志缓冲（redo log buffer）
  • 重做日志文件（redo log file）

redo log基本上是顺序写的，在数据库运行时不需要对redo log的文件进行读取操作，使用InnoDB存储引擎的时候，当事务提交的时候，必须将该事务的所有日志写入到重做日志文件中进行持久化，等待事务的Commit操作完成。而为了保证每次日志都写入重做日志文件中，在每次将重做日志缓冲写入重做日志文件后，InnoDB都需要调用一次fsync操作。

fsync操作可以理解为：将日志持久化到磁盘上

此外，mysql还提供一个参数innodb_flush_log_at_trx_commit用来控制重做日志刷新到磁盘的策略，有3个值：

  • 0：表示事务提交的时候不进行写入重做日志操作，而写入日志操作交给master线程来完成，主线程每1秒会进行一次重做日志文件的fsync操作。
  • 1：表示事务提交时必须调用一次fsync操作。（性能相比其他两个要低，因为fsync操作次数多，但是保证事务的持久性）
  • 2：事务提交时将重做日志写入重做日志文件中，但是仅写入文件系统的缓存中，不进行fsync操作。

讲到这里，先对redo log和binlog进行一个比较。

比较层面

redo log

binlog

作用层面

InnoDB存储引擎层面

Mysql数据库层

记录的内容形式

物理格式日志：记录的是对于每个页的修改

逻辑日志：记录的是对应的SQL语句

写入磁盘的时间点

在事务进行中不断被写入

只在事务提交完成后进行一次写入

对应关系

一个事务可以对应多个日志条目

对于每一个事务，仅对应事务的一个日志

那么redo是怎么进行恢复的呢？
InnoDB存储引擎在启动的时候，不管上次数据库运行时是否正常关闭，都会尝试进行恢复操作。 而进行恢复的过程，离不开Checkpoint的发挥。

由于Checkpoint表示已经刷新到磁盘页上的LSN，因此在恢复过程中只需要恢复Checkpoint开始的日志部分。

mysql的密码插件有哪些 mysql秘密_重做日志_23

2.2.5 undo

当事务需要进行回滚的时候，这时就需要用到undo log了（保证原子性）。undo存放在数据库内部的一个特殊段：Segment中。其位于共享表空间内。此外，undo log还用于MVCC的实现，当用户读取一行记录的时候，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。

此外，undo log也会产生redo log，因为undo log也需要持久性的保护。

三.总结

其实包括我自己，可能写到这里还是觉得有一丢丢乱😑😑，我对此通过FAQ的方式来进行总结。
问题1：为什么要讲重做日志块和日志组？

1.首先我们知道，InnoDB事务在进行提交之前，会先写到日志缓冲，在持久化到磁盘上，而这个日志缓冲也就是所谓的redo log buffer。
2.其次，redo log buffer像一个数组，由多个重做日志块组成，而重做日志文件也可以看成多个块构成，因此这个块的重要性可想而知。
3.第三点，谈到日志组，是想牵引出Checkpoint的概念。重点是什么？每个组的第一个文件的前4个重组块会包含两个Checkpoint，而这个Checkpoint会在数据恢复的时候使用到。

问题2：知道LSN这个概念有用吗？

1.首先，恕我无知，我一开始并没听说过这个东西。
2.第二，这个LSN和redo log息息相关。由于Checkpoint表示已经刷新到磁盘页上的LSN，因此在恢复过程中只需要恢复Checkpoint开始的日志部分。 而这个LSN就是所谓的日志序列。

问题3：综上所述，redolog是干啥的？

1.首先，redolog可以用来实现事务的持久性。redolog包含俩部分：
----第一部分：redo log buffer：我们事务提交之前，先把数据写到这。（该部分易丢失）
----第二部分： redo log file：我们事务提交后或者其他情况下，会把buffer中的内容持久化到文件中。（该部分是持久的，即安全），也因此redo log文件属于物理格式日志。
2.我们数据库实例启动的时候，都会根据redo log去尝试进行数据恢复。

问题4：undolog又有啥用？

1.首先，undolog用来回滚以及确保MVCC的实现。
2.其次undolog的原理就是生成对应SQL的逆向操作语句，在进行回滚的时候，执行对应的逆向SQL，达到正反消除即回滚的一个效果。

问题5：讲这么多种类型的文件有什么用？

1.如错误日志：文章里告诉你怎么去寻找错误日志存储在哪，什么时候需要去看。（当然是报错的时候🤣🤣）
2.慢查询日志：用来干啥？优化SQL。
3.二进制日志：用来主从复制、数据恢复（使用mysqlbinlog工具进行恢复）。
4.redolog：这个日志一定要和二进制文件搞清楚，二进制文件是基于Mysql层面的，作用于所有的存储引擎，而redolog是基于InnoDB引擎层面的。 事务是InnoDB的特性对吧？那么其特性需要什么来实现？需要redolog来保证持久性，用它来记录事务对数据页进行了哪些更改。

最后啰嗦一句，当面试问到你或者你自己想到Mysql事务是如何实现的时候，一定要想起来redolog和undolog。因为事务的持久性和原子性就是通过他们去实现的。