深入理解MySQL中的bin log、redo log、undo log
创始人
2024-05-12 06:36:11
0次

bin log（二进制日志）

什么是bin log？

记录数据库执行的写入性操作信息，以二进制的形式保存在磁盘中。

由服务层产生，所有储存引擎都支持。

bin log属于逻辑日志。

bin log日志有三种格式：STATMENT、ROW、MIXED。MySQL5.7.7之后默认是ROW。

简单普及一下逻辑日志和物理日志：

  • 逻辑日志：记录的sql语句。
  • 物理日志：记录的数据页变更。

bin log的使用场景？

  • 主从复制：在master端开启bin log，然后master将bin log发送到每个slave端，slave端重放bin log从而达到主从数据一致。

    简单画个图来理解一下MySQL的主从复制流程

  • 1、master在准备提交事务之前，将变更记录到bin log中。
  • 2、slave启动一个IO线程来读取bin log中的事件，并记录到自己的ready log（中继日志）中。
  • 3、同时slave还会启动一个SQL线程，读取ready log中的事件在备库中执行，从而实现备库的数据更新。

  • 数据恢复：通过使用mysqlbinlog工具来恢复数据。

  • 增量备份

  • # 查看是否开启bin log，这里可以看到没有开启
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set (0.05 sec)# 查看当前MySQL版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.36    |
+-----------+
1 row in set (0.20 sec)# 确认已开启bin log
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.03 sec)

    如果没有开启，编辑/etc/my.cnf文件，添加以下配置：

  • # 开启bin log日志
log-bin=binlog
# 配置server-id
server-id=1

    如何查看以及修改每个bin log文件大小最大值？

    查看本机的bin log文件大小最大值，可以看到是1073741824字节，也就是1G。

  • mysql> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)

    添加或修改MySQL的my.cnf文件：

  • max_binlog_size=2G

    如何使用bin log恢复数据？

  • # 查看当前bin log位置
mysql> show master status;# 也可以刷新日志，方便后面筛选
mysql> flush logs;# 查看二进制日志列表
mysql> show binary logs;

    bin log恢复数据：

  • # 根据事件位置恢复（--start-position是开始位置，--stop-position是结束位置）
mysqlbinlog --start-position=16275 --stop-position=16566 --database=just-test /var/lib/mysql/binlog.000004 | mysql -uroot -p123456
# 根据指定时间恢复（--start-datetime是开始时间，--stop-datetime是结束时间）
mysqlbinlog --start-datetime="2022-02-17 12:00:00" --stop-datetime="2022-02-17 18:00:00" --database=just-test /var/lib/mysql/binlog.000004 | mysql -uroot -p123456# --database是指定只恢复just-test数据库，/var/lib/mysql/binlog.000004是binlog日志文件路径

    bin log日志解码以及导出到服务器中：

  • mysqlbinlog -vvv --base64-output=decode-rows --start-position=154 --stop-position=2150 --database=just-test /var/lib/mysql/binlog.000001 > /just-test.sql | mysql -uroot -p123456 -f

    -vvv：显示出执行的SQL以及binlog_rows_query_log_events参数。

    --base64-output=decode-rows：如果bin log为row格式必须使用该选项对日志进行解析，恢复数据时不能加该选项。

  • 简单演示一下如何恢复被删掉的数据：

  • # 快速创建一个极其简单的数据表来用于测试
