性能调优 7. Innodb底层原理与MySQL日志机制深入剖析

1. MySQL的内部组件结构

‌‌‌　　大体来说，MySQL可以分为Server层和存储引擎层两部分。

1.1. Server层

‌‌‌　　主要包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

连接器

‌‌‌　　由于MySQL是开源的，有非常多种类的客户端：navicat,mysqlfront,jdbc,SQLyog等非常丰富的客户端，包括各种编程语言实现的客户端连接程序，这些客户端要向MySQL发起通信都必须先跟Server端建立通信连接，而建立连接的工作就是有连接器完成的。

‌‌‌　　第一步，你会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令一般是这么写的。

‌‌‌　　[root@192~]# mysql -h数据库地址 -u用户名 -p密码 -P端口

‌‌‌　　参数和数据之间不能有空格，如

‌‌‌　　mysql -h127.0.0.1 -uroot -p123456 -P3306

‌‌‌　　连接命令中的mysql是客户端工具，用来跟服务端建立连接。在完成经典的TCP握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

‌‌‌　　1. 如果用户名或密码不对，你就会收到一个"Accessdeniedforuser"的错误，然后客户端程序结束执行。

‌‌‌　　2. 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

‌‌‌　　这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在，连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

查询缓存

‌‌‌　　接建立完成后，你就可以执行select语句了。执行逻辑就会来到第二步：查询缓存。

‌‌‌　　MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key，那么这个value就会被直接返回给客户端。

‌‌‌　　如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

‌‌‌　　大多数情况查询缓存就是个鸡肋，因为查询缓存往往弊大于利。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。

‌‌‌　　一般建议大家在静态表里使用查询缓存。、

‌‌‌　　静态表：就是一般极少更新的表。比如，一个系统配置表、字典表，那这张表上的查询才适合使用查询缓存。好在MySQL也提供了这种“按需使用”的方式。可以修改配置文件，Windows下修改my.ini，Linux下修改my.cnf。参数query_cache_type设置成DEMAND。

‌‌‌　　[mysqld]

‌‌‌　　query_cache_type 有3个值---0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）代表当sql语句中有SQL_CACHE关键词时才缓存

‌‌‌　　query_cache_type=2，对于默认的SQL语句都不使用查询缓存。而对于确定要使用查询缓存的语句，可以用SQL_CACHE显式指定，例如

‌‌‌　　select SQL_CACHE * from test where id= 5

‌‌‌　　注意

‌‌‌　　1. MySQL8.0已经移除了查询缓存功能

分析器

‌‌‌　　如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。

‌‌‌　　分析器对SQL语句的分析过程步骤

‌‌‌　　1. 词法分析：分析器先会做词法分析。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。MySQL从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名T”，把字符串“ID”识别成“列ID”。

‌‌‌　　2 . “语法分析”：根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法(包括语法错了，词错了)。如果语句不对，就会收到“You have an erro rin your SQL syntax”的错误提醒，比如下面这个语句from写成了"fro"。

 select * fro test where id = 1;

‌‌‌　　ERROR1064(42000):You have an error in your SQL syntax;check the manual that corresponds to yo ur My SQL server version for the right syntax to use near 'fro tes t where id=1' at line 1

‌‌‌　　3. 跳过一些不重要步骤，SQL语句经过分析器分析之后，会生成一个这样的语法树。

‌‌‌　　例如下面SQL语句，经过分析后的语法树

‌‌‌　　select username，ismale from userInfo where age>20 and level >5 and 1=1

‌‌‌　　至此分析器的工作任务也基本圆满了，接下来进入到优化器。

优化器

‌‌‌　　经过了分析器，MySQL 就知道要做什么了。在开始执行之前，还要先经过优化器的处理。

‌‌‌　　优化器在表里面有多个索引的时候，会决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序(小表驱动大表)，以及一些MySQL自己内部的优化机制。

执行器

‌‌‌　　开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限。如果没有，就会返回没有权限的错误，如下所示 。(如果当前版本支持查询缓存，命中了查询缓存，会在查询缓存返回结果的时候，做权限验证)
‌‌‌　　如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

1.2. 存储引擎层

‌‌‌　　存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL5.5.5版本开始成为了默认存储引擎。如果在 create table 时不指定表的存储引擎类型，默认会给你设置存储引擎为InnoDB。

Innodb存储引擎执行流程(底层原理)

‌‌‌　　执行流程图

