MySQL 5.6 InnoDB get's currupted after crash ... Backup of the Databases (1 Viewer)

[pur_berger]

Since the switch of MySQL to 5.6 and the usage of InnoDB I'm experiancing that after a crash or forcefully restart without closing the Server properly you have a rather good chace to "kill" some of the tables in your db's. Most of teh time aRapir will help - anyway - with InnoDB it seems to be a little tricky as the standard repair task does not doe certain tasks as it did with myisam.

Anyway - I thought if someone has the same issues I post the solution for me - repair and backup.

Repair Task:

cd c:\Program Files\MySQL\MySQL Server 5.6\bin
mysqlcheck.exe -u root -pMediaPortal --auto-repair --all-databases

Backup:

For the backup I found a good working batchfile originating here: RED OLIVE INC. - www.redolive.com which only needed some small modifications (the get date was not working so I have included another routine). Basically what it does is to dump your databases to a defined location with date and time and compress them to a zipfile. The configuration steps are explained in the batchfile.

 

[Vasilich]

autorepair doesn't do anything for InnoDB. Are you sure you have all your tables in InnoDB? or something went wrong and you have now DB with mixet table types?

 

[pur_berger]

No - they are all InnoDB - well - it does something (if teh tables are locked for example) but you are right - compared to myisam it does - nothing ....

Most of the time it's that the frm and the ibd are both present but MySQL complains that there is no database (last time after a forcfully restart with progams) so MySQL would even start up so you either need to change teh startup paramter or remove the false file and start MySQL afterwards and move it back and chek if the repair task is telling you something about teh issue (at least this is showing up when you run it)

If there would be any automated repair I would be happy but couldn't find anything.

I attach the err file - so you see what I mean