Makerere University

Enter a keyword or two into the search box above and click search..

MySQL 5.0: Recovering Crashed/Corrupted InnoDB Database

You are here

If you, by any chance, forgot to create backup, you should do more work as the following:

  1. Edit my.cnf (usually located in /etc/my.cnf) and add this line into [mysqld] section
  2. Restart mysql
    (e.g: /etc/init.d/mysqld restart)
  3. Your MySQL log should tell you which table is corrupt. You can dump the table using “SELECT … INTO OUTFILE …” from mysql console or using tool like mysqldump
    • Example using MySQL query (you also need to dump the schema later)
      mysql> SELECT * FROM corrupted_table INTO OUTFILE ‘/path/to/file’
    • Example using mysqldump
      root# mysqldump –opt -u uname -p dbname corrupted_tablename > /path/to/out.sql
  4. Go back to my.cnf and comment the line you wrote from step #1
  5. Go intoMySQL console and drop the corrupted table
    mysql> DROP TABLE corrupted_tablename;
  6. Import data dump, for sql dump you can use:
    root# mysql -u uname -p < /path/to/out.sql
  7. Go back into mysql console, check and optimize all tables
  8. Done