测试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 数据库下的所有表
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
标签:bin,数据恢复,log,binlog,数据库,文件,mysql,日志 From: https://www.cnblogs.com/shigp/p/16894365.html