mysql 数据恢复 (.ibdata1, bin log)

最新推荐文章于 2024-08-07 10:26:36 发布

执霜

最新推荐文章于 2024-08-07 10:26:36 发布

阅读量2.5k

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/qq_43156556/article/details/125338964

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

测试MySQL 主题：两台电脑模拟故障后数据迁移恢复

实验环境说明：windows MySQL 5.7版本，两台电脑台式机笔记本电脑

1.实验一：数据库文件 + ibdata1

测试目的：通过拷贝数据库目录文件的方式，模拟在另一台电脑上恢复数据库crashcourse

数据库资料来源：MySQL 必知必会

1.1 ibdata1 拷贝前备份原配

实验步骤：
① 台式机 crashcourse数据库目录文件拷贝到笔记本对应的 data目录下
在这里插入图片描述
② 未拷贝 ibdata1 文件之前，可以通过 show tables;列出拷贝过来的数据库以及数据库表

在这里插入图片描述

③ 执行select查无此表

在这里插入图片描述
参考MYSQL ERROR 1146 Table doesnt exist 解析

④ 拷贝 .ibdata1文件至\data目录下

注意：笔记本上的MySQL也有.ibdata1文件，因此需要先备份笔记本上的.ibdata1文件，备份之后再拷贝.ibdata1文件

⑤ 重启数据库，再次执行 select

重启数据库：

管理员身份运行 cmd
net stop mysql
net start mysql

恢复后的buy_log
在这里插入图片描述

1.2 mysqldump 导出表结构和表数据

说明： crashcourse数据库来自 mysql 必知必会，阅读此书前言部分即可找到下载资料的网站

crashcourse目录下不只是 buy_log一张表，不过实验一仅用到 buy_log表

说明这个问题是因为：对比实验二，若数据库中有大量表，实验一的恢复策略更加便捷

上述虽然完成了将台式机上的crashcourse数据库及其下的表buy_log，

但有个问题是：笔记本上原本也有数据库文件，若使用迁移过来的ibdata1文件，

则意味着笔记本上那份数据库文件同样面临由于缺少 ibdata1系统表文件无法查询的问题

网上也有说明 Linux 下可以设置启用多个 ibdata1，在 windows 下，笔者暂未找到解决方案

参考：Mysql导出表结构和数据

所谓导出表结构和表数据：也就是导出对应的 SQL 语句，创建表的SQL 以及插入数据的SQL 语句

然后使用原来数据库的 ibdata1 文件，执行导出的 sql 文件，即可将迁移过来的数据库文件在另一台电脑上还原

① 导出数据库 crashcourse 数据库下的单表

进入到\bin目录下，该目录下包含 mysql，mysqldump，mysqlbinlog等可执行文件

mysqldump -uroot -p crashcourse buy_log > E:\sqlfile\buy_log.sql

其中mysqldump -uroot -p用于连接数据库，后面需要指定数据库名和单表名称

>：将执行结果写入到E:\sqlfile下的buy_log.sql文件中

② 导出数据库 crashcourse 数据库下的所有表

mysqldump -uroot -p crashcourse > E:\sqlfile\crashcourse.sql

导出所有表，则省略表名；同样导出多表，则用,分割表名

需要注意：导出文件夹是必不可少的，否则仅会在控制台执行操作，找不到sql文件

③ 一窥究竟：导出的 sql 文件
在这里插入图片描述
导出的 sql 文件主要分为以下3部分

删除表：进行部分恢复的时候很有必要，否则会报错 Double Entry
创建表
插入数据：执行insert into前对表加锁，插入之后释放锁
加表锁：lock tables tablename write;
释放锁：unlock tables;

④ 多插入一条数据，验证是否能通过该sql 文件还原表
在这里插入图片描述
表中【userid = 4】是在导出的文件中手动添加的，目的是为了验证通过sql文件在兼容现有数据库文件的情况下迁移的数据

1.3 ibdata1 && Insert Buffer

仅使用独立表空间文件不能恢复数据的原因：以下来自MySQL 技术内幕 InnoDB存储

① Insert Buffer是什么？

Insert Buffer 同数据页一样，是物理页的一个组成部分

② Insert Buffer与二级索引之间的关系？

非聚集索引叶子节点的插入和更新需要离散访问非聚集索引页，

为提升随机存取的性能，提供了Insert Buffer 的解决方案：

插入的非聚集索引页在缓存池中，直接插入
非聚集索引页不在缓存池中，放入到 Insert Buffer中
视情况进行 Insert Buffer和辅助索引子节点的 merge操作

优点：可讲多个插入合并到一个操作中，减少随机IO

③ ibdata1与 Insert Buffer之间的关系？

MySQL 4.1 之后的版本，全局仅有一棵 Insert Buffer B+树，负责对所有表的辅助索引进行 Insert Buffer

