ibdata1

[NNNils]

What exactly is the file /var/lib/mysql/ibdata1 used for?

It is getting quite big in soms of our servers.

[Danny_T]

MySQL databases of InnoDB format.

[NNNils]

Is innodb the default format for mysql databases?

[cpanelkenneth]

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:

[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/...iguration.html

[jols]

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:

[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:

MySQL :: MySQL 5.0 Reference Manual :: 13.2.2 InnoDB Configuration

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 ???