1. Recovery After DROP TABLE, With Innodb_file_per_table ON

Recovery After DROP TABLE, With Innodb_file_per_table ON

UPDATE (01/01/2017): We stopped further development of undrop-for-innodb and don’t support its open source versions anymore.

Introduction

In the previous post, we described a situation when the TwinDB recovery toolkit can be used to recover an accidentally dropped table with innodb_file_per_table=OFF.
In this post, we’ll show how to recover MySQL tables or databases if innodb_file_per_table is ON. So, let’s assume that the mysql server is set to innodb_file_per_table=ON. This option tells InnoDB to store each table with a user in a separate data file.

For the recovery test, we’ll use the same sakila database that we used in the previous post.

root@test:/var/lib/mysql/sakila# ls -la
total 23468
drwx------ 2 mysql mysql     4096 Jul 15 04:26 ./
drwx------ 6 mysql mysql     4096 Jul 15 04:26 ../
-rw-rw---- 1 mysql mysql     8694 Jul 15 04:26 actor.frm
-rw-rw---- 1 mysql mysql   114688 Jul 15 04:26 actor.ibd
-rw-rw---- 1 mysql mysql     2871 Jul 15 04:26 actor_info.frm
-rw-rw---- 1 mysql mysql     8840 Jul 15 04:26 address.frm
-rw-rw---- 1 mysql mysql   163840 Jul 15 04:26 address.ibd
-rw-rw---- 1 mysql mysql     8648 Jul 15 04:26 category.frm
-rw-rw---- 1 mysql mysql    98304 Jul 15 04:26 category.ibd
-rw-rw---- 1 mysql mysql     8682 Jul 15 04:26 city.frm
-rw-rw---- 1 mysql mysql   114688 Jul 15 04:26 city.ibd
-rw-rw---- 1 mysql mysql     8652 Jul 15 04:26 country.frm
-rw-rw---- 1 mysql mysql    98304 Jul 15 04:26 country.ibd
...
-rw-rw---- 1 mysql mysql       36 Jul 15 04:26 upd_film.TRN
root@test:/var/lib/mysql/sakila#

Note the two files related to the table country: country.frm, country.ibd.
We will drop this table and try to recover it. First, we take the checksum and preview the records this table contains:

Database changed
mysql> SELECT * FROM country LIMIT 10;
+------------+----------------+---------------------+
| country_id | country        | last_update         |
+------------+----------------+---------------------+
|          1 | Afghanistan    | 2006-02-15 04:44:00 |
|          2 | Algeria        | 2006-02-15 04:44:00 |
|          3 | American Samoa | 2006-02-15 04:44:00 |
|          4 | Angola         | 2006-02-15 04:44:00 |
|          5 | Anguilla       | 2006-02-15 04:44:00 |
|          6 | Argentina      | 2006-02-15 04:44:00 |
|          7 | Armenia        | 2006-02-15 04:44:00 |
|          8 | Australia      | 2006-02-15 04:44:00 |
|          9 | Austria        | 2006-02-15 04:44:00 |
|         10 | Azerbaijan     | 2006-02-15 04:44:00 |
+------------+----------------+---------------------+
10 rows in set (0.00 sec)

mysql> CHECKSUM TABLE country;
+----------------+------------+
| Table          | Checksum   |
+----------------+------------+
| sakila.country | 3658016321 |
+----------------+------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM country;
+----------+
| COUNT(*) |
+----------+
|      109 |
+----------+
1 row in set (0.00 sec)

mysql>

Accidental Drop

Now, we drop the table and look for files related to the table. As you can see from the list, files with country table data are no longer there:

mysql> SET foreign_key_checks=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE country;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> exit
Bye
root@test:~# cd /var/lib/mysql/sakila/
root@test:/var/lib/mysql/sakila# ls -la
total 23360
drwx------ 2 mysql mysql     4096 Jul 15 04:33 ./
drwx------ 6 mysql mysql     4096 Jul 15 04:26 ../
-rw-rw---- 1 mysql mysql     8694 Jul 15 04:26 actor.frm
-rw-rw---- 1 mysql mysql   114688 Jul 15 04:26 actor.ibd
-rw-rw---- 1 mysql mysql     2871 Jul 15 04:26 actor_info.frm
-rw-rw---- 1 mysql mysql     8840 Jul 15 04:26 address.frm
-rw-rw---- 1 mysql mysql   163840 Jul 15 04:26 address.ibd
-rw-rw---- 1 mysql mysql     8648 Jul 15 04:26 category.frm
-rw-rw---- 1 mysql mysql    98304 Jul 15 04:26 category.ibd
-rw-rw---- 1 mysql mysql     8682 Jul 15 04:26 city.frm
-rw-rw---- 1 mysql mysql   114688 Jul 15 04:26 city.ibd
-rw-rw---- 1 mysql mysql       40 Jul 15 04:26 customer_create_date.TRN
-rw-rw---- 1 mysql mysql     8890 Jul 15 04:26 customer.frm
-rw-rw---- 1 mysql mysql   196608 Jul 15 04:26 customer.ibd
-rw-rw---- 1 mysql mysql     1900 Jul 15 04:26 customer_list.frm
-rw-rw---- 1 mysql mysql      297 Jul 15 04:26 customer.TRG
-rw-rw---- 1 mysql mysql       65 Jul 15 04:26 db.opt
...
-rw-rw---- 1 mysql mysql       36 Jul 15 04:26 upd_film.TRN
root@ALtestTwinDB:/var/lib/mysql/sakila#

Recovery After DROP TABLE

This situation is a little bit more complex, since we need to recover a deleted file. If the database server has active communication with HDD, it’s possible that the deleted file will be rewritten with other data. Therefore, it’s critical to stop the server and to mount the partition read-only. But for the test we will just stop mysql service and continue with the recovery.

root@test:/var/lib/mysql/sakila# service mysql stop
mysql stop/waiting

Despite the fact that user data is stored in separate files per each table, data dictionary is still stored in the ibdata1 file. That’s why we need to use stream_parser for /var/lib/mysql/ibdata1. For the details of usage, please refer to the post Recover after DROP TABLE.
In order to find table_id and index_id for the table country, we will use the dictionary stored in SYS_TABLES and SYS_INDEXES. We will fetch the data from the ibdata1 file. The dictionary records are always in REDUNDANT format, therefore we specify option -4. We assume that the mysql server has flushed changes to the disk, so we add the -D option which means “find deleted records”. SYS_TABLES information is stored in the file with index_id=1 which is file pages-ibdata1/FIL_PAGE_INDEX./0000000000000001.page:

root@test:~/undrop-for-innodb# ./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t ./dictionary/SYS_TABLES.sql | grep country
000000000CDC  62000001960684  SYS_TABLES      "sakila/country"        228     3       1       0       0       ""      88
000000000CDC  62000001960684  SYS_TABLES      "sakila/country"        228     3       1       0       0       ""      88
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/home/asterix/undrop-for-innodb/dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_TABLES\t' (`NAME`, `ID`, `N_COLS`, `TYPE`, `MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`);

root@test:~/undrop-for-innodb#

We can see that the country table has table_id=228. The next step would be to find the PRIMARY index of the table country. For this purpose, we take the records of the SYS_INDEXES table from the file 0000000000000003.page (SYS_INDEXES table contains mapping between table_id and index_id). The structure of SYS_INDEXES is added to the tool with a -t option.

root@test:~/undrop-for-innodb# ./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t ./dictionary/SYS_INDEXES.sql | grep 228
000000000CDC    620000019605A8  SYS_INDEXES     228     547     "PRIMARY"       1       3       88      4294967295
000000000CDC    620000019605A8  SYS_INDEXES     228     547     "PRIMARY"       1       3       88      4294967295
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/home/asterix/undrop-for-innodb/dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXES\t' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`);

root@test:~/undrop-for-innodb#

Almost There!

We can  see that index_id of the dropped table country is 547. The following step is different from the step we took in the case with innodb_file_per_table=OFF. Since there is no file with data available, we’ll scan through the whole storage device as a raw device and look for data that fits the expected structure of the database pages. By the way, you can use this approach with corrupted data files. If some data is corrupted, the recovery tool can perform partial data recovery. In the options of the tool we specify device name and device size (can be approximate).

root@test:~/undrop-for-innodb#./stream_parser -f /dev/vda -t 20000000k
Opening file: /dev/vda
File information:

ID of device containing file:            5
inode number:                         6411
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:            1405411377 Tue Jul 15 04:02:57 2014
time of last modification:      1404625158 Sun Jul  6 01:39:18 2014
time of last status change:     1404625158 Sun Jul  6 01:39:18 2014
total size, in bytes:                    0 (0.000 exp(+0))