‌‌‌　　执行步骤

‌‌‌　　1. 执行sql语句 update set name=‘zuangbi’ where id=1。

‌‌‌　　2. 经过server层分发到存储引擎，存储引擎Buffer Pool缓存池看没有这条数据，没有则从磁盘获取数据，把这条数据关联的**那一页(磁盘页16kb)**数据加载到Buffer Pool缓存池。

‌‌‌　　3. 把要修改原数据复制到到undo日志文件，回滚用保证事务原子性。如果事务提交失败要回滚数据，undo日志里的数据用来恢复Buffer Pool里的缓存数据(MySQL应用程序管理的缓存)。

‌‌‌　　4. 把Buffer Pool缓存池数据内容更新为新的数据。数据库的增删改查都是先操作Buffer Pool，Buffer Pool大小一般设置为机器内存的60-70%左右。

‌‌‌　　5. 把这条修改数据的Redo Log写到Redo Log Buffer(MySQL应用程序管理的缓存)。

‌‌‌　　6. Redo Log Buffer数据顺序写到磁盘的Redo Log文件上。

‌‌‌　　Redo Log Buffe对Redo Log是顺序写的，写入速度很快。Redo Log日志记录的是物理修改（xxxx页做了xxx修改），文件的体积很小，恢复数据速度也很快。

‌‌‌　　如果事务提交成功，系统宕机。Buffer Pool缓存池修改的数据还没写入磁盘，可以用Redo Log的数据恢复磁盘ibd文件里的数据(重启后会自动恢复)。

‌‌‌　　磁盘文件ibd不能顺序写(因为各个表ibd文件在磁盘位置不一定是紧邻着)，如果更新在不同表多条数据，就是随机写，效率慢。但是Redo Log是一个或几个预先分配好磁盘空间的文件，写入永远都是在文件未尾追加数据，可以在磁盘顺序写入数据。(kafka,rocket mq等都支持)。

‌‌‌　　先把数据写到磁盘的Redo Log，后刷新磁盘ibd文件里的数据的机制叫WAL机制**(Write-Ahead Logging)，也叫磁盘文件预写机制，效率更高。

‌‌‌　　注意

‌‌‌　　固态硬盘下随机和顺序写速率相差不大，磁盘顺序写和内存随机写速率接近。

‌‌‌　　7. 准备提交事务，将Bin Log写入磁盘(属于Server层)。记录是逻辑修改。

‌‌‌　　Bin Log主要用在误删除，恢复数据库磁盘里数据。

‌‌‌　　8. 写入commit标记到Redo Log，该标记为了保证事务提交完成后，Redo Log与Bin Log记录修改的数据一致。提交事务完成，客户端收到服务端返回的提交成功，此时Redo Log与Bin Log记录修改的数据一致(不一致事务提交不成功)。

‌‌‌　　9. 在系统空闲时候(时间不固定)，将Bufeer Pool缓冲池修改数据的，那一页数据，随机写入到磁盘ibd。

MySQL日志机制

‌‌‌Redo Log和 Bin Log和Undo Log区别

‌‌‌　　1. Redo Log 和Undo Log是Innodb存储引擎特有的实现的机制。Bin Log是MySQL的server层实现的机制，所有存储引擎都公用。

‌‌‌　　2. Redo Log是用来恢复事务提交，系统宕机，Buffer Pool缓存池数据还没写入磁盘，可以用用Redo Log恢复数据库磁盘数据(重启后会自动恢复)，保证事务的持久性。

‌‌‌　　3. Bin Log主要用在误删除修改，恢复数据库磁盘里数据。

‌‌‌　　4. Undo Log用来实现事务回滚，MVCC机制里头需要用到，保证事务的原子性。

Redo Log

‌ 查看Redo Log参数配置

1. SQL语句查看Redo Log是否开启，一般各版本默认都是开启的。

‌‌‌　　SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled'

‌‌‌　　2. SQL语句动态开启关闭Redo Log，通过配置文件的方式配置没找到

#关闭
‌‌‌　　ALTER INSTANCE DISABLE INNODB REDO_LOG
#开启
‌‌‌　　ALTER INSTANCE ENABLE  INNODB REDO_LOG

‌‌‌　　3. 查看一些参数配置

‌‌‌　　show variables like '%innodb_%'

Redo Log关键参数

Redo Log参数可以通过配置文件配置(自己上网查)或者直接会话配置。

