arm环境vg损坏mysql数据库恢复

Posted on 2025 年 10 月 06 日 by 惜分飞

国庆节期间接到朋友咨询,原先在vg中的磁盘被重新pvcreate了,想恢复原磁盘中的mysql数据库

通过分析系统的history日志,发现操作不是简单的pvcreate,我简单梳理下操作步骤
故障之前磁盘情况

[root@0002 ~]# lsblk
NAME                  MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sr0                    11:0    1 1024M  0 rom  
vda                   253:0    0  200G  0 disk 
├─vda1                253:1    0  600M  0 part /boot/efi
├─vda2                253:2    0    1G  0 part /boot
└─vda3                253:3    0 38.4G  0 part 
  ├─klas-root         252:0    0 34.4G  0 lvm  /
  └─klas-swap         252:1    0    4G  0 lvm  [SWAP]
vdb                   253:16   0 1000G  0 disk 
└─vdb1                253:17   0  500G  0 part 
  └─mysql-mysql--mycg 252:2    0  500G  0 lvm  /mysql

这里可以看到出来vdb磁盘一共1000G,分区vdb1 为500G,然后这500G加入到vg中并分配了lv.

vdb磁盘现状

[root@0002 mysql]# lsblk /dev/vdb
NAME                  MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
vdb                   253:16   0 1000G  0 disk 
└─vdb1                253:17   0 1000G  0 part 

Disk /dev/vdb: 1000 GiB, 1073741824000 bytes, 2097152000 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0x5a6aaeee

Device     Boot Start        End    Sectors  Size Id Type
/dev/vdb1        2048 2097151999 2097149952 1000G 8e Linux LVM

这里基本上可以确定,vdb1磁盘分区从以前的500G变成了1000G（也就是说被重新分区了,后续和现场沟通确认进行了重新分区操作）

通过history日志追述大概的操作过程

  898  [2025-09-28 11:55:13][root]fdisk -l
  899  [2025-09-28 11:55:21][root]df -h
  900  [2025-09-28 11:56:41][root]lsblk
  901  [2025-09-28 11:59:44][root]fdisk /dev/vdb
  902  [2025-09-28 12:00:46][root]partprobe /dev/vdb
  903  [2025-09-28 12:00:50][root]pvresize /dev/vdb1
  904  [2025-09-28 12:00:56][root]df -h
  905  [2025-09-28 12:01:25][root]vgdisplay mysql
  906  [2025-09-28 12:01:40][root]lsblk
  907  [2025-09-28 12:02:05][root]sudo partprobe /dev/vdb
  908  [2025-09-28 12:02:10][root]pvresize /dev/vdb1
  909  [2025-09-28 12:02:27][root]sudo pvresize /dev/vdb1
  910  [2025-09-28 12:03:07][root]sudo pvcreate /dev/vdb1
  911  [2025-09-28 12:03:22][root]sudo pvscan
  912  [2025-09-28 12:03:30][root]sudo pvdisplay
  913  [2025-09-28 12:05:37][root]parted /dev/vdb
  914  [2025-09-28 12:06:11][root]pvresize /dev/vdb1
  915  [2025-09-28 12:06:15][root]lsblk
  916  [2025-09-28 12:09:48][root]lvextend -l +100%FREE /dev/mysql/mysql--mycg
  917  [2025-09-28 12:10:00][root]cd /dev/mysql/
  918  [2025-09-28 12:10:01][root]ll
  919  [2025-09-28 12:10:20][root]pwd
  920  [2025-09-28 12:10:32][root]lvextend -l +100%FREE /dev/mysql/mysql-mycg
  921  [2025-09-28 12:10:55][root]lsblk /dev/vdb

