Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 21 10 Older Entries

Displaying posts with tag: data recovery (reset)

Presenting TwinDB Data Recovery Toolkit on #SFMySQL Meetup
+0 Vote Up -0Vote Down

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

  [Read more...]
Recover Table Structure From InnoDB Dictionary
+1 Vote Up -0Vote Down

When a table gets dropped MySQL removes respective .frm file. This post explain how to recover table structure if the table was dropped.

You need the table structure to recover a dropped table from InnoDB tablespace. The B+tree structure of InnoDB index doesn’t contain any information about field types. MySQL needs to know that in order to access records of InnoDB table. Normally MySQL gets the table structure from .frm file. But when MySQL drops a table the respective frm file removed too.

Fortunately there is one more place where MySQL keeps the tables structure . It is the InnoDB dictionary.

InnoDB dictionary is a set of …

  [Read more...]
Take image from corrupted hard drive
+0 Vote Up -0Vote Down

There are at least two cases when it makes sense to take an image from a corrupted hard drive as soon as possible: disk hardware errors and corrupted filesystem. Faulty hard drives can give just one chance to read a block, so there is no time for experiments. The similar picture with corrupted filesystems. Obviously something went wrong, it’s hard to predict how the operating system will behave next second and whether it will cause even more damage.

Save disk image to local storage

Probably the best and fastest way is to plug the faulty disk into a healthy server and save the disk image locally:

# dd if=/dev/sdb …
  [Read more...]
Recover Corrupt MySQL Database
+2 Vote Up -0Vote Down

The unDROP for InnoDB tool can used to recover corrupt MySQL database. In this post we will show how to repair MySQL database if its files became corrupted and even innodb_force_recovery=6 doesn’t help.

The corruption of InnoDB tablespace may be caused by many reasons. A dying hard drive can write garbage, thus page checksum will be wrong. InnoDB then reports to the error log:

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4.

  [Read more...]
Recover after DROP TABLE. Case 2
+1 Vote Up -0Vote Down

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 …

  [Read more...]
Recover after DROP TABLE. Case 1
+0 Vote Up -0Vote Down

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 …

  [Read more...]
Recover InnoDB dictionary
+0 Vote Up -0Vote Down

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 …

  [Read more...]
UnDROP tool for InnoDB
+0 Vote Up -0Vote Down

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 …
  [Read more...]
ibdconnect and secondary keys
+0 Vote Up -0Vote Down

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

… …








  [Read more...]
InnoDB dictionary
+0 Vote Up -0Vote Down

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 …

  [Read more...]
Showing entries 1 to 10 of 21 10 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.