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.

The dreaded InnoDB "ibdata1" slush file

Discussion in 'Database Discussions' started by feldon27, Jan 22, 2015.

  1. feldon27

    feldon27 Well-Known Member

    Joined:
    Mar 12, 2003
    Messages:
    111
    Likes Received:
    11
    Trophy Points:
    18
    Location:
    Houston, TX
    So MySQL stores all innoDB tables in a single slush file called "ibdata1" which NEVER shrinks despite the optimization or even deletion of tables.

    In 2010, Tristan posted a thread "innodb_file_per_table - Converting to Per Table Data for InnoDB" advising cPanel users to change a setting in their MySQL config to store innodb tables as individual files. Without this setting this file just perpetually grows with ABSOLUTELY no way to shrink it.

    After one follows the ALTER TABLE steps as described by Tristan, the ibdata1 file does not go away, and indeed you've now duplicated all the innodb data on your server, eating up twice as much space that can never be cleaned up. Thus far, no thread on these forums has ever addressed the question of what server operators with existing databases are supposed to do to reclaim this space. All threads on the topic warn against deleting ibdata1 and/or advise hiring a MySQL expert.

    Can we please have some instructions on how to safely migrate away from using the ibdata1 file?

    Also can we please see this setting enabled by default in the MySQL config file? Having all innodb tables in a single ever-growing file seems an particularly dangerous setting on a shared host situation and yet it's been this way on cPanel for at least 7 years.

    If I do mysqldump of every innodb table on the server, delete the ibdata1 file, and then restore those tables, will that catch everything? Can I then safely delete the ibdata1 file? Are there other important things in the ibdata1 file that will be lost? My ibdata1 file is currently 34GB. Actual useful data in that file is less than 10GB. I have enough space on the server to backup all those tables, but I do not have enough space on the server to keep a full backup of the ibdata1 file AND the extra innodb files.

    Please advise.
     
    #1 feldon27, Jan 22, 2015
    Last edited: Jan 22, 2015
  2. feldon27

    feldon27 Well-Known Member

    Joined:
    Mar 12, 2003
    Messages:
    111
    Likes Received:
    11
    Trophy Points:
    18
    Location:
    Houston, TX
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

    feldon27 Well-Known Member

    Joined:
    Mar 12, 2003
    Messages:
    111
    Likes Received:
    11
    Trophy Points:
    18
    Location:
    Houston, TX
    Thank you for the link!

    As for the request to add a single entry of "innodb_file_per_table=1" to my.cnf, well if it hasn't happened after 7 years, how likely is it to happen now? Also, isn't the MySQL -> MariaDB transition right around the corner? Hopefully this setting is defauled on day 1 with MariaDB and then we don't have to worry about it going forward.

    From what I've read, on a shared server with multiple domains, there is absolutely no advantage to innoDB using a shared file. I only found huge disadvantages. For one thing, if the ibdata1 file is corrupted in a crash, then data recovery of ALL innoDB tables on the entire server is unlikely. That alone is a reason to NEVER leave the server set to use 1 file.
     
    #4 feldon27, Jan 23, 2015
    Last edited: Jan 23, 2015
  5. kernow

    kernow Well-Known Member

    Joined:
    Jul 23, 2004
    Messages:
    865
    Likes Received:
    9
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    innodb_file_per_table is by default ON since Mysql 5.6.6
     
Loading...

Share This Page