mysql> CREATE TABLE `quick-test-data` (`id` bigint(20) DEFAULT NULL,`name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;# 分别插入小明和小花两条数据
mysql> INSERT INTO `quick-test-data` (`id`, `name`) VALUES (1, '小明');
mysql> INSERT INTO `quick-test-data` (`id`, `name`) VALUES (2, '小花');
mysql> INSERT * FROM `quick-test-data`;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 小明   |
|    2 | 小花   |
+------+--------+# 删掉小花
mysql> DELETE FROM `quick-test-data` WHERE `id` = 2
mysql> INSERT * FROM `quick-test-data`;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 小明   |
+------+--------+# 查看bin log日志，确定从哪里开始恢复（数据比较多，我在此抽取一部分，binlog.000004是刷新几次之后的日志文件，根据自己实际情况来）
mysql> show binlog events in 'binlog.000004';
+---------------+-------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name      | Pos   | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                                                                     |
+---------------+-------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000004 | 15636 | Anonymous_Gtid |         1 |       15701 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                     |
| binlog.000004 | 15701 | Query          |         1 |       15984 | use `just-test`; CREATE TABLE `just-test`.`quick-test-data`  (`id` bigint NULL,`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL
) CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci |
| binlog.000004 | 15984 | Anonymous_Gtid |         1 |       16049 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                     |
| binlog.000004 | 16049 | Query          |         1 |       16126 | BEGIN                                                                                                                                                                                                                    |
| binlog.000004 | 16126 | Table_map      |         1 |       16192 | table_id: 116 (just-test.quick-test-data)                                                                                                                                                                                |
| binlog.000004 | 16192 | Write_rows     |         1 |       16244 | table_id: 116 flags: STMT_END_F                                                                                                                                                                                          |
| binlog.000004 | 16244 | Xid            |         1 |       16275 | COMMIT /* xid=1088 */                                                                                                                                                                                                    |
| binlog.000004 | 16275 | Anonymous_Gtid |         1 |       16340 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                     |
| binlog.000004 | 16340 | Query          |         1 |       16417 | BEGIN                                                                                                                                                                                                                    |
| binlog.000004 | 16417 | Table_map      |         1 |       16483 | table_id: 116 (just-test.quick-test-data)                                                                                                                                                                                |
| binlog.000004 | 16483 | Write_rows     |         1 |       16535 | table_id: 116 flags: STMT_END_F                                                                                                                                                                                          |
| binlog.000004 | 16535 | Xid            |         1 |       16566 | COMMIT /* xid=1091 */                                                                                                                                                                                                    |
| binlog.000004 | 16566 | Anonymous_Gtid |         1 |       16631 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                     |
| binlog.000004 | 16631 | Query          |         1 |       16708 | BEGIN                                                                                                                                                                                                                    |
| binlog.000004 | 16708 | Table_map      |         1 |       16774 | table_id: 116 (just-test.quick-test-data)                                                                                                                                                                                |
| binlog.000004 | 16774 | Delete_rows    |         1 |       16826 | table_id: 116 flags: STMT_END_F                                                                                                                                                                                          |
| binlog.000004 | 16826 | Xid            |         1 |       16857 | COMMIT /* xid=1094 */                                                                                                                                                                                                    |
+---------------+-------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+### 分析此处的bin log日志 ###
# 15701~15984：建表quick-test-data
# 15984~16275：插入第一条数据，即小明
# 16275~16566：插入第二条数据，即小花
# 16566~16857：删除小花
## 所以，因为数据表和小明都是存在的，所以，我们的start-position应该是16275，stop-position应该是16566
## 16566~16857这里是删除小花的，所以，这个部分不能被包括进来，不然恢复之后又被删除掉了# 使用mysqlbinlog工具恢复被删除的小花数据
# 在Linux命令行执行（注意不是在mysql命令行）
[root@lzhpo-light ~]# mysqlbinlog --start-position=16275 --stop-position=16566 --database=just-test /var/lib/mysql/binlog.000004 | mysql -uroot -p123456# 恢复之后的数据表
mysql> select * from `quick-test-data`;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 小明   |
|    2 | 小花   |
+------+--------+

    如何正确删除bin log日志?

  • reset master命令：虽然可以清空所有bin log文件，但是会导致从库异常，主从架构下无法使用。

  • expire_logs_days变量：通过该变量可以指定自动删除日期，如果日志过多，在删除时会有IO过高问题，可能导致性能抖动。

  • purge命令：推荐方法，可以快速删除指定bin log。

  • # 删除bin log到指定的文件为止
mysql> PURGE MASTER LOGS TO 'binlog.000004'# 删除指定日期之前的文件
mysql> PURGE MASTER LOGS BEFORE '2022-02-18 18:30:00'

    redo log（重做日志）

    什么是redo log？

简单一句话：redo log就是记录数据页的变更。

redo log由InnoDB的存储引擎层产生，是InnoDB 存储引擎特有的。

redo log属于物理日志，因为，它记录的是数据页的变更。

因为，这种改变记录不是说一定要全部保存下来，所以，redo log采用的是大小固定，循环写入的方式，当从开头开始写到结尾的时候，又会回到开头继续写日志。

redo log记录日志的方式（原理）？

InnoDB的redo log文件大小是固定的，假设我这里redo log大小为4G，并且我划分为4个部分，redo log就会从ib_logfile_0开始写ib_logfile_1、ib_logfile_2、ib_logfile_3，直到4个部分都写满为止，再重新回到第一个部分ib_logfile_0开始写。

write position：当前记录的LSN（Log Sequence Number，日志序列号）位置。一边写，一边顺时针移动（向前移动）。

check point：当前数据页更改记录刷盘之后所处的LSN（Log Sequence Number，日志序列号）位置。一边写，一边顺时针移动（向前移动）。

check point到write position部分就是待落盘的数据页更改记录。

