MySQL 5.6 InnoDB get's currupted after crash ... Backup of the Databases

Discussion in 'General Development (no feature request here!)' started by pur_berger, January 30, 2015.

  1. pur_berger
    • Super User

    pur_berger Super User

    Joined:
    May 14, 2007
    Messages:
    1,679
    Likes Received:
    183
    Gender:
    Male
    Location:
    Vienna
    Ratings:
    +270 / 1
    Home Country:
    Austria Austria
    Show System Specs
    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.


     

    Attached Files:

    • MySQL_Backup.zip
      File size:
      380.3 KB
      Uploaded:
      January 30, 2015
      Views:
      111
    • SQL_Repair.zip
      File size:
      471 bytes
      Uploaded:
      January 30, 2015
      Views:
      111
  2. Google AdSense Guest Advertisement



    to hide all adverts.
  3. Vasilich
    • Team MediaPortal

    Vasilich Test Group

    Joined:
    August 30, 2009
    Messages:
    3,392
    Likes Received:
    608
    Gender:
    Male
    Occupation:
    Developer
    Location:
    Germany, Mayence
    Ratings:
    +1,173 / 2
    Home Country:
    Russian Federation Russian Federation
    Show System Specs
    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?
     
  4. pur_berger
    • Super User

    pur_berger Super User

    Joined:
    May 14, 2007
    Messages:
    1,679
    Likes Received:
    183
    Gender:
    Male
    Location:
    Vienna
    Ratings:
    +270 / 1
    Home Country:
    Austria Austria
    Show System Specs
    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 :)
     

    Attached Files:

    • MediaCenter.txt
      File size:
      245.8 KB
      Uploaded:
      January 30, 2015
      Views:
      208
Loading...

Users Viewing Thread (Users: 0, Guests: 0)

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice
  • About The Project

    The vision of the MediaPortal project is to create a free open source media centre application, which supports all advanced media centre functions, and is accessible to all Windows users.

    In reaching this goal we are working every day to make sure our software is one of the best.

             

  • Support MediaPortal!

    The team works very hard to make sure the community is running the best HTPC-software. We give away MediaPortal for free but hosting and software is not for us.

    Care to support our work with a few bucks? We'd really appreciate it!