InnoDB executes a lot of transactions for MySQL users. And users and technical experts in the InnoDB community exchange lots of information about InnoDB … transacting in ideas … all the time. A great opportunity to participate in some of these transactions is the upcoming MySQL Conference and Expo.

Does your InnoDB-based application need more transactions to execute more quickly and reliably? Come hear these noted speakers present information that can help you get the most out of InnoDB:

• Distributed InnoDB Caching w/memcached — Matthew Yonkovit & Yves Trudeau (MySQL), Tue 2pm
• InnoDB database recovery techniques — Peter Zaitsev (MySQL Performance Blog), Wed 3:05pm
• …

This post is for people who are trying to roll back unwanted modifications to their MySQL database.

You cannot use the binary logs to undo unwanted changes to your data. The binary logs are for redoing statements, not undoing them. If you have a backup, you may be able to restore the backup and [...]

My MythTV store lives on an LVM volume that is spread over 2 disks, one of them is an external USB disk. So the cleaninglady seems to have touched a cable and after coming back from holiday I had a read-only filesystem that afer a remount had about 350Gb in lost+found with irrelevant filenames.

1. total 337407844
2. drwx------ 2 tv tv 4096 Dec 17 22:47 .
3. drwxrwxrwx 15 tv tv 4096 Dec 17 22:44 ..
4. -rw-r--r-- 1 root root 423343556 Dec 14 07:10 I303109.RCN
5. -rw-r--r-- 1 root root 2990538924 Dec 13 19:05 I303107.RCN
6. -rw-r--r-- 1 root root 1023691768 Dec 13 08:10 I319494.RCN
7. -rw-r--r-- 1 root root 1023622348 Dec 13 07:45 I327684.RCN
8. -rw-r--r-- 1 root root 423735892 Dec 13 07:10 I327682.RCN
9. -rw-r--r-- 1 root root 466749476 Dec 12 15:43 I135169.RCN
10. -rw-r--r-- 1 root root 1023314212 Dec 12 07:45 I098309.RCN
11. -rw-r--r-- 1 root root …

Talking with users at OSCON 2008, the biggest fear with new features was table corruption with partitioning. Until recently, this fear was substantiated by bug#20129, which reported inability of repairing corrupted partitions. The initial "fix" for this bug was to disable the offending commands.
After a while, especially when the Support department complained loudly, the bug was reopen and a proper fix implemented. Now you can maintain a partition using ALTER TABLE ... CHECK/ANALYZE/OPTIMIZE/REPAIR PARTITION.
I did some experiment, using MySQL sandbox and the employees test database, which supports partitioning.
You need to download the latest MySQL 5.1 release (5.1.28), where the patch is available.
To load the employees database …

• Suicide
  • having no backups
  • depending on slaves for backup
  • keeping backups on same SAN
  • having a single DBA - Frank didn't like this one at all
  • not keeping binlogs
• Restoring from backup
  • how much time?
  • uncompressed backup ready to mount?
  • separate network for recovery?
• In Fotolog, 1TB of data was severely hit.
  • first problem: backup was highly compressed (tar.gz)
  • uncompressing took hours
  • so keep uncompressed backups (at least last N days)
  • it should be mountable, rather than transferable
• Frank going over recovery modes at …

Even though I didn’t go to MySQL conf this year (really sad about this), this week is gonna be most active in the community so I decided to do some community stuff too Today I’ve released version 0.3 of our innodb recovery toolkit. Now it became much faster, stable and accurate. At this moment it is possible to recover almost any table from corrupted/deleted tablespace without so much effort as it was before. Here is a short changes list (since 0.1 announced here):

• More MySQL data types added: DECIMAL (both old and new), DATE, TIME
• CHAR data type handling improved in table definitions generator
• Indexes filtering added to page_parser
• 64-bit stat() support added to all tools
• Linux has no isnumber() function so we define our own …

I’m returned from my 1-week vacation today and want to say - I’ve never been so productive as I was there Blue ocean, hot sun and white sand really helped me to finish my work on the first release of one really awesome project.

Today I’m proud to announce our first public release of the Data Recovery Toolkit for InnoDB - set of tools for checking InnoDB tablespaces and recovering data from damaged tablespaces or from dropped/truncated InnoDB tables.

This release already has a pretty decent set of features:

• Supports both REDUNDANT (pre mysql 5.0) and COMPACT (mysql 5.0+) versions of tablespaces
• Works with single tablespaces and file-per-table tablespaces
• Able to recover data even when processed InnoDB page has been reassigned …

Thinking I had something new, I wrote this article about recovering deleted files. However, it turns out Frits Hoogland had already blogged about recovery of deleted files on linux, as Frits pointed out in a comment on my blog, where he also mentioned a metalink note on this matter. The note ID is: 444749.1 “Retrieve deleted [...]

So you have accidentally removed a datafile from your production database? First thing, DON’T PANIC! There’s an easy way to recover deleted datafiles, for as long as your database remains up. The procedure below works on linux, however this method conceivably can work for other platforms. This procedure will even work if your database is [...]

Progress on High Performance MySQL, Second Edition is coming along nicely. You have probably noticed the lack of epic multi-part articles on this blog lately -- that's because I'm spending most of my spare time on the book. At this point, we have significant work done on some of the hardest chapters, like Schema Optimization and Query Optimization. I've been deep in the guts of those hard optimization chapters for a while now, so I decided to venture into lighter territory: Backup and Recovery, which is one of the few chapters we planned to "revise and expand" from the first edition, rather than completely writing from scratch. I'd love to hear your thoughts and wishes -- click through to the full article for more details on the chapter and how it's shaping up.