‌‌‌　　1. innodb_log_buffer_size：设置Redo Log Buffer大小参数，默认16M ，最大值是4096M，最小值为1M。

‌‌‌　　show variables like '%innodb_log_buffer_size%';

‌‌‌　　2. innodb_log_group_home_dir：设置Redo Log文件存储位置参数。默认值为"./"，即innodb数据文件存储位置在MySQL安装目录的Data文件夹下，其中ib_logfile前缀开头的都是Redo Log的文件，如ib_logfile0和ib_logfile1。

‌‌‌　　show variables like '%innodb_log_group_home_dir%'

‌‌‌　　3. innodb_log_files_in_group：设置Redo Log文件的个数，命名方式如:ib_logfile0,iblogfile1…iblogfileN。默认2个，最大100个。

‌‌‌　　show variables like'%innodb_log_files_in_group%'

‌‌‌　　4. innodb_log_file_size：设置单个Redo Log文件最大大小，默认值为48M。
‌‌‌　　最大值为512G，注意最大值指的是整个redo log系列文件之和，即(innodb_log_files_in_group*innodb_log_file_size)不能大于最大值512G。

‌‌‌　　show variables like '%innodb_log_file_size%'

Redo Log写入磁盘过程分析

‌‌‌　　Redo Log是从头开始写，写完一个文件继续写另一个文件按顺序写，写到最后一个文件末尾就又回到第一个文件开头循环覆盖写。

‌‌‌　　如下图所示

‌‌‌　　假设Redo Log如图所示一共有四个。

‌‌‌　　1. writepos：是当前记录的位置。一边写一边后移，写到第3号文件末尾后就回到0号文件开头。

‌‌‌　　2. checkpoint：是当前要擦除的位置。也是往后推移并且循环的。擦除记录前，要把记录更新到磁盘ibd数据文件里，然后checkpoint就会移动到擦除数据后面。writepos和checkpoint之间的部分就是空着的可写部分。可以用来记录新的操作。如果writepos追上checkpoint，表示Redo Log写满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，并更新到数据库，把checkpoint推进一下。

Redo Log的写入策略

‌‌‌　　innodb_flush_log_at_trx_commit：这个参数控制Redo Log的写入策略，它有三种可能取值。

‌‌‌　　设置为0：表示每次事务提交时都只是把Redo Log留在Redo Log Buffer中，数据库宕机可能会丢失数据。(效率最高，安全最低)。

‌‌‌　　InnoDB有一个后台线程，每隔1秒，调用操作系统函数write写到文件系统的page cache，系统空闲时候，调用操作系统函数fsync持久化到磁盘文件。

‌‌‌　　设置为1(默认值)：表示每次事务提交时都将Redo Log直接持久化到磁盘，数据最安全，不会因为数据库宕机丢失数据，但是效率稍微差一点，线上系统推荐这个设置。(效率最低，安全最高)。

‌‌‌　　操作流程：调用操作系统函数write写到文件系统的page cache，再调用操作系统函数fsync持久化到磁盘文件。

‌‌‌　　设置为2：表示每次事务提交时都只是把Redo Log写到操作系统的缓存page cache里，这种情况如果数据库宕机是不会丢失数据的，但是操作系统如果宕机了，page cache里的数据还没来得及写入磁盘文件的话就会丢失数据。(效率一般，安全有点问题)。

‌‌‌　　系统空闲时候，调用操作系统函数fsync持久化到磁盘文件。

‌‌‌　　注意

‌‌‌　　1. page cache (很多中间件都用到比如MQ)是操作系统管理的缓存，是内存里头一块缓存用来解决内存数据写入磁盘的速度慢问题。应用程序将数据写入page cache就去做别的事，然后台线程空闲时候，会自己写入磁盘，来加快并发速度。但是系统宕机，就写入磁盘失败。

‌‌‌　　2. Redo Log Buffer是MySQL应用程序管理的缓存，page cache是操作系统管理的缓存。

‌‌‌　　redolog写入策略参看下图：

‌‌‌　　查看设置innodb_flush_log_at_trx_commit

	#查看innodb_flush_log_at_trx_commit参数值：

‌‌‌　　show variables like'innodb_flush_log_at_trx_commit';

	#设置innodb_flush_log_at_trx_commit参数值(也可以在my.ini或my.cnf文件里配置)：

‌‌‌　　set global innodb_flush_log_at_trx_commit=1;
 