基本上可以确定9月28日先进行了fdisk分区操作,然后尝试pvresize 操作[应该不会成功,因为重新分区导致pv信息丢失],然后进行了pvcreate之后再次进行parted分区操作,再pvresize,lvextend操作[同理pv信息丢失应该不会成功],然后10月5日继续进行的部分操作

  956  [2025-10-05 08:29:27][root]umount /mysql
  957  [2025-10-05 08:29:38][root]lsof /mysql
  958  [2025-10-05 08:29:58][root]service mysqld stop
  959  [2025-10-05 08:30:02][root]umount /mysql
  960  [2025-10-05 08:30:05][root]lsof /mysql
  961  [2025-10-05 08:30:23][root]cd /
  962  [2025-10-05 08:30:25][root]umount /mysql
  963  [2025-10-05 08:30:34][root]pvcreate --force /dev/vdb1
  964  [2025-10-05 08:30:47][root]vgextend mysql /dev/vdb1
  965  [2025-10-05 08:31:02][root]df -h
  966  [2025-10-05 08:31:33][root]pvdisplay /dev/vdb1
  967  [2025-10-05 08:31:41][root]pvcreate --force /dev/vdb1
  968  [2025-10-05 08:32:11][root]lvs | grep mysql-mysql--mycg
  969  [2025-10-05 08:32:19][root]dmsetup ls | grep mysql
  970  [2025-10-05 08:32:38][root]fuser /dev/vdb1
  971  [2025-10-05 08:32:41][root]lsof /dev/vdb1
  972  [2025-10-05 08:32:50][root]pvcreate --force /dev/vdb1
  973  [2025-10-05 08:33:14][root]reboot
  974  [2025-10-05 08:36:23][root]pvcreate --force /dev/vdb1
  975  [2025-10-05 08:36:47][root]lvdisplay /dev/mapper/mysql-mysql--mycg
  976  [2025-10-05 08:36:53][root]vgextend mysql /dev/vdb1
  977  [2025-10-05 08:37:10][root]lvextend -l +100%FREE /dev/mysql/mysql--mycg

初步看,应该是先尝试umount /dev/vdb1,但是没有成功,然后直接reboot重启了主机,起来之后,进行了pvcreate[操作成功],vgextend,lvextend等操作[失败,因为vg里面的之前的pv信息已经丢失],而且之前lv无法mount成功,数据库文件/备份均在这个lv里面,而且从库很久之前没有正常同步.基于这样的情况,就一定要对vdb磁盘中数据进行恢复.查看操作系统信息,确认是arm系统

由于arm系统一般工具均无法正常解析,只能让客户把磁盘挂载到x86环境进行处理,通过专业恢复工具解析,运气不错可以直接读取数据

传输数据到客户服务器中,并成功启动mysql,客户测试业务没有任何问题,数据完整恢复

docker回收和mysql备份导入导致数据丢失恢复

Posted on 2025 年 09 月 11 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：docker回收和mysql备份导入导致数据丢失恢复

最近遇到两例MySQL异常被删除的案例,一例是在docker环境中,由于对docker执行了删除操作,并回收了相关的挂载卷,导致数据彻底丢失

另外一个客户使用备份导入生产库,导致生产库的数据全部被重置为了当时备份的状态,这是由于mysqldump导出数据的时候,默认带有DROP TABLE IF EXISTS `xifenfei`;语句,因此导入备份的时候会先删除掉存在的表,然后创建新表,再insert插入数据.

上述的这两个case,故障发生之后,都没有第一时间保护现场,反而对数据所在分区进行了不少的写入操作,导致覆盖概率相对增加很多.对于这样的故障,一般处理思路:
1. 停掉对该分区写入的业务,如果可以尽可能umount分区,然后做快照或者进项
2. 使用反删除软件对镜像的或者快照的分区进行分析,尝试恢复出来没有被覆盖的MySQL数据,主要是ibd和frm等文件
3. 使用碎片工具对镜像的或者快照的分区进行扫描,根据数据类型生产index和blob的page文件

