标签归档：ibd恢复

MySQL 8.0版本ibd文件恢复

发表于 2022 年 12 月 18 日 由 惜分飞

对于单个的ibd文件,大部分情况下可以通过DISCARD TABLESPACE和IMPORT TABLESPACE方式进行恢复

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE `t1` (
    ->   `id` int DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 values(2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(3);
Query OK, 1 row affected (0.00 sec)

关闭mysql服务，备份mysql中的t1.ibd文件

[root@xifenfei ~]# service mysql stop
Shutting down MySQL..... SUCCESS! 
[root@xifenfei test]# cp t1.ibd  t1_bak

启动mysql服务,并删除并创建新的t1表(表结构相同)

[root@xifenfei test]# service mysql start
Starting MySQL..................... SUCCESS! 


[root@xifenfei test]# mysql -uroot -poracle test
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1;
Query OK, 0 rows affected (0.20 sec)


mysql> 
mysql> CREATE TABLE `t1` (
    ->   `id` int DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.01 sec)

DISCARD TABLESPACE操作

mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.01 sec)

把备份的t1.ibd还原回去并修改权限

[root@xifenfei test]# mv t1_bak t1.ibd
[root@xifenfei test]# ls -ltr
total 112
-rw-r-----. 1 root root 114688 Dec 18 17:24 t1.ibd
[root@xifenfei test]# chown mysql.mysql t1.ibd

IMPORT TABLESPACE并验证数据

mysql> ALTER TABLE t1 IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.24 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

在恢复途中如果遇到表定义不对,或者ibd文件损坏，或者版本不匹配等各种情况,可能在IMPORT TABLESPACE的时候可能出现类似ERROR 1808 (HY000): Schema mismatch (Clustered index validation failed. Because the .cfg file is missing, table definition of the IBD file could be different. Or the data file itself is already corrupted.)错误

mysql>  alter table       `t1` import tablespace;                    
ERROR 1808 (HY000): Schema mismatch (Clustered index validation failed. 
Because the .cfg file is missing, table definition of the IBD file could be different. 
Or the data file itself is already corrupted.)

如果出现此类错误,无法直接通过该方法进行解决,参考frm和ibd文件数据库恢复,使用专业恢复工具进行处理

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

frm和ibd文件数据库恢复

发表于 2021 年 12 月 24 日 由 惜分飞

这次客户rm -rf /var/lib/mysql删除文件,删除一半及时终止,但是已经有很多mysql相关文件被删除,重要的ibdata文件已经被删除,并且客户尝试了大量的恢复工作,对该分区进行了大量的写入操作,导致后面通过对xfs文件系统进行分析，确认无法恢复对应的ibdata文件.比较幸运客户需要的核心的mysql库都还在(frm和ibd文件还存在)
20211224105004

对于这种情况,可以参考以前类似的处理方法:[MySQL异常恢复]mysql ibd文件恢复
由于客户无法提供创建表语句需要通过对frm进行解析获取语句,利用mysqlfrm获取表创建语句

E:\3>mysqlfrm --server=root:oracle@192.168.222.79:3306 --diagnostic T_XIFENFEI.frm
WARNING: Using a password on the command line interface can be insecure.
# Source on 192.168.222.79: ... connected.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of 
  the components of the table correctly. This is especially true for damaged files. 
  It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for EVALUATOR_T.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `T_XIFENFEI` (
  `ID` varchar(32) COLLATE `utf8_general_ci` DEFAULT NULL comment '主键',
  `BO_TYPE_DEFINE_ID` varchar(32) COLLATE `utf8_general_ci` DEFAULT NULL comment '业务对象类型ID',
  `MAIN_ID` varchar(32) COLLATE `utf8_general_ci` DEFAULT NULL comment '业务对象主表记录ID',
  `PARENT_ID` varchar(32) COLLATE `utf8_general_ci` DEFAULT NULL comment '父ID',
  `ROW_NUM` decimal(32,0) DEFAULT NULL comment '行号',
  `VERSION` decimal(32,6) DEFAULT NULL comment '版本',
  `CREATE_DATE` datetime DEFAULT NULL comment '创建时间',
  `UPDATE_DATE` datetime DEFAULT NULL comment '更新时间',
  `BO_SOURCE_ROW_ID` varchar(32) COLLATE `utf8_general_ci` DEFAULT NULL comment '来源明细行ID',
  `EVALUATORS` text COLLATE `utf8_general_ci` DEFAULT NULL,
  `IMPORTANCE` decimal(32,6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8, COMMENT '评分人';
#...done.

对于有些获取语句失败,比如类似这样错误

E:\TEMP\10000246_1108\db\ync2_fssc_2000003>mysqlfrm --server=root:oracle@192.168.222.79:3306 --diagnostic T_XFF.frm
Traceback (most recent call last):
  File "G:\ade\build\Python-2.7.6-windows-x86-64bit\lib\site-packages\cx_Freeze\initscripts\Console.py",
    line 27, in <module>
  File "scripts\mysqlfrm.py", line 419, in <module>
  File ".\mysql\utilities\command\read_frm.py", line 396, in read_frm_files_diagnostic
  File ".\mysql\utilities\common\frm_reader.py", line 1538, in show_create_table_statement
  File ".\mysql\utilities\common\frm_reader.py", line 1385, in _build_create_statement
  File ".\mysql\utilities\common\frm_reader.py", line 1273, in _get_key_columns
IndexError: list index out of range

使用专门的工具对其进行解析
20211224105004

然后利用这些创建表语句在库中创建表,并利用以下方法进行操作

mysql> alter table  `t_xifenfei` discard tablespace;        
Query OK, 0 rows affected (0.00 sec)

--上传老的t_xifenfei.ibd文件,并修改所有者和属组

mysql> alter table  `t_xifenfei` import tablespace;                
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> select count(1) from   `t_xifenfei` ;              
+----------+
| count(1) |
+----------+
|       78 |
+----------+
1 row in set (0.00 sec)

使用类似的方法对于数据进行批量处理,然后使用mysqldump进行导出.在这个ibd的discard和import的过程中,有些异常情况这三种错误的处理

mysql> alter table T_LOG_XIFENFEI                   import tablespace;
ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)
mysql> alter table     `T_LOG_XIFENFEI` import tablespace;
ERROR 1817 (HY000): Index corrupt: Externally stored column(4) has a reference length of 4 in the cluster index PRIMARY
mysql> alter table       `T_LOG_XIFENFEI` import tablespace;
ERROR 1815 (HY000): Internal error: Cannot reset LSNs in table `XFF`.`T_LOG_XIFENFEI` : Data structure corruption

Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.) 这种错误是由于row_format设置不正确导致,重新创建表使用正确的row_format然后执行discard和import操作.
Index corrupt: Externally stored column(4) has a reference length of 4 in the cluster index PRIMARY 这种错误是由于表的创建语句和ibd中记录数据不匹配,主要是由于创建表语句不完全正确导致,重新获取正确语句进行恢复
Internal error: Cannot reset LSNs in table `XFF`.`T_LOG_XIFENFEI` : Data structure corruption 这种错误是由于ibd文件本身不一致无法使用该方法恢复,对于这类情况使用我们专业的工具进行处理
20211224142855

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

[MySQL异常恢复]mysql ibd文件恢复

发表于 2016 年 12 月 25 日 由 惜分飞

在mysql中由于某种原因保存有ibd文件，但是表已经被删除或者frm文件损坏亦或者ibdata文件损坏/丢失等。本文模拟在这种情况下，通过mysql自身技术即可完成ibd文件恢复.
测试环境mysql版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.25    |
+-----------+
1 row in set (0.00 sec)

mysql主要参数

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_force_recovery';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_force_recovery | 0     |
+-----------------------+-------+
1 row in set (0.00 sec)

innodb_file_per_table这个参数为on才能够实现每个表存储单独的ibd文件.innodb_force_recovery参数默认范围0

测试表情况

mysql> use xifenfei;
Database changed
mysql> show tables;
+-----------------------------+
| Tables_in_xifenfei          |
+-----------------------------+
| user_login                  |
+-----------------------------+
1 rows in set (0.00 sec)

mysql> select count(*) from user_login;
+----------+
| count(*) |
+----------+
|       48 |
+----------+
1 row in set (0.02 sec)

mysql> desc user_login;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| ID         | varchar(255) | NO   | PRI | NULL    |       |
| ACCOUNT    | varchar(255) | YES  |     | NULL    |       |
| LifeCycle  | int(11)      | YES  |     | NULL    |       |
| Name       | varchar(255) | YES  |     | NULL    |       |
| Password   | varchar(255) | YES  |     | NULL    |       |
| Role       | varchar(255) | YES  |     | NULL    |       |
| UTime      | varchar(255) | YES  |     | NULL    |       |
| UserID     | varchar(255) | YES  |     | NULL    |       |
| UserName   | varchar(255) | YES  |     | NULL    |       |
| UserStatus | int(11)      | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
10 rows in set (0.05 sec)

mysql> select * from user_login limit 1;
+----------------------------------+---------+-----------+-----------+----------
------------------------+------+---------------------+--------------------------
--------+----------+------------+
| ID                               | ACCOUNT | LifeCycle | Name      | Password
                        | Role | UTime               | UserID
        | UserName | UserStatus |
+----------------------------------+---------+-----------+-----------+----------
------------------------+------+---------------------+--------------------------
--------+----------+------------+
| 010d6c85a76c44cba80d07cbd8590bb2 | hyh     |         0 | 胡元会    | 698d51a19
d8a121ce581499d7b701668 | |6|  | 2016-08-30 06:04:32 | 0fe3bc4dd9654687a4b85065e
d5cfee8 | NULL     |          1 |
+----------------------------------+---------+-----------+-----------+----------
------------------------+------+---------------------+--------------------------
--------+----------+------------+
1 row in set (0.00 sec)

mysql> show create table user_login \G;
*************************** 1. row *************
       Table: user_login
Create Table: CREATE TABLE `user_login` (
  `ID` varchar(255) NOT NULL,
  `ACCOUNT` varchar(255) DEFAULT NULL,
  `LifeCycle` int(11) DEFAULT NULL,
  `Name` varchar(255) DEFAULT NULL,
  `Password` varchar(255) DEFAULT NULL,
  `Role` varchar(255) DEFAULT NULL,
  `UTime` varchar(255) DEFAULT NULL,
  `UserID` varchar(255) DEFAULT NULL,
  `UserName` varchar(255) DEFAULT NULL,
  `UserStatus` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> show variables like 'datadir';
+---------------+-----------------------------------------------+
| Variable_name | Value                                         |
+---------------+-----------------------------------------------+
| datadir       | D:\xifenfei\mysql-5.6.25-winx64\data\ |
+---------------+-----------------------------------------------+
1 row in set (0.00 sec)

备份ibd文件

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd
 驱动器 D 中的卷没有标签。
 卷的序列号是 4215-1F18

 D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录

2016-12-02  20:07            98,304 user_login.ibd
               1 个文件         98,304 字节
               0 个目录 78,789,591,040 可用字节
C:\Users\XIFENFEI>cp D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd d:/
C:\Users\XIFENFEI>dir d:\user_login.ibd
 驱动器 D 中的卷没有标签。
 卷的序列号是 4215-1F18

 d:\ 的目录

2016-12-25  23:15            98,304 user_login.ibd
               1 个文件         98,304 字节
               0 个目录 78,789,591,040 可用字节

模拟删除表(ibd文件也被删除)

mysql> drop table xifenfei.user_login;
Query OK, 0 rows affected (0.03 sec)


C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd
 驱动器 D 中的卷没有标签。
 卷的序列号是 4215-1F18

 D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录

找不到文件

创建新表

mysql> CREATE TABLE `user_login` (
    ->   `ID` varchar(255) NOT NULL,
    ->   `ACCOUNT` varchar(255) DEFAULT NULL,
    ->   `LifeCycle` int(11) DEFAULT NULL,
    ->   `Name` varchar(255) DEFAULT NULL,
    ->   `Password` varchar(255) DEFAULT NULL,
    ->   `Role` varchar(255) DEFAULT NULL,
    ->   `UTime` varchar(255) DEFAULT NULL,
    ->   `UserID` varchar(255) DEFAULT NULL,
    ->   `UserName` varchar(255) DEFAULT NULL,
    ->   `UserStatus` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd
 驱动器 D 中的卷没有标签。
 卷的序列号是 4215-1F18

 D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录

2016-12-25  23:19            98,304 user_login.ibd
               1 个文件         98,304 字节
               0 个目录 78,789,591,040 可用字节

mysql> select count(*) from xifenfei.user_login;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

停掉mysql,替换user_login.ibd

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd
 驱动器 D 中的卷没有标签。
 卷的序列号是 4215-1F18

 D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录

2016-12-25  23:22            98,304 user_login.ibd
               1 个文件         98,304 字节
               0 个目录 78,787,141,632 可用字节

C:\Users\XIFENFEI>cp d:\user_login.ibd D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd
C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd
 驱动器 D 中的卷没有标签。
 卷的序列号是 4215-1F18

 D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录

2016-12-02  20:07            98,304 user_login.ibd
               1 个文件         98,304 字节
               0 个目录 78,787,141,632 可用字节

启动mysql 服务,查询数据库

mysql> select count(*) from xifenfei.user_login;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> exit
Bye

C:\Users\XIFENFEI>mysql -uroot
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

mysql 日志报错

2016-12-25 23:31:07 11632 [Note] MySQL: ready for connections.
Version: '5.6.25'  socket: ''  port: 3306  MySQL Community Server (GPL)
InnoDB: Error: tablespace id is 56 in the data dictionary
InnoDB: but in file .\xifenfei\user_login.ibd it is 47!
2016-12-25 23:31:31 2eb8  InnoDB: Assertion failure in thread 11960 in file fil0fil.cc line 796
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

很明显由于替换的ibd文件和现在数据库记录的ibd文件的page的字典信息不匹配，因为数据库无法正常查询该数据，而且mysql为了安全直接把实例给crash了.

恢复操作

mysql> show variables like 'innodb_force_recovery';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_force_recovery | 1     |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> alter table xifenfei.user_login discard tablespace;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> alter table xifenfei.user_login import tablespace;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> select count(*) from xifenfei.user_login;
+----------+
| count(*) |
+----------+
|       48 |
+----------+
1 row in set (0.00 sec)

mysql> select * from xifenfei.user_login limit 1;
+----------------------------------+---------+-----------+-----------+----------
------------------------+------+---------------------+--------------------------
--------+----------+------------+
| ID                               | ACCOUNT | LifeCycle | Name      | Password
                        | Role | UTime               | UserID
        | UserName | UserStatus |
+----------------------------------+---------+-----------+-----------+----------
------------------------+------+---------------------+--------------------------
--------+----------+------------+
| 010d6c85a76c44cba80d07cbd8590bb2 | hyh     |         0 | 胡元会    | 698d51a19
d8a121ce581499d7b701668 | |6|  | 2016-08-30 06:04:32 | 0fe3bc4dd9654687a4b85065e
d5cfee8 | NULL     |          1 |
+----------------------------------+---------+-----------+-----------+----------
------------------------+------+---------------------+--------------------------
--------+----------+------------+
1 row in set (0.00 sec)

通过mysql自带的discard tablespace和import tablespace操作后,表数据已经可以完成查询了.
mysql日志

2016-12-25 23:34:08 10464 [ERROR] InnoDB: Failed to find tablespace for table '"xifenfei"."user_login"' in the cache. Attempting to load the tablespace with space id 56.
2016-12-25 23:34:08 10464 [ERROR] InnoDB: In file '.\xifenfei\user_login.ibd', tablespace id and flags are 47 and 0, but in the InnoDB data dictionary they are 56 and 0. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2016-12-25 23:34:08 10464 [ERROR] InnoDB: Could not find a valid tablespace file for 'xifenfei/user_login'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2016-12-25 23:34:08 30e8 InnoDB: cannot calculate statistics for table "xifenfei"."user_login" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
2016-12-25 23:34:08 10464 [ERROR] InnoDB: Cannot delete tablespace 56 because it is not found in the tablespace memory cache.
2016-12-25 23:34:08 10464 [Warning] InnoDB: Cannot delete tablespace 56 in DISCARD TABLESPACE. Tablespace not found
2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk
2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk - done!
2016-12-25 23:34:41 10464 [Note] InnoDB: Phase I - Update all pages
2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk
2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk - done!
2016-12-25 23:34:41 10464 [Warning] InnoDB: Tablespace 'xifenfei/user_login' exists in the cache with id 47 != 56
2016-12-25 23:34:41 10464 [Warning] InnoDB: Freeing existing tablespace 'xifenfei/user_login' entry from the cache with id 56
2016-12-25 23:34:41 10464 [Note] InnoDB: Phase III - Flush changes to disk
2016-12-25 23:34:41 10464 [Note] InnoDB: Phase IV - Flush complete

mysql日志依旧报了page字典信息不匹配.但是数据已经可以访问,通过mysqldump导出重新创建表即可.如果由于ibd损坏使用该方法无法恢复,请参考:MySQL drop database恢复(恢复方法同样适用MySQL drop table,delete,truncate table)

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