Author Andriy Lysyuk.

Introduction

In the previous post, we described a situation when the UnDrop For InnoDB 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. 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 …

Author Andriy Lysyuk.

Introduction

Unfortunately, human mistakes are inevitable. That’s how life is. Wrong DROP DATABASE or DROP TABLE may destroy critical data on the MySQL server. Obviously, backups would help, however they’re not always available. This situation is frightening but not hopeless. In many cases it’s possible to recover almost all the data that was in the database or table.
Let’s look at 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 when innodb_file_per_table=OFF. This option assumes that all tables are stored in a common file, usually located at /var/lib/mysql/ibdata1.

One Wrong Move, And The Table’s Gone

For our scenario, we use …

First step of successful MySQL data recovery is to find InnoDB pages with your data. Let’s call it first, because prerequisite steps are already done.

InnoDB page type is a two bytes integer stored in the header of a page. For MySQL data recovery two are important:

• FIL_PAGE_INDEX. Pages of this type are nodes of B+ Tree index where InnoDB stores a table.
• FIL_PAGE_TYPE_BLOB. So called external pages, where InnoDB keeps long values of BLOB or TEXT type.

stream_parser reads a stream of bytes, finds InnoDB pages and sorts them per type, per index or page id. It applies sophisticated algorithms tailored for particular page type. Of course, it assumes that page type in the header corresponds to the content of the page, otherwise it will ignore the page.

…

On 5 November, I’ll be speaking at #SFMySQL Meetup about Data Recovery Software for MySQL

Add Slipped & DROP’d your TABLE? Recover w/TwinDB’s Undrop for InnoDB toolkit to your calendar.

There will be a demo and if you want to try to undrop a table yourself bring in a laptop with Linux.

Download the latest revision of the TwinDB Data Recovery Toolkit from LaunchPad.

Internet connection isn’t necessary, but make sure you install dependencies: gcc, make, flex, bison.

The post Presenting TwinDB Data Recovery …

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 …

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; …