MYSQL复习六-Innodb底层原理与Mysql日志机

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

Server层

连接器：连接器负责跟客户端建立连接、获取权限、 维持和管理连接。

分析器：语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法，不满足会出现红线

优化器：决定使用的索引，决定到底用那个索引，决定sql的执行顺序，及一些mysql自己内部的优化机制

执行器：调用接口，查询语句

引擎（Innodb）：

Innodb底层原理

update t set name='zhuge666'

1，执行器发起，读取buffer pool缓存池，查看有没有这个id,没有，就更新，从加载到内存

2、把更新的旧数据name=zhege写入到undolog日志,undolog日志是事务原子性的基础,做数据的回滚，必须要有事务才会开启undolog

3，这时才更新内存bufferpool缓存池（内存）

4、执行器发起，写入到redolog的缓存池，redoLog buffer中（内存）

5、由内存redolog磁盘顺序写（与内存接近）入到redolog日志文件中，记录那页修改了什么，redolog用于恢复内存数据，比如突然宕机，就是redolog恢复。wal机制磁盘文件预写，-先写日志文件，在持久化到磁盘，提高了效率

6、执行器发起，把准备提交的事务binlog日志写入日志磁盘，binlog主要时用于从数据库同步主数据库，其次恢复磁盘被删除的日志文件，他是所以引擎锁共有的

7、体积标记一下redolog日志，在事务提交后，让redolog和binlog同步

8，在空闲的时候，生成线程就由内存随机写入到ibd磁盘文件

redo log重做日志关键参数

innodb_log_buffer_size：设置redo log buffer大小参数，默认16M ，最大值是4096M，最小值为1M。

show variables like '%innodb_log_buffer_size%';

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

show variables like '%innodb_log_files_in_group%';

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

redo log 写入磁盘过程分析：

redo log 从头开始写，写完一个文件继续写另一个文件，写到最后一个文件末尾就又回到第一个文件开头循环写，如 下面这个图所示

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。 checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件里。

write pos 和 checkpoint 之间的部分就是空着的可写部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示redo log写满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一 下。

innodb_flush_log_at_trx_commit：这个参数控制 redo log 的写入策略，它有三种可能取值：

设置为0：表示每次事务提交时都只是把 redo log 留在 redo log buffer 中，数据库宕机可能会丢失数 据，但效率最高

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

设置为2：表示每次事务提交时都只是把 redo log 写到操作系统的缓存page cache里，这种情况如果数 据库宕机是不会丢失数据的，但是操作系统如果宕机了，page cache里的数据还没来得及写入磁盘文件的话就 会丢失数据。

如图：

其实，像评论，日志就可以设置未0，或者2，但下单，核心业务肯定要是1

1 # 查看innodb_flush_log_at_trx_commit参数值：
2 show variables like 'innodb_flush_log_at_trx_commit';
3 # 设置innodb_flush_log_at_trx_commit参数值(也可以在my.ini或my.cnf文件里配置)：
4 set global innodb_flush_log_at_trx_commit=1;

binlog二进制归档日志

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

# 查看binlog相关参数
2 show variables like '%log_bin%';
3、log_bin：binlog日志是否打开状态
4 log_bin_basename：是binlog日志的基本文件名，后面会追加标识来表示每一个文件，binlog日志文件会滚动增加
5 log_bin_index：指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。
6 sql_log_bin：sql语句是否写入binlog文件，ON代表需要写入，OFF代表不需要写入。如果想在主库上执行一些操作，
但不复制到slave库上，可以通过修改参数sql_log_bin来实现。比如说，模拟主从同步复制异常。

MySQL5.7 版本中，binlog默认是关闭的，8.0版本默认是打开的，上图中log_bin的值是OFF就代表binlog是关闭状 态，打开binlog功能，需要修改配置文件my.ini(windows)或my.cnf(linux)，然后重启数据库。

binlog的参数配置不是太重要，需要时可以查看下

1 # log‐bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，则binlog文件默认会放在
data数据目录下
2 log‐bin=mysql‐binlog
3 # Server Id是数据库服务器id，随便写一个数都可以，这个id用来在mysql集群环境中标记唯一mysql服务器，集群环
境中每台mysql服务器的id不能一样，不加启动会报错
4 server‐id=1
5 # 其他配置
6 binlog_format = row # 日志文件格式，下面会详细解释
7 expire_logs_days = 15 # 执行自动删除binlog日志文件的天数， 默认为0， 表示不自动删除
8 max_binlog_size = 200M # 单个binlog日志文件的大小限制，默认为 1GB

binlog日志文件样式

show binary logs;  查看多少binlog文件

binlog 的日志格式

用参数 binlog_format 可以设置binlog日志的记录格式，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，推荐使用这一种。

binlog写入磁盘机制

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

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

2、也可以设置为1，表示每次提交事务都会执行 fsync 写入磁盘，这种方式最安全

3、还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write 到page cache，但累积N个事务后 才 fsync 写入磁盘，这种如果机器宕机会丢失N个事务的binlog。

 binlog日志文件会重新生成

1、服务器启动或重新启动

2、服务器刷新日志，执行命令flush logs

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

删除 binlog 日志文件

1 删除当前的binlog文件
2 reset master;
3 # 删除指定日志文件之前的所有日志文件，下面这个是删除6之前的所有日志文件，当前这个文件不删除
4 purge master logs to 'mysql‐binlog.000006';
5 # 删除指定日期前的日志索引中binlog日志文件
6 purge master logs before '2023‐01‐21 14:00:00';

查看 binlog 日志文件

1 # 查看bin‐log二进制文件（命令行方式，不用登录mysql）在cmd中执行
2 mysqlbinlog ‐‐no‐defaults ‐v ‐‐base64‐output=decode‐rows D:/dev/mysql‐5.7.25‐winx64/data/mysql‐bi
nlog.000007
3
4 # 查看bin‐log二进制文件（带查询条件）
5 mysqlbinlog ‐‐no‐defaults ‐v ‐‐base64‐output=decode‐rows D:/dev/mysql‐5.7.25‐winx64/data/mysql‐bi
nlog.000007 start‐datetime="2023‐01‐21 00:00:00" stop‐datetime="2023‐02‐01 00:00:00" start‐
position="5000" stop‐position="20000"

binlog日志文件恢复数据