MySQL Recover after DROP TABLE, innodb_file_per_table is ON

Posted by PDSERVICE on Nov 07, 2019 In

Introduction

In the previous post we described the situation when TwinDB recovery toolkit can be used to recover accidentaly dropped table in the case innodb_file_per_table=OFF setting.

In this post we will show how to recover MySQL table or database in case innodb_file_per_table is ON. So, let’s assume that mysql server has setting innodb_file_per_table=ON. This option tells InnoDB to store each table with user in a separate data file.

We will use for recovery test the same database sakila, that was used in the previous post.

root@test:/var/lib/mysql/sakila# ll

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 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 containing in this table:

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 will drop the table and look for the files, related to the table. As you can see from the list, files with country table data are gone:

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# ll

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#

Recover after DROP TABLE

This situation is a little bit more complex, since we need to recover deleted file. If the database server has active communication with HDD, it is possible that deleted file will be rewritten by another data. Therefore it is 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 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 ibdata1 file. The dictionary records are always in REDUNDANT format, therefore we specify option -4. We assume that mysql server has flushed changes to the disk, so we add option -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

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 country table has table_id=228. Next step we will take is to find PRIMARY index of table country. For this purpose we will take records of 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 -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

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#

We can see that index_id of the dropped table country is 547. The following step is different from the step we took in case of innodb_file_per_table=OFF. Since there is no file with data available, we will scan through all the storage device as raw device and look for data that fit in expected structure of the database pages. By the way, this approach can be taken in case we have corrupted data files. If some data is corrupted, recovery tool can perform partial data recovery. In the options of the tool we specify name of the device 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 necessary index is present in the files.

root@test:~/undrop-for-innodb/pages-vda/FIL_PAGE_INDEX# ll | 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 provide us 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 will prepare files for loading data back to the database. 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:

Load data back to the database

We are going to load data back to the database. Before loading the data we create empty structure of 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)

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 remained is to check the quality of recovered data. We will preview several records, calculate 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 are lucky. Despite the facts that we used for mysql data the system volume (which is not the recommended practice) and that we have not 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).

You are here

MySQL Recover after DROP TABLE, innodb_file_per_table is ON

MySQL Recover after DROP TABLE, innodb_file_per_table is ON