MySQL 数据存储和优化------MySQL架构原理 ---- （架构---索引---事务---锁---集群---性能---分库分表---实战---运维）持续更新

Mysql架构体系全系列文章主目录(进不去说明还没写完）https://blog.csdn.net/grd_java/article/details/123033016

本文只是整个系列笔记的第一章：架构原理，站在全局理解Mysql，涉及细节后面的章节会讲

1. 发展

1.1 Mysql的起源和分支

1. 目前Oracle和Mysql是市场占比最高的数据库，因为Oracle的垄断，Oracle公司的数据库市场占比非常高。都是有钱的大公司使用。
2. 早期大家通常会说使用IOE，也就是IBM的服务器，Oracle的数据库，EMC的存储设备。
3. 而Mysql是开源、免费的，大多数互联网公司都使用MySql数据库

Mysql的发展史

时间	事件
1979	瑞典的Monty Widenius在Tcx DataKonsult公司工作，开发了Unireg工具，一个面向报表的存储引擎，利用索引顺序读取数据，也是ISAM存储引擎算法的前身
1985年	Monty和David Axmart等人成立公司（MySQL AB的前身），研发出ISAM（Indexed Sequential Access Method）存储引擎工具
1990年	客户要求ISAM工具可以提供SQL接口，Monty找到David Hughes（mSql发明者）商讨合作，发现mSql的速度也不满足要求，于是Monty决心自己重写一个SQL支持，开始了MySql设计和研发
1996年	Monty与David Axmart一起协作，开发出Mysql第一个版本1.0
1996年10月	Mysql3.1发布，没有2.x版本，最开始只提供了Solaris下的二进制版本，同年11月发布Linux版本
1999-2000年	Monty、Allan和David三人在瑞典创立MySQL AB公司，与Sleepycat合作开发并引入了BDB引擎，MySql开始支持事务
2000年	MySql公布源码，并采用GPL（GNU General Public License）许可协议正式开源
2000年4月	MySql对旧的存储引擎ISAM进行整理，命名为MyISAM
2001年	Heikki Tuuri向MySql建议集成他的InnoDB存储引擎，此引擎支持事务和行级锁。MySql与InnoDB正式结合版本是4.0，自此MySQL集成了MyISAM和InnoDB两大主力引擎
2005年10月	MySQL5.0版本发布，加入了游标、存储过程和触发器的支持，具有里程碑意义的版本
2008年1月	MySQL AB公司被Sun公司以10亿美金收购，MySQL数据库进入Sun时代
2009年4月	Oracle公司以74亿美元收购Sun公司，Mysql进入Oracle时代，而其第三方存储引擎InnoDB早在2005年就被Oracle收购了
2010年4月	发布MySQL5.5版本。对MySQL版本重新划分，分为社区版和企业版，默认引擎更换为InnoDB、增加表分区等
2013年2月	MySQL5.6首个正式版5.6.10发布。MySQL5.6对InnoDB引擎进行了改造，提供全文检索能力，使InnoDB适合各种应用场景
2015年10月	MySQL5.7首个GA正式版5.7.9发布
2016年9月	MySQL8.0首个开发版发布，增加数据字典、账号权限角色表、InnoDB增强、JSON增强等
2018年4月	MySQL8.0首个GA正式版8.0.11发布

MySQL主流分支

1. 从被Oracle收购后，出现了社区版和企业版
2. Percona Server是一条非常流行的开源分支版本，由MySQL的技术公司Percona推出，实际工作中经常会碰到。
3. Percona Server在MySQL官方版本的基础上做了一些补丁和优化，同时推出一些工具
4. 另一个分支MariaDB是MySQL公司被Oracle收购后，由Mysql创始人Monty按原来思路重新写的一套新数据库，也将InnoDB作为主要存储引擎，所以也算是MySQL的分支把。

1.2 Mysql应用架构演变

1. 因为MySQL大量用于互联网公司，注重并发的处理和响应能力，因此MySQL的架构演变也主要是针对处理并发架构的演变

单机单库

1. 一个简单的小应用背后的架构可以非常简单，数据存储只需要一个MySQL Instance就可以满足数据读取和写入需求（不考虑数据备份的情况下）
2. 这个架构阶段的系统，一般会将信息存储到一个MySQL Instance中
   
3. 问题

1. 可靠性低，一旦数据访问量过大或者数据量太大，很容易超出一台服务器的承受能力
2. 可用性低，一台服务器挂掉，整个应用就瘫痪了

主从架构

1. 通过读写分离的思想（主库负责写，从库负责读），解决单机架构的高可用和读扩展问题，通过给Instance挂载从库，解决读取压力
2. 就算主库挂掉，从库也可以继续工作，保障应用的高可用性
3. MySQL场景就是通过主从结构应付操作压力（双主结构也属于特殊的主从），也就是读写的分离，主库扛写压力，从库分担读压力
4. 读多写少的应用，主从架构就完全可以应付
5. 问题

1. 可靠性低，数据量太大，很容易超出一台服务器的承受能力(写操作太多，M库服务器依然可能承受不住，因为从库主要分担读压力，此时M库挂了，S库顶上去也一样得挂)

分库分表

1. 前面两个架构，都无法解决写入和存储的瓶颈问题，可以通过水平拆分来解决（和垂直拆分区别很大，不要混淆）

1. 垂直拆分：每一个实例都拥有全部数据
2. 水平拆分：任何实例都只有全量的1/n的数据。

2. 例如将Userinfo拆分为3个Sharding，每个Sharding持有总量的1/3数据，3个Sharding数据的总和等于一份完整数据
   
3. 问题（这些问题都有了解决方案，后面会讲）

1. 贵，这么多服务器，不是小公司可以承受的
2. 数据如何路由，我的请求该去哪个分库（服务器）进行处理
3. 如何拆分，用范围？List？Hash拆分还是什么？
4. 如何保证数据的一致性？

云数据库(云计算)

1. 解决小公司承受不起那么多服务器的问题
2. 云计算是各大IT公司内部，作为节约成本的突破口，让很多服务（例如MySQL数据存储、云服务器）成为一个saas(Software as a Service)，服务提供商负责解决可配置性，可扩展性，多用户存储结构设计等这些疑难问题
3. 用户，只需要花钱买这些云计算服务就可以了，和自己买服务器部署、维护，是非常划算的。
   

2. MySQL体系结构

1. 客户端连接器，可以看做是网络接入层，用户（Java的JDBC，PHP，C等）可以根据各种各样的API协议，与MySQL Server（MySQL 服务端）建立连接
2. 各种服务，可以看做是服务层，主要是对用户请求进行各种服务，是MySQL Server的核心

1. 连接池：避免每次用户连接都分配线程资源，减少开销，还提供了缓存，权限等细节，负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接
2. 系统管理和控制工具：主要是一些系统管理工具，例如备份管理，系统恢复，集群管理，安全管理等
3. SQL接口：接收客户端发送过来的各种SQL命令，并负责最终的结果返回，比如DML、DDL、存储过程、视图、触发器等。
4. 解析器：将请求sql进行解析，解析成一颗解析树（比如用c实现的开源解析器，分为词解析和语法解析，词解析将词拆分成关键字，语法解析进行语法检查），然后根据一些MySQL规则进一步检查解析树是否合法。
5. 查询优化器：解析器将sql解析成解析树后，由查询优化器，进行优化，生成执行计划，最后交给下面的存储引擎层，也就是相同sql有不同的执行方式，但是返回结果一样，它生成执行计划交给存储引擎
6. 缓存：缓存机制有一系列小缓存组成，例如表缓存，记录缓存，权限缓存，引擎缓存等，如果查询缓存命中查询结果，查询语句可以直接去缓存中查数据，不用走存储引擎。

3. 可插拔存储引擎：负责数据库中数据的存储和提取，当查询优化器生成执行计划后，会通过接口与存储引擎交互，存储引擎才是真正执行操作的
4. 文件系统层，存储日志和数据文件的物理层文件系统，完成与存储引擎的交互，主要包含日志文件，数据文件，配置文件，pid文件，socket文件等。

1. 日志文件

1. Error log：错误日志，默认开启，可以通过如下命令查看相关信息

show variables like '%log_error%' # 加%，是为了通配符，显示和log_error参数有关的参数

2. General query log：通用查询日志，记录一般的查询语句，通过如下命令查看相关信息

show variables like '%general%'

3. binary log：二进制日志,记录对MySQL数据库执行的更改操作（不记录select查询，show等不修改数据库的SQL），记录语句发生时间，执行时长等等，主要用于数据库恢复和主从复制。

show variables like '%log_bin%'#是否开启
show variables like '%binlog%' #参数查看
show binary logs #查看日志文件

4. Slow query log：慢查询日志，记录所有执行时间超时的SQL，默认10秒钟

show variables like '%slow_query%' # 是否开启慢查询日志
show variables like '%long_query_time%' # 查询慢查询的超时时长，默认10s
set long_query_time = 5; # 设置超时时长，这里举例设置为5秒

2. 配置文件：存放MySQL所有的配置信息文件，例如my.cnf（Linux下）、my.ini（Windows下）等等
3. 数据文件

1. db.opt文件：记录这个库的默认使用的字符集和校验规则。
2. frm文件：存储与表相关的元数据（meta）信息，包括表结构的定义信息等，每张表都有一个frm文件
3. MYD文件：MyISAM存储引擎专用，存放MyISAM表的数据（data），每张表会有一个.MYD文件。
4. MYI文件：MyISAM存储引擎专用，存放MyISAM表的索引相关信息，每张MyISAM表对应一个.MYI文件
5. ibd文件和IBDATA文件：存放InnoDB的数据文件（包括索引）。InnoDB存储引擎有两种表空间方式：独享表空间和共享表空间。

1. 独享表空间使用.ibd文件存放数据，每张InnoDB表对应一个.ibd文件。
2. 共享表空间使用.ibdata文件，所有表共同使用一个（或多个，自行配置）.ibdata文件。

6. ibdata1文件：系统表空间数据文件，存储表元数据、Undo日志等。
7. ib_logfile0、ib_logfile1文件：Redo log日志文件。
8. pid文件：是mysql应用程序在Unix/Linux环境下的一个进程文件，和许多其他Unix/Linux服务端程序一样，它存放着自己的进程id。
9. socket文件：在Unix/Linux环境下，客户端连接可以不通过TCP/IP网络，直接使用Unix Socket连接MySQL。

3. SQL运行机制

3.1 连接机制

建立连接，通过客户端/服务器通信协议与MySQL建立连接，MySQL客户端与服务端的通信方式是“半双工”（同一时刻只能发或者收，不能同时发和收），对于每个MySQL的连接，时刻都有一个线程状态来标识这个连接正在做什么

1. 线程状态。可以通过show processlist;命令查看用户正在运行的线程信息，root用户可以看所有，其它用户根据权限不同，看到的东西不一样，一般只能看自己的

1. id：线程ID，可以使用kill xxxx命令强制杀死线程。
2. user：启动这个线程的用户
3. Host：发送请求的客户端的IP和端口号
4. db：当前命令在哪个库执行
5. Command：该线程正在执行的操作命令

1. Create DB：正在创建库操作
2. Drop DB：正在删除库操作
3. Execute：正在执行一个PreparedStatement
4. Close Stmt：正在关闭一个PreparedStatement
5. Query：正在执行一个语句
6. Sleep：正在等待客户端发送语句
7. Quit：正在退出
8. Shutdown：正在关闭服务器

6. Time：表示该线程处于当前状态的时间，单位是秒
7. State：线程状态

1. Updating：正在搜索匹配记录，进行修改
2. Sleeping：正在等待客户端发送新请求
3. Starting：正在执行请求处理
4. Checking table：正在检查数据表
5. Closing table：正在将表中数据刷新到磁盘中
6. Locked：被其他查询锁住了记录
7. Sending Data：正在处理Select查询，同时将结果发送给客户端

8. Info：一般记录线程执行的语句，默认显示前100个字符，想看完整的，使用show full processlist;命令

3.2 查询缓存机制

建立连接后，将进行查询缓存（Cache&Buffer），这是MySQL的一个可优化查询的地方，如果开启查询缓存，只要缓存命中（查询缓存过程中查询到完全相同的SQL语句），则直接将结果返回给客户端，没命中或者没有开启查询缓存的话，则直接交给解析器进行语法语义解析，生成解析树。

1. 作用是缓存Select 查询结果 和 SQL语句
2. 常用命令参数

# 查看查询缓存的相关信息，是否启用，空间大小，查询缓存界限等
show variables like '%query_cache%';
# 查看查询缓存的一些细节，可用缓存空间，缓冲块，缓存了多少等
show status like 'Qcache%';

3. 即时开启查询缓存，以下SQL也不能缓存

1. 查询语句使用了SQL_NO_CACHE参数，屏蔽查询缓存执行
2. 查询结果大于query_cache_limit设置的查询缓存界限
3. 查询中有不确定参数，例如now()，这些一直变，缓存也没意义。

3.3 解析和优化机制

1. 解析器（Parser），将客户端发送的SQL进行语法解析，生成解析树。预处理器根据一些MySQL规则进一步检查"解析树"是否合法，例如这里将检查数据表和数据列是否存在，还会解析名字和别名，看它们是否有歧义，最后生成新的“解析树”。
2. 查询优化器（Optimizer）根据“解析树”生成最优执行计划，MySQL使用很多优化策略生成最优执行计划。主要分两大类：静态优化（编译时优化）、动态优化（运行时优化）。

1. 例如等价变换策略：5=5 and a>5 会直接编译成 a>5。a < b and a = 5 会编译成b > 5 and a = 5。如果使用联合索引（查询条件和索引顺序不一样会失效），此策略也会对条件位置进行一定调整
2. 例如会优化count、min、max等函数。比如InnoDB引擎min函数只需要找索引最左边，InnoDB引擎max函数只需要找索引最右边，MyISAM引擎的count(*)，不需要计算，直接返回等
3. 例如使用limit查询，只获取limit所需数据，不在继续遍历后面数据。也就是提前终止查询
4. 例如in的优化，MySQL对in查询，会先排序，再用二分法查找数据，例如where id in (2,1,3)，会变成 in (1,2,3)

3.4 执行引擎机制

当优化器生成执行计划后，执行引擎就会根据执行计划，执行SQL，此时查询执行引擎会根据SQL语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或物理文件交互。最终得到查询结果返回给客户端

1. 如果开启查询缓存，此时会将SQL语句和结果完整地保存到查询缓存中，以后若有相同的SQL语句执行则直接返回结果。
2. 如果返回结果过多，会采用增量的模式返回（就是不一次返回，而是分批次的返回）

4. MySQL的存储引擎

1. 负责MySQL中数据的存储和提取，与文件交互的子系统，根据MySQL提供的文件访问层抽象接口定制的一种文件访问机制，称为存储引擎
2. 使用show engines命令，可以查看当前数据库支持的引擎信息。可见默认为InnoDB（因为这是5.5之后版本的MySQL，5.5之前版本的MySQL默认是MyISAM引擎）
   

所以，我们主要介绍MyISAM和InnoDB两个引擎，其它做了解即可

1. InnoDB:支持事务，具有提交，回滚和崩溃恢复能力，事务安全。适合处理增删改操作。一般InnoDB就够用。
2. MyISAM：因为支持的东西少（不支持事务和外键等），所以轻装上阵访问速度快，但是事务不安全，不适合增删改操作。只做查询的情况下，为了比InnoDB更快访问速度，会使用MyISAM引擎。
3. MRG_MyISAM：一组MyISAM表的组合，这些表必须结构相同，Merge表本身没有数据，对Merge操作可以对一组MyISAM表进行操作。
4. Memory：利用内存创建表，因为数据在内存，默认使用Hash索引，所以访问速度非常快，但只要关闭，数据就会丢失。所以很少使用，就算有这样的需求，redis等中间件是更好的选择。
5. Archive：归档类引擎，仅支持insert和select语句，适合中文文章处理
6. Csv：专门操作Csv文件，以CSV文件进行数据存储，由于文件现在，所有列必须强制指定为not null，不支持索引和分区，适合做数据交换的中间表。
7. BlackHole：黑洞，只进不出，进去就消失，不会保存插入的数据。会保存二进制执行文件，分布式中可以勉强用于记录执行，做恢复备份。
8. Federated：可以访问远端MySQL数据库中的表。自己会有一个本地表，不保存数据，只用来访问远程表的内容来回显。

回顾MySQL发展史，可以有如下结论

1. 早期MySQL使ISAM作为存储引擎，知道1996年MySQL1.0版本发行，才开始支持SQL接口
2. 1999-2000年，引入了BDB引擎，MySQL才开始支持事务，而ISAM依然不支持事务
3. 2000年4月，MySQL对ISAM整理，命名为MyISAM，依然不支持事务
4. 2001年，4.0版本正式集成了InnoDB引擎，此引擎支持事务和行级锁。而MyISAM只支持表锁，依然不支持事务
5. 2010年4月，MySQL5.5版本发布，分为社区和企业版本，默认引擎由MyISAM变换为InnoDB，并且增加表分区等特性
6. 2013年2月，MySQL对InnoDB进行改造，提供全文检索能力
7. 所以，MyISAM从始至终不支持事务，只支持表锁，虽然访问速度比InnoDB快，但是逐渐不被使用了。InnoDB，支持事务和行锁（也支持表锁），还有很多例如表分区的新特性，也支持全文检索。到了8.0还支持更多增强功能。

4.1 InnoDB和MyISAM的区别

1、事务和外键

1. InnoDB支持事务和外键，安全性，完整性，隔离性较高，适合有大量增删改操作的场景。
2. MyISAM不支持事务和外键，提供高速存储和检索，也提供全文检索（因为8.0InnoDB也提供了全文检索能力，这不是MyISAM的优势了），适合大量查询，少量修改的场景。

2、锁机制

1. InnoDB支持行锁和表锁，可以根据颗粒化的锁住某一特定的记录。后面我们也会细讲锁机制，InnoDB实际上是基于索引加锁来实现。
2. InnoDB只支持表锁，只能锁定整张表。

3、索引结构

1. InnoDB使用聚簇索引（聚集索引），索引和记录存储在一起，即缓存索引，也缓存记录。（因为使用b+树，数据只存储在树的叶子结点）
2. MyISAM使用非聚簇索引（非聚集索引），索引和数据文件分开存放。（也是b+树，叶子结点不存储数据了，而是存储数据的地址。）

4、并发处理能力

1. MyISAM使用表锁，发生写操作时并发能力低。读操作不会阻塞，一旦发生写操作，就会锁住整张表进行排它，此时其它操作（包括读）都会被阻塞。
2. InnoDB读写阻塞与隔离级别有关，可以采用多版本并发控制（MVCC）来支持高并发。并发支持能力强。后面的篇章会细讲。

5、存储文件

1. InnoDB表的存储文件，因为索引和数据存储在一起，所以一般有两个存储文件，.frm表结构文件（保存元数据等），.ibd数据文件（索引和数据都保存到这个文件）。早期版本因为会使用系统表空间，就不使用.ibd文件，而是使用系统的.ibdata这样的文件。
2. MyISAM表的存储文件，因为索引和数据分开存储，所以有三个存储文件，.frm表结构文件，.MYD表数据文件，.MYI表索引文件。
3. InnoDB表最大支持64TB。
4. MyISAM从MySQL5.0开始默认限制是256TB。

适用场景

1. MyISAM：不需要事务支持，并发修改操作相对较低，数据修改相对较少，以读操作为主，数据一致性要求不高的场景。
2. InnoDB：需要事务支持，可能需要高并发改操作，数据更新较频繁，数据一致性要求较高的场景。而且如果硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘IO。

4.2 InnoDB存储结构

1. 官方InnoDB引擎架构图，主要分为内存结构和硬盘结构两大部分(其中Buffer Pool直接越过系统缓存，和磁盘交互。Log Buffer需要先写入系统缓存，然后系统缓存，和磁盘交互)
   
2. In-Memory Structures：内存结构

1. Buffer Pool缓冲池（BP）：分为很多配置块（默认1个）。配置块里面是一个个的"页"(以Page页为单位，默认大小16kb，BP底层采用链表的数据结构管理Page)，主要存放配置（表记录，索引配置等，缓存到这里），有效减少磁盘IO次数，提升效率
2. Change Buffer写缓冲区(CB)：属于Buffer Pool一部分，当要对Buffer Pool中没有的数据进行DML（增删改）操作时，不直接去磁盘操作(不会立刻将磁盘页加载到缓冲池)，而是先放到Change Buffer中记录缓冲变更。等下次Buffer Pool读取相应数据时，再执行操作，将数据合并恢复到BP中。5.5之前叫Insert Buffer，只对Insert进行优化，5.5之后才对增删改都进行优化

1. 简单来讲，就是我希望增删改也在缓存中执行，而不去磁盘中执行，有DML操作时，我先将这些操作，缓存到Change Buffer，等到需要的数据，缓存到Buffer Pool中后（有人想要读这些数据），我再对Buffer Pool中数据，进行DML操作。

3. Log Buffer日志缓冲区：保存要写入磁盘的log文件数据（Redo/Undo），先放在缓冲区，并且会定期刷盘（把缓存内容刷新（同步）到磁盘）到log文件中，默认16M，缓冲区满时，也会自动将其刷新到磁盘，当遇到BLOB或多行更新的大事务操作时，增加日志缓冲区可以节省磁盘I/O。
4. Adaptive Hash Index自适应哈希索引：用于对Buffer Pool的数据查询进行优化，因为Hash索引查找的时间复杂度是O(1)，所以通过Hash索引查找缓存，会更快。InnoDB会监控对表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称为自适应，InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

3. On-Disk Structures：磁盘结构

1. System Tablespace系统表空间：多个表共享，存放这些表的数据和索引

1. InnoDB Data Dictionary(InnoDB数据字典)：由内部系统表构成，表，索引，表字段等表的元数据信息，都存在于这些系统表中（数据字典）。和.frm文件信息有重叠，但是并不完全相同。
2. Doublewrite Buffer(双写缓存)：Buffer Pool中脏页，刷盘时，先会放在Doublewrite Buffer，一方面是速度快，越过了系统缓存。另一方面是双保险，如果底层IO系统发生意外，InnoDB的双写缓存还有备份，不至于数据丢失。另外，默认Buffer Pool不会越过系统缓存，需要特定参数指定，在下方给出了相关命令
3. Change Buffer(写缓冲区)：和Buffer Pool的Change Buffer设计理念一样
4. Undo Logs(撤销（还原）日志)：为了保险，当我们增删改数据时，把修改之前的数据做一个备份（并不是把整个数据文件备份，而是逻辑操作），一旦增删改发生意外，方便我们回滚事务。

# 查看Data文件参数（系统表空间）
	# innodb_data_file_path --- ibdata1:12M:autoextend  表示系统表空间ibdata1，初始12M，满了就自动扩展大小。
show variables like '%innodb_data_file_path%'
# 查看双写缓冲区
show variables like '%innodb_doublewrite%'
# 查看当前innodb刷新的方式,默认为空，表示没有使用任何方法
show variables like '%innodb_flush_method%'
# 让innodb刷新方式为O_DIRECT，可以径直的穿过系统缓存，直接让Buffer Pool和系统表空间交互
set global innodb_flush_method = O_DIRECT;

2. File-Per-Table Tablespaces独立（单独）表空间：推荐使用独立表空间，而不推荐系统表空间。当指定innodb_file_per_table = ON时，此时创建的表占用的就是独立表空间，而不是系统表空间了。也是现在Mysql5.5以上默认的方式。此方式，创建表会为每个表生成.ibd数据文件。

show variables like '%innodb_file_per_table%'

3. General Tablespaces通用表空间：就是好多表，共用一个.ibd文件，需要先创建表空间，并为其指定数据文件和存储引擎，最后创建表的时候，需要将表指定创建到特定表空间

# 创建表空间ts1，指定添加数据文件ts1.ibd,指定存储引擎为innodb
create tablespace ts1 add datafile 'ts1.ibd' engine = innodb;
# 创建表的时候，需要指定表空间，就可以添加到特定表空间
# 创建table1表（表中就一个int型c1主键字段），指定表空间为ts1;
create table table1(c1 int primary key) tablespace ts1;

4. Undo Tablespaces撤销（还原）表空间：MySql5.7之前，占据的是系统表空间，5.7之后，从系统表空间过滤出来了，可以通过命令查看参数。

show variables like '%innodb_undo_tablespaces%'

5. Temporary Tablespace临时表空间：分为当前会话的（当前用户创建的一些临时表，和磁盘内部创建的一些临时表）、和全局临时表空间（系统底层需要使用）。数据不会保存到磁盘，不保险。
6. Undo日志，很多地方都有它的存在，临时表空间，系统表空间，Undo表空间，目的就是安全性，一旦发生意外，方便我们回滚事务。
7. RedoLog重做日志：基于磁盘的数据结构，用来实现事务持久性，当事务提交时，必须先将事务所有日志重做到日志进行持久化，事务Commit操作完成后才算事务完成，一旦中途发生意外，比如突然断电，那么可以根据重做日志，重新进行事务的操作（重做）

4.2.1 Buffer Poll原理

Page管理机制

1. Page根据状态分为3种类型

1. Free Page：空闲page，还没有被使用过的Page
2. Clean Page：被使用Page，但数据还没有被修改过
3. Dirty Page：脏页，被使用Page，并且数据被修改过了，页中数据和磁盘数据产生不一致。需要进行刷盘（将缓存数据刷新到磁盘）。

2. InnoDB通过三种链表结构，管理和维护上述三种Page类型

1. Free List：表示空闲缓冲区，管理Free Page
2. Flush List：表示需要刷新到磁盘的缓冲区，管理Dirty Page。内部Page按修改时间排序。
3. Lru List：表示正在使用的缓冲区，管理Clean Page和Dirty Page。此缓存区以midpoint为基点，基点前的链表区域称为new链表区，基点后的称为Old链表区。

1. new 链表区：存放经常访问的数据，占整个链表的63%。
2. Old链表区：存放较少使用的数据，占整个链表的37%。

4. Dirty Page脏页，即存在于Flush链表，也存在于Lru链表，两种互不影响，各司其职，并且相互配合。LRU链表负责管理Page的可用性和释放。Flush链表负责管理脏页的刷盘操作。

3. 大致的Page管理流程

1. 有新的Page数据读取到Buffer Pool时，InnoDB会先判断是否有空闲页。
2. 如果空闲页足够，就将Free Page从Free List链表中删除，放到LRU List中
3. 如果空闲页不够，就根据LRU算法淘汰LRU链表默认的页，将内存空间释放分配给新的页。

使用了改进型的LRU算法维护页（主要针对Lru List链表）

1. 普通LRU算法：采用末尾淘汰法，新数据从链表头部插入，释放空间时，从末尾淘汰Page。
2. 改进型LRU算法：分为New和Old两个部分，中间使用MidPoint指针分隔。

1. 插入元素时，直接从MidPoint位置插入。
2. 如果插入的数据是热点数据，经常被访问，会逐步向New链表头部移动。
3. 如果插入的数据不经常被访问，会逐步向Old尾部移动，等待淘汰。

4.2.2 Buffer Poll 配置参数

# 查看InnoDB页的大小
show variables like '%innodb_page_size%';
select @@innodb_page_size;# 和上面一句效果相同
select @@innodb_page_size/1024;# 最终结果/1024，也就是按字节为单位显示
# 查看InnoDB的Old区域大小（LRU List的Old List）
	# 返回参数innodb_old_blocks_pct  37 表示大小为37%
	# 返回参数innodb_old_blocks_time 1000 表示访问数据后，向New List区域移动的延迟时间。
show variables like '%innodb_old%'; 
# 查看Buffer Pool主要的一些参数
	# innodb_buffer_pool_size 8388608 表示Buffer Pool的大小
	# innodb_buffer_pool_chunk_size 8388608 表示配置块大小，默认分一块，所以和整个Buffer Pool大小一样
	# innodb_buffer_pool_instances 1 表示实例的个数，默认1个，当Buffer Pool大小较大时，可以配置多个Buffer Poll实例
show variables like '%innodb_buffer%';
# 上面查看的配置参数，都可以通过set global xxxxx进行修改，推荐将Buffer Pool设置为总内存60%~80%，增加实例个数，放在缓存争夺。

4.2.3 Change Buffer 剖析

1. ChangeBuffer占用BufferPool空间，默认占25%，最大允许占用50%，可以根据读写业务量进行调整（参数为innodb_change_buffer_max_size）

# 查看当前change buffer最大大小
show variables like '%innodb_change_buffer_max_size%';
# 修改大小为50%
set global innodb_change_buffer_max_size = 20;

2. 当MDL（增删改）一条记录时，该记录如果存在于Buffer Pool，则直接修改BufferPool，进行一次内存操作
3. 当MDL一条记录时，该记录没有命中（不在Buffer Pool），会直接在ChangeBuffer进行一次内存操作，不去磁盘查询数据，避免一次磁盘IO。

1. 会将MDL操作缓存到ChangeBuffer
2. 当下次查询发生时，会先从磁盘读数据，如果恰好是要进行MDL操作的数据那么此时从ChangeBuffer中读取信息然后对数据进行MDL操作合并，然后载入到Buffer Pool。

4. 仅适用于非唯一普通索引页，因为如果索引设置唯一性，进行修改时，InnoDB必须做一次唯一性校验，必须查询磁盘（一次IO操作）。此时不会再去写缓冲（Change Buffer）操作，而是直接将记录从磁盘查询到Buffer Pool中，然后在缓存池（Buffer Pool）修改。

4.2.4 Log Buffer

1. 主要记录InnoDB引擎日志，DML操作时会产生Redo和Undo日志。
2. LogBuffer空间满了，会自动写入磁盘
3. 常用命令

# 查看相关参数
	# innodb_log_buffer_size 1048576 表示Log Buffer的大小
	# innodb_log_file_size 50331648 表示磁盘上日志文件大小
	# innodb_log_files_in_group 2 表示磁盘上日志文件组，默认两个文件，每个是48M
show variables like '%innodb_log%';
# 计算日志文件大小，单位M，因为除了两个1024
select @@innodb_log_file_size/1024/1024
# 查看日志刷新到磁盘的时机（没满的情况下）
	# innodb_flush_log_at_timeout 1
	# innodb_flush_log_at_trx_commit 1
show variables like '%innodb_flush_log%'

4. 我们上面讲过，Buffer Pool直接越过系统缓存和磁盘交互，而LogBuffer需要先写入到系统缓存，系统缓存负责将缓存刷入磁盘。
5. 上面命令查看日志刷新到磁盘时机中，有innodb_flush_log_at_trx_commit 参数，取值为有0,1,2三种。默认为1。

1. 0：每隔1秒写日志文件(Log Buffer --> OS Cache系统缓存)和刷盘（OS Cache --> 磁盘日志文件）操作，如果发生数据丢失，会丢失掉1s的数据，高并发场景下，是不可接受的。
2. 1：当事务提交，立刻写日志文件和刷盘。数据不会丢失，但是频繁IO（高并发场景下，1秒可能会提交几千、几万、甚至上亿次事务。想想天猫双十一。）
3. 2：当事务提交时，只进行写日志文件到系统缓存，不进行刷盘。每隔1s，才进行刷盘操作。降低IO次数，将前两种做了一个优化整合。

4.2.5 各版本之间的差异

MySQL5.7版本：就是我们上面讲的结构，我们以此作为对比

和5.7之前版本的不同

1. 5.7以前的版本，没有Undo Tablespaces，只有系统表空间的Undo Logs，5.7版本抽了一个Undo Tablespaces
2. 5.7之前的版本没有Temporary Tablespace临时表空间
3. 5.7之前不支持Buffer Pool等内存结构的参数动态调整，也就是改了以后必须重启MySQL实例，5.7版本支持动态，不用重启实例。

MySQL8.x版本的不同之处

1. In-Memory Structures内存结构，没有任何变化
2. On-Disk Structures磁盘结构发生很多变化，主要发生在系统表空间

1. System Tablespace系统表空间，只保留了Change Buffer。将双写缓存，UndoLog和数据字典抽离。
2. 双写缓存从系统表空间抽离，单独分为Doublewrite Buffer Files，保存在.dblwr文件中
3. 因为原来UndoLog和Undo Tablespaces有很多重复，8.x版本将两个合并，都存储在Undo Tablespaces
4. Temporary Tablespaces临时表空间拆分的更细了，原来全局（global）和用户会话（session）产生的临时表都存储在ibtmp1文件中，现在全局存放在ibtmp1，会话session的存放在.ibt文件中。

4.2.6 后台线程

1. IO Thread：在InnoDB中使用大量的AIO（Async IO）来做读写处理，为了极大提高数据库性能。InnoDB1.0版本之前，共有4个IO Thread（write、read、insert buffer（后来改为change buffer）和log thread），后来的版本将read thread和write thread分别增大到4个，一共有10个IO Thread了

1. read thread：负责读操作，将数据从磁盘加载到缓存page页，5.7版本后有4个。
2. write thread：负责写操作，将缓存脏页刷新到磁盘，5.7版本后有4个。
3. log thread：负责将日志缓冲区内容刷新到磁盘，只有1个。
4. insert buffer thread：负责将写缓冲内容刷新到磁盘，只有1个。
   

# 查看innoDB引擎状态，有各种参数，其中就包括Io Thread的数量
show engine innodb status \G;

2. Purge Thread：事务提交后，其使用的Undo日志将不在需要，因此需要Purge Thread回收已经分配的undo页

# 查看Purge Thread状态，是否启用
	# 参数 innodb_purge_therads --- 4 表示启用，共有4个线程。如果是0表示没有启用，没有线程
show variables like '%innodb_purge_thread%';

3. Page Cleaner Thread：将脏数据刷新到磁盘，脏数据刷盘后相应的Redo Log就可以覆盖，即可以同步数据，又可以达到redo log循环使用的目的（会调用write thread线程处理）

# 查看Page Cleaner Thread状态，是否启用
	# 参数 innodb_page_cleaners --- 1 表示启用，共有1个线程。如果是0表示没有启用，没有线程
show variables like '%innodb_page_cleaners%';

4. Master Thread：InnoDB主线程，负责调度其他线程，优先级最高，作用是将缓存池中的数据异步刷新到磁盘，保证数据一致性。如果上面那些线程没有启用，比如脏页的刷新，如果page cleaner thread没有启用，就会由主线程来做，如果启用，那么主线程就会调度page cleaner thread来做，而不是自己做。内部有两个主处理，分别是间隔1秒和间隔10秒处理。

1. 间隔1秒的操作

1. 刷新日志缓冲区到磁盘
2. 合并写缓冲区数据，根据IO读写压力来决定是否操作
3. 刷新脏页数据到磁盘，根据脏页比例默认达到75%才操作（通过参数innodb_max_dirty_pages_pct查看或设置比例），一次刷新默认200页，最大2000页（通过innodb_io_capacity参数和innodb_io_capacity_max查看或设置）

2. 间隔10秒的操作

1. 刷新脏页数据到磁盘，无条件来做，和上面每隔1秒的不一样（需要达到比例，刷的页数也有限制）
2. 合并写缓冲区，无条件来做
3. 刷新日志缓冲区到磁盘
4. 删除无用的undo页（调度Purge Thread来做），根据参数innodb_pruge_batch_size来确定删除多少页（我5.7版本查询是300），也可以通过这个参数设置一次删除多少页

4.2.7 数据文件的存储结构

1. InnoDB文件存储结构如下：
   

1. 一个Tablespage表空间，可以存储很多文件（.ibd文件）
2. 每个.ibd文件又由很多Segment（段，片段的意思）组成，比如数据段（Leaf node segment，叶子结点，B+树叶子结点存储数据），索引段（Non-leaf node segment，非叶子结点，B+树非叶子结点存储索引），回滚段（Rollback segment）

1. 实际文件就一个.ibd文件，剩下的Segment等都是逻辑结构。
2. 一个.ibd文件默认有两个Segment（因为是B+树结构，一个叶子结点管理数据，一个非叶子结点管理索引），每多创建一个索引，会多两个Segment（B+树，叶子和非叶子）

3. 每个Segment又由很多Extent（区，一个范围的意思）构成
4. 每个Extent又由64个连续的Page页构成

1. 默认每个页16KB，所以一个Extent大约1M。
2. 如果表空间不够，需要分配新的页资源时，不会一页一页分配，而是直接分配一个区（Extent）

5. 每个Page页，存放很多的Row(数据记录，行记录，一排，一行的意思)

1. 默认大小16K，包含多种页类型，例如数据页，Undo页、系统页、事务数据页、大的BLOB对象页。

6. 每个Row中，包含事务ID（Trx id）、回滚指针（Roll pointer）、字段指针（Field points）、字段值（Field 1,Field 2…field n）。每个字段指针指向对应字段值。

2. Page是文件最基本单位，无论何种类型，都由Page header、Page trailer、Page body组成
   

4.2.8 数据文件格式查看

1. 通过 "show table status"命令查看表状态的信息如下，下图是dept1表状态的截图，可以通过show table status \G;以列的形式查看，否则以行的形式，不好观察
   

1. 上图中可见只显示了Row_format行格式，没有File_format文件格式，因为文件格式就两种，它们支持不同的行格式，所以通过行格式可以反向推导出是什么文件格式。
2. 如果一定想知道File文件格式的话，可以使用下面的命令

2. 通过information_schema查看指定表的文件格式，FILE_FORMAT字段
   

# 查看innodb_sys_table的文件格式 可以拼接where条件，也可以指定\G以列的形式展示结果
select * from information_schema.innodb_sys_table [where xxx] [\G];

3. 创建表和索引时，文件格式都被用于每个InnoDB表数据文件（其名称与 * .ibd匹配），修改文件格式的方法是重新创建表及其索引，最简单的方法是对要修改的每个表使用如下命令：（也就是你觉得直接创建的表的默认文件格式不满意，可以自己指定）

# 修改特定表的File文件格式
alter table 表名 file_format = 格式类型;
# 修改特定表的行格式
alter table 表名 row_format = 格式类型;

File文件格式（File-Format）

1. 早期InnoDB版本中，文件格式只有一种，随着InnoDB引擎的发展，出现了新的文件格式以支持新的功能，目前InnoDB只支持两种文件格式：

1. Antelope：MySQL5.6及以前版本默认格式，原始InnoDB文件格式，早期版本中默认的未命名的文件格式，支持两种行格式：COMPACT和REDUNDANT。
2. Barracuda：新格式，5.7版本默认的格式。支持InnoDB所有行格式，包括新的COMPRESSED和DYNAMIC行格式。

2. 通过innodb_file_format配置参数可以设置innoDB文件格式，之前默认值为Antelope，5.7版本开始改为Barracuda。
3. 一般，row_format为REDUNDANT或COMPACT，文件格式是原始的Antelope
4. 如果row_format为COMPRESSED和DYNAMIC，文件格式一定是Barracuda

Row行格式（Row_format）

1. 表的行格式决定了它的行，如何进行物理存储，会影响查询和DML操作的性能。如果在单个Page页中容纳更多行，查询和索引查找可以更快地工作，缓冲池中所需要的内存更少，写入更新时所需的I/O更少。
2. InnoDB支持4种行格式：REDUNDANT、COMPACT、DYNAMIC和COMPRESSED。

4.3 InnoDB 日志

4.3.1 Undo Log日志

1. Undo：撤销或取消的意思，返回指定某个状态的操作。
2. Undo Log：数据库事务开始前，会将要修改的记录放在Undo日志中，当事务回滚时或数据库崩溃时，可以利用Undo日志，撤销未提交事务对数据库产生的影响。
3. Undo Log产生时机和销毁时机

1. Undo Log在事务开始前产生
2. 事务提交时，innodb会将该事务对应Undo Log放入删除列表中(只是放进去，没有删除)
3. 最后统一通过后台线程Purge Thread进行回收。

4. Undo Log并不是将整个数据备份，而是物理备份，记录变化过程和数据。后面可以通过记录的变化过程，恢复状态。简单的举个例子：假设提交一个insert事务，此时Undo Log会记录一个delete，如果insert事务需要回滚，就执行Undo Log的delete，将事务回滚（插入一条数据，需要回滚，就把数据再删了就可以了）
5. Undo Log存储：前面说.ibd文件会存储很多segment段，其中有rollback segment回滚段，这个回滚段，内部包含若干个(大多数情况是1024个或1028个)Undo Log Segment

# 查看 Undo Log的相关参数
	# 参数innodb_undo_logs --- 128 指日志文件大小为128KB
	# 参数innodb_undo_tablespaces --- 0 指占用的表空间，0表示系统表空间。如果值>0表示使用Undo Tablespaces（Undo 表空间）
		# 假设取值为2，就表示Undo表空间，日志文件有两个
show variables like '%innodb_undo%'

Undo Log的作用

1. 实现事务原子性：Undo Log是为了实现事务的原子性而出现的，事务处理过程中，如果出现了错误或者用户执行RollBack回滚语句，MySQL可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。
2. 实现多版本并发控制（MVCC）：Undo Log在MySQL InnoDB存储引擎中用来实现多版本并发控制。事务未提交之前，Undo Log保存未提交之前的数据，这些数据可以作为数据旧版本快照供其他并发事务进行快照读。
   

1. 事务A手动开启事务，执行更新操作，首先会把更新命中的数据备份到Undo Buffer（不是Undo Log）中
2. 事务B手动开启事务，执行查询操作，会读取Undo Buffer中的快照数据（数据状态）并返回（进行快照读）。

4.3.2 Redo Log日志

1. 重做日志，以恢复操作为目的，数据库发生意外时重做操作。为了实现事务持久性而出现的技术，防止在发生故障的时间点，尚有脏页未写入表的IDB文件中，一旦发生意外，那么这些脏页也就随之丢失，那么重启MySQL服务后，就可以根据Redo Log记录的操作，进行事务重做。
2. 记录你准备做的操作，进行备份，一旦磁盘有问题或者其它意外情况，导致你的事务无法完成，可以根据Redo Log中的操作备份，进行重做。
3. 生成和销毁时机

1. 事务操作执行时，会生成Redo Log
2. 事务提交时，将产生的Redo Log写入Log Buffer，没有随着事务的提交而写入磁盘，只有后台工作线程log Buffer持久化日志时，才会写入磁盘。
3. 当事务操作的脏页写入磁盘后，Redo Log任务完成，其占用的空间就可以被覆盖了（注意不是特意的去删除，而是标记为可覆盖）。

4. 工作原理
   

1. 假设对User表进行事务，那么先备份到Undo Buffer，以防需要回滚
2. 然后记录事务操作到Redo Buffer，按顺序记录，保存事务提交的状态信息，随后会持久化到磁盘Redo Log。
3. 如果事务意外中断，那么根据Redo Buffer记录的状态信息，进行重做。
4. 为什么不直接写入磁盘？非要经过Redo Log？

1. 首先，一次事务，如果每个操作都直接写入磁盘，IO次数太高，而且IBD文件的记录，不是顺序排放的，磁盘寻址也会花费大量时间
2. 其次，虽然Redo Log也要走IO，但它加强了安全性，可以让我们重做事务，并且它是按顺序记录，省去了大量的磁盘寻址时间。

5. 写入机制：Redo Log文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写。
   

1. Write Pos写入指针：当前记录位置，一边写一边后移，到最后一个文件末尾后，回到开头继续写
2. checkPoint检查指针：当前擦除位置，和写入指针一样，循环后移，擦除记录前，需要把记录持久到数据文件。就是标记一下，是否可以覆盖。
3. 如果Write Pos追上CheckPoint，表示当前日志文件写满，此时不能执行新的更新，得停下来让checkPoint推进一下，擦除一些记录。

6. 相关配置参数

1. 每个InnoDB存储引擎至少有一个重做日志文件组（Group），每个文件组至少有两个重做日志文件，默认为ib_logfile0和ib_logfile1。下面的参数可以控制Redo Log存储。
   

# 查看日志相关参数
show variables like '%innodb_log%'
# 查看Redo Buffer持久化到Redo Log的策略
show variables like '%Innodb_flush_log_at_trx_commit%';
	# Innodb_flush_log_at_trx_commit --- 1参数 表示Redo Buffer持久化到Redo Log的策略
		# 取值为0：每秒提交Redo Buffer->OS cache->flush cache to disk,可能丢失1s内事务，由后台Master线程每隔1秒执行一次，mysql挂了，肯定丢失1秒数据
		# 取值为1：默认的，每次事务提交，就执行持久化Redo Buffer->OS cache->flush cache to disk，最安全，但性能最差,几乎不会丢失数据，因为到了系统缓存立即异步进行持久化
		# 取值为2:0和1的结合，每次事务提交,刷新到系统缓存,Redo Buffer->OS cache。而持久化操作由Master线程每隔1秒执行，OS cache -> flush cache to disk
		# 建议的取值是2，效率比取值为1的策略高。MySQL挂了数据没损失，整个服务器挂了损失1秒事务提交的数据

4.3.3 Binlog日志

1. Binlog是MySQL Server自己的二进制日志（Binary log），Redo Log是属于InnoDB引擎持有的日志。
2. Binlog是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记录SELECT和SHOW这类操作。以事件形式记录，还包含语句所执行的消耗时间。开启Binlog日志有两个最重要的使用场景“主从复制”、“数据恢复”。也就是你不小心把库把表删了，这个Binlog日志可以恢复。，默认是关闭的，可以通过参数打开，但会消耗额外的系统资源。

1. 主从复制：可以在主库开启binlog，主库可以把binlog传递给从库，从库可以拿Binlog实现数据的恢复，或者数据的同步，达到主从数据一致性。
2. 数据恢复：如果真有删库，删表，或者删掉重要数据跑路的情况发生，可以通过mysqlbinlog等工具，根据binlog恢复数据。

3. Binlog文件名默认为"主机名_binlog-序列号"，例如oak_binlog-000001，也可以在配置文件中指定名称，文件的记录模式有STATEMENT、ROW和MIXED三种。

1. ROW(row-based replication,RBR)：日志中会记录每一行数据被修改的情况，然后在slave端（从库）对相同的数据进行修改。可以完全记录每一个改变，实现完全的主从数据同步和数据恢复，但是会产生大量的日志，尤其批量操作时，比如alter table。它会记录每一行，产生的日志是爆炸性的。
2. STATMENT(statement-based replication，SBR)：每一条被修改数据的SQL都会记录到master的Binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。简称SQL语句复制。日志量少，减少IO，提升存储和恢复效率。但是安全性低一些。不适合主-从架构数据同步。
3. MIXED(mixed-based replication，MBR)：以上两种模式的混合使用，一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写入模式。
4. 如果要在主-从的场景使用，最好使用ROW，因为STATMENT和MIXED记录的是SQL语句，无法完全的实现数据一致性，SQL语句也有很多变量，例如now()这样的随时间变化的函数。MIXED虽然会自动根据SQL情况选择ROW，但是依然改变不了它记录的是SQL的事实。

4. Binlog文件结构：

1. MySQL的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Log event。不同的修改操作对应不同的log event，常见的log event有Query event（主要记录查询事件）、Row event（insert等修改事件）、Xid event等。
2. binlog文件的内容就是各种Log event的集合
3. Binlog文件中Log event结构如下：
   

5. 写入机制

1. 根据记录模式和操作触发event事件生成log event（根据事件生成对应log event）
2. 将事务执行过程中产生的log event写入缓冲区，每个事务线程都有一个缓冲区（写入缓冲区，保存在一个binlog_cache_mngr数据结构中，此数据结构有两个缓冲区，一个stmt_cache存放不支持事务的信息，另一个trx_cache存放支持事务的信息）
3. 事务在提交阶段会将产生的log event写入到外部binlog文件中（事务提交，log event写入binlog。不同事务以串行方式将log event写入，所以一个事务的log event信息在binlog文件是连续的，中间不会穿插其它事务的log event）
4. binlog是引擎插件上层的功能（Mysql Server），事务提交时，首先第一个就会调用binlog功能接口，然后才调用其它存储引擎的功能接口，因此先写binlog，然后再执行innodb的redo log/undo和脏页刷新操作。

4.3.4 Binlog文件操作和数据恢复

4.3.4.1 文件操作

1. Binlog状态查看：发现这里log_bin文件名为mysqlbinlog
   

show variables like '%log_bin%';

2. 开启Binlog功能
   

如果提示ERROR 1238（HT000）：Variable ‘log_bin’ is a read only variable,说明此选项只读，不可修改。需要修改my.cnf（Linux）或my.ini（Windows）配置文件

set global log_bin = mysqllogbin;

报错的话，在[mysqld]下面增加log_bin=mysqlbinlog(要和前面查看binlog状态显示的文件名对应)，重启MySQL服务。

#log-bin=ON # 此配置是开启binlog
#log-bin-basename=mysqlbinlog # 此配置是指定文件名字
log-bin=mysqlbinlog # 指定binlog的文件名并开启，相当于上面两个配置的整合
binlog-format=ROW # 指定记录模式

3. 查看binlog文件。mysql环境下，不详细
   
   
   
   

# 查看binlog有哪些文件，大小是多少
show binary logs;#等价于show master logs;
# 查看当前是往那个binlog文件写入
show master status;
# 查看binlog记录的事件，默认mysqlbinlog.000001文件的记录
show binlog events;
# 查看binlog指定文件的事件记录
show binlog events in 'mysqlbinlog.000002';

4. 查看binlog文件信息，系统环境下，结果更详细
   
   

# 查看指定文件的日志
mysqlbinlog "文件名"
# 将控制台看到的日志信息，输出到指定文件中
mysqlbinlog "文件名" > "test.sql"

4.3.4.2 数据恢复

Mysql提供很多工具，帮助我们备份，这里只介绍通过命令行，根据binlog日志进行恢复

常用工具：mysqldump：定期全部备份数据库数据。mysqlbinlog：可以增量备份和恢复

也就是说需要从9645恢复到10911

以上图日志为例，进行恢复！注意：必须在系统环境下执行命令，而不是在mysql命令行

# 按事件位置号恢复
mysqlbinlog --start-position=9645 --stop-position=10911 mysqlbinlog.000002 | mysql -u你的用户名 -p你的密码
# 按指定时间进行恢复,这里需要系统环境下查看，然后根据事件恢复，下面是我举个例子，时间并不准确。
mysqlbinlog --start-datetime="2022-09-07 18:00:00" --stop-datetime="2022-09-08 00:00:00" mysqlbinlog.000002 | mysql -uroot -p1234

4.3.4.3 binlog删除

手动删除

# 直接删除整个文件
purge binary logs to 'mysqlbinlog.000001';
# 删除指定事件之前的文件，before是之前，可以换其它单词
purge binary logs before '2020-04-08 00:00:00';
# 清除所有文件
reset master;

定时自动删除

show variables like '%expire_logs_days%'参数查看，自动清理binlog间隔

1. value为0：没有启用
2. value为非0：例如1，超出1天的文件，自动清除。通过set global expire_logs_days = xxx设置就好。

4.3.5 Redo Log和Binlog的区别

1. Redo Log是属于InnoDB引擎功能，Binlog属于Mysql Server自带的，以二进制文件记录
2. Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程
3. Redo Log日志是循环写，日志空间大小是固定的。Binlog是追加写入，写完一个写下一个，不会覆盖使用。
4. Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢复使用，Binlog没有自动crash-safe能力（应急安全），主要用于数据恢复，主从同步。