MySQL Innodb数据库误删ibdata1后MySQL数据库的恢复案例

上周，以前公司的同事朋友找我帮忙，看看能否帮忙恢复一个MySQL 数据库，具体情况为：数据库版本为MySQL 5.6（具体版本不清楚），也不清楚具体的数据库引擎; 没有数据库备份，只剩下数据库下面的一些文件（frm、idb）,具体原因是因为出现问题的时候，重装了MySQL，最要命的是ibdata1等文件也没有了，当然这中间细节过程如何，不清楚也不用去纠结了。大概就是这么一个情况。

因为数据库不大，将对应的文件拷贝到自己一台测试服务器的MySQL数据文件目录下后（下面实验测试，对数据库名等敏感信息做了一下混淆），如下所示，数据库名为test，show tables可以看到相关的表。

其中有几张表的存储引擎为MyISAM，那么这些表的数据是完全可以恢复的，但是大部分表的存储引擎为InnoDB，访问表或查看表都会提示“ERROR 1146 (42S02): Table 'xxxx' doesn't exist 不存在。

mysql> desc think_cache;ERROR 1146 (42S02): Table 'test.think_cache' doesn't existmysql> show create table think_cache;ERROR 1146 (42S02): Table 'test.think_cache' doesn't existmysql> 

由于共享表空间的ibdata1数据文件不存在了，加之有没有备份，所以我武断的判断这个数据库真的无法恢复了，但是过后一天，这个朋友跟我说找了一家数据恢复公司将这个数据库恢复了。听到这个消息颇有点学艺不精的尴尬（其实谈不上尴尬吧，本来还在学习MySQL的路上，有些知识点不清楚也很正常。经验是需要慢慢积累的），不过更多的是好奇别人是如何恢复数据的，既然别人能够恢复，那么自己下一次遇到这种情况也要能搞定。下面就来复盘一下别人是如何恢复数据的（其实只要稍稍做点功课，发现这个其实挺简单的）

首先，我们来了解一下MySQL 表空间数据文件idbdat1文件相关概念和知识点:

InnoDB采用按表空间（tablespace)的方式进行存储数据, 默认配置情况下会有一个初始大小为10MB，名字为ibdata1的文件，该文件就是默认的表空间文件（tablespce file），用户可以通过参数innodb_data_file_path对其进行设置，可以有多个数据文件，如果没有设置innodb_file_per_table的话，那些Innodb存储类型的表的数据都放在这个共享表空间中，而系统变量innodb_file_per_table=1的话，那么InnoDB存储引擎类型的表就会产生一个独立表空间，独立表空间的命名规则为：表名.idb. 这些单独的表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息，其它信息还是存放在共享表空间中。

其实当时主要是对这个概念有点模糊了，以为这个系统变量innodb_file_per_table默认是关闭的，数据都会存储在共享表空间中，那么这些文件删除了，数据就无法恢复。所以武断的下结论，其实从MySQL 5.6.6开始，系统变量innodb_file_per_table默认是启用的。只要再多了解一点或者说更深入了解一点的话，情况就会立马就会反转。也就是说如果开启了独立表空间，可从ibd文件中恢复数据。即使共享表空间的数据文件idbdata1丢失也不要紧，反之，如果未开启独立表空间时，idbdat1被删除了，数据也会被删除，只能从备份中恢复，真的没有其他办法。

那么我们接下来看看，如何从idb文件中恢复数据吧，我们需要用到mysqlfrm工具, 需要安装MySQL Utilities，下面是安装MySQL Utilities 1.5.5

# tar -xvf mysql-utilities-1.5.5.tar.gz

# cd mysql-utilities-1.5.5

# python ./setup.py build

# python ./setup.py install

提取frm文件的表结构信息

mysqlfrm 是一个恢复性质的工具，用来读取.frm文件并从该文件中找到表定义数据生成CREATE语句。此处不对mysqlfrm工具做过多介绍，我们使用msqlfrm来生成该数据库的表的CREATE语句

[root@DB-Server ~]# service mysql stopShutting down MySQL.... SUCCESS! [root@DB-Server ~]# /usr/local/bin/mysqlfrm --basedir=/usr --port=3306 --user=root /data/mysql/test/ > test_frm.sql[root@DB-Server ~]# 

检查导出的SQL语句，发现都是“ERROR: The server version for this file is too low. It requires a server version 5.6.29 or higher but your server is version 5.6.20. Try using a newer server or use diagnostic mode”这类错误：

[root@DB-Server ~]# more test_frm.sql # Spawning server with --user=root.# Starting the spawned server on port 3306 ... done.# Reading .frm files## Reading the think_cache.frm file.ERROR: The server version for this file is too low. It requires a server version 5.6.29 or higher but your server is version 5.6.20. Try using a newer server or use diagnostic mode.## Reading the think_session.frm file.ERROR: The server version for this file is too low. It requires a server version 5.6.29 or higher but your server is version 5.6.20. Try using a newer server or use diagnostic mode.## Reading the wx_activity_config.frm file.ERROR: The server version for this file is too low. It requires a server version 5.6.29 or higher but your server is version 5.6.20. Try using a newer server or use diagnostic mode.#........................................................................................

从中可以看到这个数据库之前的版本为MySQL为5.6.29，而我这里的MySQL版本比这个低（MySQL 5.6.20）。所以必须找一个跟这个版本相同或高的MySQL数据库操作才行。于是在另外一台测试服务器安装了MySQL

[root@gettestlnx02 ~]# service mysqld stop Stopping mysqld:  [  OK  ] [root@gettestlnx02 tmp]# mv test  /data/mysqldata/mysql/test [root@gettestlnx02 tmp]# cd /data/mysqldata/mysql/

