mysql从5.6.17开始自动设置innodb_file_per_table为on,每个表设置单独表空间,数据不是集中存放在ibdata1里。下面测试下无备份后drop表后的恢复。
前奏生成数据字典https://www.cnblogs.com/omsql/p/9253234.html
删除表前的准备
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 create table tb \G
*************************** 1. row ***************************
Table: tb
Create Table: CREATE TABLE `tb` (
`id` int(11) NOT NULL,
`name` varchar(16) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec) [root@redis01 tmp]# cd /data/mysql/ht/
[root@redis01 ht]# ls
db.opt person.frm person.ibd shirt.frm shirt.ibd tb.frm tb.ibd mysql> select * from tb;
+----+-------+
| id | name |
+----+-------+
| 1 | zhou |
| 2 | 430 |
| 3 | YYF |
| 4 | ChuaN |
| 5 | Faith |
| 6 | zhou |
| 7 | 430 |
| 8 | YYF |
| 9 | ChuaN |
| 10 | Faith |
| 11 | zhou |
| 12 | 430 |
| 13 | YYF |
| 14 | ChuaN |
| 15 | Faith |
+----+-------+
15 rows in set (0.00 sec) mysql> checksum table tb;
+-------+------------+
| Table | Checksum |
+-------+------------+
| ht.tb | 1499182360 |
+-------+------------+
1 row in set (0.00 sec) mysql> drop table tb;
Query OK, 0 rows affected (0.11 sec)
删除表后相对表的表空间文件也删除
[root@redis01 ht]# ls
db.opt person.frm person.ibd shirt.frm shirt.ibd tb.frm tb.ibd
[root@redis01 ht]# ls
db.opt person.frm person.ibd shirt.frm shirt.ibd
失误操作后建议关闭mysql,避免空间被占用
mysqladmin -u root -p shutdown [root@redis01 data]# df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/vg_redis01-lv_root
20830728 10397672 9459576 53% /
tmpfs 2022820 72 2022748 1% /dev/shm
/dev/sda1 487652 85768 372188 19% /boot
/dev/mapper/vg_mysql-lv_mysql01
303788 114540 189248 38% /data 从磁盘里查找,分别得到每个页文件
stream_parser
stream_parser 是分析 ibdata 文件(或者挂载的磁盘),得到每一个数据页的
./stream_parser -f /dev/mapper/vg_mysql-lv_mysql01 -t 303788k
查看页文件里的具体数据
c_parser 其实是按照 innodb 存储数据的格式来分析哪些是我们需要的数据本身,所以页上的数据可以分为两类:1. 用户数据 2. 元数据。而元数据的功能其实并不相同,有些损坏无伤大雅,有些损坏却可能导致整个页无法恢复。
./c_parser -6f pages-vg_mysql-lv_mysql01/FIL_PAGE_INDEX/0000000000000041.page -t tb.sql | head -5 生成建表语句
[root@redis01 undrop-for-innodb]# cat tb.sql
CREATE TABLE `tb` (
`id` int(11) NOT NULL,
`name` varchar(16) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 查看恢复出来的数据
[root@redis01 undrop-for-innodb]# ./c_parser -6f pages-vg_mysql-lv_mysql01/FIL_PAGE_INDEX/0000000000000041.page -t tb.sql | head -6
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (5 5)
000000000507 A70000011B0110 tb 1 "zhou"
000000000507 A70000011B011C tb 2 "430"
000000000507 A70000011B0128 tb 3 "YYF"
000000000507 A70000011B0134 tb 4 "ChuaN"
000000000507 A70000011B0140 tb 5 "Faith" 生成脚本
./c_parser -6f pages-vg_mysql-lv_mysql01/FIL_PAGE_INDEX/0000000000000041.page -t tb.sql > dumps/default/tb 2> dumps/default/tb_TABLES.sql 先建表在导入恢复出来的数据
mysql -u root -p ht < tb.sql
mysql -u root -p ht < dumps/default/tb_TABLES.sql mysql> select * from ht.tb;
Empty set (0.00 sec) mysql> select * from ht.tb;
+----+-------+
| id | name |
+----+-------+
| 1 | zhou |
| 2 | 430 |
| 3 | YYF |
| 4 | ChuaN |
| 5 | Faith |
| 6 | zhou |
| 7 | 430 |
| 8 | YYF |
| 9 | ChuaN |
| 10 | Faith |
| 11 | zhou |
| 12 | 430 |
| 13 | YYF |
| 14 | ChuaN |
| 15 | Faith |
+----+-------+
15 rows in set (0.00 sec)