Introduction

Human mistakes are inevitable. Wrong “DROP DATABASE” or “DROP TABLE” may destroy critical data on the MySQL server. Backups would help however they’re not always available. This situation is frightening but not hopeless. In many cases it is possible to recover almost all the data that was in the database or table.
Let’s look how we can do it. The recovery plan depends on whether InnoDB kept all data in a single ibdata1 or each table had its own tablespace . In this post we will consider the case innodb_file_per_table=OFF. This option assumes that all tables are stored in a common file, usually located at /var/lib/mysql/ibdata1.

Wrong action – table deletion

For our scenario we will use test database sakila that is shipped together with the tool.
Suppose we drop my mistake table actor:

mysql> SELECT * FROM actor LIMIT 10; …

Why do we need to recover InnoDB dictionary

c_parser is a tool from TwinDB recovery toolkit that can read InnoDB page and fetch records out of it. Although it can scan any stream of bytes recovery quality is higher when you feed c_parser with pages that belong to the PRIMARY index of the table. All InnoDB indexes have their identifiers a.k.a. index_id. The InnoDB dictionary stores correspondence between table name and index_id. That would be reason number one.

Another reason – it is possible to recover table structure from the InnoDB dictionary. When a table is dropped MySQL deletes respective .frm file. If you had neither backups nor table schema it becomes quite a challenge to recover the table structure. This topic however deserves a separate post which I write some other day.

Let’s assume you’re convinced …

While working on data recovery cases I had been maintaining Data Recovery Tool for InnoDB. It proved to be useful in most of disaster scenarios, but still has some limitations:

• page_parser is slow. It scans a stream of bytes in order to find InnoDB pages. Being single-threaded it couldn’t leverage power of modern multi CPU servers. The same time customer’s databases tend to grow. It’s not uncommon to see over terabyte databases (I recovered once as large as 100 terabytes database!). Taking this into account recovery time quite often was painfully long.
• You have to recompile constraints_parser for every table. It takes long if there are many tables. To make it worse constraints_parser isn’t very smart about memory allocation, so a binary required about 250MB. Thus, to …

ibdconnect is a tool to connect an ibd file to a foreign ibdata file. it’s important to understand how it works as secondary keys may cause interesting behavior.

Here is a table with a unique secondary key:

CREATE TABLE `t1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`value` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uni_value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

When I update InnoDB dictionary with ibdconnect it all goes well:

# ./ibdconnect -f  t1.idb -o  /var/lib/mysql/ibdata1 -d test -t t1

…

Updating test/t1 (table_id 13) with id 0xA3060000
SYS_TABLES is updated successfully
…

SYS_INDEXES is updated successfully
ibdconnect modifies content of ibdata1, so checksums are wrong now. innochecksum_changer from …

Why Does InnoDB Need Dictionary

InnoDB dictionary is a set of internal tables InnoDB uses to maintain various information about user tables. It serves as API between a human and the database. While the humans refer tables by their names, InnoDB works with integer identifiers. The dictionary stores correspondence between table name and index_id.

The dictionary tables are normal InnoDB tables, but they’re not visible for a user. However some versions of MySQL provide read-only access to the dictionary in information_schema database.

The dictionary is stored in ibdata1. Root page of SYS_TABLES, for example, has id 8, so it’s eighth page from the beginning of ibdata1.

The dictionary pages are in REDUNDANT format even if you use MySQL 5.6. More about record formats I will write in future posts, I hope. For now it’s enough to mention REDUNDANT is the oldest record format. It was available since 4.0 and was the …

Table structures are stored in .frm files and in the InnoDB Data Dictionary. Sometimes, usually in data recovery issues, we need to recover those structures to be able to find the lost data or just to recreate the tables.

There are different ways to do it and we’ve already written about it in this blog. For example, we can use the data recovery tools to recover table structures from InnoDB Dictionary or from the .frm files using a MySQL Server. This blog post will be an update of that last one. I will show you how to easily recover the structure from a .frm file and in some cases even …

A few years ago Yves Trudeau and Aleksandr Kuzminsky wrote posts about different ways for recovering orphaned .ibd files:

• Connecting orphaned .ibd files
• A recovery trivia or how to recover from a lost ibdata1 file

Today I want to show you how to do that in more easy and quick way. In my example I’ll restore a “payment.ibd” file (payment table) from Sakila DB on a server with MySQL 5.5 (but with …

In case you missed it, MySQL Utilities 1.3.0 (alpha) was released last week.  MySQL Utilities is a component of MySQL Workbench, but it’s been broken out into a separate download allowing command-line users access unencumbered by a pretty GUI interface.  Plus, it has some new features – most importantly to me, a utility (mysqlfrm) which can read .FRM files and produce CREATE TABLE statements as a result.  This will be very useful for recovery processes, in conjunction with InnoDB transportable tablespaces in situations where the InnoDB system tablespace is corrupted, and you don’t know the exact table definition.

…

I’ve noted previously that the new transportable tablespaces for InnoDB in MySQL 5.6 are a big step forward, and newly-released 5.6.9-rc makes importing tablespaces a bit easier.  In previous versions, you had to have a .cfg file to import the InnoDB tablespace.  That file is produced during FLUSH TABLE <tbl> FOR EXPORT operations, and contains InnoDB metadata that’s not contained in the .ibd tablespace file itself.  I filed a feature request requesting the .cfg file be made optional, and Sunny implemented it:

mysql> create table tt (a INT PRIMARY KEY, b VARCHAR(10), KEY(b)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.33 sec)

mysql> …

If you’re anything like me,  your initial reaction upon hearing about transportable tablespaces for InnoDB in 5.6 was to imagine it like MyISAM, where you can copy the .frm, .myi and .myd files around to your heart’s content, and everything will be great.  You might have read Sunny’s excellent blog, and realized that there’s a bit more to it than that – you have to explicitly prepare the tablespace for copying using FLUSH TABLES FOR EXPORT.  That’s perfectly acceptable for the bulk of use cases, such as single-table InnoDB backups, and opens up exciting new possibilities for moving or copying InnoDB data at the filesystem level.

But for situations where the need is a little different, you might really dug into it and start to wonder about the .cfg files produced during the FLUSH TABLES FOR EXPORT operation.  …