误删除ibdata1(Table doesn,t exist)恢复案例

发表于 | 分类于
字数统计: | 阅读时长 ≈

前言

MySQL从5.5版本开始，InnoDB存储引擎已经成为默认存储引擎。从物理文件的结构来看，InnoDB包含ibdata1,ib_logfilexx,数据文件等其他日志文件;其中参数innodb_file_per_table用于设置是否开启独立表空间，当开启独立表空间时数据和索引会存在于ibd文件中，当未开启独立表空间时，此时使用的是共享表空间，数据和索引会存储在ibdata1文件中。如果在清理空间时不小心把ibdata1文件删除了，未开启独立表空间时，数据也会被删除，只能从备份中恢复，没有其他办法。如果开启了独立表空间，可从ibd文件中恢复数据。本文将介绍在开启独立表空间一些恢复案例，前提是无备份。

准备

MySQL版本：percona server 5.6.36

案例步骤

  • 启动MySQL实例
  • 插入数据，中间穿插创建删除表，操持ibdata1内部tablespace的ID真实性
  • 模拟删除ibdata1和ib_logfile文件
  • 保存数据文件和表结构文件到其他目录
  • 提取该实例的所有表结构
  • 通过提取的表结构，创建实例下的所有表
  • 通过ALTER TABLE dbName.tableName DISCARD TABLESPACE删除新创建表的ibd文件
  • 拷贝原数据文件到对应的ibd文件目录
  • 修改ibd文件权限
  • 通过ALTER TABLE dbName.tableName IMPORT TABLESPACE导入拷贝的ibd文件
  • 使用mysqldump导出数据
  • 重建实例，导入数据

原理分析

  • 恢复的步骤中，其中修改InnoDB表的tablespace ID最为重要。默认情况下，当开启独立表空间时，即使表数据文件和索引存在于数据目录中，但是每个表都有一个表的空间ID做标识，这部分标识同样存在于ibdata1中，是一个关联的关系。当两者不一致时，数据字典里找不到表，InnoDB引擎就无法加载数据目录下的ibd文件。恢复的目的使两者保持一致，正常加载数据。
  • ibdata1内部结构
    image
  • ibd内部结构
    image
  • 图片来源于Jeremy Cole，原理写的很清楚，请参考。
  • ibdata1中的insert buffer/double buffer等可以不用关心，本次恢复用不到。

案例

启动MySQL实例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[root@iZuf6c08fdv8duubho2b0rZ ~]# ll /hwdata/data/percona
总用量 1341500
-rw-rw---- 1 mysql mysql         56 7月   4 23:50 auto.cnf
-rw-rw---- 1 mysql mysql 1073741824 7月  27 15:44 ibdata1
-rw-rw---- 1 mysql mysql  134217728 7月  27 15:44 ib_logfile0
-rw-rw---- 1 mysql mysql  134217728 7月  27 15:44 ib_logfile1
drwx------ 2 mysql mysql       4096 6月  20 13:43 mysql
-rw-rw---- 1 mysql mysql        151 7月  27 15:44 mysql-bin.000001
-rw-rw---- 1 mysql mysql         19 7月  27 15:44 mysql-bin.index
-rw-rw---- 1 mysql mysql      26901 7月  27 15:44 mysql-error.log
-rw-rw---- 1 mysql mysql          5 7月  27 15:44 mysql.pid
-rw-rw---- 1 mysql mysql       1037 7月  27 15:44 mysql-slow.log
drwx------ 2 mysql mysql       4096 6月  20 13:43 performance_schema
-rw-rw---- 1 mysql mysql   10485760 7月  27 15:44 undo001
-rw-rw---- 1 mysql mysql   10485760 7月  27 15:44 undo002
-rw-rw---- 1 mysql mysql   10485760 7月  27 15:44 undo003
[root@iZuf6c08fdv8duubho2b0rZ ~]# /etc/init.d/mysqld stop
Shutting down MySQL (Percona Server)..                     [确定]
[root@iZuf6c08fdv8duubho2b0rZ ~]# rm -rf /hwdata/data/percona/ib* /hwdata/data/percona/undo00*
[root@iZuf6c08fdv8duubho2b0rZ ~]# ll /hwdata/data/percona                                     
总用量 60
-rw-rw---- 1 mysql mysql    56 7月   4 23:50 auto.cnf
drwx------ 2 mysql mysql  4096 6月  20 13:43 mysql
-rw-rw---- 1 mysql mysql   174 7月  27 15:45 mysql-bin.000001
-rw-rw---- 1 mysql mysql    19 7月  27 15:44 mysql-bin.index
-rw-rw---- 1 mysql mysql 31528 7月  27 15:45 mysql-error.log
-rw-rw---- 1 mysql mysql  1037 7月  27 15:44 mysql-slow.log
drwx------ 2 mysql mysql  4096 6月  20 13:43 performance_schema
[root@iZuf6c08fdv8duubho2b0rZ ~]# /etc/init.d/mysqld start
Starting MySQL (Percona Server)............                [确定]
[root@iZuf6c08fdv8duubho2b0rZ ~]#

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
[root@iZuf6c08fdv8duubho2b0rZ ~]# cat auto_insert_data.sh   #简单的插入脚本
#!/bin/bash
# Name: auto_insert_data
# Author: jiessie
# Describe: auto insert data
# Date: 20170727

