场景:开发人员对核心业务表误删除用了drop table语句,情况为无备份、未开启BinLog特性。思路:drop删除之后只要数据不要刷新增加把现在的磁盘内存覆盖掉,可以通过undrop-for-innodb工具扫描磁盘读取数据进行恢复数据。 说明:innodb_file_per_table=on,默认端口只能使用3306,且无法指定端口。因为通过扫描磁盘页来找回数据的,为防止磁盘页由于大量的IO操作,导致磁盘被重用无法找回数据。建议在误操作之后刚磁盘挂载为只读模式或直接停止业务,避免数据页被刷新。解析磁盘,大量数据情况下会出现数据丢失情况,酌情使用该工具恢复。
一、安装undrop-for-innodb工具
shell> git clone git://github.com/twindb/undrop-for-innodb.git shell> yum install make gcc flex bison -y shell> cd undrop-for-innodb shell> make shell> gcc `$basedir/bin/mysql_config --cflags` `$basedir/bin/mysql_config --libs` -o sys_parser sys_parser.c 注:$basedir使用实际路径替换
二、模拟测试数据
2.1 创建五万条数据
mysql> create database test;
mysql> use test
Database changed
mysql> CREATE TABLE `test` (
-> `test_id` bigint NOT NULL AUTO_INCREMENT,
-> `first_name` varchar(45) NOT NULL,
-> `last_name` varchar(45) NOT NULL,
-> `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`test_id`),
-> KEY `idx_test_last_name` (`last_name`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> DELIMITER //
mysql> CREATE PROCEDURE mytest()
-> begin
-> declare i bigint;
-> set i=1;
-> while i<=10000 do
-> insert into test(first_name, last_name) values('zhang', 'san');
-> insert into test(first_name, last_name) values('zhan', 'san');
-> insert into test(first_name, last_name) values('zha', 'san');
-> insert into test(first_name, last_name) values('zh', 'san');
-> insert into test(first_name, last_name) values('z', 'san');
-> set i = i+1 ;
-> end while;
-> commit;
-> end
-> //
mysql> DELIMITER ;
mysql> call mytest();
mysql> select count(*) from test.test;
+----------+
| count(*) |
+----------+
| 50000 |
+----------+
2.2 删除数据
mysql> DROP TABLE test.test;
三、恢复表结构
3.1 解析idata1文件
由于DROP TABLE会直接从文件系统删除相关文件,需要从系统表中恢复表结构。
shell> cd /root/undrop-for-innodb/ shell> /root/undrop-for-innodb/stream_parser -f /mysql/product/data/ibdata1 Opening file: /mysql/product/data/ibdata1 File information: ID of device containing file: 2051 inode number: 17714323 protection: 100640 (regular file) number of hard links: 1 user ID of owner: 1001 group ID of owner: 1001 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 409600 time of last access: 1637309362 Fri Nov 19 00:09:22 2021 time of last modification: 1637310349 Fri Nov 19 00:25:49 2021 time of last status change: 1637310349 Fri Nov 19 00:25:49 2021 total size, in bytes: 209715200 (200.000 MiB) Size to process: 209715200 (200.000 MiB) All workers finished in 0 sec shell> ll /root/undrop-for-innodb/pages-ibdata1/FIL_PAGE_INDEX/ total 1296 -rw-r--r-- 1 root root 32768 Nov 19 00:06 0000000000000001.page -rw-r--r-- 1 root root 32768 Nov 19 00:06 0000000000000002.page -rw-r--r-- 1 root root 32768 Nov 19 00:06 0000000000000003.page -rw-r--r-- 1 root root 32768 Nov 19 00:06 0000000000000004.page -rw-r--r-- 1 root root 32768 Nov 19 00:06 0000000000000005.page -rw-r--r-- 1 root root 16384 Nov 19 00:06 0000000000000011.page -rw-r--r-- 1 root root 16384 Nov 19 00:06 0000000000000012.page -rw-r--r-- 1 root root 16384 Nov 19 00:06 0000000000000013.page -rw-r--r-- 1 root root 16384 Nov 19 00:06 0000000000000014.page -rw-r--r-- 1 root root 32768 Nov 19 00:06 0000000000000015.page -rw-r--r-- 1 root root 32768 Nov 19 00:06 0000000000000016.page -rw-r--r-- 1 root root 16384 Nov 19 00:06 0000000000000017.page -rw-r--r-- 1 root root 32768 Nov 19 00:06 0000000000000032.page -rw-r--r-- 1 root root 32768 Nov 19 00:06 0000000000000033.page -rw-r--r-- 1 root root 655360 Nov 19 00:06 0000000000000045.page -rw-r--r-- 1 root root 278528 Nov 19 00:06 0000000000000046.page -rw-r--r-- 1 root root 16384 Nov 19 00:06 18446744069414584320.page 注: 0000000000000001.page SYS_TABLES 0000000000000002.page SYS_COLUMNS 0000000000000003.page SYS_INDEXES 0000000000000004.page SYS_FIELDS
3.2 抽取数据字典
shell> mkdir -p dumps/default shell> ./c_parser -5Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql > dumps/default/SYS_TABLES 2> dumps/default/SYS_TABLES.sql shell> ./c_parser -5Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page -t dictionary/SYS_COLUMNS.sql > dumps/default/SYS_COLUMNS 2> dumps/default/SYS_COLUMNS.sql shell> ./c_parser -5Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql > dumps/default/SYS_INDEXES 2> dumps/default/SYS_INDEXES.sql shell> ./c_parser -5Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page -t dictionary/SYS_FIELDS.sql > dumps/default/SYS_FIELDS 2> dumps/default/SYS_FIELDS.sql
3.3 创建数据字典并加载到数据库
shell> mysql -uroot -pHzmc321# mysql> CREATE DATABASE recover; shell> mysql -uroot -pHzmc321# recover < /root/undrop-for-innodb/dictionary/SYS_TABLES.sql shell> mysql -uroot -pHzmc321# recover < /root/undrop-for-innodb/dumps/default/SYS_TABLES.sql shell> mysql -uroot -pHzmc321# recover < /root/undrop-for-innodb/dictionary/SYS_COLUMNS.sql shell> mysql -uroot -pHzmc321# recover < /root/undrop-for-innodb/dumps/default/SYS_COLUMNS.sql shell> mysql -uroot -pHzmc321# recover < /root/undrop-for-innodb/dictionary/SYS_INDEXES.sql shell> mysql -uroot -pHzmc321# recover < /root/undrop-for-innodb/dumps/default/SYS_INDEXES.sql shell> mysql -uroot -pHzmc321# recover < /root/undrop-for-innodb/dictionary/SYS_FIELDS.sql shell> mysql -uroot -pHzmc321# recover < /root/undrop-for-innodb/dumps/default/SYS_FIELDS.sql
3.4 获取建表语句并创建格式文件
shell> find / -name libmysqlclient.so.20 /mysql/app/lib/libmysqlclient.so.20 shell> ln -s /mysql/app/lib/libmysqlclient.so.20 /usr/local/lib/libmysqlclient.so.20 shell> vi /etc/ld.so.conf /usr/local/lib/ shell> /sbin/ldconfig -v shell> ./sys_parser -h 127.0.0.1 -u root -p Hzmc321# -d recover test/test CREATE TABLE `test`( `test_id` BIGINT NOT NULL, `first_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL, `last_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL, `last_update` TIMESTAMP NOT NULL, PRIMARY KEY (`test_id`) ) ENGINE=InnoDB; shell> vi /root/undrop-for-innodb/test.sql CREATE TABLE `test`( `test_id` BIGINT NOT NULL, `first_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL, `last_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL, `last_update` TIMESTAMP NOT NULL, PRIMARY KEY (`test_id`) ) ENGINE=InnoDB;
四、获取表的table_id和索引页
shell> ./c_parser -5Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql | grep test/test
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/root/undrop-for-innodb/dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_TABLES\t' (`NAME`, `ID`, `N_COLS`, `TYPE`, `MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`);
-- STATUS {"records_expected": 52, "records_dumped": 4, "records_lost": true} STATUS END
00000000D403 230000019102C8 SYS_TABLES "test/test" 42 4 33 0 80 "" 30
00000000D403 230000019102C8 SYS_TABLES "test/test" 42 4 33 0 80 "" 30
shell> ./c_parser -5Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql | grep 42
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/root/undrop-for-innodb/dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXES\t' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`);
-- STATUS {"records_expected": 64, "records_dumped": 20, "records_lost": true} STATUS END
0000000007A1 35000001890145 SYS_INDEXES 40 41 "PRIMARY" 1 3 24 4294967295
00000000D403 23000001910145 SYS_INDEXES 42 45 "PRIMARY" 1 3 30 4294967295
00000000D403 230000019101B7 SYS_INDEXES 42 46 "idx\_test\_last\_name" 1 0 30 4294967295
0000000007A1 35000001890145 SYS_INDEXES 40 41 "PRIMARY" 1 3 24 4294967295
00000000D403 23000001910145 SYS_INDEXES 42 45 "PRIMARY" 1 3 30 4294967295
00000000D403 230000019101B7 SYS_INDEXES 42 46 "idx\_test\_last\_name" 1 0 30 4294967295
shell> ./c_parser -5Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page -t dictionary/SYS_COLUMNS.sql | grep 42
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/root/undrop-for-innodb/dumps/default/SYS_COLUMNS' REPLACE INTO TABLE `SYS_COLUMNS` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_COLUMNS\t' (`TABLE_ID`, `POS`, `NAME`, `MTYPE`, `PRTYPE`, `LEN`, `PREC`);
-- STATUS {"records_expected": 274, "records_dumped": 34, "records_lost": true} STATUS END
00000000D403 230000019101F4 SYS_COLUMNS 42 0 "test\_id" 6 1288 8 0
00000000D403 2300000191025E SYS_COLUMNS 42 2 "last\_name" 12 2162959 135 0
00000000D403 23000001910229 SYS_COLUMNS 42 1 "first\_name" 12 2162959 135 0
00000000D403 23000001910293 SYS_COLUMNS 42 3 "last\_update" 3 525575 4 0
00000000D403 230000019101F4 SYS_COLUMNS 42 0 "test\_id" 6 1288 8 0
00000000D403 2300000191025E SYS_COLUMNS 42 2 "last\_name" 12 2162959 135 0
00000000D403 23000001910229 SYS_COLUMNS 42 1 "first\_name" 12 2162959 135 0
00000000D403 23000001910293 SYS_COLUMNS 42 3 "last\_update" 3 525575 4 0
shell> ./c_parser -5Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page -t dictionary/SYS_FIELDS.sql | grep '45\|46'
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/root/undrop-for-innodb/dumps/default/SYS_FIELDS' REPLACE INTO TABLE `SYS_FIELDS` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_FIELDS\t' (`INDEX_ID`, `POS`, `COL_NAME`);
-- STATUS {"records_expected": 92, "records_dumped": 4, "records_lost": true} STATUS END
-- Page id: 12, Format: REDUNDANT, Records list: Invalid, Expected records: (0 46)
00000000D403 23000001910110 SYS_FIELDS 45 0 "test\_id"
00000000D403 23000001910182 SYS_FIELDS 46 0 "last\_name"
-- Page id: 12, Format: REDUNDANT, Records list: Invalid, Expected records: (0 46)
00000000D403 23000001910110 SYS_FIELDS 45 0 "test\_id"
00000000D403 23000001910182 SYS_FIELDS 46 0 "last\_name"
五、恢复数据
shell> df -h Filesystem Size Used Avail Use% Mounted on devtmpfs 895M 0 895M 0% /dev tmpfs 910M 0 910M 0% /dev/shm tmpfs 910M 11M 900M 2% /run tmpfs 910M 0 910M 0% /sys/fs/cgroup /dev/sda3 28G 13G 16G 45% / /dev/sda1 297M 165M 133M 56% /boot tmpfs 182M 12K 182M 1% /run/user/42 tmpfs 182M 0 182M 0% /run/user/0
5.1 解析datadir所在磁盘
shell> /root/undrop-for-innodb/stream_parser -f /dev/sda3 -s 1G -t 28G Disk cache: 1073741824 (1.000 GiB) Opening file: /dev/sda3 File information: ID of device containing file: 5 inode number: 10808 protection: 60660 (block device) number of hard links: 1 user ID of owner: 0 group ID of owner: 6 device ID (if special file): 2051 blocksize for filesystem I/O: 4096 number of blocks allocated: 0 time of last access: 1637309122 Fri Nov 19 00:05:22 2021 time of last modification: 1637309118 Fri Nov 19 00:05:18 2021 time of last status change: 1637309118 Fri Nov 19 00:05:18 2021 total size, in bytes: 0 (0.000 exp(+0)) Size to process: 30064771072 (28.000 GiB) Worker(0): 7.14% done. 2021-11-19 00:17:30 ETA(in 00:03:02). Processing speed: 146.283 MiB/sec Worker(0): 10.71% done. 2021-11-19 00:15:20 ETA(in 00:00:50). Processing speed: 512.000 MiB/sec Worker(0): 14.29% done. 2021-11-19 00:15:20 ETA(in 00:00:48). Processing speed: 512.000 MiB/sec Worker(0): 17.86% done. 2021-11-19 00:15:20 ETA(in 00:00:46). Processing speed: 512.000 MiB/sec Worker(0): 21.43% done. 2021-11-19 00:15:20 ETA(in 00:00:44). Processing speed: 512.000 MiB/sec Worker(0): 25.00% done. 2021-11-19 00:16:26 ETA(in 00:01:45). Processing speed: 204.800 MiB/sec Worker(0): 28.57% done. 2021-11-19 00:15:23 ETA(in 00:00:40). Processing speed: 511.992 MiB/sec Worker(0): 32.14% done. 2021-11-19 00:15:43 ETA(in 00:00:57). Processing speed: 341.328 MiB/sec Worker(0): 35.71% done. 2021-11-19 00:15:24 ETA(in 00:00:36). Processing speed: 512.000 MiB/sec Worker(0): 39.29% done. 2021-11-19 00:15:24 ETA(in 00:00:34). Processing speed: 512.000 MiB/sec Worker(0): 42.86% done. 2021-11-19 00:15:07 ETA(in 00:00:16). Processing speed: 1024.000 MiB/sec Worker(0): 46.43% done. 2021-11-19 00:15:39 ETA(in 00:00:45). Processing speed: 341.333 MiB/sec Worker(0): 50.00% done. 2021-11-19 00:16:24 ETA(in 00:01:24). Processing speed: 170.667 MiB/sec Worker(0): 53.57% done. 2021-11-19 00:16:10 ETA(in 00:01:05). Processing speed: 204.797 MiB/sec Worker(0): 57.14% done. 2021-11-19 00:16:36 ETA(in 00:01:24). Processing speed: 146.283 MiB/sec Worker(0): 60.71% done. 2021-11-19 00:16:24 ETA(in 00:01:06). Processing speed: 170.664 MiB/sec Worker(0): 64.29% done. 2021-11-19 00:15:29 ETA(in 00:00:10). Processing speed: 1023.984 MiB/sec Worker(0): 67.86% done. 2021-11-19 00:15:39 ETA(in 00:00:18). Processing speed: 512.000 MiB/sec Worker(0): 71.43% done. 2021-11-19 00:15:48 ETA(in 00:00:24). Processing speed: 341.333 MiB/sec Worker(0): 75.00% done. 2021-11-19 00:16:12 ETA(in 00:00:42). Processing speed: 170.667 MiB/sec Worker(0): 78.57% done. 2021-11-19 00:16:12 ETA(in 00:00:36). Processing speed: 170.664 MiB/sec Worker(0): 82.14% done. 2021-11-19 00:15:48 ETA(in 00:00:10). Processing speed: 511.992 MiB/sec Worker(0): 85.71% done. 2021-11-19 00:15:48 ETA(in 00:00:08). Processing speed: 512.000 MiB/sec Worker(0): 89.29% done. 2021-11-19 00:15:48 ETA(in 00:00:06). Processing speed: 512.000 MiB/sec Worker(0): 92.86% done. 2021-11-19 00:15:48 ETA(in 00:00:04). Processing speed: 512.000 MiB/sec Worker(0): 96.43% done. 2021-11-19 00:15:46 ETA(in 00:00:01). Processing speed: 1024.000 MiB/sec All workers finished in 93 sec
5.2 解析查看聚集索引页
shell> /root/undrop-for-innodb/c_parser -6f pages-sda3/FIL_PAGE_INDEX/0000000000000045.page -t test.sql > /root/45.log
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/root/undrop-for-innodb/dumps/default/test' REPLACE INTO TABLE `test` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'test\t' (`test_id`, `first_name`, `last_name`, `last_update`);
-- STATUS {"records_expected": 74030, "records_dumped": 74030, "records_lost": false} STATUS END
shell> more /root/45.log
-- Page id: 125, Format: COMPACT, Records list: Valid, Expected records: (395 395)
000000009DD0 C9000001460110 test 36538 "zha" "san" "2021-11-19 00:03:52"
000000009DD1 CA000001470110 test 36539 "zh" "san" "2021-11-19 00:03:52"
000000009DD2 CB000001480110 test 36540 "z" "san" "2021-11-19 00:03:52"
000000009DD3 CC000001490110 test 36541 "zhang" "san" "2021-11-19 00:03:52"
000000009DD4 CD0000014A0110 test 36542 "zhan" "san" "2021-11-19 00:03:52"
000000009DD5 CE0000014B0110 test 36543 "zha" "san" "2021-11-19 00:03:52"
000000009DD6 CF0000014C0110 test 36544 "zh" "san" "2021-11-19 00:03:52"
000000009DD7 D00000014D0110 test 36545 "z" "san" "2021-11-19 00:03:52"
000000009DD8 D10000014E0110 test 36546 "zhang" "san" "2021-11-19 00:03:52"
000000009DD9 D20000014F0110 test 36547 "zhan" "san" "2021-11-19 00:03:52"
000000009DDA D3000001500110 test 36548 "zha" "san" "2021-11-19 00:03:52"
000000009DDB D4000001510110 test 36549 "zh" "san" "2021-11-19 00:03:52"
000000009DDC D5000001520110 test 36550 "z" "san" "2021-11-19 00:03:52"
000000009DDD D6000001530110 test 36551 "zhang" "san" "2021-11-19 00:03:52"
000000009DDE D7000001540110 test 36552 "zhan" "san" "2021-11-19 00:03:52"
000000009DDF D8000001550110 test 36553 "zha" "san" "2021-11-19 00:03:52"
000000009DE0 D9000001560110 test 36554 "zh" "san" "2021-11-19 00:03:52"
000000009DE1 DA000001570110 test 36555 "z" "san" "2021-11-19 00:03:52"
000000009DE2 DB000001580110 test 36556 "zhang" "san" "2021-11-19 00:03:52"
000000009DE3 DC000001590110 test 36557 "zhan" "san" "2021-11-19 00:03:52"
000000009DE4 DD0000015A0110 test 36558 "zha" "san" "2021-11-19 00:03:52"
000000009DE5 DE0000015B0110 test 36559 "zh" "san" "2021-11-19 00:03:52"
000000009DE6 DF0000015C0110 test 36560 "z" "san" "2021-11-19 00:03:52"
000000009DE7 E00000015D0110 test 36561 "zhang" "san" "2021-11-19 00:03:52"
000000009DE8 E10000015E0110 test 36562 "zhan" "san" "2021-11-19 00:03:52"
000000009DE9 E20000015F0110 test 36563 "zha" "san" "2021-11-19 00:03:52"
000000009DEA E3000001600110 test 36564 "zh" "san" "2021-11-19 00:03:52"
000000009DEB E4000001610110 test 36565 "z" "san" "2021-11-19 00:03:52"
000000009DEC E5000001620110 test 36566 "zhang" "san" "2021-11-19 00:03:52"
000000009DED E6000001630110 test 36567 "zhan" "san" "2021-11-19 00:03:52"
000000009DEE E7000001640110 test 36568 "zha" "san" "2021-11-19 00:03:52"
000000009DEF E8000001650110 test 36569 "zh" "san" "2021-11-19 00:03:52"
000000009DF0 E9000001660110 test 36570 "z" "san" "2021-11-19 00:03:52"
--More--(0%)
[1]+ Stopped more /root/45.log
5.3 解析并恢复至数据库
shell> mysql -uroot -pHzmc321# recover < /root/undrop-for-innodb/test.sql shell> /root/undrop-for-innodb/c_parser -6f pages-sda3/FIL_PAGE_INDEX/0000000000000045.page -t test.sql > dumps/default/test 2> dumps/default/test.sql shell> mysql -uroot -pHzmc321# recover < /root/undrop-for-innodb/dumps/default/test.sql mysql> select count(*) from recover.test; +----------+ | count(*) | +----------+ | 17808 | +----------+ 1 row in set (0.00 sec)
恢复数据,有丢失数据,在非必要情况下,不建议选择该方式恢复数据
