Showing entries 1 to 8
Displaying posts with tag: drop database (reset)
Exploring Metadata Locks with gdb - Double Checking the Initial Results

Some results in my initial post in this series led me to questions that I'll try to answer here. First of all, I noted that SELECT from a single table ended up with just one metadata lock request:

(gdb) b MDL_request::init
Breakpoint 1 at 0x648f13: file /usr/src/debug/percona-server-5.6.27-76.0/sql/, line 1266.
Breakpoint 2 at 0x648e70: file /usr/src/debug/percona-server-5.6.27-76.0/sql/, line 1245.
warning: Multiple breakpoints were set.
Use the "delete" command to delete unwanted breakpoints.
(gdb) c
[Switching to Thread 0x7ff224c9f700 (LWP 2017)]

Breakpoint 2, MDL_request::init (this=0x7ff1fbe425a8,
    mdl_namespace=MDL_key::TABLE, db_arg=0x7ff1fbe421c8 "test",

[Read more]
How to evaluate if MySQL table can be recovered

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 …

[Read more]
How to handle wrong page type in external pages

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.

[Read more]
Presenting TwinDB Data Recovery Toolkit on #SFMySQL Meetup

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 …

[Read more]
Recover Table Structure From InnoDB Dictionary

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 …

[Read more]
Recover after DROP TABLE. Case 2


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 …
[Read more]
Recover after DROP TABLE. Case 1 3


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; …
[Read more]
Removing all databases from a MySQL instance

Many out there will have different ideas about this, some using procs, some using a function, others using a shell script. Well I didn’t want to spend much time on it so decided a group_concat(concat would be enough.
There is no genius, rather laziness :) but what if you have a hundred databases and you want to drop them all?

mysql Thu Mar  3 13:50:06 2011 > pager sed 's/,/ /g'
PAGER set to 'sed 's/,/ /g''
mysql Thu Mar  3 13:50:32 2011 > select group_concat(concat('drop database ',SCHEMA_NAME,';')) from information_schema.schemata where SCHEMA_NAME !='mysql' and SCHEMA_NAME !='information_schema';
| group_concat(concat('drop database ' SCHEMA_NAME ';')) …
[Read more]
Showing entries 1 to 8