Home > Uncategorized > Innodb Recovery

Innodb Recovery

January 15th, 2013 Leave a comment Go to comments

I recently had a database server fail during a large DELETE query, this caused some problems with innodb’s ibdata1. The index of this data file was different to what MySQL expected. As this wasn’t one of our main servers I hadn’t tuned innodb and all the innodb data was in the single ibdata1 file. The only way for me to start MySQL was to add this to my.cnf:

innodb_force_recovery = 4

This forced MySQL to ignore all innodb errors and I used mysqldump to extract all the data from the innodb tables. Innodb tables were found using the following query:

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = ‘innodb’;

I stopped MySQL server again, removed the innodb_force_recovery, deleted the ibdata1 file and tuned innodb. I also made sure I added this to my.cnf:

innodb_file_per_table = 1
innodb_log_files_in_group      = 2

All tables were loaded from the mysqldump backup files and everything is all happy again.

Categories: Uncategorized Tags:
  1. Mansi
    March 5th, 2013 at 03:45 | #1

    Can you tell me in which case we use innodb_force_recovery with other values like 0, 1 etc. And why we mostly use it with value 4.

  2. March 25th, 2013 at 23:15 | #2

    Other values for innodb_force_recovery can be found here: http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html

  1. No trackbacks yet.