[技术文章] MySQL在无备份情况下恢复truncate的表的方法

近期陆续有客户遇到MySQL的数据库问题，最近正好开始研究MySQL的一些技术。大家都知道我之前是擅长Oracle恢复，如果不会MySQL 数据库恢复，总感觉却少一点什么。既然如此，就顺道研究一下吧。幸运的是，Google能够发现一些针对MySQL的恢复文章以及一些工具。

我们都知道，MySQL Server都很多存储引擎，并不是每种都可以进行异常情况之下都恢复，比如drop table/tuncate table/delete table/update table /drop database ／又或者是ibdata文件损坏之类的。用的最多的就是Myisam和innodb存储引擎。目前基本上都是5.5＋版本了，我想几乎没有人再去使用Myisam了吧。我这里所测试都5.6，5.7版本中默认都存储引擎已经是Innodb了。因此这里我以Innodb引擎为例子进行说明。

首先这里我要利用undrop_for_innodb 这个开源工具包（当然需要编译),目前该工具已经在2017年1月宣布闭源了，而且开始收费。但是我们仍然开源使用之前都开源工具包。另外这里可以告诉大家，不久的将来，odu 也会支持MySQL.

如下是我的truncate table 测试过程：

1. 创建测试表

mysql> set global innodb_file_per_table=on; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like '%file_per%'; +-----------------------+-------+ | Variable_name    | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.00 sec) mysql> use recover; Database changed mysql> create table t_enmotech(a int); Query OK, 0 rows affected (0.01 sec) mysql> insert into t_enmotech values('9999'); Query OK, 1 row affected (0.00 sec) mysql> alter table t_enmotech add primary key(a); Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> explain select * from t_enmotech where a=9999 ; +----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra    | +----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | t_enmotech | const | PRIMARY    | PRIMARY | 4    | const | 1 | Using index | +----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+ 1 row in set (0.00 sec)

2、备份表结构

[root@killdb innodb_recovery]# mysqldump --opt -d -uroot -proger recover t_enmotech > /tmp/innodb_recovery/recover/t_enmotech.sql [root@killdb innodb_recovery]#

3、truncate table

1 2	mysql> truncate table t_enmotech; Query OK, 0 rows affected (0.00 sec)

4、获取数据字典

[root@killdb innodb_recovery]# ./stream_parser -f /var/lib/mysql/ibdata1 Opening file: /var/lib/mysql/ibdata1 File information: ID of device containing file: 64768 inode number: 924765 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 496 group ID of owner: 491 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 69632 time of last access: 1496412155 Fri Jun 2 22:02:35 2017 time of last modification: 1496416863 Fri Jun 2 23:21:03 2017 time of last status change: 1496416863 Fri Jun 2 23:21:03 2017 total size, in bytes: 35651584 (34.000 MiB) Size to process: 35651584 (34.000 MiB) All workers finished in 0 sec

5、扫描逻辑卷

