mysql truncate表后恢复

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

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

mysql> truncate table t_e;
Query OK, 0 rows affected (0.06 sec)

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

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

mysql> select * from SYS_TABLES where name like '%t_e%';
+-----------------------+----+--------+------+--------+---------+--------------+-------+
| NAME                  | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+-----------------------+----+--------+------+--------+---------+--------------+-------+
| employees/departments | 41 |      2 |   33 |      0 |      80 |              |    26 |
| employees/dept_emp    | 43 |      4 |   33 |      0 |      80 |              |    28 |
| employees/titles      | 44 |      4 |   33 |      0 |      80 |              |    29 |
| employees/t_e         | 59 |      6 |   33 |      0 |      80 |              |    41 |
| employees/t_emp       | 52 |      4 |   33 |      0 |      80 |              |    34 |
+-----------------------+----+--------+------+--------+---------+--------------+-------+
5 rows in set (0.00 sec)

mysql> select * from SYS_INDEXES where table_id=59;
+----------+----+---------+----------+------+-------+---------+
| TABLE_ID | ID | NAME    | N_FIELDS | TYPE | SPACE | PAGE_NO |
+----------+----+---------+----------+------+-------+---------+
|       59 | 59 | PRIMARY |        1 |    3 |    41 |       3 |
+----------+----+---------+----------+------+-------+---------+
1 row in set (0.00 sec)

生成表t_e创建脚本

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

扫描逻辑卷

[root@redis02 undrop-for-innodb]# df -k
Filesystem           1K-blocks     Used Available Use% Mounted on
/dev/mapper/vg_redis01-lv_root
                      29088520 12026940  15754040  44% /
tmpfs                  1023664       72   1023592   1% /dev/shm
/dev/sda1               487652    85768    372188  19% /boot

./stream_parser -f /dev/mapper/vg_redis01-lv_root  -t 29088520k

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

./c_parser -6f pages-vg_redis01-lv_root/FIL_PAGE_INDEX/0000000000000059.page  -t t_e.sql |  head -6
-- Page id: 914, Format: COMPACT, Records list: Valid, Expected records: (337 337)
000000000EBC    AD000001B53431    t_e    499545    "1961-03-04"    "Shrikanth"    "Brizzi"    1    "1991-06-02"
000000000EBC    AD000001B53440    t_e    499546    "1960-08-04"    "Guadalupe"    "Bernardeschi"    1    "1992-06-13"
000000000EBC    AD000001B5344F    t_e    499547    "1963-01-26"    "Berto"    "Flowers"    1    "1986-01-28"
000000000EBC    AD000001B5345E    t_e    499548    "1955-03-16"    "Aloys"    "Bale"    1    "1987-04-27"
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看数据是否能使用

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

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

查看page57里面的数据条数

./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
[root@redis02 undrop-for-innodb]# cat dumps/default/t_e1|wc -l
50563

只能恢复出来10多万条

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

[root@redis02 undrop-for-innodb]# cat dumps/default/t_e1.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/home/mysql/undrop-for-innodb/dumps/default/t_e1'  INTO TABLE `t_e` CHARACTER SET UTF8 FIELDS TERMINATED BY '	' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 't_e	' (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`);
-- 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)