One very interesting thing I noticed with MySQL was that if you delete a database, ibdata file doesn’t shrink by that much space to minimize disk usage. I deleted the database and checked usage of /usr/local/mysql/var folder and noticed that ibdata file is still the same size. So the problem I face now is, how do I claim back this space?

After searching for a bit on google about this problem, apparently only way you can do that is by exporting your mysql databases, delete ibdata1 file, import databases. This creates new ibdata file with correct space usage. Atleast there is a way to get around this issue. But honestly, too much pain on production boxes where we might be trying to remove old databases to reclaim some of the hard drive space.

An preventive measure one can use is to use option: innodb_file_per_table (‘put innodb_file_per_table’ in your my.cnf file under [mysqld] section). This will create individual files for tables under database directory. So now when I delete the database, all the space is returned since the directory is now deleted along with database along with all the tables inside the directory. In my test after you put option innodb_file_per_table your my.cnf, you will have to still do export/import to be able to minimize disk usage and have the ability to delete database at your leisure without worrying about reclaiming the disk space. Here are the steps I took. DISCLAIMER: Please make backup of your data and use following steps at your own risk. Doing it on test server is HIGHLY recommended. Please don’t come back and tell me that you lost your data because you followed my steps. They work for me and they may not work for you!

That said, here are the steps:

1. Add innodb_file_per_table in your my.cnf file under [mysqld] section
2. run following commands at the prompt. Your path to binaries might be different.

#lets make a backup of current database. -p is used if there is pw set
mysqldump -R -q -p -v --all-databases > /tmp/all.sql

#stop mysql so we can move all the files from the dir
/etc/init.d/mysql stop

#move all the files
mv -r /var/lib/mysql/*

#install default dbs
mysql_install_db

#change ownership so mysql user can read/write to/from files
chown -R mysql.mysql /var/lib/mysql/

#start mysql so we can import our dump
/etc/init.d/mysql start #note there is no -p since defaults don't have mysql pw set

mysql < /tmp/all.sql
/etc/init.d/mysql restart

This should be all you need to do. At this point when you remove a database, it will delete the directory of the db and all the data contained within which in turn will give you your disk space back.

REMEMBER: Backup your data and be smart about using code found on internet. If you don’t know what you are doing, hire a consultant who does.

Worked pretty awesomely! After restoring the data, I had some issues with authenticating to the server, so I logged in to MySQL and flushed the privileges, then restarted MySQL:

$ mysql -u root -p
Enter password:
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
$ /etc/init.d/mysql restart