[root@killdb innodb_recovery]# ./stream_parser -f /dev/mapper/vg_oel6-lv_root -t 36000000k Opening file: /dev/mapper/vg_oel6-lv_root File information: ID of device containing file: 5 inode number: 6307 protection: 60660 (block device) number of hard links: 1 user ID of owner: 0 group ID of owner: 6 device ID (if special file): 64768 blocksize for filesystem I/O: 4096 number of blocks allocated: 0 time of last access: 1496411556 Fri Jun 2 21:52:36 2017 time of last modification: 1496113795 Tue May 30 11:09:55 2017 time of last status change: 1496113795 Tue May 30 11:09:55 2017 total size, in bytes: 0 (0.000 exp(+0)) Size to process: 36864000000 (34.332 GiB) Worker(0): 1.04% done. 2017-06-02 23:26:25 ETA(in 00:04:50). Processing speed: 119.792 MiB/sec Worker(0): 2.07% done. 2017-06-02 23:26:25 ETA(in 00:04:47). Processing speed: 119.767 MiB/sec Worker(0): 3.09% done. 2017-06-02 23:26:25 ETA(in 00:04:44). Processing speed: 119.767 MiB/sec Worker(0): 4.11% done. 2017-06-02 23:26:25 ETA(in 00:04:41). Processing speed: 119.773 MiB/sec Worker(0): 5.13% done. 2017-06-02 23:26:25 ETA(in 00:04:38). Processing speed: 119.773 MiB/sec Worker(0): 6.16% done. 2017-06-02 23:26:25 ETA(in 00:04:35). Processing speed: 119.787 MiB/sec Worker(0): 7.18% done. 2017-06-02 23:26:25 ETA(in 00:04:32). Processing speed: 119.767 MiB/sec Worker(0): 8.20% done. 2017-06-02 23:27:56 ETA(in 00:05:59). Processing speed: 89.829 MiB/sec Worker(0): 9.22% done. 2017-06-02 23:26:26 ETA(in 00:04:26). Processing speed: 119.776 MiB/sec Worker(0): 10.24% done. 2017-06-02 23:26:26 ETA(in 00:04:23). Processing speed: 119.773 MiB/sec ...... Worker(0): 96.10% done. 2017-06-02 23:26:36 ETA(in 00:00:11). Processing speed: 119.768 MiB/sec Worker(0): 97.12% done. 2017-06-02 23:26:36 ETA(in 00:00:08). Processing speed: 119.771 MiB/sec Worker(0): 98.14% done. 2017-06-02 23:26:36 ETA(in 00:00:05). Processing speed: 119.771 MiB/sec Worker(0): 99.17% done. 2017-06-02 23:26:36 ETA(in 00:00:02). Processing speed: 119.784 MiB/sec All workers finished in 306 sec [root@killdb innodb_recovery]#

6、创建数据字典表

[root@killdb innodb_recovery]# ./recover_dictionary.sh Generating dictionary tables dumps... OK Creating test database ... OK Creating dictionary tables in database test: SYS_TABLES ... OK SYS_COLUMNS ... OK SYS_INDEXES ... OK SYS_FIELDS ... OK All OK Loading dictionary tables data: SYS_TABLES ... 150 recs OK SYS_COLUMNS ... 243 recs OK SYS_INDEXES ... 120 recs OK SYS_FIELDS ... 122 recs OK All OK

该工具包提供的recover_dictionary脚本会创建一个test数据库，并创建一些数据字典表供恢复查询使用。同时也会在当前目录创建dictionary目录，该目录下会存放数据字典信息。

7、查询需要恢复的表的index_id信息

mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from SYS_TABLES where name like 'recover/t_enmotech%'; +--------------------+-----+--------+------+--------+---------+--------------+-------+ | NAME | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE | +--------------------+-----+--------+------+--------+---------+--------------+-------+ | recover/t_enmotech | 181 | 1 | 1 | 0 | 0 | | 0 | +--------------------+-----+--------+------+--------+---------+--------------+-------+ 1 row in set (0.00 sec) mysql> select * from SYS_INDEXES where table_id=181; +----------+-----+---------+----------+------+-------+---------+ | TABLE_ID | ID | NAME | N_FIELDS | TYPE | SPACE | PAGE_NO | +----------+-----+---------+----------+------+-------+---------+ | 181 | 178 | PRIMARY | 1 | 3 | 0 | 552 | +----------+-----+---------+----------+------+-------+---------+ 1 row in set (0.00 sec)

可以看到被truncate的表的index_id 为178，我们应该进一步从178 的page中获取数据。

8、确认数据是否存在

