• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1524
  • Last Modified:

MySql Innodb corruption recovery

Hi,

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

Thanks
0
Paul197466
Asked:
Paul197466
  • 5
  • 5
1 Solution
 
Paul197466Author Commented:
OK, some more information

I have restored database dump from last night and the table dump from the DB as it stands.  I have then run a utility to compare all table contnets.

Good news is the difference fit with the changes made today so I believe I can trust the table dump from the DB as it is.

So my question boils down to: How can I get the DB back to a safe state given that something in the Innodb structure must be corrupt, perhaps just an index page though

Do I have to uninstall MySql and then re-install in order to create a fresh (empty) set of Innodb files?
0
 
Titan522Commented:
You shoudn't. The inndo files are the data files not the binary program. You should be able to drop the table, create the table and import the data
0
 
Paul197466Author Commented:
I realise it's not the program but how else does one creat a complete new set of Innodb files and ideally a new mysql db too
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Titan522Commented:
http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html

The link will give you a good idea. If you have set innodb_file_per_table each table is getting its own file. In that case you can drop the table and delete the file. If it isn't set multi tables share the same datafile. In that case you have to restore all the tables in that datafile.
0
 
Paul197466Author Commented:
OK, that's useful thanks.

Each table has it's own .frm file and the database's directory but the Innodb files are in the root data directory and they are common to all datbases

SO how do I recreate those files without a re-install?
0
 
Titan522Commented:
Add innodb_file_per_table to my.cnf and restart mysql. Create your new tables. Instead of sharing files each table will get their own file. After the tables are created insert the data.

You can create temp_table insert from existing table_A drop table_A re-create table_A and insert from temp_table into table_A now table_A will have its own file
0
 
Paul197466Author Commented:
At the moment, all databases and all tables share the same file.

Are you saying there will be separate files for each table in their own database directories?

Will the existing innodb files get trashed or will any corruption in those cause corruption in the new table specific files?
0
 
Titan522Commented:
Yes, there will be seperate datafile for each table in the same directory. If this problem happens again, at most one table can be corrupted. The existing files will not be deleted because they will still be need by other tables sharing the file
0
 
Paul197466Author Commented:
Sorry, we are going round in circles a bit here.

I am not confident that the current Innodb fiels are not corrupted

I want to delte them and start again

I suggested a complete re-install to start with fresh Innodb files but you said that was not necessary

OK, but how can I force MySql to start from a clean set of Innodb files, even if it means deleting all databases to do this
0
 
Titan522Commented:
log into mysql drop database <database name> check to make sure the files are removed. Create a new database. New database with new innodb files. Since many tables are sharing the same innodb file there is no way to start refresh with dropping and creating a new database.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now