近期陆续有客户遇到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 |
鲁公网安备 37120302000001号