write position到check point部分就是redo log空闲的部分，用来记录新的操作日志。

当write position追上check point的时候，会先推动check point顺时针移动（向前移动），等到有空闲的redo log位置的时候再记录新的操作日志。

redo log如何进行异常崩溃数据恢复的？

每当启动InnoDB的时候，不管上次是正常关闭还是异常关闭，都会进行恢复操作。

因为redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如bin log)要快很多。

在此，我画一个简单的流程图方便理解

 

还有一种比较特殊的情况，数据页LSN也会大于日志LSN：

当宕机的时候，正在处理check point刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度，此时，也会出现数据页LSN也会大于日志LSN的情况。
这种情况的话，数据页剩下的这点redo log日志将不会重做，会正常启动。

如何查看以及修改redo log的大小？

mysql> show variables like 'innodb_log%';
+------------------------------------+----------+
| Variable_name                      | Value    |
+------------------------------------+----------+
| innodb_log_buffer_size             | 16777216 |
| innodb_log_checksums               | ON       |
| innodb_log_compressed_pages        | ON       |
| innodb_log_file_size               | 50331648 |
| innodb_log_files_in_group          | 2        |
| innodb_log_group_home_dir          | ./       |
| innodb_log_spin_cpu_abs_lwm        | 80       |
| innodb_log_spin_cpu_pct_hwm        | 50       |
| innodb_log_wait_for_flush_spin_hwm | 400      |
| innodb_log_write_ahead_size        | 8192     |
| innodb_log_writer_threads          | ON       |
+------------------------------------+----------+
11 rows in set (0.00 sec)

innodb_log_file_size就是redo log文件大小，50331648字节，也就是48MB。

要修改的话，打开MySQL的my.cnf文件，添加或编辑配置，比如，我修改为1G：

innodb_log_file_size=1G

如何自定义在事务提交的时候将log buffer中的日志刷入log file中的时机？

可以通过配置innodb_flush_log_at_trx_commit参数来更改刷入时机。

查看当前MySQL的配置时机：

mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

参数值解释：

  • 0：延迟写，延迟刷。
    事务提交的的时候不会将redo log buffer中的日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到redo log file中。
    这样子可能会导致，当系统崩溃，可能丢失1秒的数据。

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

  • 2：实时写，延迟刷。
    事务每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到redo log file。

 

undo log（回滚日志）

什么是undo log？

MySQL（存储引擎需要能支持事务）在修改记录之前（提交事务之前），会把原先记录的值先保存起来（也就是写入到undo log），然后再修改（提交事务），当出问题的时候MySQL可以利用undo log来回滚事务，即恢复原先的记录值。

undo log由InnoDB的存储引擎层产生，是InnoDB 存储引擎特有的（和redo log一样）。

undo log属于逻辑日志。

MySQL的事务四大特性：原子性、隔离性、持久性、一致性。其中，原子性的底层就是靠undo log实现的。

undo log的作用？

1.事务回滚

前面我们说到，当出问题需要回滚事务的时候，可以利用MySQL的undo log来回滚事务，以保证事务的一致性。

比如：执行一条delete语句:

DELETE FROM sys_log WHERE id = 1;

undo log就会记录一条与它相反的日志，即记录它的insert语句。

update语句也是同理，记录与它相反的update语句。

执行一条update语句：

# 假设，之前username是小花
UPDATE sys_user SET username = '小明' WHERE id = 1;

undo log就会记录与它相反的update语句：

UPDATE sys_user SET username = '小花' WHERE id = 1;

多版本控制（MVCC）

MVCC全称即Multi-Version Concurrency Control。

在MySQL的InnoDB存储引擎中，就是用undo log来实现MVCC的。

举个例子，当我们读取的某一行被其它事务锁定的时候，InnoDB可以从undo log中分析出该记录历史版本的数据，从而让我们可以读取到当前事务操作之前的数据（也就是快照读）。

普及一下快照读和当前读：

  • 快照读：读取的历史版本数据，不会加锁。
    普通的select就是快照读。

  • 当前读：读取的是最新版本, 会对读取的记录加锁, 以保证其它事务无法对此记录进行变更，保证安全性。
    属于当前读的：update、delete、insert、select … for update、select … lock in share mode（共享读锁）等等。

undo log的工作机制？

在MySQL的InnoDB存储引擎中，undo log是采用分段（segment）的方式保存的，简单来说，就是一种命名为rollback segment的回滚段，每个回滚段中有1024个undo log segment。

在MySQL5.5之后，能支持128个回滚段，也就是能支持128*1024个undo log segment，在此之前是只支持1个回滚段，也就是1024个undo log segment。

