update/additional:
how do you recover stored procedures? I get an error because they access corrupt tables
It shouldn't be because the procedures access corrupt tables, because stored procedures and functions aren't validated against their referenced tables in the way views are (drop a table that a view references, and then SHOW CREATE VIEW
doesn't work -- the same isn't the case with procs or functions)... MySQL doesn't care whether tables referenced by stored procedures and functions even exist (except at runtime)... however, unlike view definitions, which are stored in individual files, the definitions of procedures and functions are stored in the proc
table in the mysql
schema, which is a MyISAM table... so I would expect that you'd see error messages referencing that table and CHECK TABLE mysql.proc;
and REPAIR TABLE mysql.proc;
might get you going again. Note that the version of MySQL Server that you're using for your recovery needs to be the same release series (e.g. 5.5) or the table definition of mysql.proc
will be wrong... that shouldn't be the issue here, since if I read the question correctly, the crashed server and the recovery server are both 5.5.31.
If you can SELECT * FROM mysql.proc
then you can retrieve the definitions that way if for some reason the SHOW CREATE PROCEDURE
statement doesn't work.
And what options for a (partial) rescue are there for the tables that yielded an error in the dumps?
It depends on the nature and scope of the errors encountered. One option, discussed here, involves repeated INSERT ... SELECT ... LIMIT ... OFFSET
, extracting blocks of rows from readable pages into a MyISAM table (because you can't write to an InnoDB table while innodb_force_recovery
is on). There's also the Percona Data Recovery Tool for InnoDB, though I have never had occasion to use it.