‌‌‌　　参考文档

	1. redo写入策略

‌‌‌　　https://cloud.tencent.com/developer/article/1441303

Bin Log二进制归档日志

‌‌‌　　Bin Log保存了所有执行过的**修改操作语句**，**不保存查询操作**。如果MySQL服务意外停止，可通过二进制日志文件排查，用户操作或表结构操作，从而来恢复数据库数据。

‌‌‌　　启动Bin Log记录功能，会影响服务器性能，但如果需要恢复数据或主从复制功能，则好处则大于对服务器的影响。

查看Bin Log参数配置

‌‌‌　　查看一些参数

	#查看binlog相关参数

‌‌‌　　show variables like '%log_bin%';

‌‌‌　　log_bin：ON 日志是否打开状态

‌‌‌　　log_bin_basename：是Bin Log的基本文件名，后面会追加标识来表示每一个文件，Bin Log日志文件会滚动增加

‌‌‌　　log_bin_index：指定的是Bin Log文件的索引文件，这个文件管理了所有的Bin Log文件的目录。

‌‌‌　　sql_log_bin：sql语句是否写入Bin Log文件，ON代表需要写入，OFF代表不需要写入。 正常sql语句修改数据要记录到日志，但是如果想在主库上执行一些操作，但不复制到slave库上，可以通过修改参数sql_log_bin来实现。比如说，模拟主从同步复制异常。

开启关闭Bin Log

‌‌‌　　MySQL5.7版本中，Bin Log默认是关闭的，8.0版本默认是打开的。

‌‌‌　　5.7下要开启，设置配置就行，不加配置就是关闭。可以通过log-bin参数重命名Bin Log文件的前缀名和存储位置，同时开启Bin Log。

‌‌‌　　存储位置可以是绝对或者相对路径，相对位置是以Data根目录。

‌‌‌　　文件名不改默认叫binlog。

‌‌‌　　log-bin=mysql-bin 

‌‌‌　　8.0默认下是打开的，则可通过log-bin重命名Bin Log文件的前缀名和存储位置。

‌‌‌　　需要关闭binlog可以在配置末尾加

‌‌‌　　skip-log-bin

Bin Log参数配置

‌‌‌　　需要修改配置文件my.ini(windows)或my.cnf(linux)，然后重启数据库。

‌‌‌　　在配置文件中的[mysqld]部分增加如下配置

# Bin Log相关配置

# log-bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，则binlog文件默认会放data数据目录下
# mysql-binlog表示日志文件名前缀
#8.0不配默认叫binlog
‌‌‌　　log-bin=mysql-binlog

#ServerId是数据库服务器id，随便写一个数都可以，这个id用来在mysql集群环境中标记唯一mysql服务器，集群环境中每台mysql服务器的id不能一样，不加启动会报错
# 跟主从有关，总之主从里头每个数据库id要配，且唯一。
‌‌‌　　server-id=1

#其他配置

#日志文件格式，下面会详细解释
‌‌‌　　binlog_format=row

#执行自动删除binlog日志文件的天数，默认为0，表示不自动删除
#跟备份时间有关，设置可以大于备份天数
‌‌‌　　expire_logs_days=15 

#单个binlog日志文件的大小限制，默认为1GB
‌‌‌　　max_binlog_size=200M 

‌‌‌　　重启数据库后再去看Data数据目录会多出两个文件，第一个就是Bin Log日志文件写满就会追加新的文件，第二个是Bin Log文件的索引文件，这个文件管理了所有的Bin Log文件的目录。

查看有多少Bin Log文件

‌‌‌　　执行SQL语句

‌‌‌　　show binary logs;

Bin Log的日志格式

‌‌‌　　用参数binlog_format可以设置Bin Log日志的记录格式，MySQL支持三种格式类型：

‌‌‌　　STATEMENT：基于SQL语句的复制，每一条修改数据的SQL都会记录到master机器的bin-log中，这种方式日志量小，节约IO开销，提高性能，但是对于一些执行过程中才能确定结果的函数，比如UUID()、SYSDATE()等函数如果随SQL同步到slave机器去执行，则结果跟master机器执行的不一样。

‌‌‌　　ROW：基于行的复制，日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改记录下每一行数据修改的细节，可以解决函数、存储过程等在slave机器的复制问题，但这种方式日志量较大，性能不如Statement。举个例子，假设update语句更新10行数据，Statement方式就记录这条update语句，Row方式会记录被修改的10行数据。

