一.binlog日志

binlog是通过追加的方式进行写入的，将因为SQL修改的逻辑写到binlog中，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志

根据MySQL 文档，写Binlog 的时机是：SQL transaction 执行完，但任何相关的Locks 还未释放或事务还未最终commit 前。 这样保证了Binlog 记录的操作时序与数据库实际的数据变更顺序一致。

binlog是逻辑日志，主要用来存储sql语句

①binlog日志格式

• binlog日志有三种格式，分别为STATMENT、ROW和MIXED。

  在 MySQL 5.7.7之前，默认的格式是STATEMENT，MySQL 5.7.7之后，默认值是ROW。日志格式通过binlog-format指定。

  • STATMENT基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 从而提高了性能； 缺点：在某些情况下会导致主从数据不一致，比如执行sysdate()、slepp()等。

  修改语句是怎么样的就记录什么

  • ROW基于行的复制(row-based replication, RBR)，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了。 优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题； 缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。

  如下面这个语句：

  update table set time=now() where id=1;
  

  在binlog中的实际记录是

  update table set time=12234322 where id=1;
  

  从而保证了数据的一致性

  • MIXED基于STATMENT和ROW两种模式的混合复制(mixed-based replication, MBR)，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法保证一致性的时候使用ROW模式保存binlog

②binlog写入机制

binlog的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。

因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。

上图的 write，是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快

上图的 fsync，才是将数据持久化到磁盘的操作**

write和fsync的时机，可以由参数sync_binlog控制，默认是0。

• 0：表示每次提交事务都只write，由系统自行判断什么时候执行fsync。

优点：效率更高；缺点：page cache中的binlog会丢失

• 1：表示每次提交事务都会执行fsync，就如同 redo log 日志刷盘流程 一样。
• n : 可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。

优缺点：在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。同样的，如果机器宕机，会丢失最近N个事务的binlog日志。

③binlog缓冲区详解

答： 如下图所示，bin log缓冲区和我们的redo log和undo log缓冲区有那么点不同，可以看到redo log和undo log缓存都在存储引擎的共享缓冲区缓冲区buffer pool中，而bin log则是为每个工作线程独立分配一个内存作为bin log缓冲区。

需要补充的是bin log之所以是在每个线程中，是为保证不同存储引擎的兼容性，bin log是innodb独有的，如果将bin log放到共享缓冲区时很可能导致兼容性问题，将bin log缓冲区设置为每个线程独享也保证了事务并发的安全性。

④binlog 文件命名格式

答: 我们可以通过下面这条SQL语句看到我们本地的bin log文件

show binary logs;

输出结果如下所示，可以看到bin log的格式基本都是mysql-bin.0000xxx

mysql-bin.001606  440052  No
mysql-bin.001607  111520  No

⑤binlog作用

主从复制

在Master端开启binlog，然后将binlog发送到各个Slave端，Slave端重放binlog从而达到主从数据一致。

具体的复制原理

（1）master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；

（2）slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件

（3）同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

• 从库会生成两个线程,一个I/O线程,一个SQL线程;
• I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;
• 主库会生成一个log dump线程,用来给从库I/O线程传binlog;
• SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;

数据恢复

如果我们不小心操作数据库，比如删除了某些数据，我们可以通过binlog来进行数据的恢复。

因为binlog一直是追加记录的逻辑sql，我们可以分析binlog，删除我们的错误操作，再对binlog进行重放，就可以进行数据恢复了

我们使用mysqlbinlog工具来进行数据的恢复。具体恢复方式请看另一篇文章。

二.redolog

redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。

比如 MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。

MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。

后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。

更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。

然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

理想情况，事务一提交就会进行刷盘操作，但实际上，刷盘的时机是根据策略来进行的。

①刷盘时机

mysql可以通过配置来确定redo log buffer 写到redo log file 的时机（即用户态进入内核态并进行fsycn()）。

innodb_flush_log_at_trx_commit：默认为1

0 延迟写：事务提交时不会将 redo log buffer 中日志写入到 os buffer ，而是每秒写入 os buffer 并调用 fsync() 写入到 redo log file 中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。

1 实时写，实时刷：事务每次提交都会将 redo log buffer 中的日志写入 os buffer 并调用 fsync() 刷到 redo log file 中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。