而该 B+ 树存放在共享表中，也即 ibdata1中

2.实验二：binlog 日志：mysqlbinlog 读日志 sql | mysql 执行sql

实验目的：测试 binlog 数据恢复

测试方案：笔记本数据库crashcoure下新建user_binlog表并插入数据，仅复制 mysql-bin.000003binlog文件，

在台式机数据库crashcoure下还原该表。

测试步骤：

笔记本和台式机均开启 log-bin
笔记本crashcoure数据库下新建user_binlog表，并插入数据
拷贝 binlog 文件到台式机 \data目录下
mysqlbinlog和mysql配合使用复原user_binlog表

2.1 开启 binlog [windows]

参考：WINDOWS下MYSQL开启BINLOG

关键点：找到 my.ini文件，然后添加下面三句话

# Binary Logging.
log-bin=mysql-bin
binlog-format=ROW
service-id=1

注意：目前网上MySQL的安装教程大致分两类

别问我为什么要拎出来，因为两个电脑上MySQL的安装分别用了不同的安装方式 =_=!!!

① MySQL 安装在 C 盘，Data目录放在 ProgramData下
在这里插入图片描述
这种情况下：\ProgramData\..下的my.ini文件才是要修改的文件

提一嘴特别傻逼的事：修改my.ini文件后要重启数据库，

binlog-format=ROW我设置成了 ROM然后一直报错服务启动不了 =_=

另外推荐下 Listary软件，因许久未操作笔记本上的MySQL，导致我根本不记得它的Data目录还能和安装目录不在一起

搜索数据库文件的位置，该软件即可显示文件所在目录，使用方式也很简单，后台运行，搜索的时候直接敲就行，甚至不用聚焦光标

② 自定义安装位置，手动创建\data目录
在这里插入图片描述
此情况下：my.ini和\data在同级目录

不过添加方式并无差别

最后需要重启 mysql使改动生效

2.2 select , show 燕过不留名

MySQL 技术内幕 InnoDB 存储引擎：binary log 记录了对MySQL数据库执行更改的所有操作，但不包括 select，和 show 这类操作

验证结果：确实没有，不过这里有点乌龙

原计划是先执行 select 和 show, 看 binlog 中的内容，发现报错找不到文件

原因是：mysqlbinlog在 bin 目录下，而binlog在 data 目录下，执行命令的时候需要全路径名

2.2.1 bin log 日志

不过可以对比下找到和未找到日志的情况下，控制台的输出，以帮助分析 binlog 日志

① 使用 mysqlbinlog查看 binlog 日志

注意：mysqlbinlog在\bin目录下，需要先 cd 到 bin 目录下再执行下面的指令

mysqlbinlog --start-positon=position1 --stop-position=position2 -vv FileDir\mysql-binlog

例如查看mysql-bin4.000003日志从偏移量为496的位置开始查看

mysqlbinlog --start-position=496 -vv F:\soft\mysql\mysql-5.7.37-winx64\data\mysql-bin4.000003

其中 -vv显示详细的执行信息，由于设置 binlog-format=ROW，具体的执行内容显示为一串字符串
在这里插入图片描述
如图所示：若不加 -vv不会有下面绿色部分，绿色部分是上述操作的伪SQL注释【有阅读官文 5.7 版本说明】

不过后续又执行了 select ,以及选择数据库的操作，也没有相关的binlog 记录

② 日志结构分析

找不到日志文件时的打印内容

F:\soft\mysql\mysql-5.7.37-winx64\bin>mysqlbinlog --start-position=496 -vv mysql-bin4.000003
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File 'mysql-bin4.000003' not found (Errcode: 2 - No such file or directory)
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

能找到日志文件时的打印内容

F:\soft\mysql\mysql-5.7.37-winx64\bin>mysqlbinlog -vv F:\soft\mysql\mysql-5.7.37-winx64\data\mysql-bin1.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
...
BEGIN
/*!*/;
# at 298
#220617 12:12:13 server id 1  end_log_pos 356 CRC32 0x3f3f77b1  Table_map: `crashcourse`.`buy_log` mapped to number 108
# at 356
#220617 12:12:13 server id 1  end_log_pos 399 CRC32 0xe4b24c25  Write_rows: table id 108 flags: STMT_END_F

BINLOG '
Hf+rYhMBAAAAOgAAAGQBAAAAAGwAAAAAAAEAC2NyYXNoY291cnNlAAdidXlfbG9nAAIDCgACsXc/
Pw==
Hf+rYh4BAAAAKwAAAI8BAAAAAGwAAAAAAAEAAgAC//wEAAAA0cwPJUyy5A==
'/*!*/;
### INSERT INTO `crashcourse`.`buy_log`
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='2022:06:17' /* DATE meta=0 nullable=1 is_null=0 */
# at 399
#220617 12:12:13 server id 1  end_log_pos 430 CRC32 0xe3948a97  Xid = 50
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

