undrop for innodb
TRANSCRIPT
Toolkit for MySQL Data Recovery https://twindb.com/
UNDROP FOR INNODB
2
No Backups? UnDROP for InnoDB
3
Roadmap
•Update since last year •How toolkit works •Demo
4
Update since last year
•Multi-threaded stream_parser
Raw image
Worker #1 Worker #2 Worker #3
5
Update since last year
•SQL parser to define table structure
Table in File System
# ls -la /var/lib/mysql/sakila/actor.*
-rw-rw---- 1 mysql mysql actor.frm-rw-rw---- 1 mysql mysql actor.ibd
# ls -la /var/lib/mysql/
-rw-rw----. 1 mysql mysql ibdata1-rw-rw----. 1 mysql mysql ib_logfile0-rw-rw----. 1 mysql mysql ib_logfile1
PRIMARY index - B+ Tree Structure
1 100
page 2 page 3
1 50
page 4 page 5
100 150
page 6 page 7
actor_id 1 2 … 49
first_name A C E
last_name K D F
last_update
X Y … Z
actor_id 50 51 … 99
first_name G I K
last_name H J L
last_update
X Y … Z
Page 3Page 2
Page 1
Page 4 Page 5
Record format
A row: (10, ‘Kevin’, ‘Spacey’, ‘2015-04-08 21:47:36’)
8
Actually stored as: (10, TRX_ID, PTR_ID, ‘Kevin’, ‘Spacey’, ‘2015-04-08 21:47:36’)
…. next
Extra 5 bytes:
0x00 00 00 0A
Fields
... ... Kevin 0x5525A1FF
A bit per NULL-able field
7 Spacey
Lengths
InnoDB Dictionary
mysql> SELECT * FROM SYS_TABLES WHERE NAME = 'sakila/actor'\G *************************** 1. row *************************** NAME: sakila/actor ID: 2642 N_COLS: 4 TYPE: 41 MIX_ID: 0 MIX_LEN: 80 CLUSTER_NAME: SPACE: 2337 1 row in set (0.00 sec)
SYS_TABLES
InnoDB Dictionary
mysql> SELECT * FROM SYS_INDEXES WHERE TABLE_ID = '2642'\G *************************** 1. row *************************** TABLE_ID: 2642 ID: 6133 NAME: PRIMARY N_FIELDS: 1 TYPE: 3 SPACE: 2337 PAGE_NO: 3 *************************** 2. row *************************** TABLE_ID: 2642 ID: 6134 NAME: idx_actor_last_name N_FIELDS: 1 TYPE: 0 SPACE: 2337 PAGE_NO: 4 2 rows in set (0.00 sec)
SYS_INDEXES
11
UnDrop for InnoDB
Introducing stream_parser
• Finds InnoDB pages in bytes stream
00000000 b7 da 39 28 00 00 00 0b ff ff ff ff ff ff ff ff |..9(............| 00000010 00 00 00 00 ff 98 e1 54 45 bf 00 00 00 00 00 00 |.......TE.......| 00000020 00 00 00 00 00 00 00 1c 25 cd 00 83 23 4f 04 4c |........%...#O.L| 00000030 00 00 00 02 00 01 00 73 00 00 00 00 00 00 00 00 |.......s........| 00000040 00 00 00 00 00 00 00 00 00 03 00 00 00 00 00 00 |................| 00000050 00 02 08 72 00 00 00 00 00 00 00 02 07 b2 08 01 |...r............| 00000060 00 00 03 00 8c 69 6e 66 69 6d 75 6d 00 09 05 00 |.....infimum....| 00000070 08 03 00 00 73 75 70 72 65 6d 75 6d 00 33 2f 2b |....supremum.3/+| 00000080 27 23 1d 16 10 08 00 00 10 13 00 ce 00 00 00 00 |'#..............| 00000090 00 00 00 0b 00 00 00 00 00 00 00 0b 00 00 00 00 |................|
stream_parser in action
# df -h /var/lib/mysql/ Filesystem Size Used Avail Use% Mounted on /dev/sdb 5.8G 146M 5.4G 3% /mnt/data
# ./stream_parser -f /dev/sdb -t 5.8G Opening file: /dev/sdb … Size to process: 6227702784 (5.800 GiB) … Worker(0): 99.95% done. 2014-09-14 17:30:38 ETA(in 00:00:00). Processing speed: 808.000 MiB/sec All workers finished in 9 sec
stream_parser Result
# ls -la pages-sdb total 24 drwxr-xr-x 4 root root 4096 Sep 14 17:30 . drwxr-xr-x 7 root root 4096 Sep 14 17:30 .. drwxr-xr-x 2 root root 12288 Sep 14 17:30 FIL_PAGE_INDEX drwxr-xr-x 2 root root 4096 Sep 14 17:30 FIL_PAGE_TYPE_BLOB
# ls -la pages-sdb/FIL_PAGE_INDEX | head -100| tail -5 -rw-r--r-- 1 root root 81920 Sep 14 17:30 0000000000000727.page -rw-r--r-- 1 root root 114688 Sep 14 17:30 0000000000000728.page -rw-r--r-- 1 root root 16384 Sep 14 17:30 0000000000000729.page -rw-r--r-- 1 root root 786432 Sep 14 17:30 0000000000000730.page -rw-r--r-- 1 root root 180224 Sep 14 17:30 0000000000000731.page
Introducing c_parser
# ./c_parser -5f \
pages-sdb/FIL_PAGE_INDEX/0000000000000022.page \
-t actor.sql \
> dumps/default/actor 2> dumps/default/actor.sql
c_parser Result
# head -5 dumps/default/actor -- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (200 200) 000000000740 C0000001630110 actor 1 "PENELOPE" "GUINESS" "2006-02-15 04:34:33" 000000000740 C000000163011A actor 2 "NICK" "WAHLBERG" "2006-02-15 04:34:33" 000000000740 C0000001630124 actor 3 "ED" "CHASE" "2006-02-15 04:34:33" 000000000740 C000000163012E actor 4 "JENNIFER" "DAVIS" "2006-02-15 04:34:33"
# cat dumps/default/actor.sql SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/root/undrop-for-innodb/dumps/default/actor' REPLACE INTO TABLE `actor` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'actor\t' (`actor_id`, `first_name`, `last_name`, `last_update`);
Introducing sys_parser
# yum install mysql-community-devel.x86_64
# make sys_parser /usr/bin/mysql_config cc `mysql_config --cflags` `mysql_config --libs` -o sys_parser sys_parser.c
# ./sys_parser sakila/actor CREATE TABLE `actor`( `actor_id` SMALLINT UNSIGNED NOT NULL, `first_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL, `last_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL, `last_update` TIMESTAMP NOT NULL, PRIMARY KEY (`actor_id`) ) ENGINE=InnoDB;
Recover InnoDB Dictionary
# ./stream_parser -f /var/lib/mysql/ibdata1 Opening file: /var/lib/mysql/ibdata1
Size to process: 12582912 (12.000 MiB) All workers finished in 0 sec
# ./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 ... 46 recs OK SYS_COLUMNS ... 304 recs OK SYS_INDEXES ... 96 recs OK SYS_FIELDS ... 118 recs OK All OK
Getting index_id from Dictionary
mysql> SELECT SYS_TABLES.NAME, SYS_INDEXES.NAME, SYS_INDEXES.ID FROM SYS_TABLES LEFT JOIN SYS_INDEXES ON SYS_TABLES.ID = SYS_INDEXES.TABLE_ID WHERE SYS_TABLES.NAME = 'sakila/actor'; +--------------+---------------------+------+ | NAME | NAME | ID | +--------------+---------------------+------+ | sakila/actor | PRIMARY | 22 | | sakila/actor | idx_actor_last_name | 23 | +--------------+---------------------+------+ 2 rows in set (0.00 sec)
20
Demo
•Drop table sakila.actor •File per table •Scan disk image •Recover structure from dictionary •Recover table records
Let’s DROP TABLE `actor`?
mysql> drop table sakila.actor;ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint failsmysql> SET FOREIGN_KEY_CHECKS=0;Query OK, 0 rows affected (0.00 sec)
mysql> drop table sakila.actor;Query OK, 0 rows affected (0.01 sec)
Table’s removed from Dictionary
mysql> SELECT * FROM information_schema.INNODB_SYS_TABLES WHERE NAME = 'sakila/actor';Empty set (0.00 sec)
actor.frm is removed
# ls -la /var/lib/mysql/sakila/actor.frmls: cannot access /var/lib/mysql/sakila/actor.frm: No such file or directory
So is actor.ibd
# ls -la /var/lib/mysql/sakila/actor.ibdls: cannot access /var/lib/mysql/sakila/actor.ibd: No such file or directory
InnoDB Dictionary
•We need it for: a) index_id
b) Table structure
Where is my table?
# grep -r PENELO pages-sdb/FIL_PAGE_INDEX Binary file pages-sdb/FIL_PAGE_INDEX/0000000000000022.page matches Binary file pages-sdb/FIL_PAGE_INDEX/0000000000000706.page matches Binary file pages-sdb/FIL_PAGE_INDEX/0000000000000747.page matches
# hexdump -C pages-sdb/FIL_PAGE_INDEX/0000000000000022.page | head -15 00000000 ea a2 dd d0 00 00 00 03 ff ff ff ff ff ff ff ff |................| 00000010 00 00 00 00 00 1b 82 20 45 bf 00 00 00 00 00 00 |....... E.......| 00000020 00 00 00 00 00 06 00 33 1d cb 80 ca 00 00 00 00 |.......3........| 00000030 1d ad 00 02 00 c7 00 c8 00 00 00 00 00 00 00 00 |................| 00000040 00 00 00 00 00 00 00 00 00 16 00 00 00 06 00 00 |................| 00000050 00 02 00 f2 00 00 00 06 00 00 00 02 00 32 01 00 |.............2..| 00000060 02 00 1c 69 6e 66 69 6d 75 6d 00 05 00 0b 00 00 |...infimum......| 00000070 73 75 70 72 65 6d 75 6d 07 08 00 00 10 00 29 00 |supremum......).| 00000080 01 00 00 00 00 07 40 c0 00 00 01 63 01 10 50 45 |[email protected]| 00000090 4e 45 4c 4f 50 45 47 55 49 4e 45 53 53 43 f2 f5 |NELOPEGUINESSC..| 000000a0 a9 08 04 00 00 18 00 26 00 02 00 00 00 00 07 40 |.......&.......@| 000000b0 c0 00 00 01 63 01 1a 4e 49 43 4b 57 41 48 4c 42 |....c..NICKWAHLB| 000000c0 45 52 47 43 f2 f5 a9 05 02 00 00 20 00 21 00 03 |ERGC....... .!..| 000000d0 00 00 00 00 07 40 c0 00 00 01 63 01 24 45 44 43 |[email protected].$EDC| 000000e0 48 41 53 45 43 f2 f5 a9 05 08 04 00 28 00 27 00 |HASEC.......(.'.|
Checklist before Launch
✓InnoDB pages (credit to stream_parser) ✓Table Structure (credit to sys_parser) ✓index_id (InnoDB dictionary)
Load Table Back
# mysql sakila < actor.sql
# mysql sakila < dumps/default/actor.sql
# mysql sakila -e "SELECT COUNT(*) FROM actor" +----------+ | COUNT(*) | +----------+ | 200 | +----------+
29
https://twindb.com
Follow Us
https://twindb.com