Board logo

标题: [技术文章] MySQL在无备份情况下恢复truncate的表的方法 [打印本页]
作者: shillan    时间: 2017-12-12 09:44     标题: 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



欢迎光临 逐梦论坛 (http://temp2023.zhumeng.org/) Powered by Discuz! 7.2