场景：工作人员误操作对其整表误删除且用了truncate table语句，且无备份、未开启BinLog日志的情况。思路：数据在truncate删除不会删除表结构只清除数据，在清除之前数据已经记录到磁盘了，在这个情况下不再对磁盘进行刷新和覆盖，可以尝试通过undrop-for-innodb工具扫描磁盘进行恢复数据。 说明：innodb_file_per_table=on，因为通过扫描磁盘页来找回数据的，为防止磁盘页由于大量的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使用实际路径替换
二、手动创建模拟环境
1、手动创建模拟表
mysql> create database test; mysql> use test 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 function rand_string (n int) returns varchar(255) -> begin -> declare chars_str varchar(100) default 'ABCDEFGHIGKLMNOPQRSTUVWXYZ'; -> declare return_str varchar(255) default ''; -> declare i int default 0; -> while (i < n) do -> set return_str = concat(return_str,substring(chars_str,floor(1+rand()*26),1)); -> set i = i+1; -> end while; -> return return_str; -> END -> // mysql> CREATE PROCEDURE mytest() -> begin -> declare i bigint; -> set i=1; -> while i<=50000 do -> insert into thx(first_name, last_name) value (rand_string(3),rand_string(3)); -> set i = i+1 ; -> end while; -> commit; -> end -> // mysql> DELIMITER ; mysql> call mytest();
2、查询当前表信息
mysql> select count(*) from test; +----------+ | count(*) | +----------+ | 50000 | +----------+
3、手动删除表
mysql> truncate table test;
4、查询需要恢复的表的index_id信息
mysql> select t.name, t.table_id, i.index_id,i.NAME,i.page_no from information_schema.INNODB_SYS_TABLES t join information_schema.INNODB_SYS_INDEXES i on t.table_id=i.table_id and t.name='test/test'; +-----------+----------+----------+--------------------+---------+ | name | table_id | index_id | NAME | page_no | +-----------+----------+----------+--------------------+---------+ | test/test | 49 | 52 | PRIMARY | 3 | | test/test | 49 | 53 | idx_test_last_name | 4 | +-----------+----------+----------+--------------------+---------+ 2 rows in set (0.01 sec)
三、恢复数据
1、扫描逻辑卷
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 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: 9937 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: 1637735653 Tue Nov 23 22:34:13 2021 time of last modification: 1637735650 Tue Nov 23 22:34:10 2021 time of last status change: 1637735650 Tue Nov 23 22:34:10 2021 total size, in bytes: 0 (0.000 exp(+0)) Size to process: 30064771072 (28.000 GiB) Worker(0): 7.14% done. 2021-11-23 22:47:55 ETA(in 00:03:02). Processing speed: 146.283 MiB/sec Worker(0): 10.71% done. 2021-11-23 22:45:45 ETA(in 00:00:50). Processing speed: 512.000 MiB/sec Worker(0): 14.29% done. 2021-11-23 22:45:45 ETA(in 00:00:48). Processing speed: 512.000 MiB/sec Worker(0): 17.86% done. 2021-11-23 22:45:45 ETA(in 00:00:46). Processing speed: 512.000 MiB/sec Worker(0): 21.43% done. 2021-11-23 22:45:45 ETA(in 00:00:44). Processing speed: 512.000 MiB/sec Worker(0): 25.00% done. 2021-11-23 22:46:51 ETA(in 00:01:45). Processing speed: 204.800 MiB/sec Worker(0): 28.57% done. 2021-11-23 22:46:09 ETA(in 00:01:00). Processing speed: 341.328 MiB/sec Worker(0): 32.14% done. 2021-11-23 22:45:49 ETA(in 00:00:38). Processing speed: 511.992 MiB/sec Worker(0): 35.71% done. 2021-11-23 22:45:49 ETA(in 00:00:36). Processing speed: 512.000 MiB/sec Worker(0): 39.29% done. 2021-11-23 22:45:49 ETA(in 00:00:34). Processing speed: 512.000 MiB/sec Worker(0): 42.86% done. 2021-11-23 22:45:32 ETA(in 00:00:16). Processing speed: 1024.000 MiB/sec Worker(0): 46.43% done. 2021-11-23 22:46:04 ETA(in 00:00:45). Processing speed: 341.333 MiB/sec Worker(0): 50.00% done. 2021-11-23 22:46:49 ETA(in 00:01:24). Processing speed: 170.667 MiB/sec Worker(0): 53.57% done. 2021-11-23 22:46:35 ETA(in 00:01:05). Processing speed: 204.797 MiB/sec Worker(0): 57.14% done. 2021-11-23 22:47:01 ETA(in 00:01:24). Processing speed: 146.283 MiB/sec Worker(0): 60.71% done. 2021-11-23 22:46:37 ETA(in 00:00:55). Processing speed: 204.797 MiB/sec Worker(0): 64.29% done. 2021-11-23 22:46:04 ETA(in 00:00:20). Processing speed: 511.992 MiB/sec Worker(0): 67.86% done. 2021-11-23 22:46:04 ETA(in 00:00:18). Processing speed: 512.000 MiB/sec Worker(0): 71.43% done. 2021-11-23 22:46:13 ETA(in 00:00:24). Processing speed: 341.333 MiB/sec Worker(0): 75.00% done. 2021-11-23 22:46:37 ETA(in 00:00:42). Processing speed: 170.667 MiB/sec Worker(0): 78.57% done. 2021-11-23 22:46:37 ETA(in 00:00:36). Processing speed: 170.664 MiB/sec Worker(0): 82.14% done. 2021-11-23 22:46:13 ETA(in 00:00:10). Processing speed: 511.992 MiB/sec Worker(0): 85.71% done. 2021-11-23 22:46:13 ETA(in 00:00:08). Processing speed: 512.000 MiB/sec Worker(0): 89.29% done. 2021-11-23 22:46:13 ETA(in 00:00:06). Processing speed: 512.000 MiB/sec Worker(0): 92.86% done. 2021-11-23 22:46:13 ETA(in 00:00:04). Processing speed: 512.000 MiB/sec Worker(0): 96.43% done. 2021-11-23 22:46:11 ETA(in 00:00:01). Processing speed: 1024.000 MiB/sec All workers finished in 95 sec
2、查看误删的表结构
mysql> show create table test; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `test_id` bigint(20) 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 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.04 sec) shell> vi test.sql CREATE TABLE `test` ( `test_id` bigint(20) 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;
3、解析聚集索引页
shell> mkdir -p dumps/default shell> /root/undrop-for-innodb/c_parser -6f pages-sda3/FIL_PAGE_INDEX/0000000000000052.page -t test.sql > dumps/default/test 2> dumps/default/test.sql
4、恢复数据
shell> mysql -uroot -pHzmc321# test < /root/undrop-for-innodb/dumps/default/test.sql mysql: [Warning] Using a password on the command line interface can be insecure. mysql> select count(*) from test; +----------+ | count(*) | +----------+ | 50000 | +----------+ 1 row in set (0.01 sec)
数据恢复成功，没有数据丢失！
