ITPub博客

首页 > 数据库 > MySQL > MySQL数据灾难挽救之truncate table

MySQL数据灾难挽救之truncate table

原创 MySQL 作者:龙山游龙 时间:2021-11-29 12:02:03 0 删除 编辑

场景:工作人员误操作对其整表误删除且用了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)

数据恢复成功,没有数据丢失!


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30310891/viewspace-2844565/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
拥有超过9年以上的MySQL/Oracle数据库领域从业经验,TB级高并发数据库的管理经验。对于MySQL/Oracle内部原理、体系结构,数据页/块结构原理有着深刻认识;对于MySQL/ORACLE新特性、分布式高可用架构和性能调优有着丰富的的实战经验。擅长故障诊断以及数据库异常打开及数据灾难挽救。

注册时间:2015-06-28

  • 博文量
    79
  • 访问量
    116254