mysql: How to free space (innodb)

Question

I have a mysql DB configured with the innoDB storage engine. I dropped the database (drop database <dbname>) but the disk space hasn't be liberated.

At /var/lib/mysql there are some "big" files called ib_logfile0, ib_logfile1 and ibdata1 where the last one is really big. I pressume that these files are in fact the original DB.

How can I know if I can delete them. Or a better question is how to drop a database so that the disk space is reclaimed.

Clarification
I want to drop the complete database and free the disk space. I don't want to perform any restore.

Thanks in advance.

Answer 1

Unfortunately there is no way to shrink these files once the space has been allocated. MySQL will however re-use the space when new data is added, but the file won't shrink.

To shrink the size you will need to dump the databases and then restore them again.

You can also do the following:

Set innodb_file_per_table in your config file, which will create a separate file per table. That will then allow you to delete these files once a database is dropped and also allow you to use the OPTIMIZE TABLE command to rebuild ibd files if you shrink the data in a particular table.