近期陆续有客户遇到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等情况,建议立刻停止服务或者停止数据库,保留现场,以防止环境进一步恶化,最终导致数据无法恢复的情况出现。
标签:sec,truncate,innodb,MySQL,table,2017,root,enmotech From: https://blog.51cto.com/databasenotes/6172477