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

pur_berger

Super User
  • Team MediaPortal
  • Super User
  • May 14, 2007
    1,679
    309
    48
    Vienna
    Austria Austria
    Country flag
    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.
     

    Attachments

    Vasilich

    Test Group
  • Team MediaPortal
  • August 30, 2009
    3,393
    1,170
    Germany, Mayence
    Russian Federation Russian Federation
    Country flag
    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

    Super User
  • Team MediaPortal
  • Super User
  • May 14, 2007
    1,679
    309
    48
    Vienna
    Austria Austria
    Country flag
    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 :)
     

    Attachments

    Users Who Are Viewing This Thread (Users: 0, Guests: 1)

    Top Bottom