If you use Innodb engine for your MySQL database, you may have noticed that the /var/lib/mysql/ibdata1 file is the most important and heavy file of your MySQL server. Day by day, this file will increase and can quickly consume all your hard drive. Reducing its size can be a little bit tricky.
When "innodb _ file _ per _ table" is set to "0" in your my.cnf file, the ibdata1 file normally stores many classes of information, such as table data, table indexes, MVCC (Multiversioning Concurrency Control) data, table metadata, and so on. You can check this if you want a well explained schema.
Many people create multiple ibdata files hoping for better diskspace management and performance. It does not help.
Unfortunately, OPTIMIZE TABLE against an InnoDB table stored in ibdata1 does two things:
- Makes the table's data and indexes contiguous inside ibdata1
- It makes ibdata1 grow because the contiguous data is appended to ibdata1
You can segregate Table Data and Table Indexes from ibdata1 and manage them independently using innodb _ file _ per _ table.
To shrink ibdata1 once and for all, you must do the following:
1) MySQLDump all databases into a SQL text file (call it SQLData.sql)
2) Drop all databases (except mysql schema, phpmyadmin/mysql databases)
3) Stop mysql
4) Add the following lines to your /etc/my.cnf
[mysqld] innodb_file_per_table innodb_flush_method=O_DIRECT innodb_log_file_size=1G innodb_buffer_pool_size=4G
Note : Whatever your set for innodb _ buffer _ pool _ size, make sure innodb _ log _ file _ size is 25% of innodb _ buffer _ pool _ size
5) Delete ibdata1, ib _ logfile0 and ib _ logfile1
At this point, there should only be the mysql schema in /var/lib/mysql
6) Restart mysql
This will recreate ibdata1 at 10MB, ib _ logfile0 and ib _ logfile1 at 1G each
7) Reload SQLData.sql into mysql to restore your data
ibdata1 will grow, but only contain table metadata. Each InnoDB table will exist outside of ibdata1.
Now suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table:
- mytable.frm (Storage Engine Header)
- mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)
ibdata1 will never contain InnoDB data and indexes anymore.
Now, the best part is that with the innodb _ file _ per _ table option in /etc/my.cnf, you can now run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.
Special thanks to RolandoMySQLDBA for his great post on stackexchange.