The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Leave that poor ibdata1 file alone!

Discussion in 'Database Discussions' started by cPanelRyanR, Jun 15, 2015.

Tags:
  1. cPanelRyanR

    cPanelRyanR Member
    Staff Member

    Joined:
    Jul 17, 2014
    Messages:
    9
    Likes Received:
    9
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    Thinking about deleting that ibdata1 file because InnoDB is crashing MySQL? Don't.

    Sure, it may seem like it crashed MySQL, and has been nothing but trouble since it came into your life, but trust me, you're going to want to give it a chance to explain itself.

    The problem is, ibdata1 has the only key to your file cabinet. If you give ibdata1 the boot, it's going to take that key and swallow it (kind of a jerk move, I know). I mean, sure, you'll still know some information about what was on those files - enough to say "well I had this file.. and this file.. and those files looked kind of like this.." - but you're not going to have any of the paperwork to back it up. That makes them effectively useless.

    So, if you do decide to kick ibdata1 out, you certainly can choose to do so, but just be aware that ibdata1 is a little crazy. It's done some bad things, and it isn't afraid to go back to prison. You are going to have to buy a new file cabinet, and write new files from scratch, unless you made copies of those files at some point and put them somewhere else, in which case you're golden. Afterwards, though, you'll probably want to lay low for a while until the whole thing blows over, and avoid open areas or standing by exposed windows for any extended amount of time. Eventually, ibdata1 will probably get over it. Probably.


    tl;dr - Removing the ibdata1 file is like removing all of your InnoDB-stored data. It will seem like it "fixed" MySQL, but that's only because it's literally loading none of your InnoDB data. You'll have a much higher chance of success if you try to troubleshoot the original crash/corruption issue.
     
    madmanmachines, LostNerd and Infopro like this.
  2. LostNerd

    LostNerd Well-Known Member

    Joined:
    Mar 12, 2014
    Messages:
    258
    Likes Received:
    11
    Trophy Points:
    18
    Location:
    Hastings, East Sussex, UK
    cPanel Access Level:
    Root Administrator
    Twitter:
    Best way to describe the ibdata1 ever!

    *Looks around the office to find people staring at me from the laughing*
     
  3. feldon27

    feldon27 Well-Known Member

    Joined:
    Mar 12, 2003
    Messages:
    111
    Likes Received:
    11
    Trophy Points:
    18
    Location:
    Houston, TX
    Another thing that tempts people to delete ibdata1 is that this file grows to the maximum size of all your server's tables that use innoDB and then will NEVER shrink. Not ever. You could have 31GB of innoDB tables, delete 20GB of them and the ibdata1 file will stay at 31GB even after reboots.

    Unless you were prescient enough to change the setting manually in my.cnf BEFORE creating a bunch of large innoDB tables, or you have been running MySQL 5.6 for a while, or you have already migrated to MariaDB, there are only a few solutions:
    1. Backup all the innoDB tables to SQL backup files, delete the tables, delete the ibdata1 file, restart MySQL, and then restore them.
    2. Attempt one of the hairy tutorials out there that aren't guaranteed to work.
    3. Migrate to a new server.
    I'm already looking at #3 because I'm stuck on CentOS 5 and want to migrate to 6. Then I get ~15GB of disk space back and I should get better performance with MariaDB anyway.
     
    #3 feldon27, Jun 28, 2015
    Last edited: Jun 28, 2015
  4. cPanelRyanR

    cPanelRyanR Member
    Staff Member

    Joined:
    Jul 17, 2014
    Messages:
    9
    Likes Received:
    9
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    The good thing there is that a cPanel installation defaults to include innodb_file_per_table=1 in the my.cnf for quite some time now, to avoid issues like what you've described there. Not only that, but it's rarely a good idea to keep all your eggs in one basket. The list of reasons not to use file_per_table mode is very small. That said, if you are stuck with the situation where you have created many tables with file_per_table disabled, you're still in a bad situation if your ibdata1 file is growing large.

    That being the case, your #1 option is absolutely the best approach there. Dump 'em, drop 'em, restore 'em, and you should be good to go. For you, it sounds like the situation is a little more complicated, unfortunately. You said you've already gone through all the options - did you run into trouble when you tried to dump and restore? Let me know if you need help figuring out what's going on.
     
  5. vanessa

    vanessa Well-Known Member
    PartnerNOC

    Joined:
    Sep 26, 2006
    Messages:
    817
    Likes Received:
    22
    Trophy Points:
    18
    Location:
    Virginia Beach, VA
    cPanel Access Level:
    DataCenter Provider
    LostNerd likes this.
  6. feldon27

    feldon27 Well-Known Member

    Joined:
    Mar 12, 2003
    Messages:
    111
    Likes Received:
    11
    Trophy Points:
    18
    Location:
    Houston, TX
    Thank you for this. If I had the disk space, this is what I'd do! :)
     
  7. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator

Share This Page