无备份mysql删除表后恢复
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)
查看表的行模式
mysql> show table status like '%tb%' \G
*************************** 1. row ***************************
Name: tb
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 6
Avg_row_length: 2730
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 36700160
Auto_increment: NULL
Create_time: 2022-08-05 13:34:06
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
