Recently, we had an edge case where a MySQL slave went out-of-sync but it couldn’t be rebuilt from scratch. The slave was acting as a master server to some applications and it had data was being written to it. It was a design error, and this is not recommended, but it happened. So how do you synchronize the data in this circumstance? This blog post describes the steps taken to recover from this situation. The tools used to recover the slave were pt-slave-restart, pt-table-checksum, pt-table-sync and mysqldiff.

Scenario

To illustrate this …

We work on data recoveries quite often. In many cases, we recover table structures from the .frm files because there was no backup available. There is already a great blog post by my colleague Miguel Ángel Nieto about how we can recover structures from .frm files using MySQL utilities.

This works pretty well and we prefer to run mysqlfrm with the “–server” option to get all possible information from a .frm file. However, this option expects that MySQL is up and running so that mysqlfrm can spawn a new MySQL instance, and run the structure recovery there.

Recently I came across a tool that makes this job easier. The name of tool is …

What are odds MySQL table can be recovered?

This is the most asked question. Every single customer asks if their MySQL table can be recovered. Although it’s not possible to answer that with 100% confidence there are ways to estimate recovery chances. I will describe few tricks.

Generally speaking, if data is on media there are high odds TwinDB data recovery toolkit can fetch it. Where to look for depends on accident type.

Online MySQL data recovery toolkit

On our Data Recovery portal you can upload an .ibd file and check if the InnoDB tablespace contains any good records. The table space may be corrupt. The tool should handle that.

DROP TABLE or DATABASE with innodb_file_per_table=OFF

If …

I’ll be presenting Undrop for InnoDB data recovery toolkit on Percona Live 2015. The conference takes place in Santa Clara on 13-16 April.

You may wonder why do I care if I plan to drop none of my production databases. To name few reasons:

• Taxes, death and data loss are inevitable
• Good knowledge of how InnoDB stores data on disk help to design fast and scalable databases
• The toolkit can be used to troubleshoot bugs in InnoDB.

So bookmark the session, it’s going to be a great discussion.

Undrop for InnoDB | Percona Live MySQL Conference 2015

The post Presenting Undrop for InnoDB Toolkit on Percona Live 2015 appeared first on …

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 …

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 tables where InnoDB keeps some information about the tables. I reviewed them in details is a separate InnoDB Dictionary post earlier. After the DROP InnoDB deletes records related to the dropped table from the dictionary. So we need …

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=noerror

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

conv=noerrror tells dd to continue reading even if …

The unDROP for InnoDB tool can be 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 to the same tablespace. That leads to the corruption too. Sometimes power reset corrupts not only InnoDB files, but file system becomes unusable for the operating system.

InnoDB …

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 …