Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 19

Displaying posts with tag: data recovery (reset)

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 of=/path/on/sda/faulty_disk.img  conv=noerrror

Where /dev/sdb is the faulty disk and faulty_disk.img is the image on

  [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.

MySQL is well know for poor start-up script. A simple upgrade procedure may end up with two mysqld processes writing

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


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

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


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

  [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 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

  [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 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
  [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:

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`value` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
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

  [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 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

  [Read more...]
How to recover table structure from .frm files with MySQL Utilities
+1 Vote Up -0Vote Down

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

  [Read more...]
How to recover an orphaned .ibd file with MySQL 5.6
+0 Vote Up -0Vote Down

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

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

  [Read more...]
MySQL Utilities meets the world’s ugliest table
Employee +4 Vote Up -0Vote Down

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

  [Read more...]
Smarter InnoDB transportable tablespace management operations
Employee +1 Vote Up -0Vote Down

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
  [Read more...]
Understanding InnoDB transportable tablespaces in MySQL 5.6
Employee +5 Vote Up -0Vote Down

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

  [Read more...]
With InnoDB’s Transportable Tablespaces, Recovering Data from Stranded .ibd Files is a Thing of the Past
+2 Vote Up -0Vote Down

Being a data recovery specialist and having recovered countless GBs of corrupted, and/or stranded, InnoDB data in my days, I am very happy to hear about the new InnoDB Transportable Tablespaces coming in MySQL 5.6!

Back in the day, if you had a stranded .ibd file (the individual InnoDB data file with –innodb-file-per-table option), you basically had nothing (even though that file contained all of the data). This was because unless you had the original instance that that particular .ibd file (table) originated from, there was no way to load it, import, or dump from it. So it was not of much use, though all the data was *right* there.

Thus I created the method of Recovering an InnoDB table from only an .ibd file (I should note that this was before

  [Read more...]
On Ma.gnolia, and data recovery
+0 Vote Up -0Vote Down

There’s a good podcast from Chris Messina and Larry Halff, about what really happened at Ma.gnolia. If you’re at all interested in what happened (i.e. how did they lose all their bookmark data), don’t hesitate to watch the video. I took some quick notes:

  • half a terabyte database file got corrupted
  • a mysql 5 database
  • everything was running even though there was corruption, and eventually, the site went down
  • backup system also failed, as it didn’t backup the data from mysql
  • backup was just backing up corrupted data (file sync over a firewire network was the backup mechanism)
  • a Rails application, he now recommends clouds over running your own infrastructure for startups
  [Read more...]
MySQL Toolkit version 1254 released
+0 Vote Up -0Vote Down

This release fixes several bugs introduced in the last release as I replaced untested code with tested code -- how ironic! Actually, I knew that was virtually guaranteed to happen. Anyway, all the bugs you've helped me find are now fixed. I also fixed a long-standing bug in MySQL Table Sync, which I am otherwise trying to touch as little as possible for the time being. (Remember to contribute to the bounty, and get your employer to contribute as well, so I can do some real work on it in the next month or so!)

The other big news is that the parallel dump and restore tools are now 1.0.0 because I consider them feature-complete. I have put the most work into tab-separated dumps. These two tools can do something MySQL AB's tools can't currently do: restore data before creating

  [Read more...]
Introducing MySQL Parallel Restore
+0 Vote Up -0Vote Down

The new release of MySQL Toolkit (version 1051) updates MySQL Parallel Dump in minor ways, but more importantly, it adds MySQL Parallel Restore. Read on for details.

MySQL Toolkit version 1030 released
+0 Vote Up -0Vote Down

This release of MySQL Toolkit updates MySQL Parallel Dump. Together you and I found a few bugs in it (table locking, argument quoting, exit status code). The restore utility is in progress.

MySQL Toolkit version 1011 released
+0 Vote Up -0Vote Down

MySQL Toolkit version numbers are based on Subversion revision number. This release is the first past the 1,000-commit milestone. It also marks several days of being in Sourceforge's top 100 most active projects. It has been in the top 300 for a couple of months, and the top 1000 for, um, a long time. While I would hasten to say I'm not a popularity-contest-focused person, it's rewarding to see that people think this project is important and useful.

This release of MySQL Toolkit updates MySQL Parallel Dump. I had been using it on a relatively small server; yesterday I took a deep breath and started using it to generate backups from a large server with lots of data and lots of queries. Of course I found a couple bugs and decided I needed more functionality and error handling. The major new functionality is for efficiency; it defers locking as late as possible

  [Read more...]
Showing entries 1 to 19

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.