Skip to main content

Leave that poor ibdata1 file alone!

cPanelRyanR
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.

Comments

6 comments

Date Votes
  • LostNerd
    Best way to describe the ibdata1 ever! *Looks around the office to find people staring at me from the laughing*
    0
  • feldon27
    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:
      ]
    • Backup all the innoDB tables to SQL backup files, delete the tables, delete the ibdata1 file, restart MySQL, and then restore them.
    • Attempt one of the hairy tutorials out there that aren't guaranteed to work.
    • 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.
    0
  • cPanelRyanR
    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:
      ]
    • Backup all the innoDB tables to SQL backup files, delete the tables, delete the ibdata1 file, restart MySQL, and then restore them.
    • Attempt one of the hairy tutorials out there that aren't guaranteed to work.
    • 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.

    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.
    0
  • vanessa
    I wrote a tool a while back to help with converting to file per table, and/or fixing issues caused by people messing with (but not deleting!) ibdata1:
    0
  • feldon27
    I wrote a tool a while back to help with converting to file per table, and/or fixing issues caused by people messing with (but not deleting!) ibdata1:
    0
  • cPanelMichael
    Guys, What will be the next step we need to do if the innodb force recovery is not working in a MySQL server?

    Hello :) Please review the steps listed at:
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post