/usr/bin/mysqlfrm --basedir=/usr --port=3306 --user=root /data/mysqldata/mysql/test/ > test_frm.sql

如何要查看输出信息，可以使用参数-vvv

/usr/bin/mysqlfrm --basedir=/usr --port=3306 --user=root -vvv /data/mysqldata/mysql/test/ > test_frm.sql

生成的SQL脚本没有以分号结尾，本来想用sed命令给那些CREATE TABLE脚本加上分号结尾，但是发现其中大量CREATE TABLE的脚本结尾没有规律，都是以COMMNET='xxxxx'结尾，只能手工添加分号（如下所示）

导入frm文件的表结构信息

mysql> use test;Database changedmysql> source test_frm.sqlQuery OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec).................................

然后我们检查这个数据库的各类文件frm、ibd、MYI、MYD文件数量，后续做对比验证用途。

[root@gettestlnx02 test]# ls -lrt *.frm | wc -l

[root@gettestlnx02 test]# ls -lrt *.ibd | wc -l

[root@gettestlnx02 test]# ls -lrt *.MYI | wc -l

[root@gettestlnx02 test]# ls -lrt *.MYD | wc -l

[root@gettestlnx02 test]#

删除新建表的独立表空间文件

使用下面脚本生成删除新建表的独立空间的脚本：

select concat(concat('alter table ',table_name), ' discard tablespace;')                                                  from information_schema.tables                               where table_schema='test' and engine ='InnoDB';

使用脚本就可以生成下面SQL，执行该命令后，对应数据库下面的ibd文件全部被删除。

alter table think_cache discard tablespace;

alter table think_session discard tablespace;

alter table wx_activity_config discard tablespace;

........................................

复制待恢复的表空间文件

将待恢复的ibd文件拷贝到对应数据库目录下面，并设置好权限属性

# cd /tmp/database# ls -lrt *.ibd | wc -l84# cp *.ibd /var/lib/mysql/test # chown  mysql:mysql *.ibd# chmod 660 *.ibd

导入表空间

mysql> alter table think_cache import tablespace;Query OK, 0 rows affected, 1 warning (0.21 sec) mysql> alter table think_session import tablespace;Query OK, 0 rows affected, 1 warning (0.18 sec) mysql> select count(*) from think_cache;+----------+| count(*) |+----------+|    10919 |+----------+1 row in set (0.01 sec) mysql> select * from think_cache limit 5;+---------------------------------------+------------+----------------------------------------+---------+| cachekey                              | expire     | data                                   | datacrc |+---------------------------------------+------------+----------------------------------------+---------+| 00OLH9JvIwX42R3mPygXYN3gWZp2rH_rebate | 1533050257 | s:30:"00OLH9JvIwX42R3mPygXYN3gWZp2rH"; |         || 00SCWX7cIgqnnzHRArAXoascr1gnlA_rebate | 1516937278 | s:30:"00SCWX7cIgqnnzHRArAXoascr1gnlA"; |         || 00uVkAbOMPGQc2z02PPxVMblGY7oj7_rebate | 1528708564 | s:30:"00uVkAbOMPGQc2z02PPxVMblGY7oj7"; |         || 01dB7czgCph7hgm1qGM7qA7haChXop_rebate | 1525740805 | s:30:"01dB7czgCph7hgm1qGM7qA7haChXop"; |         || 023oMqQAAwg4WWxWgJSLNgQhYlgtVi_rebate | 1531560804 | s:30:"023oMqQAAwg4WWxWgJSLNgQhYlgtVi"; |         |+---------------------------------------+------------+----------------------------------------+---------+5 rows in set (0.00 sec) mysql> select count(*) from think_session;+----------+| count(*) |+----------+|     1347 |+----------+1 row in set (0.00 sec) mysql> select * from think_session limit 5;+----------------------------+----------------+--------------+| session_id                 | session_expire | session_data |+----------------------------+----------------+--------------+| 00onr4u3jabvi6vrts3bfeaqt4 |     1533358643 |              || 00rs65ljphuhhughujfnk2bci6 |     1533350110 |              || 01ld93n8ac31o4uorqrebtjir5 |     1533418040 |              || 01u5tv79pp8jjssh1r3s7oj6d4 |     1533351181 |              || 0261rcndf0jmq9dccou5l23mn4 |     1533346621 |              |+----------------------------+----------------+--------------+5 rows in set (0.00 sec)

导出数据库

导入数据库

如果顺利的话，一切就正常了，数据正常恢复，是否也不是什么难事，难就难在你不知道而已，如果你认证学习了一下这方面的知识点，整个事情其实并不复杂。有些细节操作问题可以参考官方文档：

https://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting-datadict.html

https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html

那么在这个恢复过程中是否会遇到一些麻烦或问题呢，答案是肯定的，下面简单介绍一些在恢复过程中可能遇到的问题

1：在实验测试过程，我一度使用版本为MySQL 5.7.21的数据库，在导入表空间是遇到下面错误：

                            
                            版权声明：本文来源CSDN，感谢博主原创文章，遵循 CC 4.0 by-sa 版权协议，转载请附上原文出处链接和本声明。

                            原文链接：https://blog.csdn.net/weixin_34302561/article/details/86010513

                            站方申明：本站部分内容来自社区用户分享，若涉及侵权，请联系站方删除。
                        
                                    发表于 2020-03-08 15:14:01
                                
阅读 ( 1042 )
分类：数据库

MySQL Innodb数据库误删ibdata1后MySQL数据库的恢复案例

你可能感兴趣的文章

精选的优质文章

0 条评论

官方社群

GO教程

推荐文章

猜你喜欢

随便看看