mysql 误删除 ibdata1,ib_logfile(Table doesn’t exist)

前情提要

简单说下事情发生原因吧，就是突然我访问我主站时发现了居然跳转到了 WP 的安装界面，WAT？

第一时间去查看数据库，发现所有表都在，表结构正常没什么问题但是网站却无法识别到数据库，当时不知道什么问题

后面直接去查看数据库根目录发现，坏了，ibdata1 和 ib_logfile 没了

不知道原因为什么会没有了，最近我在安装个人项目管理 Pear Project ，我也只是升级了下 npm 和 node 也没有删什么东西，不清楚文件消失的原因

首要问题是要先解决这个问题，原因之后再去找

恢复数据库思路

首先先找之前备份的数据库，然而虽然相隔1个月，但是还是比较算年代久远了，这期间发过文章以及装了 WIKI 系统写了很多文章，我可不想再重新写一遍，所以找之前备份的数据库这条路堵死了

然后只要 mysqld 进程没有结束过，服务器还在运行，这些文件即使删除了它们仍就存在系统中，只要 mysqld 进程没有结束过，我们就可以从 proc 找回这几个被删除的文件，通过对脏页的刷入磁盘操作来恢复

具体方法这边不涉及，搜索引擎搜下都有，我并不属于这种情况，因为我一向喜欢加大难度，对，我重启了，甚至是我 TM 重启的是服务器

具体原因不说了，所以现在这条路子便也堵死了，但是不急那会还是比较冷静的，因为我的表和表结构都在，我只是丢了个索引文件（比索引的功能多了去了），所以很快我就找到了方法，通过 frm 来进行恢复

ibdata1内部结构
ibd内部结构

进行恢复

保存数据文件

[root@ mysql]# cp -a mysql/* /usr/backup/  #数据暂时保存到/usr/backup目录下

恢复表结构

如果表结构保存的有，不需要此步骤，直接按照表结构重新创建表

此步骤使用官方工具 mysql-utilities 通过 frm 文件提取表结构

mysql-utilities 原理

默认以再生实例启动，读取frm文件，再生实例关闭，清理临时文件
另一个模式是诊断模式，需要指定 –diagnostic 选项。byte-by-byte读取.frm文件，该模式有更多的局限性，不能校验字符集
请参考官网

注意事项

某些引擎表在默认模式下不可读取的。如PARTITION, PERFORMANCE_SCHEMA，必需在诊断模式下可读
要在创建语句中改变存储引擎，可使用–new-storage-engine 选项。如果有指定该选项，同时必须指定–frmdir选项，该工具生成新的.frm文件，前缀为new_，并保存在–frmdir目录下
关掉所有信息除了CREATE 语句和警告或错误信息，使用–quiet选项
使用–show-stats 选项统计每个.frm文件信息
使用–user 选项指定再生的实例以哪个权限运行
如果再生的实例超过10秒启动，需调大–start-timeout 选项参数

mysql-utilities 安装

由于yum安装的是1.3的版本，1.6有bug，使用1.5.6版本，故采用源码包的形式安装，安装过程中如报错，请先安装驱动程序Connector/Python

[root@ ~]# cd /usr/src/
[root@ src]# wget https://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.5.6.tar.gz
[root@ src]# tar -zxf mysql-utilities-1.5.6.tar.gz 
[root@ src]# cd mysql-utilities-1.5.6
[root@ mysql-utilities-1.5.6]# 
[root@ mysql-utilities-1.5.6]# python ./setup.py build   #编译,过程省略
[root@ mysql-utilities-1.5.6]# python ./setup.py install #安装,过程省略
[root@ mysql-utilities-1.5.6]# ll /usr/bin/mysql* -t     #以下为生成的可执行文件
-rwxr-xr-x 1 root root  11966 7月  27 11:50 /usr/bin/mysqlauditadmin
-rwxr-xr-x 1 root root  12217 7月  27 11:50 /usr/bin/mysqlauditgrep
-rwxr-xr-x 1 root root  16680 7月  27 11:50 /usr/bin/mysqldbcompare
-rwxr-xr-x 1 root root  13918 7月  27 11:50 /usr/bin/mysqldbcopy
-rwxr-xr-x 1 root root  14815 7月  27 11:50 /usr/bin/mysqldbexport
-rwxr-xr-x 1 root root  13605 7月  27 11:50 /usr/bin/mysqldbimport
-rwxr-xr-x 1 root root  10722 7月  27 11:50 /usr/bin/mysqldiff
-rwxr-xr-x 1 root root   7385 7月  27 11:50 /usr/bin/mysqldiskusage
-rwxr-xr-x 1 root root  14197 7月  27 11:50 /usr/bin/mysqlfabric
-rwxr-xr-x 1 root root  15457 7月  27 11:50 /usr/bin/mysqlfailover
-rwxr-xr-x 1 root root  18222 7月  27 11:50 /usr/bin/mysqlfrm
-rwxr-xr-x 1 root root   6251 7月  27 11:50 /usr/bin/mysqlindexcheck
-rwxr-xr-x 1 root root   5356 7月  27 11:50 /usr/bin/mysqlmetagrep
-rwxr-xr-x 1 root root   5984 7月  27 11:50 /usr/bin/mysqlprocgrep
-rwxr-xr-x 1 root root   7694 7月  27 11:50 /usr/bin/mysqlreplicate
-rwxr-xr-x 1 root root  16669 7月  27 11:50 /usr/bin/mysqlrpladmin
-rwxr-xr-x 1 root root   6407 7月  27 11:50 /usr/bin/mysqlrplcheck
-rwxr-xr-x 1 root root  15542 7月  27 11:50 /usr/bin/mysqlrplms
-rwxr-xr-x 1 root root   6695 7月  27 11:50 /usr/bin/mysqlrplshow
-rwxr-xr-x 1 root root  11489 7月  27 11:50 /usr/bin/mysqlrplsync
-rwxr-xr-x 1 root root   8642 7月  27 11:50 /usr/bin/mysqlserverclone
-rwxr-xr-x 1 root root   5945 7月  27 11:50 /usr/bin/mysqlserverinfo
-rwxr-xr-x 1 root root   6923 7月  27 11:50 /usr/bin/mysqluc
-rwxr-xr-x 1 root root   8048 7月  27 11:50 /usr/bin/mysqluserclone