Size to process:               20480000000 (19.073 GiB)
Worker(0): 1.06% done. 2014-07-15 04:57:37 ETA(in 00:01:36). Processing speed: 199.848 MiB/sec
Worker(0): 2.09% done. 2014-07-15 04:57:37 ETA(in 00:01:35). Processing speed: 199.610 MiB/sec
Worker(0): 3.11% done. 2014-07-15 04:59:13 ETA(in 00:03:09). Processing speed: 99.805 MiB/sec
...
Worker(0): 97.33% done. 2014-07-15 04:57:15 ETA(in 00:00:05). Processing speed: 99.828 MiB/sec
Worker(0): 98.35% done. 2014-07-15 04:57:20 ETA(in 00:00:06). Processing speed: 49.941 MiB/sec
Worker(0): 99.38% done. 2014-07-15 04:57:17 ETA(in 00:00:01). Processing speed: 99.961 MiB/sec
All workers finished in 77 sec
root@test:~/undrop-for-innodb#

Stream parser stores the resulted files with pages to the folder pages-vda (name derived from the title of the device). We can see that the necessary index is present in the files.

root@test:~/undrop-for-innodb/pages-vda/FIL_PAGE_INDEX# ls -la | grep 547
-rw-r--r-- 1 root root    32768 Jul 15 04:57 0000000000000547.page
root@test:~/undrop-for-innodb/pages-vda/FIL_PAGE_INDEX#

We will look for the data in the file 0000000000000547.page. Utility c_parser provides us with information according to expected table structure, supplied with -t option.

root@test:~/undrop-for-innodb# ./c_parser -6f pages-vda/FIL_PAGE_INDEX/0000000000000547.page -t sakila/country.sql |  head -5
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (109 109)
000000000C4B    F30000038C0110  country 1       "Afghanistan"   "2006-02-15 04:44:00"
000000000C4B    F30000038C011B  country 2       "Algeria"       "2006-02-15 04:44:00"
000000000C4B    F30000038C0126  country 3       "American Samoa"        "2006-02-15 04:44:00"
000000000C4B    F30000038C0131  country 4       "Angola"        "2006-02-15 04:44:00"
root@test:~/undrop-for-innodb#

The result looks valid, so we prepare files for loading data back to the database. The LOAD DATA INFILE command with necessary options is sent to stderr device.

root@test:~/undrop-for-innodb# ./c_parser -6f pages-vda/FIL_PAGE_INDEX/0000000000000547.page -t sakila/country.sql > dumps/default/country 2> dumps/default/country_load.sql

root@test:

Loading Data Back To The Database

Now, we’re going to load data back to the database. Before loading the data, we create an empty structure of the table country:

root@test:~/undrop-for-innodb# service mysql start
mysql start/running, process 31035
root@test:~/undrop-for-innodb# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.5.37-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

mysql> use sakila;
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> source sakila/country.sql
Query OK, 0 rows affected (0.00 sec)
...

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> show create table country\G
*************************** 1. row ***************************
       Table: country
Create Table: CREATE TABLE `country` (
  `country_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `country` varchar(50) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB AUTO_INCREMENT=110 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

And now we are loading data itself.

root@testB:~/undrop-for-innodb# mysql --local-infile -uroot -p
Enter password:
...
mysql> USE sakila;
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> source dumps/default/country_load.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 327 rows affected (0.00 sec)
Records: 218  Deleted: 109  Skipped: 0  Warnings: 0
mysql>

Checking Data Quality

So, the last thing that remaining is to check the quality of recovered data. We’ll preview several records, calculate the total number of records and checksum.

mysql> SELECT COUNT(*) FROM country;
+----------+
| COUNT(*) |
+----------+
|      109 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM country LIMIT 5;
+------------+----------------+---------------------+
| country_id | country        | last_update         |
+------------+----------------+---------------------+
|          1 | Afghanistan    | 2006-02-15 04:44:00 |
|          2 | Algeria        | 2006-02-15 04:44:00 |
|          3 | American Samoa | 2006-02-15 04:44:00 |
|          4 | Angola         | 2006-02-15 04:44:00 |
|          5 | Anguilla       | 2006-02-15 04:44:00 |
+------------+----------------+---------------------+
5 rows in set (0.00 sec)

mysql> CHECKSUM TABLE country;
+----------------+------------+
| Table          | Checksum   |
+----------------+------------+
| sakila.country | 3658016321 |
+----------------+------------+
1 row in set (0.00 sec)

mysql>

So, we’re in luck. Despite the fact that we used the system volume (which is not the recommended practice) for mysql data, and that we haven’t re-mounted partition as read-only (and other processes were continuing to perform writing to the disk), we managed to recover all the records. Calculated checksum after the recovery (3658016321) is equal to the checksum taken before the drop (3658016321).

Have a question? Ask the experts!

Previous Post Next Post