love wife & love life —Roger的Oracle&MySQL技术博客

Phone:18180207355 提供专业Oracle&MySQL数据恢复、性能优化、迁移升级、紧急救援等服务

无备份情况下恢复MySQL truncate table

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger的Oracle&MySQL技术博客

本文链接地址: 无备份情况下恢复MySQL truncate table

近期陆续有客户遇到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
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等情况,建议立刻停止服务或者停止数据库,保留现场,以防止环境进一步恶化,最终导致数据无法恢复的情况出现
云和恩墨,汇集了国内专业的Oracle、MySQL等数据恢复专家,愿为你的数据库保驾护航!

Leave a Reply

You must be logged in to post a comment.