分类目录归档：MySQL

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

发表于 2025 年 6 月 18 日 由 惜分飞

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

对于这样的情况,只能寄希望于mysql 数据块层面扫描恢复,通过工具扫描发现大量的数据块page文件
block-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)

发表在 MySQL恢复 | 标签为 , | 留下评论

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

发表于 2025 年 4 月 19 日 由 惜分飞

在一个存储故障的环境中,通过做硬件恢复,恢复出来一个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.

这个报错主要含义是：

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

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

发表在 MySQL恢复 | 标签为 , , | 评论关闭

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

发表于 2025 年 3 月 13 日 由 惜分飞

有朋友接到一个mariadb库被加密的case,部分文件被加密为:.[D2BB58C7].[OnlyBuy@cyberfear.com].REVRAC扩展名
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文件没有被破坏
225026

这种情况恢复相对比较简单,可以直接通过对单独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:107644445QQ咨询惜分飞    E-Mail:dba@xifenfei.com
系统安全防护措施建议：
1.多台机器，不要使用相同的账号和口令
2.登录口令要有足够的长度和复杂性，并定期更换登录口令
3.重要资料的共享文件夹应设置访问权限控制，并进行定期备份
4.定期检测系统和软件中的安全漏洞，及时打上补丁。
5.定期到服务器检查是否存在异常。
6.安装安全防护软件，并确保其正常运行。
7.从正规渠道下载安装软件。
8.对不熟悉的软件，如果已经被杀毒软件拦截查杀，不要添加信任继续运行。
9.保存良好的备份习惯，尽量做到每日备份，异地备份。

发表在 MySQL恢复, 勒索恢复 | 标签为 , , , | 评论关闭