‌‌‌　　MIXED：混合模式复制，实际就是前两种模式的结合，在Mixed模式下，MySQL会根据执行的每一条具体的SQL语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种，如果SQL里有函数或一些在执行时才知道结果的情况，会选择Row，其它情况选择Statement，推荐使用这一种。

‌‌‌　　三者区别，比如执行 update account set balance=200 where id >1 一共有10条语句更新。

‌‌‌　　STATEMENT—只记录了这条更新的SQL语句。

‌‌‌　　ROW—记录这条更新的SQL语句，同时记录修改的每条数据变化。

‌‌‌　　MIXED—混合，如果没有UUID()、SYSDATE()等函数，需要SQL语句执行完才能确定数据结果，就用ROW不然就用STATEMENT。

Bin Log写入磁盘机制(没有像redologbuffer的缓存)

‌‌‌　　Bin Log写入磁盘机制主要通过sync_binlog参数控制，默认值是0。

‌‌‌　　为0，每次提交事务都只write到pagecache，由系统自行判断什么时候执行fsync写入磁盘。虽然性能得到提升，但是机器宕机，pagecache里面的Bin Log会丢失。

为1，每次提交事务都会执行fsync写入磁盘，这种方式最安全。

‌‌‌　　还可以设置为N(N>1)，表示每次提交事务都write到pagecache，但累积N个事务后才fsync写入磁盘，这种如果机器宕机会丢失N个事务的Bin Log。

Bin Log的重新生成

‌‌‌　　发生以下任何事件时，Bin Log文件会重新生成，原来的不删除。新的日志会写到新生成的Bin Log文件上。

‌‌‌　　1. 服务器启动或重新启动

‌‌‌　　2. 服务器刷新日志，执行命令flushlogs(会重新生成日志，记录数据会从这个文件开始)

‌‌‌　　3. 日志文件大小达到max_binlog_size值，默认值为1GB

删除Bin Log日志文件

#删除当前的Bin Log文件，回到初始化两个文件

‌‌‌　　reset master;

#删除指定日志文件之前的所有日志文件，下面这个是删除6之前的所有日志文件，当前这个文件不删除

‌‌‌　　purge master logs to'mysql-binlog.000006';

#删除指定日期前的日志索引中Bin Log文件

‌‌‌　　purge master logs before'2023-01-21 14:00:00';

查看Bin Log日志文件

‌‌‌　　可以用MySQL自带的命令工具mysqlbinlog查看Bin Log日志二进制内容，没有配置环境变量话需要cd到MySQL安装目录执行如下命令。

查看Bin Log二进制文件（命令行方式，不用登录MySQL）

‌‌‌　　mysqlbinlog --no-defaults -v --base64-output=decode-rows D:\dSoftware\mysql\mysql-8.0.33-winx64\Data\mysql-binlog.000001

‌‌‌　　查看Bin Log二进制文件（带查询条件）

‌‌‌　　mysqlbinlog --no-defaults -v --base64-output=decode-rows D:\dSoftware\mysql\mysql-8.0.33-winx64\Data\mysql-binlog.000001 start-datetime="2023-01-2100:00:00" stop-datetime="2023-02-0100:00:00" start-
‌‌‌　　position="5000"stop-position="20000"

使用Bin Log恢复数据

‌‌‌　　下图是Bin Log文件的一部分内容。

‌‌‌　　1. at是文件的偏移位置(重要位置)。

‌‌‌　　2. SET TIMESTAMP 一般记录一个事务修改语句的开始时间，没开启事务下，单条语句就是一个事务。

‌‌‌　　如果不小心删除或者修改数据库一条数据要恢复，可以用Bin Log文件恢复数据其实就是回放执行之前记录在Bin Log文件里的SQL语句。

‌‌‌　　恢复逻辑，如删除一些数据，则执行这些数据，新增到删除之前的SQL语句恢复就行。修改了一些数据，执行之前的修改SQL语句恢复就行，如果要恢复的修改数据不能通过执行之前修改SQL语句恢复。那么给先删除数据，然后给执行新增，到修改数据操作前的SQL语句恢复。

‌‌‌　　举一个数据恢复的例子。

#先执行刷新日志的命令生成一个新的binlog文件，后面我们的修改操作日志都会记录在最新的这个文件里

‌‌‌　　flush logs;

