mysql: How to free space

tags:

mysql

views:

352

answers:

+2 Q:

mysql: How to free space

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.

One solution is to dump and then restore.

cherouvim 2009-05-27 08:51:22

+2 A:

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.

Andre Miller 2009-05-27 09:01:07

Thanks for you answer. BTW, which are the drawbacks of creating one separate file per table (if any)? Do I loose performance?

Luixv 2009-05-27 09:23:00

I cannot think of a drawback to using the innodb_file_per_table option off the top of my head, other than it will create many more files which might make management of them slightly more difficult.But using the option certainly has a few benefits, such as less fragmentation, ability to shrink individual table files and if you have a filesystem with a 2GB limit (very rare, these days) the limit now applies on a per table basis instead of to the whole thing.

Andre Miller 2009-05-27 09:52:39

Thanks for you answer. I set you answer as 'accepted'

Luixv 2009-05-27 10:56:10

ansaurus

tags:

views:

answers:

mysql: How to free space

related questions