Innodb Recovery

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.

2 thoughts on “Innodb Recovery

  1. Mansi

    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.

Leave a Reply

Your email address will not be published.