mysql truncate表后恢复

 1 mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
 2 +-----------------------+-------+
 3 | Variable_name         | Value |
 4 +-----------------------+-------+
 5 | innodb_file_per_table | ON    |
 6 +-----------------------+-------+
 7 1 row in set (0.00 sec)
 8 
 9 mysql> select count(1) from t_e;
10 +----------+
11 | count(1) |
12 +----------+
13 |   300024 |
14 +----------+
15 1 row in set (0.10 sec)
16 
17 mysql> truncate table t_e;
18 Query OK, 0 rows affected (0.06 sec)
19 
20 mysql> select count(1) from t_e;
21 +----------+
22 | count(1) |
23 +----------+
24 |        0 |
25 +----------+
26 1 row in set (0.00 sec)

生成数据字典，参考https://www.cnblogs.com/omsql/p/9253234.html

 1 mysql> select * from SYS_TABLES where name like '%t_e%';
 2 +-----------------------+----+--------+------+--------+---------+--------------+-------+
 3 | NAME                  | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
 4 +-----------------------+----+--------+------+--------+---------+--------------+-------+
 5 | employees/departments | 41 |      2 |   33 |      0 |      80 |              |    26 |
 6 | employees/dept_emp    | 43 |      4 |   33 |      0 |      80 |              |    28 |
 7 | employees/titles      | 44 |      4 |   33 |      0 |      80 |              |    29 |
 8 | employees/t_e         | 59 |      6 |   33 |      0 |      80 |              |    41 |
 9 | employees/t_emp       | 52 |      4 |   33 |      0 |      80 |              |    34 |
10 +-----------------------+----+--------+------+--------+---------+--------------+-------+
11 5 rows in set (0.00 sec)
12 
13 mysql> select * from SYS_INDEXES where table_id=59;
14 +----------+----+---------+----------+------+-------+---------+
15 | TABLE_ID | ID | NAME    | N_FIELDS | TYPE | SPACE | PAGE_NO |
16 +----------+----+---------+----------+------+-------+---------+
17 |       59 | 59 | PRIMARY |        1 |    3 |    41 |       3 |
18 +----------+----+---------+----------+------+-------+---------+
19 1 row in set (0.00 sec)

