mysql日志篇

最新推荐文章于 2024-03-25 22:48:00 发布
最新推荐文章于 2024-03-25 22:48:00 发布
阅读量529 收藏
点赞数
分类专栏： Mysql进阶之路 文章标签： mysql 数据库 java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/liulong999/article/details/128330445
版权

mysql日志篇

1、Undo-log 撤销日志

       Undo即撤销的意思，大家通常也习惯称它为回滚日志。如果大家有仔细研究过 MySQL 的日志，应该会发现 Undo-log 并不存在单独的日志文件，也就是磁盘中并不会存在 xx-undo.log 这类的文件，那 Undo-log 存在哪儿呢？InnoDB 默认是将 Undo-log 存储在 xx.ibdata 共享表数据文件当中，默认采用段的形式存储。
       也就是当一个事务尝试写某行表数据时，首先会将旧数据拷贝到 xx.ibdata 文件中，将表中行数据的隐藏字段：roll_ptr 回滚指针会指向 xx.ibdata 文件中的旧数据，然后再写表上的数据。实际上当一个事务需要回滚时，本质上并不会以执行反 SQL 的模式还原数据，而是直接将 roll_ptr 回滚指针指向的 Undo 记录，从 xx.ibdata 共享表数据文件中拷贝到 xx.ibd 表数据文件，覆盖掉原本改动过的数据。
在这里插入图片描述
备注:如果是 insert 操作，由于插入之前这条数据都不存在，那么就不会产生 Undo 记录，此时回滚时如何删除这条记录呢？因为插入操作不会产生 Undo 旧记录，因此隐藏字段中的 roll_ptr=null，因此直接用 null 覆盖插入的新记录即可，这样也就实现了回滚的效果

       Undo-log 中记录的旧数据并不仅仅只有一条，一条相同的行数据可能存在多条不同版本的 Undo 记录，内部会通过 roll_ptr 回滚指针，组成一个单向链表，而这个链表则被称之为 Undo 版本链，案例如下：

-- 事务T1：trx_id=1（两次修改同一条数据）
UPDATE `zz_users` SET user_name = "竹子" WHERE user_id = 1;
UPDATE `zz_users` SET user_sex = "男" WHERE user_id = 1;

Undo-log 中的旧数据版本链示意图大致如下：
在这里插入图片描述

       InnoDB 在 MySQL 启动时，会在内存中构建一个 BufferPool，而这个缓冲池主要存放两类东西，一类是数据相关的缓冲，如索引、锁、表数据等，另一类则是各种日志的缓冲，如 Undo、Bin、Redo… 等日志。而当一条写 SQL 执行时，不会直接去往磁盘中的 xx.ibdata 文件写数据，而是会写在 undo_log_buffer 缓冲区中，因为工作线程直接去写磁盘太影响效率了，写进缓冲区后会由后台线程去刷写磁盘。

2、Redo-log 重做日志

       详细聊明白了 Undo-log 后，紧接着再来看看它的同胞兄弟：Redo-log 日志，为啥说它两是同胞兄弟呢？因为这两日志都是 InnoDB 引擎独有的，Undo-log 主要用于实现事务回滚和 MVCC 机制，而 Redo-log 则用来实现数据的恢复。众所周知，MySQL 绝大部分引擎都是是基于磁盘存储数据的，但如若每次读写数据都走磁盘，其效率必然十分低下，因此 InnoDB 引擎在设计时，当 MySQL 启动后就会在内存中创建一个 BufferPool，运行过程中会将大量操作汇集在内存中进行，比如写入数据时，先写到内存中，然后由后台线程再刷写到磁盘。因为数据写到内存后mysql宕机了就会有数据丢失风险，这明显违背了事务 ACID 原则中的持久性，所以 Redo-log 的出现就是为了解决该问题。Redo-log 是一种预写式日志，即在向内存写入数据前，会先写日志，当后续数据未被刷写到磁盘、MySQL 崩溃时，就可以通过日志来恢复数据，确保所有提交的事务都会被持久化。
