To recover a dropped or corrupt table with Percona Data Recovery Tool for InnoDB you need two things: media with records(ibdata1, *.ibd, disk image, etc.) and a table structure. Indeed, there is no information about the table structure in an InnoDB page. Normally we either recover the structure from .frm files or take it from [...]
MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
As part of Percona Remote DBA for MySQL service we recognize that reliable backups are one of the most important things we can bring to the table. In my experience handling emergencies, the single worst thing that can happen is finding out you don’t have backups available when some sort of data loss or catastrophic [...]
Percona Toolkit Webinar followup Q&A
December 19, 2012 By 1 Comment
First, a thank you to everyone who attended the webinar Today, I appreciate your time and nice comments. As promised, here are answers to questions that couldn’t be answered during the talk: Q: How do you install the tools? The manual has full details, but it’s important to know that the latest release for [...]
Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
I had a customer recently who needed to reduce their database size on disk quickly without a lot of messy schema redesign and application recoding. They didn’t want to drop any actual data, and their index usage was fairly high, so we decided to look for unused indexes that could be removed. Collecting data It’s [...]
Recovery after DROP & CREATE
August 22, 2012 By 1 Comment
In a very popular data loss scenario a table is dropped and empty one is created with the same name. This is because mysqldump in many cases generates the “DROP TABLE” instruction before the “CREATE TABLE”:
1 2 3 4 5 6 7 8 9 10 11 12 | DROP TABLE IF EXISTS `actor`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`) ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; |
If there were no subsequent CREATE TABLE the recovery would be trivial. Index_id of the PRIMARY index of [...]
On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
The MySQL manual tells us that regardless of whether or not we use “SET FOREIGN_KEY_CHECKS=0″ before making schema changes, InnoDB will not allow a column referenced by a foreign key constraint to be modified in such a way that the foreign key will reference a column with a mismatched data type. For instance, if we [...]
Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By 3 Comments
LVM snapshots is one powerful way of taking a consistent backup of your MySQL databases – but did you know that you can now restore directly from a snapshot (and binary logs for point in time recovery) in case of that ‘Oops’ moment? Let me show you quickly how. This howto assumes that you already [...]
How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 Comments
Sometimes we need to restore only some tables from a full backup maybe because your data loss affect a small number of your tables. In this particular scenario is faster to recover single tables than a full backup. This is easy with MyISAM but if your tables are InnoDB the process is a little bit [...]
Connecting orphaned .ibd files
May 13, 2011 By 19 Comments
There are two ways InnoDB can organize tablespaces. First is when all data, indexes and system buffers are stored in a single tablespace. This is typicaly one or several ibdata files. A well known innodb_file_per_table option brings the second one. Tables and system areas are split into different files. Usually system tablespace is located in [...]
Shard-Query EC2 images available
May 11, 2011 By 2 Comments
Infobright and InnoDB AMI images are now available There are now demonstration AMI images for Shard-Query. Each image comes pre-loaded with the data used in the previous Shard-Query blog post. The data in the each image is split into 20 “shards”. This blog post will refer to an EC2 instances as a node from here [...]