**2 实时写，延迟刷：**每次提交都仅写入到 os buffer（其实就是page cache） ，然后是每秒调用 fsync() 将 os buffer 中的日志写入到 redo log file 。

另外，InnoDB 存储引擎有一个后台线程，每隔1 秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。

也就是说，一个没有提交事务的 redo log 记录，也可能会刷盘。

为什么呢？

因为在事务执行过程 redo log 记录是会写入redo log buffer 中，这些 redo log 记录会被后台线程刷盘。

除了后台线程每秒1次的轮询操作，还有一种情况，当 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘。

下面是不同刷盘策略的流程图。

innodb_flush_log_at_trx_commit=0

为0时，如果MySQL挂了或宕机可能会有1秒数据的丢失。

innodb_flush_log_at_trx_commit=1

为1时， 只要事务提交成功，redo log记录就一定在硬盘里，不会有任何数据丢失。

如果事务执行期间MySQL挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失。

innodb_flush_log_at_trx_commit=2

为2时， 只要事务提交成功，redo log buffer中的内容只写入文件系统缓存（page cache）。

如果仅仅只是MySQL挂了不会有任何数据丢失，但是宕机可能会有1秒数据的丢失。

②日志文件组

redo log 实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此 redo log 实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志。

write pos到check point之间的空白数据就是待写入的。

硬盘上存储的 redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。

比如可以配置为一组4个文件，每个文件的大小是 1GB，整个 redo log 日志文件组可以记录4G的内容。

它采用的是环形数组形式，从头开始写，写到末尾又回到头循环写，如下图所示。

在个日志文件组中还有两个重要的属性，分别是 write pos、checkpoint

• write pos 是当前记录的位置，一边写一边后移
• checkpoint 是当前要擦除的位置，也是往后推移

每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。

每次 MySQL 加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把 checkpoint 后移更新。

write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录。

如果 write pos 追上 checkpoint ，表示日志文件组满了，这时候不能再写入新的 redo log 记录，MySQL 得停下来，清空一些记录，把 checkpoint 推进一下。

③mysql重启时怎么知道要读redo log

同时我们很容易得知， 在innodb中，既有 redo log 需要刷盘，还有 数据页 也需要刷盘， redo log 存在的意义主要就是降低对 数据页 刷盘的要求 。在上图中， write pos 表示 redo log 当前记录的 LSN (逻辑序列号)位置， check point 表示 数据页更改记录** 刷盘后对应 redo log 所处的 LSN (逻辑序列号)位置。 write pos 到 check point 之间的部分是 redo log 空着的部分，用于记录新的记录； check point 到 write pos 之间是 redo log 待落盘的数据页更改记录。当 write pos 追上 check point 时，会先推动 check point 向前移动，空出位置再记录新的日志。

启动 innodb 的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。因为 redo log 记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如 binlog )要快很多。 重启 innodb 时，首先会检查磁盘中数据页的 LSN ，如果数据页的 LSN 小于日志中的 LSN ，则会从 checkpoint 开始恢复。 还有一种情况，在宕机前正处于 checkpoint 的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度，此时会出现数据页中记录的 LSN 大于日志中的 LSN ，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

④为什么要设计binlog，直接poolbuffer数据页刷盘不好吗

1. innodb是以页为单位与磁盘进行交互的，而一个事务可能只会更新一个页中的几个字节，这时候将整个页与磁盘进行保存，非常浪费资源，消耗时间。
2. 一个事务当中的多个修改操作可能涉及到多个数据页，这些页在物理磁盘上也不一定是连续的，使用io进行读写操作性能太差。

⑤崩溃恢复能力

innodb专属，主要功能就是恢复buffer pool的数据

三.undo log

①简介

我们知道如果想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。

如果执行过程中遇到异常的话，我们直接利用 回滚日志 中的信息将数据回滚到修改之前的样子即可！并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

另外，MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改

undo日志与多版本并发控制有很大的关系（MVCC）

undo日志其实是保证了事务回滚的有效性，保证了原子性（可回滚）

隔离级别中 可重复度和读已提交两种隔离级别就是通过MVCC来实现的。

②详解

与undo日志有关的两个重点：

• undo日志版本链：undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚 日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链

其中trx_id是事务的id，roll_pointer指向上一条undo log记录。

• 一致性视图

在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的**一致性视图read-view，**该视图在事务结束 之前都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成)，这个视图由执行查询时所有未提交事 务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应

版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。

版本链比对规则：

