分类目录归档：MySQL

mysql 数据库目录被删除恢复

发表于 2020 年 2 月 16 日由惜分飞

接到朋友请求,把mysql数据库的datadir目录给删除了,数据库目前还处于运行状态,但是很多操作已经无法正常进行
数据库可以登录,但是已经看不到任何业务数据库,可以结合表名查询

[root@hy-db-xff-s-110 mysql3306]# mysql -uroot -ptSQghoV^J1GE^U8*wPElImv5
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 443214
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> select count(1) from xifenfei.orders;
+----------+
| count(1) |
+----------+
| 16451326 |
+----------+
1 row in set (4.17 sec)

数据无法导出(into outfile不行是由于secure-file-priv参数默认导致)

mysql> select * from xifenfei.orders into outfile '/bakcup/orders_new.sql' 
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

[root@hy-db-cps-s-110 fd]# mysqldump  -uroot -pwww.xifenfei.com xifenfei orders >/linshi/1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1049: Unknown database 'xifenfei' when selecting the database

因为mysql没有crash,因此相关文件已经存在(没有被真正删除)

通过此类方法恢复相关数据文件到新服务器上,然后尝试启动数据库,发现无法正常启动,有部分文件丢失,最后对单独ibd单独处理进行恢复,具体参考:[MySQL异常恢复]mysql ibd文件恢复,实现绝大多数数据的恢复,对于部分无法通过此类方法恢复出来的数据,在磁盘级别没有覆盖的情况下,可以先按照os层面方法恢复,参考:extundelete恢复Linux被删除文件,如果此类方法也无法正常恢复,可以尝试数据库磁盘碎片级别恢复:MySQL drop database恢复(恢复方法同样适用MySQL drop table,delete,truncate table)

发表在 MySQL恢复 | 标签为 mysql rm恢复, rm mysql恢复, 删除mysql文件恢复 | 评论关闭

mysql数据库被加密恢复

发表于 2020 年 1 月 29 日由惜分飞

有客户mysql数据库被加密,加密信息如下

由于是mysql 5.6的版本,默认情况innodb引擎,Innodb_file_per_table参数默认为true,因此数据为每个表存在为一个单独的ibd文件中,让客户提供需要恢复的表的ibd被加密文件

通过一系列底层操作,实现数据完美恢复

如果是Innodb_file_per_table参数为false(5.6之前版本默认为false),需要通过ibdata文件进行恢复
如果您的数据库(oracle,mysql sql server)不幸被比特币加密,可以联系我们
Tel/微信:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com提供专业的解密恢复服务.

发表在 MySQL恢复 | 标签为 ibd损坏恢复, mysql 加密恢复, mysql加密, YOUR FILES ARE ENCRYPTED | 评论关闭

[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恢复 | 标签为 ibd恢复, mysql drop database恢复, mysql drop table 恢复 | 评论关闭

17813235971

QQ 咨询

分类目录归档：MySQL

mysql 数据库目录被删除恢复

mysql数据库被加密恢复

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

文章分类

最近发表

分类目录归档：MySQL

mysql 数据库目录被删除恢复

mysql数据库被加密恢复

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

标签云

文章分类

最近发表