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.

ibdata1

Discussion in 'Database Discussions' started by NNNils, Nov 30, 2007.

  1. NNNils

    NNNils Well-Known Member

    Joined:
    Sep 17, 2002
    Messages:
    580
    Likes Received:
    0
    Trophy Points:
    16
    What exactly is the file /var/lib/mysql/ibdata1 used for?

    It is getting quite big in soms of our servers.
     
  2. Danny_T

    Danny_T Well-Known Member

    Joined:
    Jul 19, 2005
    Messages:
    181
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Netherlands
    MySQL databases of InnoDB format.
     
  3. NNNils

    NNNils Well-Known Member

    Joined:
    Sep 17, 2002
    Messages:
    580
    Likes Received:
    0
    Trophy Points:
    16
    Is innodb the default format for mysql databases?
     
  4. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,461
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    The default table type is MyISAM. One must manually specify INNODB when creating a table to user that table type. By default, MySQL stores all INNODB tables in a once, continuously growing, file in /var/lib/mysql. This behavior can be altered by setting:

    Code:
    [mysqld]
    innodb_file_per_table
    
    in /etc/my.cnf and restarting the MySQL service. This will cause new INNODB tables to be created in the directory of the database. e.g /var/lib/mysql/user_dbname/table_name.ibd rather than the central file. Tables that exist already in the central file must be:

    1. Dumped
    2. Dropped
    3. Restored

    in order to be moved to the per-database file.

    You can learn more here:

    http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html
     
  5. jols

    jols Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,111
    Likes Received:
    2
    Trophy Points:
    38

    So what you are saying is that we have to dump, drop and restore every database on the server to get around having giant ibdata1 files in the very limited /var partition?

    Isn't there some other way to shrink just this one file without having to upset the entire applecart?

    Barring that, could we just move ibdata1 to the home directory and then symlink from /var/lib/mysql ???
     
Loading...

Share This Page