#执行两条插入语句
‌‌‌　　INSERT INTO`mytest`.`account`(`id`,`name`,`balance`)VALUES('14','zhuge','666');

‌‌‌　　INSERT INTO`mytest`.`account`(`id`,`name`,`balance`)VALUES('15','zhuge1','888');

#假设现在误操作执行了一条删除语句把刚新增的两条数据删掉了
‌‌‌　　delete from account where id>14;

现在需要恢复被删除的两条数据。

‌‌‌　　第一种根据at偏移量。

‌‌‌　　找到两条插入数据的SQL，每条SQL的上下都有BEGIN和COMMIT。这两条SQL语句一定又先后执行顺序，我们找到先执行的第一条SQL，BEGIN前面的文件位置标识at 219(这是文件的位置标识)，再找到后面执行的第二条SQL，COMMIT后面的文件位置标识at 701

‌‌‌　　可以根据这两个文件位置标识来恢复数据，执行如下SQL：

‌‌‌　　start-position—要恢复数据执行的sql，偏移量开始位置

‌‌‌　　stop-position—要恢复数据执行的sql，偏移量结束位置

‌‌‌　　database—恢复的数据库

‌‌‌　　u—数据库用户名

‌‌‌　　p—数据库密码

‌‌‌　　v—恢复的数据库

‌‌‌　　mysqlbinlog --no-defaults --start-position=375 --stop-position=727 --database=mytest D:\dSoftware\mysql\mysql-8.0.33-winx64\Data\mysql-binlog.000002 | mysql -uroot -p123456 -v mytest

‌‌‌　　第二种 根据时间戳，要转成datetime格式，一样根据执行逻辑找到合适位置的时间戳。

‌‌‌　　我们找到第一条SQL BEGIN前面的时间戳标记SETTIMESTAMP=1674833544，再找到第二条SQL COMMIT后面的时间戳标记SETTIMESTAMP=1674833663，都转成datetime格式。

‌‌‌　　执行如下命令恢复数据。

‌‌‌　　mysqlbinlog --no-defaults --start-datetime="2023-08-07 10:49:52" --stop-datetime="2023-08-07 14:10:22" --database=mytest D:\dSoftware\mysql\mysql-8.0.33-winx64\Data\mysql-binlog.000001 | mysql -uroot -p123456 -v mytest

‌‌‌　　注意

‌‌‌　　1. 恢复数据一定要是从SQL语句的，begin前的at或者时间开始，到commit后的at或者时间结束，这样才算事务提交，才能恢复数据。

‌‌‌　　2. 如果要恢复大量数据，比如程序员经常说的删库跑路的话题，假设我们把数据库所有数据都删除了要怎么恢复了，如果数据库之前没有备份，所有的Bin Log都在的话，就从Bin Log第一个文件开始逐个恢复每个Bin Log文件里的数据，这种一般不太可能，因为Bin Log日志比较大，早期的Bin Log文件会定期删除的，所以一般不可能用Bin Log文件恢复整个数据库的。

‌‌‌　　直接根据Bin Log文件恢复命令如下

‌‌‌　　mysqlbinlog --no-defaults --database=mytest D:\dSoftware\mysql\mysql-8.0.33-winx64\Data\mysql-binlog.000002 | mysql -uroot -p123456 -v mytestt

‌‌‌　　 一般我们推荐的是每天(在凌晨后)需要做一次全量数据库备份，那么恢复数据库可以用最近的一次全量备份，再加上备份时间点之后的Bin Log来恢复数据(按照时间恢复)。Bin Log做好备份多几天，防止数据备份失败。

‌‌‌　　备份数据库一般可以用mysqldump命令工具

  #备份整个数据库到指定目录
‌‌‌　　mysqldump -u root -p123456 --databases mytest  > E:\mytest-dump.sql

   #备份指定表，备份到指定目录文件
‌‌‌　　mysqldump -u root -p123456 --databases mytest --tables t1 > D:\dSoftware\mysql\mysql-8.0.33-winx64\dump\table_1-dump.sql

‌‌‌　　用备份文件恢复数据库的数据

‌‌‌　　先连接数据库

‌‌‌　　mysql -h localhost -P 3306 -u root -p123456

‌‌‌　　windows下连接后，输入命令不生效，可以ctrl+c回退下出现mysql字样就行。

‌‌‌　　1. 恢复指定的数据库

