Reduce InnoDB Hard Drive Usage: ibdata1 ib_log

InnoDB databases in MySQL are stored in one huge file by default. This file does not release used space when you delete tables. If you are developing a database and make frequent changes to the structure, you will quickly notice that the size of this file is much larger than all of your databases combined. The file is called ibdata1.

So what can you do about it? MySQL allows you to store InnoDB databases on a file-per-table basis. Here's how you do it:

1. dump all databases, sprocs, triggers, etc (you need to restore them after)
2. DROP all databases (except phpmyadmin or other system DBs)
3. /etc/init.d/mysql stop (stop MySQL server)
4. nano /etc/mysql/my.cnf (edit the MySQL configuration file, add this line)
   
   [mysqld]
   innodb_file_per_table=1
   
5. rm /var/lib/mysql/ibdata1 (delete ibdata1 and ib_log files)
6. /etc/init.d/mysql/start (Start MySQL server)
7. restore dbs from dump (Now all databases will be split into many files)

The difference is that when you drop a table, the corresponding file is deleted and your OS can reclaim that hard drive space.