# basic variables
MySQL_CMD=/usr/local/mysql/bin/mysql
MySQL_User=root
MySQL_Pass=123456
MySQL_Host=localhost
MySQL_Db=test1

# create database
$MySQL_CMD -h$MySQL_Host -u$MySQL_User -p$MySQL_Pass -e "create database if not exists $MySQL_Db;"

# insert data
for ((i=1;i<=10;i++));
do
        $MySQL_CMD -h$MySQL_Host -u$MySQL_User -p$MySQL_Pass $MySQL_Db -e "create table employee$i(id int(10) unsigned NOT NULL AUTO_INCREMENT,empname varchar(64) NOT NULL DEFAULT '',PRIMARY KEY (id));"
        for((j=1;j<=1000;j++));
        do
                $MySQL_CMD -h$MySQL_Host -u$MySQL_User -p$MySQL_Pass $MySQL_Db -e "insert into employee$i values(null,'employee$j');"
        done
        # create other table and delete
        $MySQL_CMD -h$MySQL_Host -u$MySQL_User -p$MySQL_Pass $MySQL_Db -e "create table tmp$i(id int);drop table tmp$i;"
done


[root@iZuf6c08fdv8duubho2b0rZ ~]# nohup ./auto_insert_data.sh &     #执行插入数据
[1] 4562
[root@iZuf6c08fdv8duubho2b0rZ ~]# nohup: 忽略输入并把输出追加到"nohup.out"

[root@iZuf6c08fdv8duubho2b0rZ ~]#

查看数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
[root@iZuf6c08fdv8duubho2b0rZ ~]# 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 10202
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] 17:04:40 > show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| employee1       |
| employee10      |
| employee2       |
| employee3       |
| employee4       |
| employee5       |
| employee6       |
| employee7       |
| employee8       |
| employee9       |
+-----------------+
10 rows in set (0.00 sec)

MySQL [test1] 17:04:42 > select count(*) from employee1;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

MySQL [test1] 17:04:44 > exit
Bye
[root@iZuf6c08fdv8duubho2b0rZ ~]#

