MySQL通过bin log恢复数据|手撕MySQL|对线面试官

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 作为《手撕MySQL》系列的第二篇文章,今天介绍一下MySQL的二进制日志(bin log)进行数据恢复的功能,并且配合实例演示,让你更懂MySQL。
关注微信公众号【程序员白泽】,进入白泽的知识分享星球🌍

前言

作为《手撕MySQL》系列的第二篇文章,今天介绍一下MySQL的二进制日志(bin log),注意不要和MySQL的InnoDB存储引擎特有的重写日志(redo log)混淆,bin log是记录所有数据库表数据及表结构变更的二进制日志(不会记录查询操作),借助这个日志可以实现:数据恢复主从复制(不难理解,因为所有涉及变更的操作都记录了下来,可以追溯)。

这篇文章侧重于讲解使用bin log进行数据恢复,下一篇文章讲解主从复制

预备知识

SSH工具推荐

接下来会频繁在控制台终端中输入命令,因此推荐一款开源免费的ssh客户端electermhttps://github.com/electerm/electerm,这是地址,可以直接下载安装,非常好用!

image-20220303160935317

bin log 状态管理

在开始讲解bin log可以提供的两个功能之前,先要学会管理自己MySQL服务的bin log状态,并且通过修改参数对其进行控制。先来查看一下自己MySQL服务是否已经开启了bin log,可以看到我的二进制日志已经开启。如果你的没有,这里建议通过修改MySQL配置文件的方式将bin log声明为开启,然后重新启动MySQL服务即可。

以Linux系统为例,MySQL数据库是按照 /etc/my.cnf —— /etc/mysql/my.cnf —— /usr/local/mysql/etc/my.cnf —— ~/.my.cnf 的顺序读取配置文件的,且如果出现参数重复设置则后一个配置文件中参数会覆盖前者。如果你的MySQL服务没有配置文件,那就直接自己创建一个,放在上面某个位置之一,然后在创建的配置文件中输入你从网上搜到的设置bin log开启的配置代码,重启MySQL服务即可。

现在假设你已经开启了MySQL的二进制日志,如下:

mysql> show variables like '%log_bin%';

image-20220303172905803

bin log 数据文件

观察上面的查询结果,可以看到两个路径变量:log_bin_basenamelog_bin_index,分别表示bin log开启后,数据文件的生成位置(/usr/local/mysql/data/)和文件名规则(binlog.xxxxx1、binlog.xxxxx2以此类推),以及索引文件binlog.index,其中存放着bin log数据文件列表。

# 查看MySQL数据文件列表(大部分MySQL数据文件都在这个路径下,下面展示部分,主要是bin log相关的数据文件和索引文件)
lilithgamesdeMacBook-Pro-42:~ lilithgames$ sudo ls -al /usr/local/mysql/data

image-20220303172953921

# 查看bin log索引文件内容(完全对应上面列出的三个数据文件)
lilithgamesdeMacBook-Pro-42:~ lilithgames$ sudo cat /usr/local/mysql/data/binlog.index

image-20220303173017882

既然上面说到,bin log记录所有对数据库的更改操作,那么它是将SQL语句记录在数据文件中还是将改动之后的行结果记录下来呢?这里有三种记录模式:

  • ROW:数据文件中会记录每一行数据被修改的情况,这样就能保证在恢复数据或者主从复制时不会因为一些函数(如now()函数执行两次获取的时间是不一致的)导致数据不完全一致的情况,缺点是对于整张表的修改会导致大量数据插入到数据文件中。
  • SRATEMENT:记录修改数据的SQL语句,和ROW相反,在数据同步时,某些情况下会出现不完全一致的情况。
  • MIXED:混合使用上面两种记录模式,在一般情况下使用SRATEMENT,在特殊情况使用ROW。

但是,新版本MySQL的ROW模式已经进行了优化,对于表结构的修改会以STATEMENT模式记录,而对于记录的修改则会在数据文件中记录所有的行的变更。因此,ROW模式是bin log默认的工作模式。

mysql> show variables like 'binlog_format'; 

image-20220303173033257

数据恢复

准备数据

Talk is cheap,show me the code!我知道你已经迫不及待想体验bin log的数据恢复了,那就让我们开始吧~

为了方便展示,我们在MySQL登录态下执行flush logs命令,可以生成一个新的日志文件(为了将后面操作数据库的命令单独放在一个新的数据文件中方便查看)

# 生成新的二进制数据文件(序号增加)
mysql> flush logs;
# 查看当前所有二进制数据文件
mysql> show binary logs;

因为我执行了两次flush log命令,因此以此生成了两个新的二进制数据文件,而且明显可以看到这两个数据文件很小,因为还没有新的修改表的操作被记录下来。

image-20220303174818706

接下来我们建立一个测试数据库:test_database,然后创建一张用户表,并且为其插入几条测试数据。