生成表t_e创建脚本

 1 mysql> show create table t_e;
 2 +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 3 | Table | Create Table                                                                                                                                                                                                                                                                             |
 4 +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 5 | t_e   | CREATE TABLE `t_e` (
 6   `emp_no` int(11) NOT NULL,
 7   `birth_date` date NOT NULL,
 8   `first_name` varchar(14) NOT NULL,
 9   `last_name` varchar(16) NOT NULL,
10   `gender` enum('M','F') NOT NULL,
11   `hire_date` date NOT NULL,
12   PRIMARY KEY (`emp_no`)
13 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
14 +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
15 1 row in set (0.00 sec)

扫描逻辑卷

1 [root@redis02 undrop-for-innodb]# df -k
2 Filesystem           1K-blocks     Used Available Use% Mounted on
3 /dev/mapper/vg_redis01-lv_root
4                       29088520 12026940  15754040  44% /
5 tmpfs                  1023664       72   1023592   1% /dev/shm
6 /dev/sda1               487652    85768    372188  19% /boot
7 
8 ./stream_parser -f /dev/mapper/vg_redis01-lv_root  -t 29088520k

查看page里面的数据是否存在

1 ./c_parser -6f pages-vg_redis01-lv_root/FIL_PAGE_INDEX/0000000000000059.page  -t t_e.sql |  head -6
2 -- Page id: 914, Format: COMPACT, Records list: Valid, Expected records: (337 337)
3 000000000EBC    AD000001B53431    t_e    499545    "1961-03-04"    "Shrikanth"    "Brizzi"    1    "1991-06-02"
4 000000000EBC    AD000001B53440    t_e    499546    "1960-08-04"    "Guadalupe"    "Bernardeschi"    1    "1992-06-13"
5 000000000EBC    AD000001B5344F    t_e    499547    "1963-01-26"    "Berto"    "Flowers"    1    "1986-01-28"
6 000000000EBC    AD000001B5345E    t_e    499548    "1955-03-16"    "Aloys"    "Bale"    1    "1987-04-27"
7 000000000EBC    AD000001B5346D    t_e    499549    "1964-06-21"    "Kagan"    "Veeraraghavan"    2    "1995-11-27"

./c_parser -6f pages-vg_redis01-lv_root/FIL_PAGE_INDEX/0000000000000059.page -t t_e.sql  > dumps/default/t_e 2> dumps/default/t_e.sql

数据存在看下行数

[mysql@redis02 default]$ cat t_e|wc -l
77278

7万多条数据不全，t_e这张表存在30万条数据，查看前面的page看数据是否能使用

 1 [mysql@redis02 undrop-for-innodb]$ ./c_parser -6f pages-vg_redis01-lv_root/FIL_PAGE_INDEX/0000000000000058.page  -t t_e.sql |  head -6
 2 -- Page id: 974, Format: COMPACT, Records list: Valid, Expected records: (300 300)
 3 751600000000    0EBBAC00000196    t_e    -2147483635    "2293-04-00"    "XNF4Di"    "panka"    114    "10680-03-05"
 4 751700000000    0EBBAC00000196    t_e    -2147483635    "2300-12-00"    "XOX(T"    "orsten"    67    "14267-03-14"
 5 751800000000    0EBBAC00000196    t_e    -2147483635    "2308-04-00"    "XPQ"    "JaewooSch"    108    "12983-03-26"
 6 751900000000    0EBBAC00000196    t_e    -2147483635    "2315-12-00"    "XQO\_Nec"    "tariosA"    114    "13234-11-14"
 7 751A00000000    0EBBAC00000196    t_e    -2147483635    "2323-04-00"    "XRK*"    "DekangZie"    108    "13495-03-19"
 8 [mysql@redis02 undrop-for-innodb]$ ./c_parser -6f pages-vg_redis01-lv_root/FIL_PAGE_INDEX/0000000000000057.page  -t t_e.sql |  head -6
 9 -- Page id: 768, Format: COMPACT, Records list: Valid, Expected records: (338 338)
10 000000000E66    C80000019C39D1    t_e    418555    "1952-03-20"    "Kristina"    "Luan"    1    "1985-11-05"
11 000000000E66    C80000019C39E0    t_e    418556    "1961-11-17"    "Fatemeh"    "Laventhal"    1    "1995-06-23"
12 000000000E66    C80000019C39EF    t_e    418557    "1957-05-26"    "Jinpo"    "Kitai"    1    "1991-03-02"
13 000000000E66    C80000019C39FE    t_e    418558    "1961-01-20"    "Fumiko"    "Perry"    1    "1993-08-25"
14 000000000E66    C80000019C3A0D    t_e    418559    "1953-08-30"    "Moni"    "Binkley"    1    "1993-12-07"

只有page57的还可以使用，其他page页里面基本上都是乱码数据不能使用，mysql数据时存放在根目录/data下，恢复出的page也是存放在根目录下空间被占用只能恢复出部分数据，生产库中/data目录一定要是单独的逻辑卷。

查看page57里面的数据条数

1 ./c_parser -6f pages-vg_redis01-lv_root/FIL_PAGE_INDEX/0000000000000057.page -t t_e.sql  > dumps/default/t_e1 2> dumps/default/t_e1.sql
2 [root@redis02 undrop-for-innodb]# cat dumps/default/t_e1|wc -l
3 50563

只能恢复出来10多万条

在导入数据时，需要修改下自动生成的t_e1.sql脚本，不修改page57里面的数据加载不进去

1 [root@redis02 undrop-for-innodb]# cat dumps/default/t_e1.sql
2 SET FOREIGN_KEY_CHECKS=0;
3 LOAD DATA LOCAL INFILE '/home/mysql/undrop-for-innodb/dumps/default/t_e1'  INTO TABLE `t_e` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 't_e\t' (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`);
4 -- STATUS {"records_expected": 50265, "records_dumped": 50265, "records_lost": false} STATUS END

验证数据

mysql> select count(1) from t_e;
+----------+
| count(1) |
+----------+
|   126173 |
+----------+
1 row in set (0.04 sec)

mysql> select count(distinct emp_no) from t_e;
+------------------------+
| count(distinct emp_no) |
+------------------------+
|                 126173 |
+------------------------+
1 row in set (0.13 sec)

 

转载于:https://www.cnblogs.com/omsql/p/10980785.html