删除ibdata1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
[root@iZuf6c08fdv8duubho2b0rZ ~]# cd /hwdata/data/percona
[root@iZuf6c08fdv8duubho2b0rZ percona]# ll
总用量 1345228
-rw-rw---- 1 mysql mysql         56 7月   4 23:50 auto.cnf
-rw-rw---- 1 mysql mysql      14522 7月  27 17:02 ib_buffer_pool
-rw-rw---- 1 mysql mysql 1073741824 7月  27 17:03 ibdata1
-rw-rw---- 1 mysql mysql  134217728 7月  27 17:03 ib_logfile0
-rw-rw---- 1 mysql mysql  134217728 7月  27 17:02 ib_logfile1
drwx------ 2 mysql mysql       4096 6月  20 13:43 mysql
-rw-rw---- 1 mysql mysql    3325813 7月  27 17:03 mysql-bin.000001
-rw-rw---- 1 mysql mysql         19 7月  27 17:02 mysql-bin.index
-rw-rw---- 1 mysql mysql     496222 7月  27 17:05 mysql-error.log
-rw-rw---- 1 mysql mysql          5 7月  27 17:02 mysql.pid
-rw-rw---- 1 mysql mysql       2383 7月  27 17:04 mysql-slow.log
drwx------ 2 mysql mysql       4096 6月  20 13:43 performance_schema
drwx------ 2 mysql mysql       4096 7月  27 17:03 test1
-rw-rw---- 1 mysql mysql   10485760 7月  27 17:03 undo001
-rw-rw---- 1 mysql mysql   10485760 7月  27 17:03 undo002
-rw-rw---- 1 mysql mysql   10485760 7月  27 17:03 undo003
[root@iZuf6c08fdv8duubho2b0rZ percona]# rm -rf ib_buffer_pool ib_logfile* undo00* ibdata1   #删除ibdata1相关信息
[root@iZuf6c08fdv8duubho2b0rZ percona]# /etc/init.d/mysqld restart                          #重启服务
Shutting down MySQL (Percona Server)..                     [确定]
Starting MySQL (Percona Server)............                [确定]
[root@iZuf6c08fdv8duubho2b0rZ percona]# 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 17
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] 17:08:29 > select count(*) from employee1;                                    #提示表不存在
ERROR 1146 (42S02): Table 'test1.employee1' doesn't exist
MySQL [test1] 17:08:32 > exit
Bye
[root@iZuf6c08fdv8duubho2b0rZ percona]# ll test1/                                           #查看frm和ibd文件存在
总用量 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 e
yee9.ibd
[root@iZuf6c08fdv8duubho2b0rZ percona]#

保存数据文件

1
2
3
4
[root@iZuf6c08fdv8duubho2b0rZ percona]# cp -a test1/ /usr/src/  #数据暂时保存到/usr/src目录下
[root@iZuf6c08fdv8duubho2b0rZ percona]# ll /usr/src/test1 |wc -l
22
[root@iZuf6c08fdv8duubho2b0rZ percona]#

恢复表结构