开始提取 frm 表结构

此用法读取所有frm文件，没有指定数据库，生成了以最后一个文件夹为 DB 名字的表结构

[root@ ]# mysqlfrm --basedir=/usr/local/mysql --port=3456 --user=mysql /hwdata/data/percona/test1/ > table_frm.sql
[root@]# mysqlfrm --help     #其他使用方法请参考帮助
[root@]# sed '/^#/d;/^$/d' table_frm.sql |head -5    #如下显示
CREATE TABLE `test1`.`employee1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `empname` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
[root@]# sed -i 's@CHARSET=gbk@CHARSET=gbk;@g' table_frm.sql #由于导出后没有以分号结尾，此命令处理添加上分号

导入frm表结构

确认数据文件已经保存至其他目录

[root@]# ll /usr/src/test1/      #确认下数据文件是否保存在此
总用量 1404
-rw-rw---- 1 mysql mysql     59 7月  27 17:03 db.opt
-rw-rw---- 1 mysql mysql   8592 7月  27 17:03 employee10.frm
-rw-rw---- 1 mysql mysql 131072 7月  27 17:03 employee10.ibd
-rw-rw---- 1 mysql mysql   8592 7月  27 17:03 employee1.frm
-rw-rw---- 1 mysql mysql 131072 7月  27 17:03 employee1.ibd
-rw-rw---- 1 mysql mysql   8592 7月  27 17:03 employee2.frm
-rw-rw---- 1 mysql mysql 131072 7月  27 17:03 employee2.ibd
-rw-rw---- 1 mysql mysql   8592 7月  27 17:03 employee3.frm
-rw-rw---- 1 mysql mysql 131072 7月  27 17:03 employee3.ibd
-rw-rw---- 1 mysql mysql   8592 7月  27 17:03 employee4.frm
-rw-rw---- 1 mysql mysql 131072 7月  27 17:03 employee4.ibd
-rw-rw---- 1 mysql mysql   8592 7月  27 17:03 employee5.frm
-rw-rw---- 1 mysql mysql 131072 7月  27 17:03 employee5.ibd
-rw-rw---- 1 mysql mysql   8592 7月  27 17:03 employee6.frm
-rw-rw---- 1 mysql mysql 131072 7月  27 17:03 employee6.ibd
-rw-rw---- 1 mysql mysql   8592 7月  27 17:03 employee7.frm
-rw-rw---- 1 mysql mysql 131072 7月  27 17:03 employee7.ibd
-rw-rw---- 1 mysql mysql   8592 7月  27 17:03 employee8.frm
-rw-rw---- 1 mysql mysql 131072 7月  27 17:03 employee8.ibd
-rw-rw---- 1 mysql mysql   8592 7月  27 17:03 employee9.frm
-rw-rw---- 1 mysql mysql 131072 7月  27 17:03 employee9.ibd
[root@]# ll
总用量 1351956
-rw-rw---- 1 mysql mysql         56 7月   4 23:50 auto.cnf
-rw-r--r-- 1 root  root           0 7月  27 17:08 exit
-rw-rw---- 1 mysql mysql       2563 7月  27 17:08 ib_buffer_pool
-rw-rw---- 1 mysql mysql 1073741824 7月  27 17:08 ibdata1
-rw-rw---- 1 mysql mysql  134217728 7月  27 17:08 ib_logfile0
-rw-rw---- 1 mysql mysql  134217728 7月  27 17:08 ib_logfile1
drwx------ 2 mysql mysql       4096 6月  20 13:43 mysql
-rw-rw---- 1 mysql mysql    3325836 7月  27 17:08 mysql-bin.000001
-rw-rw---- 1 mysql mysql        191 7月  27 17:08 mysql-bin.000002
-rw-rw---- 1 mysql mysql         38 7月  27 17:08 mysql-bin.index
-rw-rw---- 1 mysql mysql    7003893 7月  28 10:44 mysql-error.log
-rw-rw---- 1 mysql mysql          6 7月  27 17:08 mysql.pid
-rw-rw---- 1 mysql mysql     387620 7月  28 10:37 mysql-slow.log
drwx------ 2 mysql mysql       4096 6月  20 13:43 performance_schema
-rw-r--r-- 1 root  root           0 7月  27 17:08 show
-rw-r--r-- 1 root  root        3064 7月  28 10:42 table_frm.sql
drwx------ 2 mysql mysql       4096 7月  28 10:27 test1
-rw-rw---- 1 mysql mysql   10485760 7月  27 17:08 undo001
-rw-rw---- 1 mysql mysql   10485760 7月  27 17:08 undo002
  w-rw---- 1 mysql mysql   10485760 7月  27 17:08 undo003
[root@]# rm -rf test1/*
[root@]# /etc/init.d/mysqld restart
Shutting down MySQL (Percona Server)..                     [确定]
Starting MySQL (Percona Server)..                          [确定]
[root@]# mysql -uroot -p123456 test1
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 4
Server version: 5.6.36-82.0-log Source distribution

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, 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 [test1] 10:47:30 > source /hwdata/data/percona/table_frm.sql;
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

MySQL [test1] 10:47:31 > show tables;           #新表已创建
+-----------------+
| Tables_in_test1 |
+-----------------+
| employee1       |
| employee10      |
| employee2       |
| employee3       |
| employee4       |
| employee5       |
| employee6       |
| employee7       |
| employee8       |
| employee9       |
+-----------------+
10 rows in set (0.00 sec)

MySQL [test1] 10:49:54 > select count(*) from employee1;    #目录无数据
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

MySQL [test1] 10:50:01 >

删除新创建表的ibd文件

由于表比较多，通过脚本的形式去执行批量删除操作，这边暂不进行演示

导入原数据ibd文件

mysqldump导出

[root@ ~]# mysqldump -uroot -p123456 test1 --set-gtid-purged=OFF --opt -q --master-data=2 --single-transaction -R --events --triggers > test1_20170728.sql
Warning: Using a password on the command line interface can be insecure.
[root@ ~]# ll test1_20170728.sql 
-rw-r--r-- 1 root root 206957 7月  28 15:16 test1_20170728.sql
[root@ ~]#

重启导入

可把 test1 库删除后，重启服务，重新创建 test1 库，把 mysqldump 出的文件再次导入

还有一种笨办法

有工具可以提取表结构，直接从 frm 提取出来后手动新建然后再用 sql 语句恢复

总结

像实验中案例，误删除ibdata1数据的经常会出现
但是在最新的5.6.36版本中，不管是当前实验案例，还是直接从其他实例拷贝frm和ibd文件，在当前实例中重新创建表，再discard掉ibd文件，最后再import ibd文件，竟然没有报表空间ID不一致的错误。以前在5.5版本中，确认是会报表空间ID不一致情况，不确定是不是5.6版本改进了这方面的功能
使用可传输表空间，可以解决在删除 ibdata1 和 ib_logfile 的情况下恢复MySQL数据库，当然，本文测试的前提是数据库正常关闭下删除的 ibdata1 和 ib_logfile
使用可传输表空间，建议新建表的表结构和原来的表结构完全一致，同时，在导入表空间前，只需COPY回原来的数据文件，即 ibd
删除 ibdata1 和 ib_logfile 的情况下，所有业务数据库的所有表都要经过上述方法来进行恢复，通过恢复所有数据库中的所有表之后，备份数据库，然后重新初始化数据库，然后在把备份导入数据库是比较好的解决方法

12点发现问题，耗时3小时解决，因为第一次遇到当时也确实有一点心理波动，最近也刚好在服务器上玩 node，Linux 操作水平大幅提升

花有重开日，人无再少年

TDEH

mysql 误删除 ibdata1,ib_logfile(Table doesn’t exist)

前情提要

恢复数据库思路

进行恢复

保存数据文件

恢复表结构

mysql-utilities 原理

注意事项

mysql-utilities 安装

开始提取 frm 表结构

导入frm表结构

删除新创建表的ibd文件

导入原数据ibd文件

mysqldump导出

重启导入

还有一种笨办法

总结

郑渊洁:做人没有底线，你的人生就会前功尽弃

火绒似乎并不太适合开发人员使用

TDEH

Comments | NOTHING

TDEH