⚠️注意: 工作线程执行 SQL 前，写的 Redo-log 日志，也是写在了内存中的 redo_log_buffer 缓冲区。既然 Redo-log 日志也是先写内存，那 Redo-log 有没有丢失的风险呢？这跟 Redo-log 的刷盘策略有关。

2.1、Redo-log 刷盘策略

  1. 间隔一段时间，然后再刷写一次日志到磁盘（性能最佳）。
  2. 每次提交事务时，都刷写一次日志到磁盘（性能最差，最安全，默认策略）。
  3. 有事务提交的情况下，每间隔一秒时间刷写一次日志到磁盘。

       默认是处于第二个级别，也就是每次提交事务时都会刷盘，这也就意味着一个事务执行成功后，相应的 Redo-log 日志绝对会被刷写到磁盘中，因此无需担心会出现丢失风险。

       再来思考一个问题：既然 Redo-log 要写磁盘，那为何不在写日志的时候，直接把数据写到磁盘里面去呢？先刷写一次 Redo-log 日志到磁盘，后台线程再根据 Redo-log 日志把数据落盘，这个动作似乎看起来有些多余对吧？但实际上这样做好处很大：
①日志比数据先落入磁盘，因此就算 MySQL 崩溃也可以通过日志恢复数据。
②写日志时是以追加形式写到末尾，而写数据时则是计算数据位置，随机插入。
       对于第一点好处就不多说了，重点来聊一聊第二点，因为写日志的时候，只需要将记录追加到日志文件的尾部即可，这是按顺序写入，但写入表数据时，还需要先先计算数据的位置，比如修改一条数据时，需要先判断这条数据在磁盘文件中的那个位置，找到了位置再写入，这是随机写入，顺序写入的速度会比随机写入快很多很多。

3、Bin-log 变更日志

        Bin-log 日志也被称之为二进制日志，作用与 Redo-log 类似，主要是记录所有对数据库表结构变更和表数据修改的操作，对于 select、show 这类读操作并不会记录。bin-log 是 MySQL-Server 级别的日志，也就是所有引擎都能用的日志，而 redo-log、undo-log 都是 InnoDB 引擎专享的，无法跨引擎生效。下面看一下sql语句的执行流程图：
在这里插入图片描述
       重点观察里面的第⑨步，无论当前表使用的是什么引擎，实际上都需要完成记录 bin-log 日志这步操作，和之前分析的两种日志相同，bin-log 也由内存日志缓冲区 + 本地磁盘文件两部分组成，这也就意味着：写 bin-log 日志时，也会先写缓冲区，然后由后台线程去刷盘。

3.1、Bin-log 缓冲区

       为啥要单独把 bin-log 的缓冲区拎出来讲呢？因为它跟 redo-log、undo-log 的缓冲区并不同，前面分析的两种日志缓冲区，都位于 InnoDB 创建的共享 BufferPool 中，而 bin_log_buffer 是位于每条线程中的，关系图如下：
在这里插入图片描述
        也就是说，MySQL-Server 会给每一条工作线程，都分配一个 bin_log_buffer，而并不是放在共享缓冲区中，这是为啥呢？因为 MySQL 设计时要兼容所有引擎，直接将 bin-log 的缓冲区，设计在线程的工作内存中，这样就能够让所有引擎通用，并且不同线程 / 事务之间，由于写的都是自己工作内存中的 bin-log 缓冲，因此并发执行时也不会冲突！

        在 bin-log 的本地文件中，其中存储的日志记录共有 Statment、Row、Mixed 三种格式，分别是啥意思呢？

