Khanderao on Emerging And Integration Technologies

Thursday, October 04, 2012

Reclaiming Space from Deleted Big Tables from MySQL

So, in my earlier post, I mentioned about a need of dynamically resizing (increasing) EBS volume on EC2. Here is how I landed in the situation. In the prototype, my database grew very high and I could not reclaim the innodb space of mysql even after dropping large tables or even database. The ibdata1 seems to be greedy and never gives up. And there must be a good technical reason why mysql does not support an utility to release unused space.

Any how, here are the steps for reclaiming the space. Disclaimer: As you know I am not a DBA but I have to do what I have to do:

1. Take a sqldump of entire db 2

2. Shutdown mysql

3. delete (filesystem) ibdata1, ib_logfile0 and 1

4. Edit my.cnf (/etc/my.cnf) : add: innodb_file_per_table
    With this param, table data would be in separate files and only metadata will reside in ibdata1

5. Start mysqld

6. Reload the data dump.


Add to Technorati Favorites

Save This Page on


Post a Comment

<< Home