‌‌‌　　直接执行备份文件恢复数据

 source E:\mytest-dump.sql
 

‌‌‌　　2. 恢复指定的表

‌‌‌　　查看有哪些数据库

‌‌‌　　show databases

‌‌‌　　指定使用的数据库

 	use mytest

‌‌‌　　执行备份文件恢复表数据

 	source E:/table_1-dump.sql
 

‌‌‌　　备份参考文档

	1. MySQL8.0-备份与恢复
	
‌‌‌　　https://juejin.cn/post/7057330327047372837

Undo Log

Undo Log回滚日志

‌‌‌　　InnoDB对Undo Log文件的管理采用段的方式，也就是回滚段（rollbacksegment）。每个回滚段记录了1024个undologsegment，每个事务只会使用一个undologsegment。

‌‌‌　　在MySQL5.5的时候，只有一个回滚段，那么最大同时支持的事务数量为1024个。在MySQL5.6开始，InnoDB支持最大128个回滚段，故其支持同时在线的事务限制提高到了128*1024。

查看Undo Log参数配置

‌‌‌　　查看一些参数

‌‌‌　　show variables like '%innodb_undo%'; 

‌‌‌　　

‌‌‌　　innodb_undo_directory：设置undo log文件所在的路径。该参数的默认值为".\"，即innodb数据文件存储位置。即Data目录下ibdata1文件就是undo log存储的位置。

‌‌‌　　innodb_undo_logs:设置Undo Log文件内部回滚段的个数，默认值为128。

‌‌‌　　innodb_undo_tablespaces:设置Undo Log文件的数量，这样回滚段可以较为平均地分布在多个文件中。设置该参数后，会在路径innodb_undo_directory下，看到undo为前缀的文件。

	默认1下，则数据都会在ibdata1文件中。
	
‌‌‌　　设置为2，则会有undo_1,undo_2等，然后undo日志就会平均存储到这些文件中，默认128个回滚段就会平均分布在这些文件中

Undo Log什么时候删除

‌‌‌　　1. 新增类型的，在事务提交之后就可以清除掉了。

‌‌‌　　2. 修改类型的，事务提交之后不能立即清除掉，这些日志会用于MVCC。只有当没有事务用到该版本信息时才可以清除。

错误日志

‌‌‌　　MySQLl还有一个比较重要的日志是错误日志，它记录了数据库启动和停止，以及运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。
‌‌‌　　在MySQL数据库中，错误日志功能是默认开启的，而且无法被关闭。

    #查看错误日志存放位置
‌‌‌　　show variables like '%log_error%'

通用查询日志

‌‌‌　　通用查询日志记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给MySQL数据库服务器的所有SQL指令等，如select、show等，无论SQL的语法正确还是错误、也无论SQL执行成功。还是失败，MySQL都会将其记录下来。
‌‌‌　　通用查询日志用来还原操作时的具体场景，可以帮助我们准确定位一些疑难问题，比如重复支付等问题。

   # 刻意通过下面命令，查看存储位置
‌‌‌　　show variables like '%general_log%';

	#打开通用查询日志
‌‌‌　　SE TGLOBAL general_log=on;

‌‌‌　　general_log：是否开启日志参数，默认为OFF，处于关闭状态，因为开启会消耗系统资源并且占用磁盘空间。一般不建议开启，只在需要调试查询问题时开启。

‌‌‌　　general_log_file：通用查询日志记录的位置参数。

2. 面试题

为什么会有Redo Log和Bin Log两份日志呢？

‌‌‌　　有了Redo Log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

‌‌‌　　因为最开始MySQL里并没有InnoDB引擎。MySQL自带的引擎是MyISAM，但是MyISAM没有crash-safe的能力，Bin Log日志只能用于归档。而InnoDB是另一个公司以插件形式引入MySQL的，既然只依靠Bin Log是没有crash-safe能力的，所以InnoDB使用另外一套日志系统——也就是Redo Log来实现crash-safe能力。

为什么MySQL不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL了？

‌‌‌　　因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能，可能相当差。因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。
‌‌‌　　MySQL这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干甚至上万的读写请求。

‌‌‌　　总之，保证数据一致性，比如宕机事务提交，数据还没来的及写入磁盘，可以通过Redo Log恢复磁盘数据。能抗住更高的并发，因为磁盘随机写效率低，数据先写到BufferPool缓存池，等待空闲再更新磁盘数据。