[root@killdb innodb_recovery]# ./c_parser -6f pages-vg_oel6-lv_root/FIL_PAGE_INDEX/0000000000000178.page -t recover/t_enmotech.sql |head -10 -- Page id: 552, Format: COMPACT, Records list: Valid, Expected records: (0 0) -- Page id: 552, Found records: 0, Lost records: NO, Leaf page: YES -- Page id: 552, Format: COMPACT, Records list: Valid, Expected records: (0 0) -- Page id: 552, Found records: 0, Lost records: NO, Leaf page: YES -- Page id: 557, Format: COMPACT, Records list: Valid, Expected records: (1 1) 000000001306 870000013F0110t_enmotech 9999 -- Page id: 557, Found records: 1, Lost records: NO, Leaf page: YES -- Page id: 552, Format: COMPACT, Records list: Valid, Expected records: (0 0) -- Page id: 552, Found records: 0, Lost records: NO, Leaf page: YES -- Page id: 552, Format: COMPACT, Records list: Valid, Expected records: (0 0) [root@killdb innodb_recovery]#

9、抽取page中的数据

[root@killdb innodb_recovery]# ./c_parser -6f pages-vg_oel6-lv_root/FIL_PAGE_INDEX/0000000000000178.page -t recover/t_enmotech.sql > dumps/default/t_enmotech 2> dumps/default/t_enmotech.sql [root@killdb innodb_recovery]# [root@killdb innodb_recovery]# ls -ltr dumps/default/t_enmotech* -rw-r--r--. 1 root root 222 Jun 3 06:04 dumps/default/t_enmotech.sql -rw-r--r--. 1 root root 1455 Jun 3 06:04 dumps/default/t_enmotech

抽取数据之前，必须提前准备好表的表结构，由于这里是truncate，因此表结构是存在的，很容易获取。我这里是测试，所以之前就备份了结构。
那么如果是drop table 呢？ 实际上我们也可以通过该工具来恢复表结构。

10、加载数据到mysql server

mysql> use recover; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-------------------+ | Tables_in_recover | +-------------------+ | t_enmotech | | t_recover | | test_0731 | | test_drop | +-------------------+ 4 rows in set (0.00 sec) mysql> source dumps/default/t_enmotech.sql Query OK, 0 rows affected (0.00 sec) Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

11、验证数据

mysql> select * from t_enmotech; +------+ | a | +------+ | 9999 | +------+ 1 row in set (0.00 sec)

我们可以看到，被truncate 掉的数据被成功恢复了回来。


这里我测试的truncate table的场景，其实对于drop table、delete table 恢复方法均类似（已测试过）。另外，对于更为严重的drop database 其实也是可以进行恢复的。

当然，对于实际的生产库来讲，数据不一定能够恢复，因为有可能被覆盖而导致数据恢复不全。MySQL 对于空间的重用机制与Oracle 有很大区别，对于Oracle 而言，如果是delete的数据，还是很难被覆盖掉的，对于drop 和truncate 则领导别论。然而MySQL则有所不同，MySQL 默认会启动一些purge 进程来进行空间重用，这是MySQL 5.6的情况：

mysql> show global variables like '%purge%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | innodb_max_purge_lag | 0 | | innodb_purge_batch_size | 20 | | innodb_purge_threads | 0 | | relay_log_purge | ON | +-------------------------+-------+ 4 rows in set (0.00 sec)

在MySQL 5.7 版本中更为坑爹，MySQL 默认会启动4个purge 线程，因此很容易就会导致空间被重用，最终导致数据无法恢复，如下是MySQL 5.7的purge相关参数：

mysql> show global variables like '%purge%'; +--------------------------------------+-------+ | Variable_name | Value | +--------------------------------------+-------+ | gtid_purged | | | innodb_max_purge_lag | 0 | | innodb_max_purge_lag_delay | 0 | | innodb_purge_batch_size | 300 | | innodb_purge_rseg_truncate_frequency | 128 | | innodb_purge_threads | 4 | | relay_log_purge | ON | +--------------------------------------+-------+ 7 rows in set (0.01 sec)

因此，一旦你遭遇turncate table/drop table/delete /drop database等情况，建议立刻停止服务或者停止数据库，保留现场，以防止环境进一步恶化，最终导致数据无法恢复的情况出现。

本文引自：http://www.killdb.com/2017/07/31 ... truncate-table.html