可以看到/*!50530 ...相关的是公共部分，即使日志未读取成功也会显示

而日志部分：以事务BGEIN ... COMMIT的形式包装 SQL 操作

实验的时候，并未开启事务，这里是由于默认 autocommit = ON

即每条事务都算是独立事务

还有一点就是：最后的 Xid可用于判断 binlog 日志是否完整

补充关于 MySQL 45 讲中看到该部分的一个小知识点：
在这里插入图片描述
这个我测试了，commmit 是都会有的，是和 begin 配套的，而 Xid 也是都有的，不知道是理解错了还是操作有误

更多关于 binlog 日志的解读，可阅读技术分享 | MySQL binlog 日志解析

以及官方文档MySQL 5.7 Reference Manual

2.2.2 binlog_format : statement && row

关于设置binlog 日志格式为 statement 和 row 的区别，可阅读 MySQL 技术内幕 InnoDB 存储引擎

使用 crashcourse数据库下的 customer表，分别设置：

binlog-format=ROW、binlog-format=STATEMENT

测试说明：在现有行格式为ROW的情况下设置binlog-format=STATEMENT，

需要先修改my.ini文件，再重启，此时再通过 mysqlbinlog查看的日志格式才是 STATEMENT格式的

不重启或者重启后再设置 my.ini无法修改，

原因是通过set session binlog_format='statement'; 设置时，当前binlog 还是基于系统启动时设置的 row 格式

而启动后再设置，修改前，binlog 已经再启动数据库的时候，根据 my.ini设置为 row 格式了

或者可以尝试 flush logs【笔者未试】

① 表结构：

CREATE TABLE `customers` (
  `cust_id` int(11) NOT NULL AUTO_INCREMENT,
  `cust_name` char(50) NOT NULL,
  `cust_address` char(50) DEFAULT NULL,
  `cust_city` char(50) DEFAULT NULL,
  `cust_state` char(5) DEFAULT NULL,
  `cust_zip` char(10) DEFAULT NULL,
  `cust_country` char(50) DEFAULT NULL,
  `cust_contact` char(50) DEFAULT NULL,
  `cust_email` char(255) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10010 DEFAULT CHARSET=utf8;

在这里插入图片描述
② 执行更新操作：将cust_name 列转化为大写

执行SQL 语句为：update customers set cust_name=upper(cust_name) where cust_id = '10001';

③ binlog-format=ROW下的binlog日志

下图仅截取了注释内容伪SQL部分
在这里插入图片描述
④ binlog-format=STATEMENT下的binlog日志

可以看到 STATEMENT格式如何忠实地记下 SQL语句，可以明显看到两者占用的字节数相差很大

因此：书中89279条记录执行上述大小写转换的时候，STATEMENT格式大约占用 200 字节，而ROW格式下大约占用13MB

⑤ row 格式和 statement 格式的区别

通过上述比较，最直观的是：row 格式无论修改几列，都会检索出指定记录的所有列的信息，还包含原数据的信息

更改后也会显式更改后各列的信息。因此 row 显式的日志是基于记录行的

而 statement 格式下，会如实记录 sql 语句，而不包含记录的列信息等内容

基于内容上的差别，自然 statement 格式下占用的内存较小

2.3 mysqlbinlog | mysql

前面啰嗦了一堆都是在铺垫，不过笔者了解不多，所述内容还不够详细

测试说明：笔记本数据库crashcoure下新建user_binlog表并插入数据，仅复制 mysql-bin4.000003binlog文件，

在台式机数据库crashcoure下还原该表。

参考：Mysql 通过binlog日志恢复数据

① 拷贝 mysql-bin4.000003文件至\data目录下
在这里插入图片描述
名字有点怪：因为测试失败过几次

② 读取binlog 中的日志，并执行其中的 sql

主要流程说明：

利用mysqlbinlog读取日志
通过|将读取到的内容，传入到mysql中执行

具体的执行命令：

mysqlbinlog --start-position=496 --database=crashcourse F:\soft\mysql\mysql-5.7.37-winx64\data\mysql-bin4.000003 | mysql -u root -p -v crashcourse

上述指令需要注意的点：

--start-position=496：若 binlog 中混有其他数据表的内容，或者有该表之前的数据，必须要指定开启执行位置；
否则会因为数据表的一致性约束导致事务失败回滚，后续的sql操作无法执行
比如：在原记录存在的情况下，执行 binlog 中的插入操作，会提示 double entry 然后退出
--database=crashcourse：两次指定数据库名称，一次是在 mysqlbinlog命令下，一次是在mysql命令下
F:\soft\mysql\mysql-5.7.37-winx64\data\mysql-bin4.000003：文件全路径名，否则会报错找不到文件
|：管道操作，将读取的mysqlbinlog中的内容写入到mysql中