\1. 如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的；

\2. 如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的 trx_id 就是当前自己的事务是可见的）；3. 如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况

a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自 己的事务是可见的)；

b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。

MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取

同一条数据在版本链上的不同版本数据。

四.两阶段提交协议

答： 哦，这个就是人们常说的为什么我有了undo log，你还需要bin log呢？而且这两个日志我到底要先写哪个才能保证主从数据库的一致性呢？ 对此我们不妨用反正法来说明:

1. 假设我们先写bin log，当事务提交后bin log写入成功，结果再写redo log期间，数据库挂了。重启恢复后，主数据库工具redo log恢复到bin log写入前的样子，而从数据库在工具bin log进行数据同步时发现bin log有一条写入操作，最终从数据库比主数据库多了一条数据。
2. 我们再假设写redo log，假设事务执行期间我们就写了redo log，在事务提交之后写bin log数据库挂了，我们重启数据库后主主库恢复。主库根据redo log进行灾备恢复，将我们更新的数据同时恢复回来，而从库根据bin log进行数据同步时，并没有察觉到主库刚刚写入的数据，这就导致了从库比主库少了一条数据。

所以MySQL设计者提出了二阶段提交的概念，整体步骤为:

1. 在事务开始时，先写redo-log(prepare)。
2. 事务提交时，再写bin log。
3. 事务提交成功，再写redo-log(commit)。

有了这样一个整体步骤我们不妨用两种情况来举个例子吧:

假设我们有一张user表，这张表只有id、name两个字段。我们执行如下SQL:

update user set name='aa' where id=1;

按照二阶段提交，

①假如我们在redo log提交时数据库宕机，二阶段是如何保证数据一致性的呢？

首先数据库重启恢复，然后主库发现redo log日志处于prepare而且bin log也没有写入，所以一切恢复到之前的样子(事务回滚)，而从库对此无感，同步时也是同步成操作失败之前的样子，一切风平浪静。

②假如我们bin log进行commit成功之后数据库宕机，二阶段提交是如何保证数据库一致性的呢？

还是老规矩，数据库重启恢复，然后主库发现bin log有个commit成功的数据，虽然redo log处于prepare阶段，但是我们还是可以根据情况推断出有个当前主库有个commit成功的事务，所以redo log会根据bin log将未commit的数据commit了，然后从库根据主库的bin log发现有新增一条新数据，由此同步一条更新数据，双方都有了一条新数据，数据库一致性由此保证

五.ACID的特性是如何保证的

InnoDB 是 MySQL 中常用的存储引擎之一，它通过实现ACID（原子性、一致性、隔离性和持久性）特性来保证数据的完整性和可靠性。以下是 InnoDB 如何保证这些特性的简要解释：

1. 原子性（Atomicity）： 原子性确保事务中的所有操作要么全部完成，要么全部回滚，以保持数据的一致性。InnoDB 通过日志记录和回滚段（undo log）来实现原子性。当事务开始时，InnoDB 将所有的修改操作写入一个事务日志（transaction log）。如果事务无法成功完成，系统可以根据日志中的信息来回滚所有操作，保证数据的原子性。
2. 一致性（Consistency）： 一致性确保在事务开始和结束时，数据的状态保持一致。InnoDB 在执行修改操作前会进行一些约束和检查，以确保数据的完整性。如果违反了约束，事务将无法提交，保证了数据的一致性。
3. 隔离性（Isolation）： 隔离性指的是每个事务都应该与其他事务隔离开来，互不干扰。InnoDB 支持多个事务同时并发执行，使用了多版本并发控制（MVCC）来实现隔离性。MVCC 通过为每个事务创建不同的版本，使得读操作不会阻塞写操作，并且读操作不会读取到未提交的数据。
4. 持久性（Durability）： 持久性确保一旦事务提交，数据将被永久保存，即使发生了系统崩溃。InnoDB 使用日志记录（redo log）来实现持久性。在事务提交时，修改操作会首先写入 redo log，然后再写入数据页。即使在崩溃后，系统可以根据 redo log 重新应用所有修改操作，以恢复数据到一致的状态。

InnoDB 的ACID特性和其底层的存储结构、事务管理和日志机制紧密结合，以确保数据的安全性和一致性。这些特性使得 InnoDB 成为许多应用场景下的首选存储引擎，尤其是那些需要高度可靠性和事务支持的应用。