-- 查询一次用户表数据，如下：
SELECT * FROM `zz_users`;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       1 | 熊猫      | 女       | 6666     | 2022-08-14 15:22:01 |
|       2 | 竹子      | 男       | 1234     | 2022-09-14 16:17:44 |
|       3 | 子竹      | 男       | 4321     | 2022-09-16 07:42:21 |
|       4 | 猫熊      | 女       | 8888     | 2022-09-27 17:22:59 |
|       9 | 黑竹      | 男       | 9999     | 2022-09-28 22:31:44 |
+---------+-----------+----------+----------+---------------------+

-- 将用户表中所有 ID>3的密码重置
update `zz_users` set `password` = "1111" where user_id > 3;
3.1.1 Statment

        Statment：每一条会对数据库产生变更的 SQL 语句都会记录到 bin-log 中;比如上述这个事务执行时，MySQL 会将第二条 update 语句记录在 bin-log 日志中，但对于 select 语句则不会记录（在记录 SQL 时，还会记录一下 SQL 的上下文信息，如执行时间、事务 ID、日志量…）。这种方式的优势很明显，由于只记录对数据库产生变更操作的 SQL，所以不会产生太大的日志量，节约空间，恢复数据时因为数据量小，所以磁盘 IO 次数少，因此性能会比较不错。同时做主备等高可用架构时，数据同步也会较小，因此比较节省带宽。但虽然优势不小，但缺点页很明显，即恢复数据、主从同步数据时，有时会出现数据不一致的情况，如 SQL 中使用了 sysdate()、now() 这类函数，比如举个简单的例子：

insert into `zz_users` values(11,"棕熊","男","3333",sysdate());

        比如这条插入语句，由于对用户表产生了变更操作，所以会被记录到 bin-log 中，但当主从架构之间做数据同步时，假设将这条 SQL 同步到从机上执行，此时问题就来了，sysdate() 函数会获取机器的当前时间，但主机和从机执行这条 SQL 显然不是同一时间，因此就会导致 ID=11 的这条数据，在主机和从机的用户表中，注册时间会出现不一致。

3.1.2 Row

        Row：这种模式就是为了解决 Statment 模式的缺陷，Row 模式中不再记录每条造成变更的 SQL 语句，而是记录具体哪一个分区中的、哪一个页中的、哪一行数据被修改了。这又怎么理解呢？还是以前面的重置密码的例子来说：

-- 将用户表中所有 ID>3的密码重置（ID=4、9的两条数据会被重置）
update `zz_users` set `password` = "1111" where user_id > 3;

        在这种模式下，就不会记录这条 update 语句，而是记录发生改变的行数据，即 ID=4、9 的两条用户数据，会将其更改后的值记录到 bin-log 日志中。这种方式因为不记录 SQL，而是记录修改后的值，因此有个很大的好处是：当主从同步数据时，复制的是主机上的数据，因此不会出现主从数据不一致的情况。但缺陷同样很明显，比如表中有 800W 数据，现在我对 ID<600W 的所有数据进行了修改操作，哪也就意味着会有 600W 条记录写入 bin-log 日志，这个数据量可想而知，其磁盘 IO、网络带宽开销会很高。

3.1.3 Mixed

        Mixed：这种被称为混合模式，即 Statment、Row 的结合版，因为 Statment 模式会导致数据出现不一致，而 Row 模式数据量又会很大，因此 Mixed 模式结合了两者的优劣势，对于可以复制的 SQL 采用 Statment 模式记录，对于无法复制的 SQL 采用 Row 记录。这样即保留了 Statment 模式的数据量小，又具备 Row 模式的数据精准性，如果比较熟悉 Redis4.x 版本的小伙伴应该会有种熟悉感，Redis 的 RDB、AOF 持久化模式，正好对应 MySQL 的 Statment、Row 模式，而 Redis4.0 引入了混合持久化机制，MySQL5.1 版本也引入了混合日志模式。

4、总结

4.1、Redo-log、Bin-log 两者的区别

  • 生效范围不同，Redo-log 是 InnoDB 专享的，Bin-log 是所有引擎通用的。
  • 写入方式不同，Redo-log 是用两个文件循环写，而 Bin-log 是不断创建新文件追加写。
  • 文件格式不同，Redo-log 中记录的都是变更后的数据，而 Bin-log 会记录变更 SQL 语句。
  • 使用场景不同，Redo-log 主要实现故障情况下的数据恢复，Bin-log 则用于数据灾备、同步。