undo log工作原理？

在此，简单画一张图来理解一下：

  1. 事务A提交之前，会备份之前的数据到对应的undo buffer，然后undo log保存之前的记录数据，然后再将最新的数据持久化到ibd文件。
  2. 此时事务B查询，直接读取undo buffer缓存，因为这时候事务A还没提交且它需要回滚事务，所以，这时候事务B是不读取磁盘的，是直接从undo buffer缓存中读取。

 

词库加载错误:未能找到文件“E:\highferrum_mysql\Configuration\Dict_Stopwords.txt”。

上一篇：C语言文件操作

下一篇：php网上书城|基于PHP实现网上书店商城藉项目

相关内容

热门资讯

美国不提安卓系统华为,迈向自主... 华为与美国：一场关于技术、市场与政策的较量在当今这个数字化的世界里，智能手机已经成为我们生活中不可或...
安卓系统怎么打开ppt,选择文... 你有没有遇到过这种情况：手里拿着安卓手机，突然需要打开一个PPT文件，却怎么也找不到方法？别急，今天...
谷歌退回到安卓系统,探索创新未... 你知道吗？最近科技圈可是炸开了锅，谷歌竟然宣布要退回到安卓系统！这可不是一个简单的决定，背后肯定有着...
安卓系统待机耗电多少,深度解析... 你有没有发现，手机电量总是不经用？尤其是安卓系统，有时候明明没怎么用，电量就“嗖”的一下子就下去了。...
小米主题安卓原生系统,安卓原生... 亲爱的手机控们，你是否曾为手机界面单调乏味而烦恼？想要给手机换换“衣服”，让它焕然一新？那就得聊聊小...
voyov1安卓系统,探索创新... 你有没有发现，最近你的手机是不是变得越来越流畅了？没错，我要说的就是那个让手机焕发青春的Vivo V...
电脑刷安卓tv系统,轻松打造智... 你有没有想过，家里的安卓电视突然变得卡顿，反应迟钝，是不是时候给它来个“大保健”了？没错，今天就要来...
安卓系统即将要收费,未来手机应... 你知道吗？最近有个大消息在科技圈里炸开了锅，那就是安卓系统可能要开始收费了！这可不是开玩笑的，这可是...
雷凌车载安卓系统,智能出行新体... 你有没有发现，现在的汽车越来越智能了？这不，我最近就体验了一把雷凌车载安卓系统的魅力。它就像一个聪明...
怎样拍照好看安卓系统,轻松拍出... 拍照好看，安卓系统也能轻松搞定！在这个看脸的时代，拍照已经成为每个人生活中不可或缺的一部分。无论是记...
安卓车机系统音频,安卓车机系统... 你有没有发现，现在越来越多的汽车都开始搭载智能车机系统了？这不，咱们就来聊聊安卓车机系统在音频方面的...
老苹果手机安卓系统,兼容与创新... 你手里那台老苹果手机，是不是已经陪你走过了不少风风雨雨？现在，它竟然还能装上安卓系统？这可不是天方夜...
安卓系统7.dns,优化网络连... 你有没有发现，你的安卓手机最近是不是有点儿“慢吞吞”的？别急，别急，让我来给你揭秘这可能与你的安卓系...
安卓手机系统怎么加速,安卓手机... 你有没有发现，你的安卓手机最近变得有点“慢吞吞”的？别急，别急，今天就来给你支几招，让你的安卓手机瞬...
小米note安卓7系统,探索性... 你有没有发现，手机更新换代的速度简直就像坐上了火箭呢？这不，小米Note这款手机，自从升级到了安卓7...
安卓和鸿蒙系统游戏,两大系统游... 你有没有发现，最近手机游戏界可是热闹非凡呢！安卓和鸿蒙系统两大巨头在游戏领域展开了一场激烈的较量。今...
安卓手机没有系统更,揭秘潜在风... 你有没有发现，现在安卓手机的品牌和型号真是五花八门，让人挑花了眼。不过，你知道吗？尽管市面上安卓手机...
充值宝带安卓系统,安卓系统下的... 你有没有发现，最近手机上的一款充值宝APP，在安卓系统上可是火得一塌糊涂呢！这不，今天就来给你好好扒...
安卓系统8.0镜像下载,轻松打... 你有没有想过，想要给你的安卓手机升级到最新的系统，却不知道从哪里下载那个神秘的安卓系统8.0镜像呢？...
安卓系统修改大全,全方位修改大... 你有没有想过，你的安卓手机其实是个大宝藏，里面藏着无数可以让你手机焕然一新的秘密？没错，今天就要来个...