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

测试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等可执行文件

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

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

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

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

1 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文件，然后添加下面三句话

1 # Binary Logging.
2 log-bin=mysql-bin
3 binlog-format=ROW
4 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的位置开始查看

1 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 记录

② 日志结构分析

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

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

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

 1 F:\soft\mysql\mysql-5.7.37-winx64\bin>mysqlbinlog -vv F:\soft\mysql\mysql-5.7.37-winx64\data\mysql-bin1.000001
 2 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
 3 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 4 DELIMITER /*!*/;
 5 ...
 6 BEGIN
 7 /*!*/;
 8 # at 298
 9 #220617 12:12:13 server id 1  end_log_pos 356 CRC32 0x3f3f77b1  Table_map: `crashcourse`.`buy_log` mapped to number 108
10 # at 356
11 #220617 12:12:13 server id 1  end_log_pos 399 CRC32 0xe4b24c25  Write_rows: table id 108 flags: STMT_END_F
12 
13 BINLOG '
14 Hf+rYhMBAAAAOgAAAGQBAAAAAGwAAAAAAAEAC2NyYXNoY291cnNlAAdidXlfbG9nAAIDCgACsXc/
15 Pw==
16 Hf+rYh4BAAAAKwAAAI8BAAAAAGwAAAAAAAEAAgAC//wEAAAA0cwPJUyy5A==
17 '/*!*/;
18 ### INSERT INTO `crashcourse`.`buy_log`
19 ### SET
20 ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
21 ###   @2='2022:06:17' /* DATE meta=0 nullable=1 is_null=0 */
22 # at 399
23 #220617 12:12:13 server id 1  end_log_pos 430 CRC32 0xe3948a97  Xid = 50
24 COMMIT/*!*/;
25 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
26 DELIMITER ;
27 # End of log file
28 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
29 /*!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【笔者未试】

① 表结构：

 1 CREATE TABLE `customers` (
 2   `cust_id` int(11) NOT NULL AUTO_INCREMENT,
 3   `cust_name` char(50) NOT NULL,
 4   `cust_address` char(50) DEFAULT NULL,
 5   `cust_city` char(50) DEFAULT NULL,
 6   `cust_state` char(5) DEFAULT NULL,
 7   `cust_zip` char(10) DEFAULT NULL,
 8   `cust_country` char(50) DEFAULT NULL,
 9   `cust_contact` char(50) DEFAULT NULL,
10   `cust_email` char(255) DEFAULT NULL,
11   PRIMARY KEY (`cust_id`)
12 ) 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中执行

具体的执行命令：

1 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中

关于如何确定从那个位置开始恢复？

在参考的blog中 mysql.exe 下执行 show binlog events in binlogname

可以清楚地显示某行修改操作的开始 begin 和结束位置 commit

但若是从另一台机器上拷贝 binlog 文件的话，笔者通过上述方法未成功定位 position

原因是 binlog 日志会在新建数据库的时候生成，也就是说和原有的数据库之间应该有绑定关系。

包括 mysql-bin.index 也是在MySQL配置binlog开启的时候才生成，与相应的服务有关系。

在这种情况下通过 mysql 下的指令查询的时候，找不到迁移过来的binlog文件

③ 检验crashcourse下是否有新迁移过来的表user_binlog

说明：原来数据库下是不存在user_binlog表的，该表完全是通过binlog日志迁移过来的

本质上 binlog日志中也是 sql 语句

写在最后，本来还有一个实验三：redo log 恢复的，结果看了两天 redo log 的内容后。

发现一时半会儿，未能理解，尤其是卡在 double write 上面死活理解不了，还有就是不知道怎么查看 redo log 的内容

不过通过这次的实验再去看MySQL 45 讲两阶段提交部分，突然理解了点。

两阶段提交意图的意图是保证 bin log 和 redo log 的一致性

而在生产实践中，目前了解到的是定时全量备份【数据库文件】+ 增量备份【bin log】

关于 double write 理解中的问题：

问题描述：在写磁盘的时候宕机，也就是发生了页中断

MySQL 技术内幕存储引擎：部分写失效时，不能直接使用 redo log 恢复，原因是此时页已经损坏

看到阿里云上一篇 blog 觉得有点道理，但是最后一点还是没明白

https://developer.aliyun.com/article/414745

① 先写数据页到 ibtable1，然后再写入到磁盘

② 写入到共享表时宕机，则用磁盘上未修改的那份和redo log 还原

③ 写入到磁盘时宕机，则利用共享表的那份和 redo log 进行还原

有问题的就是第三点：虽然这时的数据页未写入 .ibd 文件，但是从内存中刷出来的修改过的页。

这样的话，为什么还要用 redo log 修复呢？如果说目的是将页读入内存然后再写入到 .idb 文件

这里关于写入 ibdata1 和写入 .ibd 文件的区别，看MySQL45讲的时候，理解到是写入粉板和写入账本的区别

后者是有组织结构和目录的。这样理解的话，可能就要用到 redo 中关于页物理信息修改情况，写入到 .ibd 文件的指定位置

但若从这个角度来理解，那部分写失效的页面，读入到内存的时候，

不相当于是已经进行了部分写入，剩下的再利用 redo log 写入就行了吗？

就迷在这一点了，暂时无解，等有解了再回头补充理解，顺便嘲笑下自己的无知
————————————————
版权声明：本文为CSDN博主「执霜」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_43156556/article/details/125338964