4. 对于2中恢复的ibd,frm文件,可以尝试通过DISCARD TABLESPACE/IMPORT TABLESPACE方式进行恢复,如果不行对ibd文件进行解析恢复,参考:又一起mysql rm删除数据库目录事故
5. 对于3中恢复出来的page文件,利用工具结合表结构对其进行解析,恢复数据
通过上述恢复,基本上是对于MySQL数据的drop table/truncate table/drop database/rm -rf/格式化等相关误操作的终极恢复思路,对于类似MySQL故障,我们可以实现比较好的恢复效果,如果需要专业恢复技术支持请联系我们:
电话/微信:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com

文件系统格式化MySQL数据库恢复

Posted on 2025 年 06 月 18 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：文件系统格式化MySQL数据库恢复

有客户在做迁移的时候,不慎把存放mysql数据库的硬盘进行了重新分区格式化,重新初始化mysql,并且导入了部分历史数据,不能满足客户需求,希望我们帮忙进行数据恢复.里面大概有100套左右mysql数据库,每个库里面表结构相同,数据不一样.接手这个故障,第一操作就是对磁盘进行镜像,然后使用恢复工具进行底层分析,尝试从文件系统层面恢复出来被格式化之前的数据库文件(需要有对应库目录,不然也没有意义,因为每个库里面表结构一样的,没有正确的库名字无法做到有效的区分),通过底层扫描分析,没有发现一个有效数据文件

对于这样的情况,只能寄希望于mysql 数据块层面扫描恢复,通过工具扫描发现大量的数据块page文件

然后尝试分析字典信息,主要是对于0000000000000001.page对应的表创建语句为:

CREATE TABLE `SYS_TABLES` (
  `NAME` varchar(255) NOT NULL DEFAULT '',
  `ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `N_COLS` int(10) DEFAULT NULL,
  `TYPE` int(10) unsigned DEFAULT NULL,
  `MIX_ID` bigint(20) unsigned DEFAULT NULL,
  `MIX_LEN` int(10) unsigned DEFAULT NULL,
  `CLUSTER_NAME` varchar(255) DEFAULT NULL,
  `SPACE` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

通过上述信息可以获取到name和id列的值(其中name中包含库名/表名),然后再通过0000000000000003.page文件结合对应的表创建语句:

CREATE TABLE `SYS_INDEXES` (
  `TABLE_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `NAME` varchar(120) DEFAULT NULL,
  `N_FIELDS` int(10) unsigned DEFAULT NULL,
  `TYPE` int(10) unsigned DEFAULT NULL,
  `SPACE` int(10) unsigned DEFAULT NULL,
  `PAGE_NO` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`TABLE_ID`,`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

这个主要是或者表id和page id的对应关系(表id直接关联SYS_TABLES.ID,然后获取SYS_TABLES.NAME和SYS_INDEXES.ID对应关系,从而可以确定每一个表对应的需要恢复的page文件信息,再通过客户提供的表语句和对应的page关系,实现相关数据恢复,具体操作可以参考以往类似文章:
xfs文件系统mysql删库恢复
 又一起mysql rm删除数据库目录事故
 [MySQL异常恢复]恢复数据字典表讲解
 [MySQL异常恢复]mysql drop table 数据恢复
 mysql ibd文件反删除恢复之后异常处理
 [MySQL异常恢复]使用工具直接抽取MySQL数据字典
 MySQL drop database恢复(恢复方法同样适用MySQL drop table,delete,truncate table)

[MY-013183] [InnoDB] Assertion failure故障处理

Posted on 2025 年 04 月 19 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：[MY-013183] [InnoDB] Assertion failure故障处理

在一个存储故障的环境中,通过做硬件恢复,恢复出来一个mysql数据库,但是直接启动报错

[mysql@localhost bin]$ ./mysqld
2025-04-17T03:34:50.352302Z 0 [System] [MY-010116] [Server] /data/mysql/mysql/bin/mysqld (mysqld 8.0.34) starting as process 58239
2025-04-17T03:34:50.356910Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-04-17T03:34:51.031054Z 0 [ERROR] [MY-011971] [InnoDB] Tablespace ‘innodb_undo_002′ Page [page id: space=4294967278, page number=160] log sequence number 1728577790947 is in the future! Current system log sequence number 1728577469817.
2025-04-17T03:34:51.031090Z 0 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB redo log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
2025-04-17T03:34:51.031118Z 0 [ERROR] [MY-011971] [InnoDB] Tablespace ‘innodb_undo_002′ Page [page id: space=4294967278, page number=131] log sequence number 1728577833027 is in the future! Current system log sequence number 1728577469817.
2025-04-17T03:34:51.031124Z 0 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB redo log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
2025-04-17T03:34:51.031138Z 0 [ERROR] [MY-011971] [InnoDB] Tablespace ‘innodb_undo_002′ Page [page id: space=4294967278, page number=3621] log sequence number 1728577635513 is in the future! Current system log sequence number 1728577469817.
2025-04-17T03:34:51.031142Z 0 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB redo log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
2025-04-17T03:34:51.031193Z 0 [ERROR] [MY-011971] [InnoDB] Tablespace ‘innodb_undo_002′ Page [page id: space=4294967278, page number=167] log sequence number 1728577760219 is in the future! Current system log sequence number 1728577469817.
2025-04-17T03:34:51.042480Z 0 [ERROR] [MY-011971] [InnoDB] Tablespace ‘innodb_undo_001′ Page [page id: space=4294967279, page number=184] log sequence number 1728577792529 is in the future! Current system log sequence number 1728577469817.
2025-04-17T03:34:51.042486Z 0 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB redo log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
2025-04-17T03:34:51.042359Z 0 [ERROR] [MY-011971] [InnoDB] Tablespace ‘innodb_undo_001′ Page [page id: space=4294967279, page number=1975] log sequence number 1728577800027 is in the future! Current system log sequence number 1728577469817.
2025-04-17T03:34:51.042681Z 0 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB redo log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
2025-04-17T03:34:51.059937Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-04-17T03:34:51.159245Z 0 [ERROR] [MY-011971] [InnoDB] Tablespace ‘xff/t_xifenfei’ Page [page id: space=153, page number=4] log sequence number 1728577926919 is in the future! Current system log sequence number 1728577498088.
2025-04-17T03:34:51.159280Z 0 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB redo log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
2025-04-17T03:34:51.163187Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: fut0lst.ic:81:addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA thread 140491735693056
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
2025-04-17T03:34:51Z UTC – mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=f183cd3ecfc35a4aa5da997063d5e8c97ffca986
Thread pointer: 0x7fc6bc000b60
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = 7fc6c7ffeaf0 thread_stack 0×100000
/data/mysql/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0×41) [0x21323b1]
/data/mysql/mysql/bin/mysqld(print_fatal_signal(int)+0x2a2) [0xfef932]
/data/mysql/mysql/bin/mysqld(my_server_abort()+0×75) [0xfefb75]
/data/mysql/mysql/bin/mysqld(my_abort()+0xe) [0x212c24e]
/data/mysql/mysql/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0×309) [0x237cde9]
/data/mysql/mysql/bin/mysqld() [0x2349cf0]
/data/mysql/mysql/bin/mysqld() [0x234aa54]
/data/mysql/mysql/bin/mysqld(trx_purge(unsigned long, unsigned long, bool)+0xeb) [0x234d56b]
/data/mysql/mysql/bin/mysqld(srv_purge_coordinator_thread()+0×450) [0x23224b0]
/data/mysql/mysql/bin/mysqld(void Detached_thread::operator()<void (*)()>(void (*&&)())+0xca) [0x224bcaa]
/lib64/libstdc++.so.6(+0xc2ba3) [0x7fc731c11ba3]
/lib64/libpthread.so.0(+0x814a) [0x7fc732fe614a]
/lib64/libc.so.6(clone+0×43) [0x7fc7312eef23]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 0
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

这个报错主要含义是：

多个表空间（特别是innodb_undo_*）的日志序列号(LSN)比当前系统LSN要大，这表明可能存在数据损坏或不一致
系统最终因为断言失败而崩溃

对于这样的情况,可以通过mysql强制拉库的方式启动mysql,如果可以启动成功直接使用mysqldump导出数据,然后重建新库,如果无法启动mysql成功,那就考虑通过对单个的ibd基表进行discard+import方式进行恢复参考:MySQL 8.0版本ibd文件恢复，如果这个方法不能成功考虑直接通过工具读取ibd文件参考:frm和ibd文件数据库恢复

.[OnlyBuy@cyberfear.com].REVRAC勒索mysql恢复

Posted on 2025 年 03 月 13 日 by 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：.[OnlyBuy@cyberfear.com].REVRAC勒索mysql恢复

有朋友接到一个mariadb库被加密的case,部分文件被加密为:.[D2BB58C7].[OnlyBuy@cyberfear.com].REVRAC扩展名

黑客预留的+README-WARNING+.txt内容类似:

YOUR FILES ARE ENCRYPTED

Your files, documents, photos, databases and other important files are encrypted.

You are not able to decrypt it by yourself! The only method of recovering files is to purchase an unique private key.
Only we can give you this key and only we can recover your files.

To be sure we have the decryptor and it works you can send an 
    email: TechSupport@cyberfear.com  and decrypt one file for free.

Before paying you can send us up to 1 file for free decryption. The total size of files must be less than 1Mb 
(non archived), and files should not contain valuable information. (databases,backups, large excel sheets,sql. etc.) 

Do you really want to restore your files?
Write to email: OnlyBuy@cyberfear.com

Your personal ID is indicated in the names of the files and in the end of this message, before writing a message by email
indicate the name of the ID indicated in the files IN THE SUBJECT OF THE EMAIL

Attention!
 * Do not rename encrypted files.
 * Do not try to decrypt your data using third party software, it may cause permanent data loss.
 * Decryption of your files with the help of third parties may cause increased price (they add their fee to our) 
   or you can become a victim of a scam.

YOUR ID: D2BB58C7

通过分析ibd文件没有被破坏

这种情况恢复相对比较简单,可以直接通过对单独ibd文件会的思路进行处理,类似恢复文章:
frm和ibd文件数据库恢复
MySQL 8.0版本ibd文件恢复
[MySQL异常恢复]mysql ibd文件恢复
InnoDB: Cannot open table db/tab from the internal data dictionary of InnoDB though the .frm file for the table exists
当然前提需要有表创建语句,这个客户有昨天的备份的被的.sql备份,通过技术手段分析,确认只有3个表的创建语句丢失,对于丢失的ddl语句,通过直接对ibdata文件解析获取,基于这些信息结合,实现数据的完美恢复

对于类似这种被加密的勒索的数据文件,我们可以实现比较好的恢复效果,如果此类的数据库(oracle,mysql,sql server)等被加密,需要专业恢复技术支持，请联系我们:
电话/微信:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
系统安全防护措施建议：
1.多台机器，不要使用相同的账号和口令
2.登录口令要有足够的长度和复杂性，并定期更换登录口令
3.重要资料的共享文件夹应设置访问权限控制，并进行定期备份
4.定期检测系统和软件中的安全漏洞，及时打上补丁。
5.定期到服务器检查是否存在异常。
6.安装安全防护软件，并确保其正常运行。
7.从正规渠道下载安装软件。
8.对不熟悉的软件，如果已经被杀毒软件拦截查杀，不要添加信任继续运行。
9.保存良好的备份习惯，尽量做到每日备份，异地备份。