Logging in to my Webmin control panel, I noticed that virtually all of my disk space is full. I searched for the ten largest files/ directories on my system and found that a file called ibdata1 is taking up around 94GB of space. It resides in my /var/lib/mysql directory.
What does ibdata1 do? Am I safe to remove it? My assumption is that it's a dump of some kind, but that's just a wild guess.
Best Answer
The file
ibdata1
is the system tablespace for the InnoDB infrastructure.It contains several classes for information vital for InnoDB
Please note ibdata1's place in the InnoDB Universe (on Right Side)
You can separate Data and Index Pages from
ibdata1
by enablinginnodb_file_per_table
. This will cause any newly created InnoDB table to store data and index pages in an external.ibd
file.Example
/var/lib/mysql
CREATE TABLE mydb.mytable (...) ENGINE=InnoDB;
, creates/var/lib/mysql/mydb/mytable.frm
innodb_file_per_table
enabled, Data/Index Pages Stored in/var/lib/mysql/mydb/mytable.ibd
innodb_file_per_table
disabled, Data/Index Pages Stored inibdata1
No matter where the InnoDB table is stored, InnoDB's functionality requires looking for table metadata and storing and retrieving MVCC info to support ACID compliance and Transaction Isolation.
Here are my past articles on separating table data and indexes from ibdata1
Oct 29, 2010
: My Original Post in StackOverflowNov 26, 2011
: ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is fullFeb 03, 2012
: Scheduled optimization of tables in MySQL InnoDBMar 25, 2012
: Why does InnoDB store all databases in one file?Apr 01, 2012
: Is innodb_file_per_table advisable?WHAT TO DO NEXT
You can continue having
ibdata1
stored everything, but that makes doing LVM snapshots real drudgery (my personal opinion).You need to use my StackOverflow post and shrink that file permanently.
Please run this query:
This will tell how much wasted space can be reclaimed after applying the InnoDB Cleanup.