如果表结构保存的有，不需要此步骤，直接按照表结构重新创建表
此步骤使用官方工具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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
[root@iZuf6c08fdv8duubho2b0rZ ~]# cd /usr/src/
[root@iZuf6c08fdv8duubho2b0rZ src]# wget https://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.5.6.tar.gz
[root@iZuf6c08fdv8duubho2b0rZ src]# tar -zxf mysql-utilities-1.5.6.tar.gz 
[root@iZuf6c08fdv8duubho2b0rZ src]# cd mysql-utilities-1.5.6
[root@iZuf6c08fdv8duubho2b0rZ mysql-utilities-1.5.6]# 
[root@iZuf6c08fdv8duubho2b0rZ mysql-utilities-1.5.6]# python ./setup.py build   #编译,过程省略
[root@iZuf6c08fdv8duubho2b0rZ mysql-utilities-1.5.6]# python ./setup.py install #安装,过程省略
[root@iZuf6c08fdv8duubho2b0rZ 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

以上为生成的可执行文件中，我们只需要用到mysqlfrm，用于表结构恢复

开始提取frm表结构

此用法启动了3333的实例，读取hwdata/data/percona/test1/下的所有frm文件，没有指定数据库，生成了以最后一个文件夹为DB名字的表结构

1
2
3
4
5
6
7
8
9
[root@iZuf6c08fdv8duubho2b0rZ percona]# mysqlfrm --basedir=/usr/local/mysql --port=3333 --user=mysql /hwdata/data/percona/test1/ > table_frm.sql
[root@iZuf6c08fdv8duubho2b0rZ percona]# mysqlfrm --help     #其他使用方法请参考帮助
[root@iZuf6c08fdv8duubho2b0rZ percona]# 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@iZuf6c08fdv8duubho2b0rZ percona]# sed -i 's@CHARSET=gbk@CHARSET=gbk;@g' table_frm.sql #由于导出后没有以分号结尾，此命令处理添加上分号

导入frm表结构

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
[root@iZuf6c08fdv8duubho2b0rZ percona]# 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@iZuf6c08fdv8duubho2b0rZ percona]# 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@iZuf6c08fdv8duubho2b0rZ percona]# rm -rf test1/*
[root@iZuf6c08fdv8duubho2b0rZ percona]# /etc/init.d/mysqld restart
Shutting down MySQL (Percona Server)..                     [确定]
Starting MySQL (Percona Server)..                          [确定]
[root@iZuf6c08fdv8duubho2b0rZ percona]# 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文件

由于表比较多，通过脚本的形式去执行批量删除操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
[root@iZuf6c08fdv8duubho2b0rZ ~]# cat auto_delete_tablespace.sh     #简单的批量删除表空间脚本
#!/bin/bash
# Name: auto_delete_tablespace
# Author: jiessie
# Describe: auto delete create table tablespace
# Date: 20170727

# basic variables
MySQL_CMD=/usr/local/mysql/bin/mysql
MySQL_User=root
MySQL_Pass=123456
MySQL_Host=localhost
MySQL_Db=test1
MySQL_Table_list=/tmp/table_list.sql

# generate table list and exec delete tablespace sql
$MySQL_CMD -h$MySQL_Host -u$MySQL_User -p$MySQL_Pass $MySQL_Db -e "select concat('alter table $MySQL_Db.',table_name,' discard tablespace;') from information_schema.tables where table_schema='$MySQL_Db'" > $MySQL_Table_list
sed -i '1d' $MySQL_Table_list
[root@iZuf6c08fdv8duubho2b0rZ ~]# ./auto_delete_tablespace.sh       #执行获取删除表空间脚本
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@iZuf6c08fdv8duubho2b0rZ ~]# cat /tmp/table_list.sql           #查看生成的删除表空间的SQL语句
alter table test1.employee1 discard tablespace;
alter table test1.employee10 discard tablespace;
alter table test1.employee2 discard tablespace;
alter table test1.employee3 discard tablespace;
alter table test1.employee4 discard tablespace;
alter table test1.employee5 discard tablespace;
alter table test1.employee6 discard tablespace;
alter table test1.employee7 discard tablespace;
alter table test1.employee8 discard tablespace;
alter table test1.employee9 discard tablespace;
[root@iZuf6c08fdv8duubho2b0rZ ~]# ll /hwdata/data/percona/test1/*.ibd   #查询新创建表后生成的ibd文件
-rw-rw---- 1 mysql mysql 98304 7月  28 10:47 /hwdata/data/percona/test1/employee10.ibd
-rw-rw---- 1 mysql mysql 98304 7月  28 10:47 /hwdata/data/percona/test1/employee1.ibd
-rw-rw---- 1 mysql mysql 98304 7月  28 10:47 /hwdata/data/percona/test1/employee2.ibd
-rw-rw---- 1 mysql mysql 98304 7月  28 10:47 /hwdata/data/percona/test1/employee3.ibd
-rw-rw---- 1 mysql mysql 98304 7月  28 10:47 /hwdata/data/percona/test1/employee4.ibd
-rw-rw---- 1 mysql mysql 98304 7月  28 10:47 /hwdata/data/percona/test1/employee5.ibd
-rw-rw---- 1 mysql mysql 98304 7月  28 10:47 /hwdata/data/percona/test1/employee6.ibd
-rw-rw---- 1 mysql mysql 98304 7月  28 10:47 /hwdata/data/percona/test1/employee7.ibd
-rw-rw---- 1 mysql mysql 98304 7月  28 10:47 /hwdata/data/percona/test1/employee8.ibd
-rw-rw---- 1 mysql mysql 98304 7月  28 10:47 /hwdata/data/percona/test1/employee9.ibd
[root@iZuf6c08fdv8duubho2b0rZ ~]# 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 1753
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] 11:10:40 > source /tmp/table_list.sql;                    #执行删除表空间
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MySQL [test1] 11:10:46 > exit
Bye
[root@iZuf6c08fdv8duubho2b0rZ ~]# ll /hwdata/data/percona/test1/*.ibd   #验证表空间已经删除
ls: 无法访问/hwdata/data/percona/test1/*.ibd: 没有那个文件或目录
[root@iZuf6c08fdv8duubho2b0rZ ~]#

导入原数据ibd文件

由于原表过多，通过脚本实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
[root@iZuf6c08fdv8duubho2b0rZ ~]# cat auto_recovery_data.sh         #恢复ibd文件脚本
#!/bin/bash
# Name: auto_delete_tablespace
# Author: jiessie
# Describe: auto delete create table tablespace
# Date: 20170727

# basic variables
MySQL_CMD=/usr/local/mysql/bin/mysql
MySQL_User=root
MySQL_Pass=123456
MySQL_Host=localhost
MySQL_Db=test1
MySQL_Ibd_filepath=/usr/src/test1/
MySQL_Table_list=/tmp/tables.sql
MySQL_Table_path=/hwdata/data/percona/test1

# import ibd file
/etc/init.d/mysqld stop
cd $MySQL_Ibd_filepath && cp -a * $MySQL_Table_path
chown -R mysql:mysql $MySQL_Table_path
/etc/init.d/mysqld start
$MySQL_CMD -h$MySQL_Host -u$MySQL_User -p$MySQL_Pass $MySQL_Db -e "select table_name from information_schema.tables where table_schema='$MySQL_Db'" > $MySQL_Table_list
sed -i '1d' $MySQL_Table_list
cat $MySQL_Table_list | while read line
do
        $MySQL_CMD -h$MySQL_Host -u$MySQL_User -p$MySQL_Pass $MySQL_Db -e "alter table $MySQL_Db.$line import tablespace;"
done
[root@iZuf6c08fdv8duubho2b0rZ ~]# ./auto_recovery_data.sh           #执行恢复脚本
Shutting down MySQL (Percona Server)...                    [确定]
Starting MySQL (Percona Server)..                          [确定]
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@iZuf6c08fdv8duubho2b0rZ ~]#

数据验证

通过count计数，比较数据是否恢复正常

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
[root@iZuf6c08fdv8duubho2b0rZ ~]# 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 704
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] 14:51:58 > select count(*) from employee1
    -> union all
    -> select count(*) from employee2
    -> union all
    -> select count(*) from employee3
    -> union all
    -> select count(*) from employee4
    -> union all
    -> select count(*) from employee5
    -> union all
    -> select count(*) from employee6
    -> union all
    -> select count(*) from employee7
    -> union all
    -> select count(*) from employee8
    -> union all
    -> select count(*) from employee9
    -> union all
    -> select count(*) from employee10;
+----------+
| count(*) |
+----------+
|     1000 |
|     1000 |
|     1000 |
|     1000 |
|     1000 |
|     1000 |
|     1000 |
|     1000 |
|     1000 |
|     1000 |
+----------+
10 rows in set (0.01 sec)

MySQL [test1] 14:52:04 >

mysqldump导出

1
2
3
4
5
[root@iZuf6c08fdv8duubho2b0rZ ~]# 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@iZuf6c08fdv8duubho2b0rZ ~]# ll test1_20170728.sql 
-rw-r--r-- 1 root root 206957 7月  28 15:16 test1_20170728.sql
[root@iZuf6c08fdv8duubho2b0rZ ~]#

重启导入

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

总结

  • 像实验中案例，误删除ibdata1数据的经常会出现。
  • 但是在最新的5.6.36版本中，不管是当前实验案例，还是直接从其他实例拷贝frm和ibd文件，在当前实例中重新创建表，再discard掉ibd文件，最后再import ibd文件，竟然没有报表空间ID不一致的错误。以前在5.5版本中，确认是会报表空间ID不一致情况，不确定是不是5.6版本改进了这方面的功能。
  • 5.5版本版本恢复案例请参考(https://dbarobin.com/2016/04/23/ibd-recovery/)，思路不错。
-------------本文结束感谢您的阅读-------------