How to recreate corrupt innodb tables with innodb_file_per_table?

Question

MySQL refuses to start up and I was only able to start in read-only mode using innodb_force_recovery = 6. Every answer I've found on this topic suggests making a backup, deleting ibdata and ib_logfiles and then letting them get recreated:

• https://serverfault.com/questions/592793/mysql-crashed-and-wont-start-up
• innodb_force_recovery when InnoDB corruption
• https://stackoverflow.com/questions/41997197/mysql-data-recovery-with-innodb

In my case since innodb-file-per-table is enabled, I'm wondering if I also need to delete files like /var/lib/mysql/MY_DATABASE/table.ibd? Also, as redo logging is disabled, there is no logfile to remove.

The specific error I got is this:

[InnoDB] Server was killed when InnoDB redo logging was disabled. Data files could be corrupt. You can try to restart the database with innodb_force_recovery=6

At this point I've been able to start in read-only mode and I've made a backup of all the tables I need. What specifically should I delete? Or is there a better approach to completely rebuilding these tables?

I'm considering simply uninstalling mysql completely and purging the whole directory but looking for feedback here if there is a simpler approach.

Answer 1

The only safe way to use the dump-and-recreate method of restoring corrupt InnoDB tables, is to recreate all InnoDB tables.

Even if you use innodb_file_per_table, the ibdata1 file potentially contains part of any InnoDB table, because it can contain parts of the change buffer. So if you remove the ibdata1, you could (and probably will) corrupt other InnoDB tables.

I'm not certain that disabling the redo log led to your table corruption, but based on the error you show, I infer that it did. Don't disable the redo log and then kill your MySQL Server.

The manual says:

Disabling Redo Logging

This feature is intended only for loading data into a new MySQL instance. Do not disable redo logging on a production system. It is permitted to shutdown and restart the server while redo logging is disabled, but an unexpected server stoppage while redo logging is disabled can cause data loss and instance corruption.

Assuming you were following this advice, you were in the process of loading initial data for your new database instance, so you must have a full backup already. In that case, the cleanest thing to do would be to remove everything in the datadir and start over by initializing, and then re-load the whole backup.

Then before you update any data, you should immediately re-enable the redo log.

• You Old Fool: Wow, that page has my error verbatim. Indeed I was using myloader --disable-redo-log to restore a database as part of a nightly sync to a development server. This has been running for a couple years without issue but looks like the server might have crashed yesterday in the middle of the update. Fortunately there was nothing particularly crucial here but lesson learned! I did end up wiping the data and reinstalling mysql-server which wasn't really a big deal in this case. Thanks for the link and have a great weekend.