MySQL – Unable to Load Dump File

In general case once InnoDB tablespace is corrupt you need to re-create tablespaces from scratch. This is the safest way especially if you're not familiar with InnoDB files/format.

In some cases InnoDB writes to the error log file information about corrupted page number and index it belongs to. For example,

InnoDB: Page may be an index page where index id is 2575
InnoDB: (index "idx_actor_last_name" of table "sakila"."actor")
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4.

InnoDB crashed at that page but it doesn't mean other pages are OK. To estimate how badly a tablespace is corrupt innochecksum might help. Note however it's often misleading. innochecksum checks individual pages, but not the tablespace structure. For example, all-zero page is valid in innochecksum's opinion while the page should have contained some data.

To check that all tablespaces are good you can read data from all indexes (PRIMARY and all secondary) of all tables. For example, to check sakila.actor you need two selects:

SELECT * FROM actor FORCE INDEX ('PRIMARY')
SELECT * FROM actor FORCE INDEX ('idx_actor_last_name')

If you suspect that only a secondary index is corrupt you may drop and re-create it. I describe this case in https://twindb.com/repair-corrupted-innodb-table-with-corruption-in-secondary-index/ .

If PRIMARY index is corrupt you may want to dump the table drop it and reload again. I.e. you don't reload whole database, just one table.

Perhaps you should dump the users and load the users into the VM

Here is my post : Export all MySQL users

After running the code in it and saving it to a file called MyDatabaseUSers.sql, you log into mysql on the VM and run this:

mysql> source MyDatabaseUSers.sql

This will create all the users with the same grants and passwords. Don't worry: Passwords are encrypted.

After importing the users, you should be able to reload the mysqldump.

GIVE IT A TRY !!!

NOTE: If you loaded all your data successfully, the error might have occurred loading a MySQL object that has a DEFINER associated with it

• stored procedures
• triggers
• views
• events.

Do the following:

cat newdump.sql | grep -A 2 -n "dc_reporter"

This will show you what might not have loaded. Your data may all be there.

You could try dumping your stored procedures and triggers and reloading them instead of reloading everything. Please read my old post Can mysqldump dump triggers and procedures?, go dump your triggers and stored procedures, and reload them into the VM. If you have views to import, please read my old post Modify DEFINER on Many Views and use the code to mysqldump just the views.