# 创建user表
CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`)) ENGINE = INNODB DEFAULT CHARSET=utf8;
# 插入测试用户数据
INSERT INTO user (id, username) VALUES (null, 'AAA');
INSERT INTO user (id, username) VALUES (null, 'BBB');
INSERT INTO user (id, username) VALUES (null, 'CCC');
# 查询测试
SELECT * FROM user

接下来我们通过mysqlbinlog命令来查看bin log的数据文件(猜测表的变更被记录在binlog.000010二进制文件中),这里展示一部分。

sudo mysqlbinlog /usr/local/mysql/data/binlog.000010

下面是binlog.000010文件的部分内容,我们找到了建user表的语句(上面说了,新版MySQL的ROW数据记录模式对于表结构的更改是STATEMENT形式的),下面是数据文件中一些重要的字段解释:

  • 第一个at表示一个事件的起始位置pos,中间的是此次事件的二进制数据,而末尾的at表示下一个事件开始位置pos(也就是当前时间的结束位置pos)
  • 220303 17:59:37 server id 1 表示server 1执行该事件的时间
  • exec_time 表示执行时间(具体时间在主从复制时master和slave有所不同,下篇文章讲解)

image-20220303182831102

# 这个mysqlbinlog命令还可以添加参数,如指定查询开始pos到结束pos之间的数据,Google一下~
sudo mysqlbinlog /usr/local/mysql/data/binlog.000010 
# 当然,不借助mysqlbinlog命令,在mysql登录状态下也是可以直接查询bin log数据文件内容的,测试如下:
mysql> show binlog events in 'binlog.000010' from 447 limit 10;

image-20220303183743885

模拟失误

# 失误删除id为1的用户
DELETE FROM user where id=1
# 又插入两个用户
INSERT INTO user (id, username) VALUES (null, 'DDD');
INSERT INTO user (id, username) VALUES (null, 'EEE');

image-20220303193523466

数据恢复

要明确的是:借助bin log二进制日志文件进行数据恢复的本质,是重新执行两个pos区间内的SQL(所以上面才花了较大篇幅讲解查看二进制文件,为的是学会定位pos点,也就是at后面那个数字)

首先通过mysql命令查看binlog.000010数据文件

mysql> show binlog events in 'binlog.000010';

这里要找到两个pos点,一个是user表建立的pos:447,另一个是执行delete操作之前的pos,这里要求结束的pos不能直接选择delete_rows操作的pos:1864,而是要选择它前一个commit事件的下一个pos:1636(否则会出现警告⚠️)

image-20220303193018580

使用mysqlbinlog命令生成pos为447—1636之间的SQL文件(上面说了数据恢复的本质是重新执行两个pos区间内的SQL语句),通过下面的命令,生成了一个return.sql文件。

sudo mysqlbinlog --start-position=447 --stop-position=1636 /usr/local/mysql/data/binlog.000010 > return.sql

执行return.sql文件(相当于又执行了一遍这个区间的SQL语句),进行数据恢复!大功告成!删除的AAA回来了!!不用被开除了!!

mysql> source ~/return.sql

image-20220303193820814

结束语

本篇文章简单讲述了利用bin log进行数据恢复的案例,并且花费了较大篇幅讲解一些bin log的基础知识,为的是为后续讲解利用bin log进行主从复制打下基础,希望阅读本文之后,您感到对二进制日志的理解在八股文的基础之上,更进一步了。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
7天前
|
缓存 关系型数据库 MySQL
MySQL数据库——InnoDB引擎-架构-内存结构(Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer)
MySQL数据库——InnoDB引擎-架构-内存结构(Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer)
23 3
|
7天前
|
SQL 关系型数据库 MySQL
MySQL数据库——索引(3)-索引语法(创建索引、查看索引、删除索引、案例演示),SQL性能分析(SQL执行频率,慢查询日志)
MySQL数据库——索引(3)-索引语法(创建索引、查看索引、删除索引、案例演示),SQL性能分析(SQL执行频率,慢查询日志)
14 2
|
7天前
|
存储 关系型数据库 MySQL
第14章_MySQL事务日志(2)
第14章_MySQL事务日志
10 0
|
7天前
|
存储 缓存 关系型数据库
第14章_MySQL事务日志(1)
第14章_MySQL事务日志
11 0
|
9天前
|
SQL 监控 关系型数据库
解密MySQL二进制日志:深度探究mysqlbinlog工具
解密MySQL二进制日志:深度探究mysqlbinlog工具
35 3
|
9天前
|
SQL 监控 关系型数据库
MySQL慢查询日志配置指南:发现性能瓶颈,提升数据库效率
MySQL慢查询日志配置指南:发现性能瓶颈,提升数据库效率
17 0
|
2天前
|
SQL 关系型数据库 MySQL
MySQL中如何查看所有数据库的名称?
【6月更文挑战第12天】MySQL中如何查看所有数据库的名称?
12 3
|
2天前
|
关系型数据库 数据管理 数据库
数据管理DMS产品使用合集之如何极速恢复RDS(关系型数据库服务)中的数据表
阿里云数据管理DMS提供了全面的数据管理、数据库运维、数据安全、数据迁移与同步等功能,助力企业高效、安全地进行数据库管理和运维工作。以下是DMS产品使用合集的详细介绍。
|
3天前
|
NoSQL 关系型数据库 Serverless
Serverless 应用引擎产品使用合集之连接RDS、Redis等数据库时,是否需要通过安全组来控制访问权限
阿里云Serverless 应用引擎(SAE)提供了完整的微服务应用生命周期管理能力,包括应用部署、服务治理、开发运维、资源管理等功能,并通过扩展功能支持多环境管理、API Gateway、事件驱动等高级应用场景,帮助企业快速构建、部署、运维和扩展微服务架构,实现Serverless化的应用部署与运维模式。以下是对SAE产品使用合集的概述,包括应用管理、服务治理、开发运维、资源管理等方面。
|
3天前
|
SQL 关系型数据库 MySQL
Python 操作 MySQL 数据库
Python 操作 MySQL 数据库