4.2、误删了大量数据，用 Redo-log还是Bin-log恢复数据更合适呢？

        答案是 Bin-log，因为 Redo-log 采用循环写的方式，一边写会一边擦，里面无法得到完整的数据，而 Bin-log 是追加写的模式，你不去主动删除磁盘的日志文件，并且磁盘的空间还足够，一般 Bin-log 日志文件都会在本地，因此当你删库后，可以直接去本地找 Bin-log 的日志文件，然后拷贝出来一份，再打开最后一个文件，把里面删库的记录手动移除，再利用 mysqlbinlog 工具导出 xx.SQL 文件，最后执行该 SQL 文件即可恢复删库前的数据。

4.3、为了保证数据的一致性，mysql采用两阶段提交

在这里插入图片描述
        其中第⑤、⑩步，分别会写两次 Redo-log 日志，这个日志的作用前面讲的很明白了，主要用来做崩溃恢复，但为什么要分两次写呢？写一次不行嘛？如果只写一次的话，那到底先写 bin-log 还是 redo-log 呢？

        先写 bin-log，再写 redo-log：当事务提交后，先写 bin-log 成功，结果在写 redo-log 时断电宕机了，再重启后由于 redo-log 中没有该事务的日志记录，因此不会恢复该事务提交的数据。但要注意，主从架构中同步数据是使用 bin-log 来实现的，而宕机前 bin-log 写入成功了，就代表这个事务提交的数据会被同步到从机，也就意味着从机会比主机多出一条数据。
        先写 redo-log，再写 bin-log：当事务提交后，先写 redo-log 成功，但在写 bin-log 时宕机了，主节点重启后，会根据 redo-log 恢复数据，但从机依旧是依赖 bin-log 来同步数据的，因此从机无法将这个事务提交的数据同步过去，毕竟 bin-log 中没有撒，最终从机会比主机少一条数据。
        经过上述分析后可得知：如果 redo-log 只写一次，那不管谁先写，都有可能造成主从同步数据时的不一致问题出现，为了解决该问题，redo-log 就被设计成了两阶段提交模式，设置成两阶段提交后，整个执行过程有三处崩溃点：

  • redo-log(prepare)：在写入准备状态的 redo 记录时宕机，事务还未提交，不会影响一致性。
  • bin-log：在写 bin 记录时崩溃，重启后会根据 redo 记录中的事务 ID，回滚前面已写入的数据。
  • redo-log(commit)：在 bin-log 写入成功后，写 redo(commit) 记录时崩溃，因为 bin-log 中已经写入成功了，所以从机也可以同步数据，因此重启时直接再次提交事务，写入一条 redo(commit) 记录即可。

        通过这种两阶段提交的方案，就能够确保 redo-log、bin-log 两者的日志数据是相同的，bin-log 中有的主机再恢复，如果 bin-log 没有则直接回滚主机上写入的数据，确保整个数据库系统的数据一致性。

