X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> MySQL Innodb Corruption Recovery    
post Apr 24 2015, 08:49 AM

Posts: 1
Joined: 24-April 15


I have a MySQL database, just containing Innodb tables, that seems to have corrupted itself.

It replicates to another server and it has corrupted that one too!

The symprom is that I can restart the server but as soon as I try to connect to it, it crashes.

The log file talks about a possible index page corruption to a specific table and that fits in terms of the table being accessed when the problem occurred.

Following the Innodb recovery guidelines in the MySQL manual, I have set the innodb_force_recovery option to 6 which lets me restart the database and dump the tables.

Anything less than 6 and it will not stay up.

My questions are:
How do I know the extent of the innodb corruption?
Is the only safe option to recreate the innodb files from scratch (I assume via a re-install) or is there a smarter way to do this
Can I trust the table dumps or should I go back to the most recent backup (also a table dump) and accept the data loss
If it is an index page corruption, can I rebuild the primary index of the affected table and then restart the DB without the innodb_force_recovery option

Go to the top of the page
post Apr 25 2015, 03:36 PM

Posts: 1
Joined: 25-April 15

See below short guide for repairing MySQL databases after corruption…

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
You must supply the password when asked,

Or you can run this one but it's not recommended because the password is written in clear text:

mysqlcheck -u root --password=THEPASSWORD --auto-repair --check --optimize --all-databases
Go to the top of the page
post Jan 29 2020, 12:25 AM

Posts: 10
Joined: 25-August 15

To estimate how badly a tablespace is corrupt innochecksum might help. Note however it's often misleading. innochecksum checks individual pages, but not the tablespace structure.

The best way to recover data from a corrupted InnoDB database is by using the latest backups and restoring it from that backup. This method is the safest and most reliable method to recover data from a corrupted database.

Another method that can be used to check and repair MySQL tables is using mysqlcheck. You can learn more about using this method to recover data from https://www.thegeekstuff.com/2011/12/mysqlcheck/ . This method sometimes doesn’t work in case of high corruption so in those scenarios you can make use of a DIY tool that helps in repairing InnoDB and MyISAM tables of MySQL database and also recovers keys, tables, data types, views and triggers. Refer to this page to learn more: https://forums.mysql.com/read.php?20,674682,674730
Go to the top of the page
post Jan 29 2020, 03:29 AM

Posts: 697
Joined: 26-May 15
From: The middle of Germany

I have a MySQL database, just containing Innodb tables, that seems to have corrupted itself.

I used to work quite a lot with MySQL including InnoDb tables. The only case of corruption I encountered in many years was caused by a faulty hard disk. So, keep in mind to check for this.

A professional Access developer tool: Find and Replace for Access and VBA
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    30th May 2020 - 10:02 AM