MySQL日志系统原理笔记

深碍√TFBOYSˉ_ 2023-01-01 15:53 129阅读 0赞

# MySQL日志系统原理 #

## Undo log ##

undo log意思为取消或者撤销，以撤销作为目的，返回某个状态的操作。

数据库事务开始之前，会将要修改的记录存放到Undo log日志里，当事务回滚时候或者数据库崩溃时，可以利用undo log 日志，撤销未提交事务对数据库产生的影响。

#### Undo log 产生销毁: ####

undo log 在事务开始之前产生；在事务提交时，并不会立刻删除undo log， InnoDb会将该事务对应的undo log 放入到删除列表中，后面会通过后台线程进行回收处理。

Undo log属于逻辑日志，记录数据变化过程；  
删除对应插入； delete -> insert  
更新对应更新（旧数据）； update->update

#### Undo log 存储 ####

undo log 采用段的方式管理和记录。  
在InnoDB数据文件中包含一中rollback segment 回滚段，内部包含1024个undo log segment。

查看undo log参数

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70]

#### Undo log作用 ####

*  实现事务的原子性  
    实现事务回滚数据，事务如果执行过程中出现错误或者异常或者用户执行回滚语句，可以利用undo log恢复到提交之前的数据（事务回滚)；
 *  实现多版本并发控制MVCC  
    事务未提交之前，undo log保存提交之前的版本数据，undo log中的数据可作为数据旧版本快照供其他并发事务进行快照读。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 1]

*  事务A手动开启事务，执行更新操作，首先会把数据备份更新到undo buffer缓存中。
 *  事务B手动开启事务，执行查询操作，会读取undo buffer 中的数据，进行快照读，最后刷会磁盘。

这样就不会产生影响，当事务A在更新的时候，其他事务查询就不会产生互相影响干扰。

## Redo log ##

#### Redo log是什么？ ####

Redo log是InnoDB 特有的一个日志，顾名思义就是重做的意思。用来恢复操作，在数据库发生意外的时候重新操作。

Redo log 指的是事务中修改的任何数据，将最新的数据备份存储在Redo log中，被称为重做日志；

Redo log的生成和释放：当有数据进行更新的时候，InnoDB引擎先把记录写到Redo log中(这里并不是更新一次就写磁盘一次,减少IO次数，提高效率)，然后再刷单磁盘Ibd文件里（系统空闲的时候)，然后释放Redo log空间。

`Redo log 和 IBD刷盘都是IO 有什么区别?`

> Redo log是顺序写，刷磁盘IBD需要找到某个记录的位置，在磁盘上是随机IO，效率很低。

工作原理如下图

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 2]

Redo Buffer 是 Redo log的缓存，Undo Buffer是Undo log的缓存，现在buffer缓存中进行然后再通过线程刷到文件里；

#### Redo log写入机制 ####

write 写入位置，一边写一边后移，写到最后一个文件末尾就返回到0号文件开头。  
check point 是擦除位置，擦除记录前要把数据更新到文件也就是刷到磁盘。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 3]

InndoDB Redo log大小是固定的，如果写满的时候我们可能要去擦掉一部分，然后再进行重写，属于循环写，一般开发中我们会将Redo log尽量调大点。

#### Redo log 配置参数 ####

每个InnoDB存储引擎至少有1个重做日志文件组，每个文件组至少有2个重做日志，默认是ib\_logfile0和ib\_logfile1。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 4]

#### Redo buffer 持久化到Redo log 策略 ####

参数设置 `set innodb_flush_log_at_trx_commit = 1`（默认是1）

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 5]

*  0：每秒提交Redo buffer 可能会丢失一秒的事务数据，后台master线程每隔1秒执行一次;  
    由redo buffer -> os cache -> flush cache to disk
 *  1 默认值：每次提交事务都执行redo buffer -> os cache -> flush cache to disk ,数据最安全，性能最差。
 *  2 每次事务提交执行Redo buffer -> os cache 。然后master线程才每隔1秒刷盘操作。

## Bin log ##

Bin log是所有server 层的二进制日志，属于逻辑日志，以事件形式记录了数据完整变化日志。  
如：表结构变更，数据变更等。

使用场景：

*  主从复制
    
    > 在主库中开启Binlog 功能，主库就可以把Binlog传递给从库，从库拿到之后实现和主库数据一致性。
 *  数据恢复
    
    > 通过mysqlbinlog 工具来恢复数据。

#### bin log 行格式 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 6]

## bin log 日志机制 ##

写入机制  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 7]

## bin log 日志分析和数据恢复 ##

binlog 状态查看

默认是关闭的

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 8]

开启Bin log功能

在配置my.conf / my.init文件下 增加log\_bin=mysql\_bin\_log,然后重启服务。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 9]

Binlog配置并且打开成功

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 10]

#### 查看binlog日志文件 ####

`show binary logs`

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 11]

查看当前Binlog 文件

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 12]

查看当前Binlog文件内容

`show binlog events in 'mysql-bin-log.000001'`  
可以看到大量的日志信息  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 13]

使用`mysqlbinlog`查看内容

首先查找日志

`show variables like 'log_%’;`

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 14]

首先找到日志文件位置

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 15]

`mysqlbinlog` 命令查看 mysqlbinlog ‘mysql-bin-log.000001’

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 16]

#### 转换为sql 脚本命令 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 17]

#### 使用binlog恢复日志 ####

回滚基于时间恢复

> mysqlbinlog --start-datetime=“2016-09-25 21:57:19” --stop-datetime=“2016-09-25 21:58:41” mysql-bin-log.000001 | mysql -uroot -proot

基于事件位置号恢复

> mysqlbinlog mysql-bin-log.000001 --start-position 154 --stop-position 755 | mysql -uroot -p mytest

## bin log 和redo log区别 ##

*  redo log 是 InnoDB 引擎特有的；
 *  binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；
 *  binlog 是逻辑日志，记录的是这个语句的原始逻辑。redo log 是循环写的，空间固定会用完；
 *  binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

`为什么会有bin log 和redo log两份日志呢？`

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70]: /images/20221120/ff3a2e808ae94e85aa0358e88e9595e4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 1]: /images/20221120/8726de9d15774ccb84699e77422a3fa7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 2]: /images/20221120/0c1e54ebb0834d269aa3659afd5f31a1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 3]: /images/20221120/8d6c7f3ed92f4d42a5f70a8f704fb754.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 4]: /images/20221120/c485cdc199c14257ad854720f72df5ce.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 5]: /images/20221120/f7991fe2fff84097a402e2a4d21278f5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 6]: /images/20221120/b50b16fa66a04923b49a6d9409ed3aa3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 7]: /images/20221120/e94333a54a034770a8048337f3f832c9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 8]: /images/20221120/9b5f05ea0e464a8a895fc1084b58e84d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 9]: /images/20221120/e5cb72b577eb400e9a03adb1865a916c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 10]: /images/20221120/0cb3d50bbbe44d13b33d7228807b5c5f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 11]: /images/20221120/e8eb95ebb2e344d6ae682e34c15138e1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 12]: /images/20221120/8a64f585170d47d4bda9f678f85e0c0d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 13]: /images/20221120/db964c1b64cc4171a415797253469915.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 14]: /images/20221120/bc6aa2e6aad24d3fa35e8b1b157ceea2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 15]: /images/20221120/b58c1e1ae8de46539f38286c8068fe41.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 16]: /images/20221120/a1965397e42045e29b3b09f3efcb9b7a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 17]: /images/20221120/693f757d81b14081b840317e1f54721e.png