小火柴66
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
MySQL 日志
看清所以看轻
05-21 3182
MySQL日志记录了MySQL数据库日常操作和错误信息。MySQL有不同类型的日志文件（各自存储了不同类型的日志），从日志当中可以查询到MySQL数据库的运行情况、用户的操作、错误的信息等。 MySQL的日志分为以下四大类： 错误日志：记录mysql服务的启动，运行或停止mysql服务时出现的问题； 查询日志：记录建立的客户端的连接和执行的语句； 二进制日志：记录所有更改数据的语句，可以用于数据的复制； 慢查询日志：记录所有执行的时间超过long_query_time的所有查询或不使用索引的查询； 默认情
MySQL优化篇：慢查询日志.pdf
04-18
主要介绍MySQL数据库中慢查询日志的使用流程、使用方式、以及日志分析工具mysqldumpslow的相关内容
mysql博客日志_mysql日志
weixin_39869693的博客
02-18 94
参考博客:```http://blog.csdn.net/timchen525/article/details/75268151```# mysql日志类型mysql中的日志包括:- 错误日志- 二进制日志- 通用查询日志- 慢查询日志这里主要介绍下比较常用的两个功能: 通用查询日志和慢查询日志1) 通用查询日志: 记录简历的客户端连接和执行的语句2) 慢查询日志: 记录所有执行时间超过long_...
MYSQL日志管理、备份与恢复
最新发布
AH99999的博客
03-25 1453
物理备份—》关闭mysqld服务----》tar命令打包data目录（恢复就是解压他的tar包即可）逻辑备份—》mysqldump -u(用户) -p(密码) --databases 库1 库2 > xxx.sqlmysqldump -u -p 库1 表1 表2 > xxx.sql完全恢复mysql -u -p < xxx.sql (恢复数据库)mysql -u -p 库名 < xxx.sql (恢复表)增量备份开启二进制日志，设置二进制格式MIXED（混合）
mysql博客日志_MySQL日志
weixin_42463480的博客
02-18 254
日志文件对于任何应用程序都是非常重要的，许多操作都需要记录到日志，然后可以利用日志文件查错纠错等。mysql中当然也有日志，mysql的日志大概分为五种，即错误日志、查询日志、慢查询日志、二进制日志和事务日志。查看日志相关的全局变量。--查询日志相关全局变量show global variables like '%log%';一、错误日志在mysql数据库中，错误日志功能是默认开启的，并且，错误日...
mysql博客日志_MYSQL日志文件
weixin_39980234的博客
01-19 62
MYSQL有不同类型的日志文件(各自存储了不同类型的日志)，从它们当中可以查询到MYSQL里都做了些什么，对于MYSQL的管理工作，这些日志文件是不可缺少的。1.错误日志(The error log)：记录了数据库启动、运行以及停止过程中错误信息；2.ISAM操作日志(The isam log)：记录了所有对ISAM表的修改，该日志仅仅用于调试ISAM模式；3.SQL执行日志(The query ...
mysql之日志
Crazy博客
01-09 307
一条数据在更新过程当中，如果中途 mysql crash 了，mysql 是如何保证数据的一致性和持久性的？在这个过程中 mysql 的日志系统起到了至关重要的作用。本文将会介绍 mysql 中的 undo log、redo log 和 bin log 在这其中的作用。
Mysql数据库之Binlog日志使用总结(必看篇)
09-09
下面小编就为大家带来一篇Mysql数据库之Binlog日志使用总结(必看篇)。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧
解说mysql之binlog日志以及利用binlog日志恢复数据的方法
09-09
下面小编就为大家带来一篇解说mysql之binlog日志以及利用binlog日志恢复数据的方法。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧
MySQL入门到精通-运维篇
11-21
MySQL 运维篇涵盖了 MySQL 的日志、主从复制、分库分表、读写分离等多方面的运维知识点。 日志 MySQL 中的日志是非常重要的，日志记录了 MySQL 服务器的各种操作记录，包括错误日志、二进制日志、查询日志、慢...
基于MySQL的存储引擎与日志说明(全面讲解)
09-09
下面小编就为大家分享一篇基于MySQL的存储引擎与日志说明(全面讲解)，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧
MYSQL的日志
Mario7th的博客
02-28 1334
在MySQL中有三个比较重要的日志：1. bin log（二进制日志） 2. redo log（重做日志） 3. undo log（回滚日志）。 Redo log（重做日志） Redo log是InnoDB数据库引擎独有的一种日志，他可以让数据库拥有故障崩溃之后的恢复能力。mysql挂了或者宕机之后，innodb依赖redo log恢复数据，保证了数据的持久性。 ...
MySQL日志
m0_47218990的博客
04-17 578
MySQL日志
【MySQL高级】MySQL的日志
dzqxwzoe的博客
12-07 1625
二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从 ， 就是通过该binlog实现的。二进制日志，MySQl8.0默认已经开启，低版本的MySQL的需要通过配置文件开启，并配置MySQL日志的格式。Windows系统：my.ini Linux系统:my.cnf。
Mysql的几种日志
当一堆用不熄灭的柴火
04-09 7286
错误日志 错误日志记录着mysqld启动和停止,以及服务器在运行过程中发生的错误的相关信息。在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出。 指定日志路径两种方法 编辑my.cnf 写入 log-error=[path] 通过命令参数错误日志 mysqld_safe –user=mysql –log-error=[path] &显示错误日志的命令（如下图
MySql运维篇---008：日志：错误日志、二进制日志、查询日志、慢查询日志，主从复制：概述 虚拟机更改ip注意事项、原理、搭建步骤
aa35434的博客
10-04 5964
二进制日志（BINLOG）记录了所有的 DDL（数据定义语言：创建数据库…）语句和 DML（数据操纵语言：增删改）语句，但不包括数据查询（SELECT、SHOW）语句。①. 灾难时的数据恢复；因为二进制日志中记录了数据库、表、以及数据的变更。只需要把这里面的语句再次执行就可以恢复数据了。②. MySQL的主从复制。在MySQL8版本中主从复制底层原理就是基于二进制日志的，具体查看下一章。
【MySQL系列】- MySQL日志详解
记录总结工作过往
10-30 1335
Bin Log是一个二进制格式的文件，是MySQL最重要的日志，它记录了所有的DDL及DML语句（除了数据查询select、show等），以事件形式记录，还包含语句所执行的消耗时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。
MYSQL数据库日志
lxp503238的博客
06-29 8578
MySQL有不同类型的日志文件，用来存储不同类型的日志，分为 二进制日志 、 错误日志 、 通用查询日志和 慢查询日志 ，这也是常用的4种。MySQL 8又新增两种支持的日志： 中继日志 和 数据定义语句日志 。使用这些日志文件，可以查看MySQL内部发生的事情。 **这6类日志分别为： **除二进制日志外，其他日志都是 文本文件 。默认情况下，所有日志创建于 MySQL数据目录 中。日志功能会 降低MySQL数据库的性能 。 日志会 占用大量的磁盘空间 。通用查询日志用来 记录用户的所有操作 ，包括启动和
深入浅出--Mysql的三种日志
weixin_51299539的博客
05-31 1785
当write_pos追上check_point时，redo log写满，mysql会被阻塞，此时不能再进行写操作，这时会停下来将buffer pool中的脏页刷新到磁盘，接着，既然脏页中的记录已经落盘，就可以擦除redo中的一些记录，check_point也向后顺时针移动，这样，write_pos与check_point之间就有了可以写入的空间。接着，从库的IO线程会通过连接主库的log dump线程，读取主库的binlog日志并将读取到的数据写入到从库的relay log中继日志中，
黑马mysql运维篇
08-03
黑马MySQL运维篇是一门关于MySQL数据库运维的课程。在这门课程中，有关于修改日志格式、配置MySQL服务器以及环境搭建的内容。在修改日志格式方面，可以通过编辑/etc/my.cnf文件，在文件末尾加上一行"binlog_format = STATEMENT"，然后重新启动mysqld服务来生效[1]。在配置完毕后，可以通过重新启动MySQL服务器进行测试，并查看慢日志文件中记录的信息，慢日志文件的路径为/var/lib/mysql/localhost-slow.log[2]。在环境搭建方面，可以创建一个名为itcast的数据库，并创建一个名为tb_user的表，表中包含id、name和sex三个字段，然后插入一些测试数据[3]。这些内容都是黑马MySQL运维篇课程中的一部分，旨在帮助学习者了解和掌握MySQL数据库的运维技术。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值